Bloated toast table with empty associated table

From: Paul Allen <paulcrtool(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Bloated toast table with empty associated table
Date: 2025-03-20 11:39:39
Message-ID: CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

Preconditions.

I have some empty table and constantly try to execute `insert ... on
conflict do update ...` on it. My data in row which I try to insert is
invalid by violation of foreing key constraint, so I am getting error
while inserting and table keeps being empty. This table have some bytea
columns with default storage type. It's purpose is to keep images.
PostgreSQL version is 15, everything is default, autovacuum settings is

```
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 60
autovacuum_vacuum_cost_delay 20
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1
log_autovacuum_min_duration -1
```

Problem.

My backend application attempts unsuccessfully repeatedly to insert the
same ~100 rows with images, and despite table's row count remains 0,
toast table's size is growing up permanently, reaching 100, 200, 300 GB
until it takes all available space.

VACUUM FULL fixes this, but a want some automatic solution. I tried to
alter table, believing that the settings below would force autovacuum to
clean toast anyway, but it had no effect.

```
alter table controlzone_passage set (
autovacuum_enabled = true,
toast.autovacuum_enabled = true,
autovacuum_vacuum_threshold = 0,
toast.autovacuum_vacuum_threshold = 0,
autovacuum_vacuum_scale_factor = 0,
toast.autovacuum_vacuum_scale_factor = 0,
autovacuum_analyze_threshold = 0,
autovacuum_analyze_scale_factor = 0);
```

At the moment, my assumption is that the autovacuum is not working
because the number of rows in the table does not change and remains
zero. Any solution will suit me, for example, not to write rows to toast
if their insertion failed. Or the proper setting of the autovacuum.
Please tell me what can be done.

Thanks. Daniil Rozanov

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Siraj G 2025-03-20 11:52:21 Export operation efficiency in read replica
Previous Message Luca Ferrari 2025-03-20 10:47:37 Re: After upgrading libpq, the same function(PQftype) call returns a different OID