Improved performance of INSERT into a partitioned table

Detail name	Value
Changelog Number	9451
Type	Improvement
Status	Resolved
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Background

Exasol 6.1 introduced automated table partitioning, where newly inserted data is organized based on dynamically calculated data ranges.
To do so, the engine would allocate one table segment (set of data blocks) per data range, and split that range in two when the segment is fully populated with rows of data.

Problem

There was no limit imposed on the splitting, and every segment would allocate a minimum amount of storage. In combination with large inserts of quasi-random data, this could lead to the effect that many data segments were written concurrently, using up much more RAM than would be required for non-partitioned insert.

End of the story is degraded system performance (especially for the INSERT part) when data does no longer fit in RAM – which is exactly the scenario partitioning was supposed to handle (on the reading part).

Improvement

The number of active data segments (ie. partition ranges) per table is now limited based on system RAM:

• When there are more than 10 of those segments
• and they amount to 10% of available database RAM (default value, can be changed),

the algorithm no longer creates ever-smaller partition ranges but starts merging and re-arranging existing ranges.

Notes:

• The limit is imposed across all data segments that are potentially open for writing, regardless of how many or which values are currently being inserted.
• Re-arranging of data ranges does not modify existing data segments. As such, it should not impact performance of affected statements like delete reorganization does.
• Reaching the segment limit for a table potentially means that larger data ranges are stored together, resulting in more data to be read for specific filters. However, the actual affect will strongly depend on amount of data per active segment and distribution of new data to be inserted.