Strategy for managing partioned table

Question

I have a table that grows at a rate of about 250 million rows per year. The primary key is a timestamp and two numeric identifiers. The timestamp is not uniformly distributed over year but follows the same time distribution year after year. There are 12 other number columns in the table. The data is read-only after data ingest. The data should be kept perpetually; however, there is some motivation to purge older (perhaps 10 years) data. The table is currently not partitioned and query performance has been rolling off over the years and has become slow enough to negatively affect the work flow.

The queries are usually one of the following (in order of most to least frequent):

1. A narrow time window (a few seconds) with a bounds on the numeric identifiers,
2. A wide time window (at most a day) with a bounds on the numeric identifiers,
3. A wide time window (at most a week) with bounds on one or more of the other columns,
4. One or more years without bounds on the numeric identifiers to generate summary statistics (count, monthly average, etc)--this is done infrequently,
5. Bounds on the two numeric identifiers and/or the other columns with no restriction on the timestamp--very rare.

Looking at the different partitioning options, it appears either a range or an interval approach makes the most sense. My thought is to have each year in a separate tablespace and three ways come to mind on how to manage the partitioning:

1. A range only approach where each year a new tablespace is created the partition definition is changed to add the new range,
2. An interval approach and alter the default tablespace each year, and
3. An interval approach keeping the default tablespace the same (e.g. current) and move partitions out of the current to a previous calendar year tablespace (e.g. ts2023, ts2022, etc).

My priorities are (in order of highest to lowest priority):

1. Query performance,
2. Reliability and speed of data ingest, and
3. Ease of maintenance (though ease probably contributes to data ingest reliability).

Option #3 strikes me as the most straightforward because it can be performed at any time after crossing into a new calendar year and data ingest would not need to be stopped.

I have also thought about subpartitioning on one of the numeric identifiers but I am not sure there is much benefit for 250 million rows per annual partition. Performance was more than fine for most queries when there was less than 1 billion rows. That said, the overhead of subpartitioning appears negligible with a possibility of a modest performance enhancement, so this might fall into a "why not" situation.

Any thoughts or suggestions?

Answer 1

let me guess - harrow and medium wide searches are done with current data.

If so you don't need evenly partition older data you are good with two partitions

• old
• active (current)

Range partitioning by date is your friend. To make maintenance easier you can split it by year making partitions like

• old
• last year
• active (current)

I have also thought about subpartitioning on one of the numeric identifiers but I am not sure there is much benefit for 250 million rows per annual partition

I don't think sub-partitions worth complexity but feel free to test it. You have to test your solution anyways so why not to add more test cases.

My point is - older years could be stored together. Last year stays in it's own partition just to avoid merging it with old stuff.

Extra bonus - data on old partition could be on read-only tablespace and excluded from backup/restore.

Query performance

Think about making IOT based on timestamp. (you are going to reorganize your data anyways)