Make sure table statistics are up to date in PostgreSQL
We are going to use one of the system tables in PostgreSQL to get row count estimates for multiple tables. I understand that for these estimates to be better, statistics must be up to date; The wiki here states that you need to make sure you "run ANALYZE
on the desktop to update statistics".
We expect some tables to end up being written or updated quite often (say about a hundred and fifty times per second my focus is "quite often" for me, but I'm not sure how this fits with real DBs). The graphs should happen about once a second, and I would say they would need to return a value that reflects the number of rows that have changed in the table with some level of precision (i.e. it would be strange if the number didn't change after a couple seconds if there were many inserts during this period).
What's good for "enough"? Is there a way to automate working ANALYZE
with tables? If you need more information, please let me know and I will edit the post as soon as possible.
Many thanks!
source to share
AutoVacuum
should be able to handle what you are looking for. If it is running, you can adjust the setting autovacuum_analyze_threshold (integer)
in postgresql.conf
according to your needs.
In the documentation:
autovacuum_analyze_threshold (integer)
Specifies the minimum number of inserted, updated, or deleted tuples that ANALYZE should be called on any table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This parameter can be overridden for individual tables by changing the storage parameters.
Keep in mind that the number is not a guarantee of how often it will run, the minimum threshold. As with most configuration options, it's a good idea to run multiple tests with multiple values to get the best compromise between accuracy and performance and make sure it suits your needs.
source to share