How can I check for fragmentation in PostgreSQL 9.1?
Postgresql – Fragmentation in PostgreSQL 9.1
fragmentationpostgresqlpostgresql-9.1
Related Question
- Postgresql – Postgres 9.1 wait events
- Postgresql – Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux
- Postgresql – Upgrade PostgreSQL 9.1 to 9.3 and server no longer starts
- Postgresql – Migrating PostgreSQL 9.1 database to Ubuntu 14.04
- Postgresql – To get rid of heavy archive logs in PostgreSQL 9.1
- Table and Index fragmentation
- Postgresql – How to change pg_largeobject data from psql? (posgresql-9.1)
Best Answer
Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. You can start looking at the wiki Show database bloat for more information on fragmentation issues. Bearing in mind that this query will not give you exact numbers, it's a useful tool to have:
Once you know where bloat is happening, you can take measures to clean the table. The operation is called VACUUMing and can be launched on demand. It is also usually done in the background with the autovacuum worker, so please do not disable it.
As noted in https://www.postgresql.org/support/versioning/, PostgreSQL 9.1 has been unsupported for one year, consider upgrading.