I have a database with two tables where every 5 minutes the entire content of those tables is updated (mainly just a single column). The biggest of the two tables has about 170K rows. The autovacuum seems to run quite frequently on those tables, yet their disk size becomes very big (2 GB, lots of dead rows) and after a few weeks the whole database performance is noticeably degraded (higher loads, higher read/write latency, higher IOPS). A
VACUUM FULL solves the issue, restoring the tables to an acceptable size and performances are back to the expected levels.
Now, I understand that this type of use is not ideal for autovacuum. And it doesn't look like there are long running queries blocking the autovacuum. Yet I have a few questions:
- Why the disk increase? I would expect that autovacuum is able to mark dead rows as "deleted" and reuse the disk space instead of a continuous growth.
- What parameters could I tweak to avoid such performance loss?
- How to better debug what is blocking autovacuum to properly cleanup things?
I also noticed the tables' statistics are quite off: the estimated number of rows is an order of magnitude inferior than the live rows count.
Here the output of
VACUUM VERBOSE ANALYZE on one of the two tables:
vacuuming "public.table1" index "leads_pkey" now contains 310038 row versions in 22625 pages index "leads_uuid_key" now contains 310038 row versions in 40682 pages "table1": found 0 removable, 280589 nonremovable row versions in 14781 out of 34929 pages vacuuming "pg_toast.pg_toast_2042410" index "pg_toast_2042410_index" now contains 0 row versions in 1 pages "pg_toast_2042410": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages analyzing "public.table1" "table1": scanned 30000 of 34929 pages, containing 148231 live rows and 121206 dead rows; 30000 rows in sample, 172585 estimated total rows
VACUUM FULL VERBOSE ANALYZE has the output:
vacuuming "public.table1" "table1": found 0 removable, 487921 nonremovable row versions in 34929 pages analyzing "public.table1" "table1": scanned 13614 of 13614 pages, containing 171987 live rows and 315934 dead rows; 30000 rows in sample, 171987 estimated total rows