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:
SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class 
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') 
ORDER BY size_in_bytes DESC LIMIT 10;
  • Giving the sum of the sizes of 10 biggest tables:
SELECT SUM(sizes.size_in_bytes) AS total_size_for_top_10_tables FROM (SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class 
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY size_in_bytes DESC LIMIT 10) AS sizes;
  • Giving the sum of the sizes of all tables:
SELECT SUM(sizes.size_in_bytes) AS total_size_for_all_tables FROM (SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class 
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AS sizes;
  • No labels