Thread: 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
Attachment
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
>> >
>> >
>>
>