Postgres tracking count of recent items in count table or by selecting count of recent records from history table

Database Administrators Asked on August 24, 2020

Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has passed for that unique id.
Option 2 is not storing a count and fetching the count of records from a history table, by the records’ unique ids and within the past so many time units.
I would add an index on the id and timestamp fields for option 2.
The history table is in the low millions of records and doesn’t grow that fast.
Is the periodic vacuuming of the counts table likely to be much worse than selecting the count from the history table using indexed fields?
It seems like it would require less operations, no insert or update with Option 2.

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP