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 1sKLFU-009eCz-8p for pgsql-general@arkaria.postgresql.org; Thu, 20 Jun 2024 17:07:00 +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 1sKLFR-00BdjF-Vf for pgsql-general@arkaria.postgresql.org; Thu, 20 Jun 2024 17:06:58 +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 1sKLFR-00Bdj5-I6 for pgsql-general@lists.postgresql.org; Thu, 20 Jun 2024 17:06:58 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sKLFQ-002gIb-8v for pgsql-general@postgresql.org; Thu, 20 Jun 2024 17:06:57 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-24c9f892aeaso604918fac.2 for ; Thu, 20 Jun 2024 10:06:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718903214; x=1719508014; darn=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=dIZZ/xTOzH7TYqLSgUkXSEaAp6QTJipXGRJ6hqV6BAM=; b=ctRDXczyDuD9B4E2+mo+6IIUszAEcl3f4zfZ1V8dxTKGenctfIR5w47QgzQyQ6jmzR 228D8sBLReE8xJrW1XgxxH4gYxnAl8VrSU96IGLoYkoQfASlXoaLjrp0aUbYAuVAcP8Q yrOeQiB6Zd+43SJGbiXhciDpZkZs3CA5ncXVn67MBtZL2nL3QaqwI9BLNoiJ8PeMBNOr nuiyExQQNW1BO9dSb5e+leDwC65oHLaw52oe5dNhwMocyYWx6XhW7Le4L7mhtm44wMiv d0TX4mTBqYr800E9ZL80pq0q5RLPnZzh0av8KxM7xPkjEnIlKAcEcnd6xhMoR6eZMTNg BGCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718903214; x=1719508014; 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=dIZZ/xTOzH7TYqLSgUkXSEaAp6QTJipXGRJ6hqV6BAM=; b=cz/PfvTH6opYmG8nPbiqEtpShHtoRhAI+9bjhWVT9fytrI27qdRjIS8/OAoe+uK0KN h+Fo8OeCBkJdYl/YpiE5T2wHmaxYpQ3V1G4q6fGApQZv/2E2vJteFhtF/el4f0OyjV15 zMEM4P+T4iJsp4bphSSMT6oOdRHr24JqkGgmjkF9XgNXNf9zjs9lvIpoaM8YuBM9cS1a c/bo1FLyYCBLLVzkFNVKeuEg8elpzwaihzw02JscBhsPCdiYjEt/A1s5thM4BlBdSdPK JSt9rRrO8Dw2YzMZF+GV3QUTtlbbSQjOBCvW7F3R76CHNH5GVTDN8Lm+tn2zudtvX+mr v4Iw== X-Gm-Message-State: AOJu0YwCE2Z/6wzwdxdi6FXLXWVI4Nc+JYfbGRRhallI6KdmvBL0Hd+1 4rVOft/whuosJd5Ll8MeBVuySzVY3kGTu/euAmA02WJq4W2IByfquUhdEWJkd4GLp/IjreBqMMR Bgg4r6qHOpm8vFWnTlWPSPtdzX2jr9YUi X-Google-Smtp-Source: AGHT+IG/LdlbC2YpdrDFrkjgd/3rTCg/a/piULPUjiVT8GHd/oR03eiAkvJfUhQMBuOfQHxKpCsZUM44S3yR/wTLZ4c= X-Received: by 2002:a05:6870:1612:b0:25c:b7f4:39ad with SMTP id 586e51a60fabf-25cb7f4d95dmr3173239fac.58.1718903213939; Thu, 20 Jun 2024 10:06:53 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 20 Jun 2024 13:06:42 -0400 Message-ID: Subject: Re: Autovacuum, dead tuples and bloat To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000007863b9061b5557a9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007863b9061b5557a9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jun 20, 2024 at 12:47=E2=80=AFPM Shenavai, Manuel 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=3D# ALTER TABLE t1 SET (autovacuum_enabled =3D off); db=3D# ALTER TABLE t2 SET (autovacuum_enabled =3D off); db=3D# ALTER TABLE t3 SET (autovacuum_enabled =3D off); db=3D# DELETE FROM t1 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90 DAY'); db=3D# DELETE FROM t2 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90 DAY'); db=3D# DELETE FROM t3 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90 DAY'); $ vacuumdb --jobs=3D3 -t t1 -t t2 -t t3 db=3D# ALTER TABLE t1 SET (autovacuum_enabled =3D on); db=3D# ALTER TABLE t2 SET (autovacuum_enabled =3D on); db=3D# ALTER TABLE t3 SET (autovacuum_enabled =3D on); pgstattuple shows that that free percentage stays pretty constant. That seems to be what you're asking about. --0000000000007863b9061b5557a9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jun 20, 2024 at 12:47=E2=80=AFPM = Shenavai, Manuel <manuel.shen= avai@sap.com> wrote:

Hi everyone,

=C2=A0

we can see in our database, tha= t the DB is 200GB of size, with 99% bloat. After vacuum full the DB decreas= es to 2GB.

DB total size: 200GB<= /u>

DB bloat: 198 GB<= /span>

DB non-bloat: 2GB=

=C2=A0

We further see, that during bul= k 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.

=C2=A0

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).

<= /div>

That's also my understanding of h= ow vacuum works.
=C2=A0
Note: I disable autovacuum befo= re bulk modifications, manually VACUUM ANALYZE and then reenable=C2=A0autov= acuum.=C2=A0 That way, autovacuum doesn't jump in the middle of what I&= #39;m doing.

=C2=A0How could I ve= rify my assumption?

=C2=A0

I think of two possibilities:

  1. My assumption is wrong and for some reason the dea= d tuples are not cleaned so that the space cannot be reused
  2. The bulk-update indeed exceeds the curre= nt DB size. (Then the growth is expected).

=C2=A0

Can you help me to verify these= assumptions? Are there any statistics available that could help me with my= verification?

=C2=A0
I've got a weekly process that deletes all records older than N days= from a set of tables.
db=3D# ALTER= TABLE t1 SET (autovacuum_enabled =3D off);
db=3D#=C2=A0ALTER TABLE t2 SET (autovacuum_e= nabled =3D off);
db=3D#=C2=A0ALTER TABLE t3 SET (autovacuum_enabled =3D off);
db=3D#=C2=A0DELETE= FROM t1 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL '90 DAY= 9;);
db=3D#=C2=A0DELETE FROM t2 WHERE created_on < (CURRENT_TIMESTAMP - INTERVAL = 9;90 DAY');
db=3D#=C2=A0<= font face=3D"monospace">DELETE FROM t3 WHERE created_on < (CURRENT_TIMES= TAMP - INTERVAL '90 DAY');
$ vacuumdb --jobs=3D3 -t t1 -t t2 -t t3
<= span style=3D"font-family:monospace">db=3D#=C2=A0ALTER TABLE t1 SET (auto= vacuum_enabled =3D on);
db=3D#=C2=A0ALTER TABLE t2 SET (autovacuum_enabled =3D on);<= /font>
db=3D#=C2=A0A= LTER TABLE t3 SET (autovacuum_enabled =3D on);

<= div>pgstattuple shows that that free percentage stays pretty constant.=C2= =A0 That seems to be what you're asking about.



--0000000000007863b9061b5557a9--