SequenceStyleGenerator that supports time partitioning

[beikov]

We recently discovered that it is necessary to list all columns that are part of a partition key in the where clause to achieve good update/delete query performance, because if we don't do this, a database query planner has to look for a row in every partition. This is actually mostly a problem for MySQL/MariaDB and PostgreSQL, as these databases do not support global unique indexes, which means that any unique constraint must contain the partition key columns.

Today I read about how to overcome this problem for timestamp partitioned data in an article. The approach essentially encodes some timestamp parts to the upper bits of a sequence value. Doing this, means that people don't need a dedicated partition column as they can use range partitioning. A nice side effect of this is that this might also speed up primary key selects due to partition pruning.

At the end of the day, I think that this approach could also be implemented by creating partitions based on the current sequence value at the start of every period. Since partitions need to be created anyway and sequence values are given out in monotonic order, this is a good approximation already, so there might not be an actual need for a custom sequence generator at all. AFAIU, this also might only be useful for databases that don't support global unique indexes, although I have no knowledge about how partitioning works in practice with other DBMS.

I just wanted to share this regardless, to let others chime in. So, what do you think about a custom generator that encodes timestamp parts?