MIME-Version: 1.0
References: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
 <CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@mail.gmail.com>
In-Reply-To: <CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@mail.gmail.com>
From: Paul Allen <paulcrtool@gmail.com>
Date: Thu, 20 Mar 2025 16:21:57 +0400
Message-ID: <CAExgkpfNg-3oDE+LsbePSTU8tS-wzku18WLYGHE4yfw-PdoamQ@mail.gmail.com>
Subject: Re: Bloated toast table with empty associated table
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAExgkpfNg-3oDE%2BLsbePSTU8tS-wzku18WLYGHE4yfw-PdoamQ%40mail.gmail.com>
Precedence: bulk

> 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.

> I'd create a cron entry that does a regular "vacuumdb -d the_db -t  contr=
olzone_passage".  How often you run it depends on how quickly it bloats.

Seems like it is the only solution for now.

On Thu, Mar 20, 2025 at 4:03=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.co=
m> wrote:
>
> On Thu, Mar 20, 2025 at 7:40=E2=80=AFAM Paul Allen <paulcrtool@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 =3D true,
>> toast.autovacuum_enabled =3D true,
>> autovacuum_vacuum_threshold =3D 0,
>> toast.autovacuum_vacuum_threshold =3D 0,
>> autovacuum_vacuum_scale_factor =3D 0,
>> toast.autovacuum_vacuum_scale_factor =3D 0,
>> autovacuum_analyze_threshold =3D 0,
>> autovacuum_analyze_scale_factor =3D 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  contr=
olzone_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!