Table and Index bloating

Started by Vikas Sharmaover 8 years ago7 messagesgeneral

shavikas@gmail.com

over 8 years ago

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

Import Notes

Reply to msg id not found: CAN6gwKyROyDzhxsXkgpOGi5AXoBodtVty+Okt6qrUnZLPXp6Sg@mail.gmail.comReference msg id not found: CAN6gwKyh+CvRni0Kcn7i0iEgqqkWC=iFAhgQUL+gx-=qxYCpOw@mail.gmail.comReference msg id not found: CAN6gwKyROyDzhxsXkgpOGi5AXoBodtVty+Okt6qrUnZLPXp6Sg@mail.gmail.com

Stefan Fercot

stefan.fercot@protonmail.com

over 8 years ago

In reply to: Vikas Sharma (#1)

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

Carlos Martinez

camarti@gmail.com

over 8 years ago

In reply to: Stefan Fercot (#2)

Re: Table and Index bloating

Hi.

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

Best regards.

Carlos Martinez

Show quoted text

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

Vikas Sharma

shavikas@gmail.com

over 8 years ago

In reply to: Carlos Martinez (#3)

Re: Table and Index bloating

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:

Show quoted text

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

Carlos Martinez

camarti@gmail.com

over 8 years ago

In reply to: Vikas Sharma (#4)

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

Show quoted text

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

Steven Chang

stevenchang1213@gmail.com

over 8 years ago

In reply to: Carlos Martinez (#5)

Re: Table and Index bloating

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 <stefan.fercot@dalibo.com> 日期： 2017/11/27 17:17 (GMT+08:00) 至： Vikas Sharma <shavikas@gmail.com> 副本： 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

Import Notes

Resolved by subject fallback

Steven Chang

stevenchang1213@gmail.com

over 8 years ago

In reply to: Steven Chang (#6)

Re: Table and Index bloating

After reading the web pages, index part should be succeeded, but tables may not fit if they got foreign key relations.
steven
-------- 原始訊息 --------自： Carlos Martinez <camarti@gmail.com> 日期： 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

Show quoted text

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

Import Notes

Resolved by subject fallback