1

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:

  1. 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.
  2. What parameters could I tweak to avoid such performance loss?
  3. 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.

Thanks.


(edit)

Vacuum verbose

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

Where 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
2
  • Maybe the autovac is getting interrupted and never finishes. Look in the log file.
    – jjanes
    Feb 1, 2022 at 19:19
  • 2 fold bloat is expected. You didn't give us much in the way of quantitative data to compare this to--were you seeing much more than 2-fold?. The VERBOSE data you gave seems to have been generated only after the problem was already fixed (and after the evidence was destroyed).
    – jjanes
    Feb 1, 2022 at 19:23

1 Answer 1

3

If you update all rows of one table in a single statement, the table will be bloated to twice its minimal size (it will contain a live tuple and a dead tuple for each row), and that bloat cannot be removed by autovacuum. If autovacuum doesn't finish fast enough, the next update will only increase the problem.

Ideas for a solution:

  • Run the update in batches, where each batch updates only a fraction of the rows. Between the updates, run an explicit VACUUM. That will keep the bloat at bay.

  • Accept the bloat of 50%+, but configure autovacuum to run as fast as possible by setting autovacuum_vacuum_cost_delay = 0 and maintenance_work_mem = '1GB'. That way, you have a chance that the table won't be bloated even more.

  • Deliberately create the table will a fillfactor of 45, so that it is deliberately bloated from the beginning, but make sure that the update only modifies columns that are not indexed. Then you will get HOT updates, which will at least reduce the need for VACUUM.

2
  • Thank you very much. In fact yesterday I already started playing with fillfactor and it looks promising.
    – Nicholas
    Feb 1, 2022 at 9:27
  • 1
    bwt, missed to say that I already did autovacuum_vacuum_cost_delay = 0 before posting my question.
    – Nicholas
    Feb 1, 2022 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.