MIME-Version: 1.0
From: Paul Allen <paulcrtool@gmail.com>
Date: Thu, 20 Mar 2025 15:39:39 +0400
Message-ID: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
Subject: Bloated toast table with empty associated table
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAExgkpc90tp5iDJE%3D3mPCsS%3Dmvu8Y8ZiY1s1OZvyPZ5ck%2BBMpQ%40mail.gmail.com>
Precedence: bulk

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