MIME-Version: 1.0
References: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
In-Reply-To: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 20 Mar 2025 08:03:40 -0400
Message-ID: <CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@mail.gmail.com>
Subject: Re: Bloated toast table with empty associated table
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000639d7d0630c4ee39"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn%2BrF%3DABSgM6FQ%40mail.gmail.com>
Precedence: bulk

--000000000000639d7d0630c4ee39
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 20, 2025 at 7:40=E2=80=AFAM Paul Allen <paulcrtool@gmail.com> w=
rote:

> 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
controlzone_passage".  How often you run it depends on how quickly it
bloats.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000639d7d0630c4ee39
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Mar 20, 2025 at 7:40=E2=80=AFAM P=
aul Allen &lt;<a href=3D"mailto:paulcrtool@gmail.com">paulcrtool@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex">Hello.<br>
<br>
Preconditions.<br>
<br>
I have some empty table and constantly try to execute `insert ... on<br>
conflict do update ...` on it. My data in row which I try to insert is<br>
invalid by violation of foreing key constraint, so I am getting error<br>
while inserting and table keeps being empty. This table have some bytea<br>
columns with default storage type. It&#39;s purpose is to keep images.<br>
PostgreSQL version is 15, everything is default, autovacuum settings is<br>
<br>
```<br>
autovacuum=C2=A0 on<br>
autovacuum_analyze_scale_factor=C2=A0 0.1<br>
autovacuum_analyze_threshold=C2=A0 50<br>
autovacuum_freeze_max_age=C2=A0 200000000<br>
autovacuum_max_workers=C2=A0 3<br>
autovacuum_multixact_freeze_max_age=C2=A0 400000000<br>
autovacuum_naptime=C2=A0 60<br>
autovacuum_vacuum_cost_delay=C2=A0 20<br>
autovacuum_vacuum_cost_limit=C2=A0 -1<br>
autovacuum_vacuum_scale_factor=C2=A0 0.2<br>
autovacuum_vacuum_threshold=C2=A0 50<br>
autovacuum_work_mem=C2=A0 -1<br>
log_autovacuum_min_duration=C2=A0 -1<br></blockquote><div><br></div><div>Yo=
ur _scale_factor values are too high.=C2=A0 Drop them down to about 5%.</di=
v><div><br></div><div>That&#39;s not the proximate cause, though.</div><div=
>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
```<br>
<br>
Problem.<br>
<br>
My backend application attempts unsuccessfully repeatedly to insert the<br>
same ~100 rows with images,</blockquote><div><br></div><div>Instantaneously=
 and repeatedly, while ignoring the error?</div><div>=C2=A0</div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"> and despite table&#39;s row count =
remains 0,<br>
toast table&#39;s size is growing up permanently, reaching 100, 200, 300 GB=
<br>
until it takes all available space.<br>
<br>
VACUUM FULL fixes this, but a want some automatic solution. I tried to<br>
alter table, believing that the settings below would force autovacuum to<br=
>
clean toast anyway, but it had no effect.<br>
<br>
```<br>
alter table controlzone_passage set (<br>
autovacuum_enabled =3D true,<br>
toast.autovacuum_enabled =3D true,<br>
autovacuum_vacuum_threshold =3D 0,<br>
toast.autovacuum_vacuum_threshold =3D 0,<br>
autovacuum_vacuum_scale_factor =3D 0,<br>
toast.autovacuum_vacuum_scale_factor =3D 0,<br>
autovacuum_analyze_threshold =3D 0,<br>
autovacuum_analyze_scale_factor =3D 0);<br>
```<br>
<br>
At the moment, my assumption is that the autovacuum is not working<br>
because the number of rows in the table does not change and remains<br>
zero. Any solution will suit me, for example, not to write rows to toast<br=
>
if their insertion failed. Or the proper setting of the autovacuum.<br>
Please tell me what can be done.<br></blockquote><div><br></div><div>I&#39;=
d create a cron entry that does a regular &quot;vacuumdb -d the_db -t=C2=A0

controlzone_passage&quot;.=C2=A0 How often you run it depends on how quickl=
y it bloats.</div></div><div><br></div><span class=3D"gmail_signature_prefi=
x">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr=
">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39=
;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></d=
iv></div></div>

--000000000000639d7d0630c4ee39--