undefined | Better HN

0 pointstda9y ago0 comments

I wanted to partition a table by the foreign key, as the table receives a few hundred rows per foreign key per hour (it is a timeseries db).

So I figured partitioning the table by foreign key would group all data together in a way that allows for faster access (typical access pattern would be select * where foreign_key = x). However, as the number of keys in the foreign table is unbounded and can be quite large, I wanted to partition the data to a limited number of tables, with

  mod(foreign_key, number_of_partions)

If I understood correctly, check constraints can't operate on a calculated value

0 comments

amitlan9y ago

Yes, it is not possible to optimize (ie, prune useless partitions for quicker access) the query select * from tab where key = x. You'd need actual hash partitioning for that. The mechanism Postgres uses to perform partition-pruning (constraint exclusion) does not work for the hashing case.

j / k navigate · click thread line to collapse

0 pointstda9y ago0 comments

I wanted to partition a table by the foreign key, as the table receives a few hundred rows per foreign key per hour (it is a timeseries db).

  mod(foreign_key, number_of_partions)

If I understood correctly, check constraints can't operate on a calculated value

0 comments

amitlan9y ago

j / k navigate · click thread line to collapse