On Thu, Mar 20, 2025 at 7:40 AM Paul Allen <paulcrt...@gmail.com> 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!
