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?
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.