> It's the general principle that the _scale_factor defaults are in my experience too high. Sorry, didn't mentioned Ok, thanks, I will use the cron task. It's toast behaviour still seems odd to me. Is there a way to prevent this behaviour at all, to not store rows which were failed to insert? On Thu, Mar 20, 2025 at 4:28 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote: > > On Thu, Mar 20, 2025 at 8:22 AM Paul Allen <paulcrtool@xxxxxxxxx> wrote: >> >> > Instantaneously and repeatedly, while ignoring the error? >> Yes, that's how it should work and I probably can't do anything about it. >> >> > Your _scale_factor values are too high. Drop them down to about 5%. >> >> Okay, but what about altering controlzone_passage table, where I set >> all _scale_factor >> values to 0? If this did not have an effect, then how will the value >> of 5% affect? Maybe I >> misunderstand, but the table does not change by any number of rows and >> its logical size >> remains zero. Anyway I will try it. > > > It's the general principle that the _scale_factor defaults are in my experience too high. > > Like I said: "That's not the proximate cause, though" of this table's bloat. > >> >> > 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. >> >> Seems like it is the only solution for now. > > > The autovacuum daemon can't know/see everything. > >> >> On Thu, Mar 20, 2025 at 4:03 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote: >> > >> > 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! > > > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster!
