public inbox for [email protected]
help / color / mirror / Atom feedRe: TOAST Table / Dead Tuples / Free Pages
2+ messages / 2 participants
[nested] [flat]
* Re: TOAST Table / Dead Tuples / Free Pages
@ 2024-06-13 08:08 Kashif Zeeshan <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Kashif Zeeshan @ 2024-06-13 08:08 UTC (permalink / raw)
To: Shenavai, Manuel <[email protected]>; +Cc: pgsql-general
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 <[email protected]>
wrote:
> Hi everyone,
>
>
>
> I created a simple scenario to understand the handling of TOASTs
> <https://www.postgresql.org/docs/current/storage-toast.html;: 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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: TOAST Table / Dead Tuples / Free Pages
@ 2024-06-13 14:38 Adrian Klaver <[email protected]>
parent: Kashif Zeeshan <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-06-13 14:38 UTC (permalink / raw)
To: Kashif Zeeshan <[email protected]>; Shenavai, Manuel <[email protected]>; +Cc: pgsql-general
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
> <[email protected] <mailto:[email protected]>> wrote:
>
> Hi everyone,____
>
> __ __
>
> I created a simple scenario to understand the handling of TOASTs
> <https://www.postgresql.org/docs/current/storage-toast.html;: 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
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-13 14:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-13 08:08 Re: TOAST Table / Dead Tuples / Free Pages Kashif Zeeshan <[email protected]>
2024-06-13 14:38 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox