1

I'm considering moving some of our large log-table to range partitioning for ease of archiving them. They are rarely used for queries, other than support and related stuff. Say my oldest data is from 2013 and I will be using 1 partition per year. What negative effects will it have if I add partitions for a couple of years ahead (say until 2025)? Also, will it be ok to use a timestamp column as a partitioning key, or is there any advantage in using a generated column for the year?

1 Answer 1

1

What negative effects will it have if I add partitions for a couple of years ahead

None. A partition is basically an invisible table, so you'll have a handful of extra rows in various catalog tables and four preallocated extents per empty partition.

will it be ok to use a timestamp column as a partitioning key, or is there any advantage in using a generated column for the year

I don't see any reason to use a generated column. I don't think the optimizer will be able to eliminate partitions defined on year(timestamp_column) given a search predicate on timestamp_column itself, so you'll likely need to modify existing queries to take advantage of partition elimination. Also, if you want partitioned indexes, you'd need to add the partitioning key column to them, and year(timestamp_column) as an index column is useless from pretty much any other aspect.

1
  • Thanks, that was my suspicion. The primary use will be to detach partitions as they grow old. The tables are really only used to investigate bugs. Sep 23, 2021 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.