public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Bloated toast table with empty associated table
Date: Thu, 20 Mar 2025 08:03:40 -0400
Message-ID: <CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@mail.gmail.com> (raw)
In-Reply-To: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>
References: <CAExgkpc90tp5iDJE=3mPCsS=mvu8Y8ZiY1s1OZvyPZ5ck+BMpQ@mail.gmail.com>

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!

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]
  Subject: Re: Bloated toast table with empty associated table
  In-Reply-To: <CANzqJaD7P-Cc3hyi4XeJ--n33uG2moDGdP-jtn+rF=ABSgM6FQ@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