0

I'm trying to figure out how to implement a bi-temporal table for a product catalog on a PostgreSQL database. The catalog table could become quite big over time. I would like to achieve very good performance for frequent queries like "what is the price for product x today" and "what was the price for product x a week ago". I would like to achieve good performance for queries like "what was the price for product x two months ago". I also need to be able to answer queries like "what did I think two weeks ago, what the price for product x was three weeks before that" (hence bi-temporal). Therefore I'm also considering partitioning of the data.

I did find https://pgxn.org/dist/temporal_tables/ to aid in the system-time aspect of the implementation (business time would be "manual") and https://github.com/keithf4/pg_partman for partitioning, but I'm wondering whether the two would work well together and/or whether someone has used these (or alternatives) successfully.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
MichaelP
  • 121
  • 2
  • 6
  • Partitions usually are done on date range and in your case I think that temporal tables would be redundant and only make it overly complicated. Unless you plan to partition your data by something else? – Łukasz Kamiński Aug 08 '17 at 12:38
  • @ŁukaszKamiński the temporal_tables plugin helps to ensure that data that is supposed to be modified in one table is recorded in another table to preserve change history (e.g. "catalog" and "catalog_history") e.g. for auditing purposes or historic reporting. Partitioning could help further breaking down catalog_history into monthly partitions that can be queried more efficiently and can be removed or moved to less expensive storage after whatever period of time is appropriate. It may also be possible to split the catalog into more recent pricing and less frequently used older pricing. – MichaelP Aug 09 '17 at 14:16

0 Answers0