public inbox for [email protected]
help / color / mirror / Atom feedPg_repack
14+ messages / 8 participants
[nested] [flat]
* Pg_repack
@ 2024-08-06 11:08 Sathish Reddy <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:08 UTC (permalink / raw)
To: [email protected]; [email protected]; Keith <[email protected]>; khan Affan <[email protected]>
Hi
We planning to create store procedure (function) in postgres database to
run pg_repack on removing bloating of table or index by using within
postgres instance.
Please help me on details on steps with example for same.
Thanks
Sathishreddy
^ permalink raw reply [nested|flat] 14+ messages in thread
* Pg_repack
@ 2024-08-06 11:09 Sathish Reddy <[email protected]>
2024-08-06 11:15 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
2024-08-06 11:24 ` Re: Pg_repack Kashif Zeeshan <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:09 UTC (permalink / raw)
To: [email protected]
Hi
We planning to create store procedure (function) in postgres database to
run pg_repack on removing bloating of table or index by using within
postgres instance.
Please help me on details on steps with example for same.
Thanks
Sathishreddy
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-06 11:15 ` hubert depesz lubaczewski <[email protected]>
2024-08-06 11:20 ` Re: Pg_repack Sathish Reddy <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: hubert depesz lubaczewski @ 2024-08-06 11:15 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]
On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
> We planning to create store procedure (function) in postgres database to
> run pg_repack on removing bloating of table or index by using within
> postgres instance.
> Please help me on details on steps with example for same.
That will be impossible and/or hard.
The problem is that stored procedure/functions runs in database. and
pg_repack is external program, that *does stuff in database*, but it not
*all* in database.
It's kinda as if you wanted to make stored procedure to run photoshop.
You can kinda work around it by using some PL/* language that allows
external program execution, but it is extremely unlikely to do what
you'd think it will do.
Best regards,
depesz
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:15 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
@ 2024-08-06 11:20 ` Sathish Reddy <[email protected]>
2024-08-06 11:22 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
2024-08-06 11:24 ` Re: Pg_repack abbas alizadeh <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:20 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
Thanks for the information.please provide me solution on clearing bloating
on table or index bloating with out blocking any user and we are not run
any vaccum full .freeze, and reindex and also analized ...with out these
provide me perfect solution to run and remove bloat instance level with in
postgres..
Thanks
Sathishreddy
On Tue, Aug 6, 2024, 4:45 PM hubert depesz lubaczewski <[email protected]>
wrote:
> On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
> > We planning to create store procedure (function) in postgres database
> to
> > run pg_repack on removing bloating of table or index by using within
> > postgres instance.
> > Please help me on details on steps with example for same.
>
> That will be impossible and/or hard.
>
> The problem is that stored procedure/functions runs in database. and
> pg_repack is external program, that *does stuff in database*, but it not
> *all* in database.
>
> It's kinda as if you wanted to make stored procedure to run photoshop.
>
> You can kinda work around it by using some PL/* language that allows
> external program execution, but it is extremely unlikely to do what
> you'd think it will do.
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:15 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
2024-08-06 11:20 ` Re: Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-06 11:22 ` hubert depesz lubaczewski <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: hubert depesz lubaczewski @ 2024-08-06 11:22 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]
On Tue, Aug 06, 2024 at 04:50:33PM +0530, Sathish Reddy wrote:
> Thanks for the information.please provide me solution on clearing bloating
> on table or index bloating with out blocking any user and we are not run
> any vaccum full .freeze, and reindex and also analized ...with out these
> provide me perfect solution to run and remove bloat instance level with in
> postgres..
1. Configure autovacuum properly, so the problem doesn't happen.
2. If need is - run manual vacuum
3. If you really have to, run pg_repack - but this is not a thing that
one calls *from within pg* - you run it on some
server/computer/whatever, and pg_repack connects to database, and
does its magic.
Best regards,
depesz
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:15 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
2024-08-06 11:20 ` Re: Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-06 11:24 ` abbas alizadeh <[email protected]>
2024-08-06 11:32 ` Re: Pg_repack Sathish Reddy <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: abbas alizadeh @ 2024-08-06 11:24 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]; [email protected]
--Apple-Mail-8D50908C-B986-4154-90B3-A45EDB2F28CC
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi<div>Why you don=E2=80=99t use pg_squeeze=
?</div><div>It has its own scheduling to remove bloating.</div><div><br id=3D=
"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Regards<div>Abbas</div></=
div><div dir=3D"ltr"><br><blockquote type=3D"cite">On 6 Aug 2024, at 2:51=E2=
=80=AFPM, Sathish Reddy <[email protected]> wrote:<br>=
<br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<=
p dir=3D"ltr">Thanks for the information.please provide me solution on clear=
ing bloating on table or index bloating with out blocking any user and we ar=
e not run any vaccum full .freeze, and reindex and also analized ...with out=
these provide me perfect solution to run and remove bloat instance level wi=
th in postgres..<br></p>
<p dir=3D"ltr">Thanks <br>
Sathishreddy </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue,=
Aug 6, 2024, 4:45=E2=80=AFPM hubert depesz lubaczewski <<a href=3D"mailt=
o:[email protected]">[email protected]</a>> wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;p=
adding-left:1ex">On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wro=
te:<br>
> We planning to create store procedure (function) in postgre=
s database to<br>
> run pg_repack on removing bloating of table or index by using within<br=
>
> postgres instance.<br>
> Please help me on details on steps with example for s=
ame.<br>
<br>
That will be impossible and/or hard.<br>
<br>
The problem is that stored procedure/functions runs in database. and<br>
pg_repack is external program, that *does stuff in database*, but it not<br>=
*all* in database.<br>
<br>
It's kinda as if you wanted to make stored procedure to run photoshop.<br>
<br>
You can kinda work around it by using some PL/* language that allows<br>
external program execution, but it is extremely unlikely to do what<br>
you'd think it will do.<br>
<br>
Best regards,<br>
<br>
depesz<br>
<br>
</blockquote></div>
</div></blockquote></div></body></html>=
--Apple-Mail-8D50908C-B986-4154-90B3-A45EDB2F28CC--
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:15 ` Re: Pg_repack hubert depesz lubaczewski <[email protected]>
2024-08-06 11:20 ` Re: Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:24 ` Re: Pg_repack abbas alizadeh <[email protected]>
@ 2024-08-06 11:32 ` Sathish Reddy <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:32 UTC (permalink / raw)
To: abbas alizadeh <[email protected]>; +Cc: [email protected]; [email protected]
Thanks for the tip! We are trying in RDS postgres database environment and
also we are trying to run as instance level as like functional
Thanks
Sathishreddy
On Tue, Aug 6, 2024, 4:56 PM abbas alizadeh <[email protected]> wrote:
> Hi
> Why you don’t use pg_squeeze?
> It has its own scheduling to remove bloating.
>
> Regards
> Abbas
>
> On 6 Aug 2024, at 2:51 PM, Sathish Reddy <
> [email protected]> wrote:
>
>
>
> Thanks for the information.please provide me solution on clearing bloating
> on table or index bloating with out blocking any user and we are not run
> any vaccum full .freeze, and reindex and also analized ...with out these
> provide me perfect solution to run and remove bloat instance level with in
> postgres..
>
> Thanks
> Sathishreddy
>
> On Tue, Aug 6, 2024, 4:45 PM hubert depesz lubaczewski <[email protected]>
> wrote:
>
>> On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
>> > We planning to create store procedure (function) in postgres database
>> to
>> > run pg_repack on removing bloating of table or index by using within
>> > postgres instance.
>> > Please help me on details on steps with example for same.
>>
>> That will be impossible and/or hard.
>>
>> The problem is that stored procedure/functions runs in database. and
>> pg_repack is external program, that *does stuff in database*, but it not
>> *all* in database.
>>
>> It's kinda as if you wanted to make stored procedure to run photoshop.
>>
>> You can kinda work around it by using some PL/* language that allows
>> external program execution, but it is extremely unlikely to do what
>> you'd think it will do.
>>
>> Best regards,
>>
>> depesz
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-06 11:24 ` Kashif Zeeshan <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Kashif Zeeshan @ 2024-08-06 11:24 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]
Hi Sathish
It's better to script it using any scripting language e.g. SHELL Scripting
as PL/pgSQL etc don't allow accessing Utilities from stored procedures.
Thanks
Kashif Zeeshan
On Tue, Aug 6, 2024 at 4:10 PM Sathish Reddy <
[email protected]> wrote:
> Hi
> We planning to create store procedure (function) in postgres database to
> run pg_repack on removing bloating of table or index by using within
> postgres instance.
>
> Please help me on details on steps with example for same.
>
>
> Thanks
> Sathishreddy
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Pg_repack
@ 2024-08-12 11:47 Sathish Reddy <[email protected]>
2024-08-12 15:48 ` Re: Pg_repack Alvaro Herrera <[email protected]>
2024-08-13 08:15 ` Re: Pg_repack Muhammad Imtiaz <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Sathish Reddy @ 2024-08-12 11:47 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-12 15:48 ` Alvaro Herrera <[email protected]>
2024-08-12 16:06 ` Re: Pg_repack Ron Johnson <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Alvaro Herrera @ 2024-08-12 15:48 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]
On 2024-Aug-12, Sathish Reddy wrote:
> 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.
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
2024-08-12 15:48 ` Re: Pg_repack Alvaro Herrera <[email protected]>
@ 2024-08-12 16:06 ` Ron Johnson <[email protected]>
2024-08-12 17:54 ` Re: Pg_repack Rui DeSousa <[email protected]>
2024-08-12 17:55 ` Re: Pg_repack Rui DeSousa <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Ron Johnson @ 2024-08-12 16:06 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Mon, Aug 12, 2024 at 11:49 AM Alvaro Herrera <[email protected]>
wrote:
> On 2024-Aug-12, Sathish Reddy wrote:
>
> > 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.
>
> 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.
>
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!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
2024-08-12 15:48 ` Re: Pg_repack Alvaro Herrera <[email protected]>
2024-08-12 16:06 ` Re: Pg_repack Ron Johnson <[email protected]>
@ 2024-08-12 17:54 ` Rui DeSousa <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Rui DeSousa @ 2024-08-12 17:54 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Aug 12, 2024, at 12:06 PM, Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
2024-08-12 15:48 ` Re: Pg_repack Alvaro Herrera <[email protected]>
2024-08-12 16:06 ` Re: Pg_repack Ron Johnson <[email protected]>
@ 2024-08-12 17:55 ` Rui DeSousa <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Rui DeSousa @ 2024-08-12 17:55 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Aug 12, 2024, at 12:06 PM, Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Pg_repack
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
@ 2024-08-13 08:15 ` Muhammad Imtiaz <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Muhammad Imtiaz @ 2024-08-13 08:15 UTC (permalink / raw)
To: Sathish Reddy <[email protected]>; +Cc: [email protected]
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 protected]> wrote:
> 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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-08-13 08:15 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-06 11:08 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:09 Pg_repack Sathish Reddy <[email protected]>
2024-08-06 11:15 ` hubert depesz lubaczewski <[email protected]>
2024-08-06 11:20 ` Sathish Reddy <[email protected]>
2024-08-06 11:22 ` hubert depesz lubaczewski <[email protected]>
2024-08-06 11:24 ` abbas alizadeh <[email protected]>
2024-08-06 11:32 ` Sathish Reddy <[email protected]>
2024-08-06 11:24 ` Kashif Zeeshan <[email protected]>
2024-08-12 11:47 Pg_repack Sathish Reddy <[email protected]>
2024-08-12 15:48 ` Alvaro Herrera <[email protected]>
2024-08-12 16:06 ` Ron Johnson <[email protected]>
2024-08-12 17:54 ` Rui DeSousa <[email protected]>
2024-08-12 17:55 ` Rui DeSousa <[email protected]>
2024-08-13 08:15 ` Muhammad Imtiaz <[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