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!

Related Questions

Table access for all users and logins

2  Asked on November 8, 2021 by thepieman314


save/export PGAdmin server list configuration settings

5  Asked on November 5, 2021 by phill-pafford


Unused index in range of dates query

1  Asked on November 5, 2021 by andrew-cooke


Combining GROUP BY and JOIN

1  Asked on November 5, 2021


Correcting wrong timezones/timestamps in existing tables

0  Asked on October 28, 2021 by moritz-roessler


MSA and gMSA and SQL Services

1  Asked on October 28, 2021 by marcin-wilczek


Could not find a “psql” to execute

1  Asked on October 28, 2021 by apt605


Ask a Question

Get help from others!

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