PostgreSQL vacuuming a big table

Question

I have Postgres 9.4.7 and I have a big table ~100M rows and 20 columns. The table queries are 1.5k selects, 150 inserts and 300 updates per minute, no deletes though. Here is my autovacuum config:

autovacuum_analyze_scale_factor 0
autovacuum_analyze_threshold 5000
autovacuum_vacuum_scale_factor 0
autovacuum_vacuum_threshold 5000
autovacuum_max_workers 6
autovacuum_naptime 5s

In my case database are almost always in the constant state of vacuuming. When one vacuuming session ends another one begins.

So the main question: Is there a common way to vacuum big tables?

Here are some other questions.

Standard vacuum do not scan entire table and 'analyze' only scans 30k rows. So under the same load I should have a constant execution time, is it true? Do I really need to analyze table? Can frequent 'analyze' do any useful changes in query plans for a large table?

do you have 9.6?.. If not statement Standard vacuum do not scan entire table is false — Vao Tsun

Vao Tsun Vao Tsun · Accepted Answer · 2017-04-20T15:36:02

vacuum

VACUUM reclaims storage occupied by dead tuples.

So it changes only affected pages, but it will scan entire table.

That regards what you probably call "Standard vacuum". Now if you have 9.6, then

VACUUM will skip pages based on the visibility map

analyze

amount of data that ANALYZE scans depends on table size and default_statistics_target set per instance or per table - it is not 30K per se:

For large tables, ANALYZE takes a random sample of the table contents, rather than examining every row... change slightly each time ANALYZE is run, even if the actual table contents did not change. This might result in small changes in the planner's estimated costs shown by EXPLAIN.

So if you want more stable results for EXPLAIN run smth like

alter table ... alter COLUMN ... set STATISTICS 200;

or increase default_statistics_target, otherwise too often analyze has more chances to change plan.

One more thing - you have 5K threshold. In a table with 100000K rows it is 0.002% - right? so the scale is 0.00002? while default one in 0.2 or 0.1... It makes me thing that maybe you have threshold too low. Running vacuum more often is recommended indeed, but here it looks too often. Like a thousand times more often then it would be by default...

PostgreSQL vacuuming a big table

1 Answers