Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22163

Adding OVERFLOW partition

    XMLWordPrintable

    Details

      Description

      OVERFLOW partitions for RANGE and LIST.
      The difference between DEFAULT (that we have for list) and OVERFLOW
      is that OVERFLOW contain rows that matches other partition keys.

      In other words, when doing partition pruning, we have always to include
      OVERFLOW partition and all searches (select, update and delete) will also
      have to do a lookup in the OVERFLOW partition. Inserts should never go
      into the OVERFLOW partition unless explicitly forced (how?).

      The purpose of the OVERFLOW partition is to allow one to do efficient
      archiving by first writing rows into the OVERFLOW partition and only
      when the OVERFLOW partition gets too big then move data to the relevant
      partition. This is especially important for S3 tables where the cost
      of updating a partitioned tables can be very high.

      For example:

      You store all your archived transactions in a table partitioned per
      month. Each partition is read only to save space (either S3 or packed
      with aria_pack or myisampack).  Each month you get a deliver of
      transactions to be stored. For September you may get something like:

      September: 10M rows
      August:    100,000 rows
      July:      1000 rows
      June:      100 rows
      May:       5 rows

      In all cases you would create a new partition for September and move all
      September rows there.
      Without a scrap partition you would then to unpack all partitions tables
      for May to August, insert the rows and then repack them.

      With a OVERFLOW partition you can either store all renaming rows into
      the OVERFLOW partition, or just move the August rows into August
      partition and store the rest into the OVERFLOW partition.

      You want to store rows into the OVERFLOW partition until there is no more
      rows coming for a month and then you just move that month to it's
      appropriate partition.

      Normally the OVERFLOW partition would not be compressed/packed to make it
      faster to add rows to it.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: