public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Autovacuum, dead tuples and bloat
Date: Thu, 20 Jun 2024 13:06:42 -0400
Message-ID: <CANzqJaD8w4YzwDWBhSBxGt6R-7TxuYV5g=HeB1HUe=xEsbjr2g@mail.gmail.com> (raw)
In-Reply-To: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>

On Thu, Jun 20, 2024 at 12:47 PM Shenavai, Manuel <[email protected]>
wrote:

> Hi everyone,
>
>
>
> we can see in our database, that the DB is 200GB of size, with 99% bloat.
> After vacuum full the DB decreases to 2GB.
>
> DB total size: 200GB
>
> DB bloat: 198 GB
>
> DB non-bloat: 2GB
>
>
>
> We further see, that during bulk updates (i.e. a long running
> transaction), the DB is still growing, i.e. the size of the DB growth by
> +20GB after the bulk updates.
>
>
>
> My assumption is, that after an autovacuum, the 99% bloat should be
> available for usage again. But the DB size would stay at 200GB. In our
> case, I would only expect a growth of the DB, if the bulk-updates exceed
> the current DB size (i.e. 220 GB).
>
>
That's also my understanding of how vacuum works.

Note: I disable autovacuum before bulk modifications, manually VACUUM
ANALYZE and then reenable autovacuum.  That way, autovacuum doesn't jump in
the middle of what I'm doing.

 How could I verify my assumption?
>
>
>
> I think of two possibilities:
>
>    1. My assumption is wrong and for some reason the dead tuples are not
>    cleaned so that the space cannot be reused
>    2. The bulk-update indeed exceeds the current DB size. (Then the
>    growth is expected).
>
>
>
> Can you help me to verify these assumptions? Are there any statistics
> available that could help me with my verification?
>

I've got a weekly process that deletes all records older than N days from a
set of tables.
db=# ALTER TABLE t1 SET (autovacuum_enabled = off);
db=# ALTER TABLE t2 SET (autovacuum_enabled = off);
db=# ALTER TABLE t3 SET (autovacuum_enabled = off);
db=# DELETE FROM t1 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t2 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
db=# DELETE FROM t3 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90
DAY');
$ vacuumdb --jobs=3 -t t1 -t t2 -t t3
db=# ALTER TABLE t1 SET (autovacuum_enabled = on);
db=# ALTER TABLE t2 SET (autovacuum_enabled = on);
db=# ALTER TABLE t3 SET (autovacuum_enabled = on);

pgstattuple shows that that free percentage stays pretty constant.  That
seems to be what you're asking about.

view thread (11+ 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]
  Subject: Re: Autovacuum, dead tuples and bloat
  In-Reply-To: <CANzqJaD8w4YzwDWBhSBxGt6R-7TxuYV5g=HeB1HUe=xEsbjr2g@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