PostgreSQL with PostGIS - autovacuum for raster table

Question

I have PostGIS enabled and use raster data in the table. This table has over 2 million rows and is being updated, inserted or deleted several times a day (aprox. every 2 hours is entire table rewritten with a new data). In the same time, data from table is read by the users via select query as part of the web page.

I have autovacuum enabled by default. However, autovacuum query time is large, nearly an hour. Should I turn off autovacuum for this table and do it manually once or twice a day if there is less users on the web? Or should I leave it enabled and the hour of runtime is normal?

• jjanes: "aprox. every 2 hours is entire table rewritten with a new data" In one giant transaction, or the culmination of 2 million independent transactions?
• Martin Perry: @jjanes independent transactions during the 2 hours.
• jjanes: I would think that if anything you would want to vacuum more often, not less. But I would say there is no evidence that you need to change anything. It would be nice to vacuum during slow times but with 12 data turn-overs a day I think you don't have enough slow times.

Answer 1

What determines if you have a problem or not is whether the table keeps growing inordinately or not. If autovacuum is fast enough, dead tuples get cleaned up, and the space can be reused for new rows. If autovacuum is too slow, new rows will cause the table to be extended to make more room.

If you have the feeling that autovacuum is too slow, make it faster:

ALTER TABLE rastertable SET (autovacuum_vacuum_cost_delay = 1);

The default value for that parameter varies: before v12, it was 20 milliseconds, after that it is 2. So if you are using an old version, you might not need to go as low as 1 to see an effect.

The idea is that autovacuum is fast enough to clean up dead data in time, but slow enough not to cause undue stress on your resources.