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.
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
Normally the OVERFLOW partition would not be compressed/packed to make it
faster to add rows to it.