TOAST Table / Dead Tuples / Free Pages

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 PM Shenavai, Manuel <manuel.shenavai@sap.com>
wrote:

Hi Shenavai,

Here are some more options..

VACUUM <your tablename>

VACUUM FULL <your tablename>

You may also reindex to reclaim space

REINDEX TABLE <your tablename>

REINDEX INDEX <indexname>

Regards,
Muhammad Ikram
Bitnine

On Thu, Jun 13, 2024 at 1:09 PM Kashif Zeeshan <kashi.zeeshan@gmail.com>
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 PM Shenavai, Manuel <manuel.shenavai@sap.com>
wrote:

Hi everyone,

I created a simple scenario to understand the handling of TOASTs
<https://www.postgresql.org/docs/current/storage-toast.html&gt;: 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’t want to use VACUUM FULL due to the
exclusive lock.

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

Thanks in advance &

Best regards,

Manuel

--
Muhammad Ikram

On 6/13/24 01:08, 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.

From OP:

"I don’t want to use VACUUM FULL due to the exclusive lock."

From here

https://www.postgresql.org/docs/current/sql-cluster.html

"When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired
on it. This prevents any other database operations (both reads and
writes) from operating on the table until the CLUSTER is finished."

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 PM Shenavai, Manuel
<manuel.shenavai@sap.com <mailto:manuel.shenavai@sap.com>> wrote:

Hi everyone,____

__ __

I created a simple scenario to understand the handling of TOASTs
<https://www.postgresql.org/docs/current/storage-toast.html&gt;: 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’t want to use VACUUM FULL
due to the exclusive lock.____

__ __

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

__ __

Thanks in advance &____

Best regards,____

Manuel____

__ __

--
Adrian Klaver
adrian.klaver@aklaver.com