Postgres Pro
Downloads
Home   >   mailing lists

Thread: Pg_repack

Pg_repack

From
Sathish Reddy
Date:
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 

Re: Pg_repack

From
hubert depesz lubaczewski
Date:
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

Re: Pg_repack

From
Sathish Reddy
Date:

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 <depesz@depesz.com> 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

Re: Pg_repack

From
hubert depesz lubaczewski
Date:
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

Re: Pg_repack

From
Kashif Zeeshan
Date:
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 <sathishreddy.postgresql@gmail.com> 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 

Re: Pg_repack

From
Sathish Reddy
Date:

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 <ramkly@yahoo.com> 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 <sathishreddy.postgresql@gmail.com> 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 <depesz@depesz.com> 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