Subject: Pg_repack

From: Sathish Reddy <[email-masked]>
Date: Mon, 12 Aug 2024 17:17:52 +0530

Hi
  We have configure pg_repack on database.when we ran pg_repak it is using
temporary table on repack once repack done it is going to swap temporary
table to original .on these case it is genarate huse wal files and it
getting size increase be end .

    We need help on these instead of using temporary table can we use unlog
table on reduce these wal case.

Thanks
Sathishreddy

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 12 Aug 2024 11:48:44 -0400

> table on reduce these wal case.

I bet you'll find that pg_squeeze gives you better characteristics on
those aspects.  In any case, it's better if you can find a way to avoid
running either of these tools in a regular manner, and instead treat
them as if they were an emergency solution only, and rely on a better
configured autovacuum to avoid having to schedule them regularly.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

---

From: Ron Johnson <[email-masked]>
Date: Mon, 12 Aug 2024 12:06:27 -0400

> configured autovacuum to avoid having to schedule them regularly.

But pg_repack is just a better VACUUM FULL, and VACUUM FULL has to be
better than autovacuum because it *fully* vacuums a table.

Right? /s

-- 
Death to America, and butter sauce.
Iraq lobster!

---

From: Rui DeSousa <[email-masked]>
Date: Mon, 12 Aug 2024 13:54:50 -0400

> On Aug 12, 2024, at 12:06 PM, Ron Johnson <[email-masked]> wrote:
> 
> But pg_repack is just a better VACUUM FULL, and VACUUM FULL has to be better than autovacuum because it fully vacuums a table.
> 

No.

Vacuum — actually vacuums by removing dead tuples that are no longer needed, freezing tuples, etc. The removal of dead tuples frees space on the given page and it also truncates the fully empty pages that are located at the end of the file if it can.

Vacuum FULL — is something completely different.  It rebuilds the entire table thus it coalesces all free space and by proxy does the same as vacuum (removing dead tuples that are no longer needed).
	— It does this by creating a new table and then swapping in the new table when; regardless of the number of dead tuples.

Vacuum FULL should not be run on a regular basis.

---

From: Rui DeSousa <[email-masked]>
Date: Mon, 12 Aug 2024 13:55:47 -0400

No.

---

From: Muhammad Imtiaz <[email-masked]>
Date: Tue, 13 Aug 2024 13:15:44 +0500

Hi,

Pg_repack doesn’t use unlogged tables, but you can minimize WAL file size
by enabling wal_compression in postgresql.conf. Additionally, you can
fine-tune wal_buffers, checkpoint_timeout, and checkpoint_completion_target
to better manage WAL file size.

Regards,
Muhammad Imtiaz

On Mon, Aug 12, 2024 at 4:48 PM Sathish Reddy <
[email-masked]> wrote:

>     We need help on these instead of using temporary table can we use
> unlog table on reduce these wal case.
>
>
> Thanks
> Sathishreddy
>