The PostgreSQL Database Is Too Big
A large number of “Delete” or “Update” operations carried out on the table may lead to a so-called ‘index bloat’. The problem occurs in indexes based on B-trees and consists in the appearance of many empty or almost empty pages in the index. Such a ‘bloating’ results in an increased size of the database and decreases its efficiency; the efficiency may even fall below the acceptable threshold. The solution is to reconstruct the index in accordance with the information given in the PostgreSQL documentation.
The following SQL queries may be helpful when checking the size of the table size before and after the reconstruction of indexes:
- Giving the table size in bytes:
- Giving the sum of the sizes of 10 biggest tables:
- Giving the sum of the sizes of all tables: