Обсуждение: Table and Index bloating

Hi All, Could someone please provide the query/queries to find table and Index bloating in PgSql 9.3 onwards? and are there any other maintenance tasks inPgSql except vacuum & analyse. Regards Vikas

Hi,

You can track that with some monitoring tools like check_pgactivity. The
queries can be found here :
https://github.com/ioguix/pgsql-bloat-estimation.

Tables :
https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
Indexes :
https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql

Backups should also be part of your maintenance plan.
Vacuum/Analyse/Reindex is already a good basis.


Kind regards


On 11/27/2017 10:10 AM, Vikas Sharma wrote:
> Hi All,
>
> Could someone please provide the query/queries  to find table and
> Index bloating in PgSql 9.3 onwards? 
> and are there any other maintenance tasks inPgSql except vacuum & analyse.
>
> Regards
> Vikas

--
Stefan FERCOT
http://dalibo.com - http://dalibo.org

Hi.

You can consider use pg_repack to remove bloat
(http://reorg.github.io/pg_repack/)

Best regards.

Carlos Martinez


On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot <stefan.fercot@dalibo.com> wrote:
> Hi,
>
> You can track that with some monitoring tools like check_pgactivity. The
> queries can be found here :
> https://github.com/ioguix/pgsql-bloat-estimation.
>
> Tables :
> https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
> Indexes :
> https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql
>
> Backups should also be part of your maintenance plan.
> Vacuum/Analyse/Reindex is already a good basis.
>
>
> Kind regards
>
>
> On 11/27/2017 10:10 AM, Vikas Sharma wrote:
>> Hi All,
>>
>> Could someone please provide the query/queries  to find table and
>> Index bloating in PgSql 9.3 onwards?
>> and are there any other maintenance tasks inPgSql except vacuum & analyse.
>>
>> Regards
>> Vikas
>
> --
> Stefan FERCOT
> http://dalibo.com - http://dalibo.org
>
>

Thank you Stefan for the queries. Thanks you Carlos for pg_repack suggestion, Can we use pg_repack with standard community edition of postgresql? and can it be used on the databases with streaming replication? Regards Vikas Sharma On 27 November 2017 at 14:58, Carlos Martinez wrote: > Hi. > > You can consider use pg_repack to remove bloat > (http://reorg.github.io/pg_repack/) > > Best regards. > > Carlos Martinez > > > On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot > wrote: > > Hi, > > > > You can track that with some monitoring tools like check_pgactivity. The > > queries can be found here : > > https://github.com/ioguix/pgsql-bloat-estimation. > > > > Tables : > > https://github.com/ioguix/pgsql-bloat-estimation/blob/ > master/table/table_bloat.sql > > Indexes : > > https://github.com/ioguix/pgsql-bloat-estimation/blob/ > master/btree/btree_bloat.sql > > > > Backups should also be part of your maintenance plan. > > Vacuum/Analyse/Reindex is already a good basis. > > > > > > Kind regards > > > > > > On 11/27/2017 10:10 AM, Vikas Sharma wrote: > >> Hi All, > >> > >> Could someone please provide the query/queries to find table and > >> Index bloating in PgSql 9.3 onwards? > >> and are there any other maintenance tasks inPgSql except vacuum & > analyse. > >> > >> Regards > >> Vikas > > > > -- > > Stefan FERCOT > > http://dalibo.com - http://dalibo.org > > > > > >

Hi.

Can we use pg_repack with standard community edition of postgresql? Yes.
and can it be used on the databases with streaming replication? Yes.

Best regards.

Carlos Martinez



On Mon, Nov 27, 2017 at 10:08 AM, Vikas Sharma <shavikas@gmail.com> wrote:
> Thank you Stefan for the queries.
>
> Thanks you Carlos for pg_repack suggestion, Can we use pg_repack with
> standard community edition of postgresql? and can it be used on the
> databases with streaming replication?
>
> Regards
> Vikas Sharma
>
> On 27 November 2017 at 14:58, Carlos Martinez <camarti@gmail.com> wrote:
>>
>> Hi.
>>
>> You can consider use pg_repack to remove bloat
>> (http://reorg.github.io/pg_repack/)
>>
>> Best regards.
>>
>> Carlos Martinez
>>
>>
>> On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot <stefan.fercot@dalibo.com>
>> wrote:
>> > Hi,
>> >
>> > You can track that with some monitoring tools like check_pgactivity. The
>> > queries can be found here :
>> > https://github.com/ioguix/pgsql-bloat-estimation.
>> >
>> > Tables :
>> >
>> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql
>> > Indexes :
>> >
>> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql
>> >
>> > Backups should also be part of your maintenance plan.
>> > Vacuum/Analyse/Reindex is already a good basis.
>> >
>> >
>> > Kind regards
>> >
>> >
>> > On 11/27/2017 10:10 AM, Vikas Sharma wrote:
>> >> Hi All,
>> >>
>> >> Could someone please provide the query/queries  to find table and
>> >> Index bloating in PgSql 9.3 onwards?
>> >> and are there any other maintenance tasks inPgSql except vacuum &
>> >> analyse.
>> >>
>> >> Regards
>> >> Vikas
>> >
>> > --
>> > Stefan FERCOT
>> > http://dalibo.com - http://dalibo.org
>> >
>> >
>>
>

hello, https://www.postgresql.org/docs/9.3/static/pgstattuple.html some enhancements onward you can try create index concurrent to maintain your index to achieve the goal of reindex.  Steven  -------- 原始訊息 --------自： Stefan Fercot 日期： 2017/11/27 17:17 (GMT+08:00) 至： Vikas Sharma 副本： pgsql-general@postgresql.org 主旨： Re: Table and Index bloating Hi, You can track that with some monitoring tools like check_pgactivity. The queries can be found here : https://github.com/ioguix/pgsql-bloat-estimation. Tables : https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql Indexes : https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql Backups should also be part of your maintenance plan. Vacuum/Analyse/Reindex is already a good basis. Kind regards On 11/27/2017 10:10 AM, Vikas Sharma wrote: > Hi All, > > Could someone please provide the query/queries  to find table and > Index bloating in PgSql 9.3 onwards?  > and are there any other maintenance tasks inPgSql except vacuum & analyse. > > Regards > Vikas -- Stefan FERCOT http://dalibo.com - http://dalibo.org

After reading the web pages, index part should be succeeded, but tables may not fit if they got foreign key relations.  steven  -------- 原始訊息 --------自： Carlos Martinez 日期： 2017/11/27 23:13 (GMT+08:00) 至： pgsql-general@postgresql.org 主旨： Re: Table and Index bloating Hi. Can we use pg_repack with standard community edition of postgresql? Yes. and can it be used on the databases with streaming replication? Yes. Best regards. Carlos Martinez On Mon, Nov 27, 2017 at 10:08 AM, Vikas Sharma wrote: > Thank you Stefan for the queries. > > Thanks you Carlos for pg_repack suggestion, Can we use pg_repack with > standard community edition of postgresql? and can it be used on the > databases with streaming replication? > > Regards > Vikas Sharma > > On 27 November 2017 at 14:58, Carlos Martinez wrote: >> >> Hi. >> >> You can consider use pg_repack to remove bloat >> (http://reorg.github.io/pg_repack/) >> >> Best regards. >> >> Carlos Martinez >> >> >> On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot >> wrote: >> > Hi, >> > >> > You can track that with some monitoring tools like check_pgactivity. The >> > queries can be found here : >> > https://github.com/ioguix/pgsql-bloat-estimation. >> > >> > Tables : >> > >> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql >> > Indexes : >> > >> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql >> > >> > Backups should also be part of your maintenance plan. >> > Vacuum/Analyse/Reindex is already a good basis. >> > >> > >> > Kind regards >> > >> > >> > On 11/27/2017 10:10 AM, Vikas Sharma wrote: >> >> Hi All, >> >> >> >> Could someone please provide the query/queries  to find table and >> >> Index bloating in PgSql 9.3 onwards? >> >> and are there any other maintenance tasks inPgSql except vacuum & >> >> analyse. >> >> >> >> Regards >> >> Vikas >> > >> > -- >> > Stefan FERCOT >> > http://dalibo.com - http://dalibo.org >> > >> > >> >