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 1tvFP6-002kGw-E4 for pgsql-general@arkaria.postgresql.org; Thu, 20 Mar 2025 12:53:44 +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 1tvFP5-001DoC-3R for pgsql-general@arkaria.postgresql.org; Thu, 20 Mar 2025 12:53:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvFP4-001DkP-GP for pgsql-general@lists.postgresql.org; Thu, 20 Mar 2025 12:53:42 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tvFP2-00087C-1W for pgsql-general@lists.postgresql.org; Thu, 20 Mar 2025 12:53:41 +0000 Received: by mail-oi1-x22b.google.com with SMTP id 5614622812f47-3f8ae3ed8f4so378203b6e.3 for ; Thu, 20 Mar 2025 05:53:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742475219; x=1743080019; 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=Xqf7++KRQ5ep/VFT8asN1PWyhI4etpqat8hCthTswX8=; b=fyY6muvSPAY7gv2BgfS562SCm3+Ubd4sKBw9ZOZ97snGWL/CLnj/iSVNcGr5HkVb4k PHvCVbOLupdb4Ir17fUSBtHAUjDxiM0x6WYBEcrYl+ongQ7E/1uMdJdoZjuairbh47F/ hKTjcsmFvIWnW/Ls62eX3/M2BeGq5BDqeze4qj6dIONxPQqkYjtaD7y7JPDP3VPKJC2H wSayGhphipqRdNTw7ZQb9zUZeWLQcZ0UNsNkDOhhRgOxJv/hNk7ef8+b8JPdY5xlMQ+a eDRaKLhF2lkxGKAp6uj2P1ZCifeM/HWFR97m5DEYoP7G3rmH36Wd5SL+qIr7jfz1OU// MCnQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742475219; x=1743080019; 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=Xqf7++KRQ5ep/VFT8asN1PWyhI4etpqat8hCthTswX8=; b=B5VG8i+Lh++gExd5DE8eDEAHRIdwSi64IE5AzHkNLMhthl8IwLAmHf3vHWJv7Oq1eD OfRrzNcnWsGqazTJh3A/9MrbnV4bKGP70ORToIHCl8zdx2kvVhSFUpupEwjkCSH21nmG 3+E9j/B1wvIlNDfThRKcwsnoHxGQYARwYOrWHOUIb86O72YbRHZeBbUPPtJPDLzHD0OK 6ERlpd2CRs6kUcfXtRfEJA8WG94+uefXYNIPcH7nACse1YaFULHE97nI2qXvaf3XVSvd JNF9JLM9KHCy3VVaOaGaGyBpPKDKaHiklEnF5MMFo/+vRV7yBbMTIfZNAi/Wb5IYlTd6 WMyg== X-Gm-Message-State: AOJu0Yy+DyhJW68TMcYmvtN/Zt9rTXBSObJFwVrd2mNi5FUZN/Iwe7Fv dlxWCg9ol4eyQw1AlU2IviHqXft07O2WpDcjT+B2NfOfLEhaqcfsIoauPHptKiaT4XW9TArPvJg OyNxmxxqf3mG+9dSfOwKHJD0DoDx64g== X-Gm-Gg: ASbGnctx8W5x2Nkyv7SHcZkV8lh2tpIgkWh9OXBbxYLZOPBWbvujqz7orRX1UOASYXG j0g5Uyj1+ohhAAbUwETnwjUxVojVJitqIxNzK1TaCgaSiMTw6DS9L8rc2Ov5YpCyq2oU60iabY4 qd50AcnR5jr8Qu5h8XYfSOA0gAZji0ShTmP5hHeAFvvq3YU5rdS3oVUkqSIpy8 X-Google-Smtp-Source: AGHT+IG7gwRNL2U9aRrZBIy3TaLyaghZi0FguR+SFkQhbdGn0U8rcDRIbYiQiCF6Q45+O9J51ON0vvMwz290PEF+MC8= X-Received: by 2002:aca:2b17:0:b0:3fe:aedb:4106 with SMTP id 5614622812f47-3feaedb4174mr3357148b6e.11.1742475219119; Thu, 20 Mar 2025 05:53:39 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 20 Mar 2025 08:53:27 -0400 X-Gm-Features: AQ5f1Jq8s0uLb2L45G2XntRMpXT_HBU_ASZsnfSS3oDqumRdqU0-lH7bVIU7vFQ Message-ID: Subject: Re: Bloated toast table with empty associated table To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000007735900630c5a0bc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007735900630c5a0bc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 20, 2025 at 8:45=E2=80=AFAM Paul Allen w= rote: > > 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? > Fix the cause of the FK violation? Check for possible FK violations before they happen? See, the problem you're having is that PG is properly doing what it was told to do: INSERT records. And... it did just that. But what *must* PG do when it discovers a constraint violation 99% of the way through inserting those 100 records? That's right: remove the records. Thus, you get bloat. > On Thu, Mar 20, 2025 at 4:28=E2=80=AFPM Ron Johnson > wrote: > > > > On Thu, Mar 20, 2025 at 8:22=E2=80=AFAM Paul Allen 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=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 ... o= n > >> >> 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 err= or > >> >> 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 setting= s > 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, 30= 0 > 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. > >> > > >> > -- > >> > Death to , and butter sauce. > >> > Don't boil me, I'm still alive. > >> > lobster! > > > > > > > > -- > > 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! --0000000000007735900630c5a0bc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Mar 20, 2025 at 8:45=E2=80=AFAM P= aul Allen <paulcrtool@gmail.com<= /a>> wrote:
> It's the general princ= iple 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?

Fix the cause of the FK violation?=C2=A0 Che= ck for possible FK violations before they happen?

= See, the problem you're having is that PG is properly doing what it was= told to do: INSERT records.

And... it did just th= at.=C2=A0 But what *must* PG do when it discovers a constraint violation 99= % of the way through inserting those 100 records?=C2=A0 That's right: r= emove the records.
=C2=A0
Thus, you get bloat.


On Thu, Mar 20, 2025 at 4:28=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> w= rote:
>
> On Thu, Mar 20, 2025 at 8:22=E2=80=AFAM Paul Allen <paulcrtool@gmail.com> wro= te:
>>
>> > Instantaneously and repeatedly, while ignoring the error?
>> Yes, that's how it should work and I probably can't do any= thing about 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 s= et
>> all _scale_factor
>> values to 0? If this did not have an effect, then how will the val= ue
>> 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 "vacuumd= b -d the_db -t=C2=A0 controlzone_passage".=C2=A0 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 <ronljohnsonjr@gmail.com<= /a>> 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 `in= sert ... 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 g= etting error
>> >> while inserting and table keeps being empty. This table h= ave some bytea
>> >> columns with default storage type. It's purpose is to= keep images.
>> >> PostgreSQL version is 15, everything is default, autovacu= um settings 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 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, reachin= g 100, 200, 300 GB
>> >> until it takes all available space.
>> >>
>> >> VACUUM FULL fixes this, but a want some automatic solutio= n. I tried to
>> >> alter table, believing that the settings below would forc= e 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 no= t working
>> >> because the number of rows in the table does not change a= nd remains
>> >> zero. Any solution will suit me, for example, not to writ= e rows to toast
>> >> if their insertion failed. Or the proper setting of the a= utovacuum.
>> >> Please tell me what can be done.
>> >
>> >
>> > I'd create a cron entry that does a regular "vacuumd= b -d the_db -t=C2=A0 controlzone_passage".=C2=A0 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!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000007735900630c5a0bc--