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 1sHg2B-00A2Cv-D8 for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 08:42:15 +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 1sHg29-00Flej-4v for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 08:42:14 +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 1sHg28-00Fleb-NI for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 08:42:13 +0000 Received: from mail-lj1-x22b.google.com ([2a00:1450:4864:20::22b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHg26-001REW-My for pgsql-general@postgresql.org; Thu, 13 Jun 2024 08:42:12 +0000 Received: by mail-lj1-x22b.google.com with SMTP id 38308e7fff4ca-2eabd22d441so10187651fa.2 for ; Thu, 13 Jun 2024 01:42:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718268129; x=1718872929; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=/JAQ1t7beRfYAr/yzNnqj3+HGgjBzqr35nOqm0GvKaY=; b=EzKxhOipqXjXtPIPaiCYTp2w/HtqTW0n8IXX3BTxuwpcDXiUttUHTCwZdf3WsRSO2i trDlE7AO0UBs1M58FqejrUiDYKqd9EX/3PVR9Kplk27/LxQv7I8diYD0DCxv6ZWvKJo2 cHqurluQzKBcSCq0PwKU4JuGU0FP5E7UY6CRBnvtCAdBADjn6IbU7ilG4nQr5kuH7og+ BCX9zqB8ftIFuhgDTSSf8vPSoXhXfi8knaIHu7Q8gs8IQGo8iViUB/VOEuZP82xMvR82 oXq569hG3yVbbbcZipeJe1XwA5rYCl3WDSJlAWEmkYvjvwEbVKCpIRDe5rd1CYZZsQUw ubNw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718268129; x=1718872929; h=cc: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=/JAQ1t7beRfYAr/yzNnqj3+HGgjBzqr35nOqm0GvKaY=; b=DAIsCgj0W7QSgINtsts5yUMPbWoV1GTa8boEatdWf8libhp16Zi8t5yaMmbjjQfFJ9 B9xGquMI5Gy7wslxcFjUW6nqzCSxDoqQkjHabNeFSLh5L8Xf1an0/CKLaPVQ42kAQO8q zeeRI7ACFLrNPi7/5YU56JwCZCfgVREI5ihj+OY90sWhtDQGz1AZBhpA8KQbLERwwE/2 IF1PGD3TI15VSPIMwPTtcFj1l1zKCaNGk39EOONkK1Mo1zPsyMeDreOGSg5gIktAE2AD sdclXSnDiJK8mYFFo9sNmr4XONltH+6YIw5agKdKN0TpbqybQ9nzAZNUsnP1LRqpRK1r CKYA== X-Forwarded-Encrypted: i=1; AJvYcCWs4e+bpBIRopaJMMcOb8GUUrJwGi4dT/2bpKmmVuaaKwAveUvGigSSdLTkNAlIVKBzizoMj6uDPvBvHHFzU2OteQouF8kq46652Hum X-Gm-Message-State: AOJu0YyAimXcMwd72/LKj3nCx0ZhEPt5lP59JMR3fQIs1GgSKcCSkXt2 UjERKgNE9lqryKtn/KPHC1B1jBJmhSW16hbCeSYX1RqZGSoRNP9ywl3hxekJmLCoRviftotcH1J UNxjIQsnGV1XmtehfyUHC3EcSy5KFKeMHzxs= X-Google-Smtp-Source: AGHT+IFJGgFw9FSwGwk0+NUfpHQuToC2TinuFjxy/m+ELo7u1fpLWslkMmPNT9NWOP9foGqbaBtSMrtgZlO6IR4ARCo= X-Received: by 2002:a2e:b5ac:0:b0:2eb:e266:f9ba with SMTP id 38308e7fff4ca-2ebfc9bf1d0mr30243721fa.23.1718268128918; Thu, 13 Jun 2024 01:42:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Ikram Date: Thu, 13 Jun 2024 13:41:52 +0500 Message-ID: Subject: Re: TOAST Table / Dead Tuples / Free Pages To: Kashif Zeeshan Cc: "Shenavai, Manuel" , pgsql-general Content-Type: multipart/alternative; boundary="00000000000074038e061ac179ce" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000074038e061ac179ce Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Shenavai, Here are some more options.. VACUUM VACUUM FULL You may also reindex to reclaim space REINDEX TABLE REINDEX INDEX Regards, Muhammad Ikram Bitnine On Thu, Jun 13, 2024 at 1:09=E2=80=AFPM Kashif Zeeshan wrote: > Hi > > You can use the CLUSTER command, which will physically reorder the table > based on index, effectively reducing the size of the table without using > VACUUM. > > CLUSTER your_table USING your_index; > > Or you can use the pg_repack extension as well. > > pg_repack -d your_database -t your_table > > Regards > Kashif Zeeshan > > > > On Thu, Jun 13, 2024 at 12:55=E2=80=AFPM Shenavai, Manuel > wrote: > >> Hi everyone, >> >> >> >> I created a simple scenario to understand the handling of TOASTs >> : There is >> an empty database with a single table and record. The single record gets >> updated multiple times with 10MB (bytea column). I can see that the >> table/toasttable size is growing (500MB). >> >> >> >> Now I tried to find a way to get the DB size down again (it should be >> around 10MB instead of 500MB). I don=E2=80=99t want to use VACUUM FULL d= ue to the >> exclusive lock. >> >> >> >> Is there any way to remove the dead tuples and free the pages? >> >> >> >> Thanks in advance & >> >> Best regards, >> >> Manuel >> >> >> > --=20 Muhammad Ikram --00000000000074038e061ac179ce Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


Hi Shenavai,

Here are some more options..

VACUUM <y= our tablename>

VACUUM=C2=A0 FULL <your = tablename>

You may also reindex to reclaim=C2= =A0space

REINDEX TABLE <your tablename>

REINDEX INDEX <indexname>
=

Regards,
Muhammad Ikram
B= itnine=C2=A0

On Thu, Jun 13, 2024 at 1:09=E2=80=AFPM Kashif Zeeshan &l= t;kashi.zeeshan@gmail.com>= ; wrote:
Hi

You can use the CLUSTER command,= which will physically reorder the table based on index, effectively reduci= ng the size of the table without using VACUUM.

CLU= STER your_table USING your_index;

Or you can use t= he pg_repack extension as well.

pg_repack -d your_= database -t your_table

Regards
Kashi= f Zeeshan



On Thu, Jun 13, 2024 at 12= :55=E2=80=AFPM Shenavai, Manuel <manuel.shenavai@sap.com> wrote:

Hi everyone,

=C2=A0

I created a simple scenario to = understand the handling of TOASTs: There is an empty database with a single table an= d record. The single record gets updated multiple times with 10MB (bytea co= lumn). I can see that the table/toasttable size is growing (500MB).

=C2=A0

Now I tried to find a way to ge= t the DB size down again (it should be around 10MB instead of 500MB). I don= =E2=80=99t want to use VACUUM FULL due to the exclusive lock.=

=C2=A0

Is there any way to remove the = dead tuples and free the pages?

=C2=A0

Thanks in advance &<= u>

Best regards,

Manuel

=C2=A0



--
Muhammad Ikram

--00000000000074038e061ac179ce--