On Thu, Mar 20, 2025 at 8:22 AM Paul Allen <paulcrt...@gmail.com> 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 <ronljohnso...@gmail.com> > wrote: > > > > 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! > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
