Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvF0T-002gcE-D4 for pgsql-general@arkaria.postgresql.org; Thu, 20 Mar 2025 12:28:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tvF0S-000OWl-3O for pgsql-general@arkaria.postgresql.org; Thu, 20 Mar 2025 12:28:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvF0R-000OWO-N4 for pgsql-general@lists.postgresql.org; Thu, 20 Mar 2025 12:28:15 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tvF0P-0008B2-1e for pgsql-general@lists.postgresql.org; Thu, 20 Mar 2025 12:28:15 +0000 Received: by mail-oi1-x235.google.com with SMTP id 5614622812f47-3feaedb39e9so188714b6e.1 for ; Thu, 20 Mar 2025 05:28:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742473691; x=1743078491; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=YIO5q9IqRYHzA0NvlD6JSBhetSl8AL4dHTJOzAs5huU=; b=LecytyaLJzMjDzqjrtp2PEyvVx/HM6pS7W9KAj5+liDr0fs+hw40bjWiqjgWN2fyQA WkkCF4UhhleiqE2hrcxDfwNjmDRLkeRxY6G2rhIj6WE8MGx+fSecXgJuMOwaFcR7IHA4 MgJ2JCsTyae9KPXiqY/VJTicm87RHUNgylmsPCXQ9DIeQHTkaL1W9EXZHHyH0G6gqnCu ptKqSSjtk3lsArr8XhNmCKuUHOIfdDD74nt0uXbRvfxVv8YKJbiqLGeq6Q7sgIXMlrCB 7DovI8xdVwC/qRVFv2BvXYJI4wZyc+96G+9X5lZm2Zdh7t4lPrwme1PS7y84LmdR05jy rvhA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742473691; x=1743078491; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=YIO5q9IqRYHzA0NvlD6JSBhetSl8AL4dHTJOzAs5huU=; b=A9TbF3kqQhe7P0qcO9Prb5oQHgkisLN9eTl9PnyaT3qWZMoqARpgiL7dgU2FXQriNo 6LlxOQEwY7cK5U6Xr4UnL+w39SP/XvrtknX2sixGZEp8bQfIpjoDtzHBhoaa9Vf4wt3E o3OZFU1sKfYWeYrru3lxUzVk7GioYiTJreUYvmDImdA+fnFMPgKDAvw+0Oua/odlxhFk 9fwBLgPC11OQLCk3Hl+AaK60lAP9vut5IaiAdFDiE2PRw8mdfdFf0gFfvEJ01YfFsqG7 +8WUgnonoTL3srkbw9oOlP86aqd/OQCXUsjOolH8/smV/93M5cAATbAVjOsct40mLSsJ hFKA== X-Gm-Message-State: AOJu0Yyspr0ydiRpMyWrJdZplGKitDaFWy4WQszPbjaiSww9aGLu14tt MTL+bF1gUtrJca84RoQKJ0Xswk0L1xxtEnur2S0WayxyVPDSGhuE6EobVeinHzyeE0vkrm9fbgq G6/xlucW/HBidmgXn0DR5Vhx0vmWR5Zr1 X-Gm-Gg: ASbGncuoo5NiEho3GLgXgF1/mGLe+fIZpzLV9W2JOiMvdut776Eu0Bjc7+jWDCY7/am 78jyzsiTLNGr6v+LO97lx6d1GV2smfoE3DEv1qKYpxgl0N4Za3cmnAXXjqDev+cTZtm/jG19DX8 4YV5fHvTSbReZozhtAjE3BTQ6R5iCweTlxsJTaE+mXFJy8NlcSGlWI576WvuyR X-Google-Smtp-Source: AGHT+IHeX7Ph+2dUWoBhYboQv4UT1YYdTp9+3JJLHikvIcYLC/O8W7H4cxcw/vnpOUGswLeIaR79Zwix+vjjux2crZ0= X-Received: by 2002:a05:6808:4191:b0:3f9:73c5:68d3 with SMTP id 5614622812f47-3fead575b43mr4029844b6e.5.1742473691115; Thu, 20 Mar 2025 05:28:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 20 Mar 2025 08:27:59 -0400 X-Gm-Features: AQ5f1JoFsZX8aojeqFqSr3zbv6mDcmfLwb9qHDwPLjEp5_zQOsdc7_C_tAwD8tw Message-ID: Subject: Re: Bloated toast table with empty associated table To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000063b8ce0630c5459c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000063b8ce0630c5459c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 20, 2025 at 8:22=E2=80=AFAM Paul Allen w= rote: > > 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=E2=80=AFPM Ron Johnson > wrote: > > > > On Thu, Mar 20, 2025 at 7:40=E2=80=AFAM Paul Allen 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 byte= a > >> columns with default storage type. It's purpose is to keep images. > >> PostgreSQL version is 15, everything is default, autovacuum settings i= s > >> > >> ``` > >> 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 th= e > >> 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 G= B > >> 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 toa= st > >> 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 , and butter sauce. > > Don't boil me, I'm still alive. > > lobster! > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000063b8ce0630c5459c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Mar 20, 2025 at 8:22=E2=80=AFAM P= aul Allen <paulcrtool@gmail.com<= /a>> wrote:
> Instantaneously and repeat= edly, while ignoring the error?
Yes, that's how it should work and I probably can't do anything abo= ut it.

