Identifying and fixing bloated tables and indexes
PostgreSQL implements Multiversion Concurrency Control (MVCC), which allows users to read data at the same time as writers make changes. This is an important feature for concurrency in database applications as it can allow the following:
- Better performance because of fewer locks
- Greatly reduced deadlocking
- Simplified application design and management
Bloated tables and indexes are a natural consequence of MVCC design in PostgreSQL. Bloat is caused mainly by updates, as we must retain both the old and new updates for a certain period. Since these extra row versions are required to provide MVCC, some amount of bloat is normal and acceptable. Tuning to remove bloat completely isn't useful and probably a waste of time.
Bloating results in increased disk consumption, as well as performance loss – if a table is twice as big as...
