public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul Allen <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Bloated toast table with empty associated table
Date: Thu, 20 Mar 2025 16:45:39 +0400
Message-ID: <CAExgkpfsx=u-X64FFcSfgNhDzhhTXJDhE4WeuS+ZvRHGvwNx2w@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaD5kNoE_bw2HfW8bztcKBTGGHDpaKOrDLfuTu5K=_ZMWg@mail.gmail.com>
References: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
<CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@mail.gmail.com>
<CAExgkpfNg-3oDE+LsbePSTU8tS-wzku18WLYGHE4yfw-PdoamQ@mail.gmail.com>
<CANzqJaD5kNoE_bw2HfW8bztcKBTGGHDpaKOrDLfuTu5K=_ZMWg@mail.gmail.com>
> 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?
On Thu, Mar 20, 2025 at 4:28 PM Ron Johnson <[email protected]> wrote:
>
> On Thu, Mar 20, 2025 at 8:22 AM Paul Allen <[email protected]> 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 <[email protected]> wrote:
>> >
>> > On Thu, Mar 20, 2025 at 7:40 AM Paul Allen <[email protected]> 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!
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Bloated toast table with empty associated table
In-Reply-To: <CAExgkpfsx=u-X64FFcSfgNhDzhhTXJDhE4WeuS+ZvRHGvwNx2w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox