Making sure table statistics are up to date in PostgreSQL [closed]

Question

We are going to be using one of the system tables in PostgreSQL to get row count estimates for several tables. I understand that for these estimates to be better, statistics need to be up to date; the wiki here states that one should make sure "you have been running ANALYZE on the table enough to keep these statistics up to date".

We expect certain tables to eventually be written to or updated quite frequently (say, around a hundred and fifty times a second is my ballpark - this is "quite frequent" to me, but I'm not sure how that qualifies in real-life DB's). Counts should be happening about once every second, and I would say that it would be necessary for them to return a value that reflects the number of rows that changed in the table with some level of accuracy (i.e. it would be strange if the number did not change after a couple of seconds, if there were many inserts over that period).

What is a good value for "enough"? Is there some way to automate the running of ANALYZE on the tables? If more information is needed, please say so and I'll edit the post ASAP.

Thank you very much!

Answer 1

AutoVacuum should be able to handle what you are looking to do. If it is running, you can adjust the parameter autovacuum_analyze_threshold (integer) in postgresql.conf to meet your needs.

Per the documentation:

autovacuum_analyze_threshold (integer)

Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

Keep in mind that the number is not a guarantee of how often it will run, just a minimum threshold. As with most configuration options, it is a good idea to do some tests with several values to get the best trade-off between accuracy and performance and make sure it is meeting your needs.