AnswerBun.com

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 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP