On Thu, Mar 20, 2025 at 7:40 AM Paul Allen <paulcrtool@xxxxxxxxx> wrote:
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
Your _scale_factor values are too high. Drop them down to about 5%.
That's not the proximate cause, though.
```
Problem.
My backend application attempts unsuccessfully repeatedly to insert the
same ~100 rows with images,
Instantaneously and repeatedly, while ignoring the error?
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.
I'd create a cron entry that does a regular "vacuumdb -d the_db -t
controlzone_passage". How often you run it depends on how quickly it bloats.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