> Your _scale_factor values are too high.=C2=A0 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&= #39;s the general principle that the _scale_factor defaults are in my exper= ience too high.

Like=C2=A0I said: "That's= not the proximate cause, though" of this table's bloat.
=C2=A0
> I'd= create a cron entry that does a regular "vacuumdb -d the_db -t=C2=A0 = controlzone_passage".=C2=A0 How often you run it depends on how quickl= y it bloats.

Seems like it is the only solution for now.

=
The autovacuum daemon can't know/see everything.
=C2=A0<= /div>
On Thu, Mar 20, 2025= at 4:03=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> On Thu, Mar 20, 2025 at 7:40=E2=80=AFAM Paul Allen <paulcrtool@gmail.com> wro= te:
>>
>> 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 inser= t is
>> invalid by violation of foreing key constraint, so I am getting er= ror
>> while inserting and table keeps being empty. This table have some = bytea
>> columns with default storage type. It's purpose is to keep ima= ges.
>> PostgreSQL version is 15, everything is default, autovacuum settin= gs is
>>
>> ```
>> autovacuum=C2=A0 on
>> autovacuum_analyze_scale_factor=C2=A0 0.1
>> autovacuum_analyze_threshold=C2=A0 50
>> autovacuum_freeze_max_age=C2=A0 200000000
>> autovacuum_max_workers=C2=A0 3
>> autovacuum_multixact_freeze_max_age=C2=A0 400000000
>> autovacuum_naptime=C2=A0 60
>> autovacuum_vacuum_cost_delay=C2=A0 20
>> autovacuum_vacuum_cost_limit=C2=A0 -1
>> autovacuum_vacuum_scale_factor=C2=A0 0.2
>> autovacuum_vacuum_threshold=C2=A0 50
>> autovacuum_work_mem=C2=A0 -1
>> log_autovacuum_min_duration=C2=A0 -1
>
>
> Your _scale_factor values are too high.=C2=A0 Drop them down to about = 5%.
>
> That's not the proximate cause, though.
>
>>
>> ```
>>
>> Problem.
>>
>> My backend application attempts unsuccessfully repeatedly to inser= t 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, 20= 0, 300 GB
>> until it takes all available space.
>>
>> VACUUM FULL fixes this, but a want some automatic solution. I trie= d to
>> alter table, believing that the settings below would force autovac= uum 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 remain= s
>> 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=C2=A0 controlzone_passage".=C2=A0 How often you run it depends o= n 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> lobs= ter!
--00000000000063b8ce0630c5459c--