CODEWITHSUNDEEP
postgresqlautovacuum
chai
chai

Reputation: 1

Postgresql autovacuum_vacuum

The disk size increases periodically, even though the dead tuple count is reduced by increasing the performance of Autovacuum. The amount of inserts is less than the number of dead tuples.

Test Environment:

I checked disk size and dead tuple status periodically. Disk size:

SELECT nspname || '.' || relname AS "relation",
    pg_total_relation_size(C.oid) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 3;

Dead tuples:

SELECT relname, n_live_tup, n_dead_tup,
       n_dead_tup / (n_live_tup::float) as ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0
  AND n_dead_tup > 1000
ORDER BY ratio DESC;

It takes more than 3 days for Autovacuum. I changed it to run within 30 minutes with this setting like this:

ALTER SYSTEM SET maintenance_work_mem ='1GB';
select pg_reload_conf();
alter table pm_reporthour set (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE PM_REPORTHOUR SET (autovacuum_vacuum_cost_delay =0);

Please advise which one to review.

Upvotes: 0

Views: 183

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246338

If you have large deletes, you will always get bloat. The king's way in this case is partitioning: partition by range so that you can run DROP TABLE on a partition instead of a massive DELETE. Apart from better performance, that will avoid all bloat.

Upvotes: 1

Related Questions