PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Follow these steps to learn a little about the database:

1. Use this query to retrieve a list of the top 20 largest tables in the current database:

SELECT oid::REGCLASS::TEXT AS table_name, 
         pg_size_pretty( 
           pg_total_relation_size(oid) 
         ) AS total_size 
   FROM pg_class 
  WHERE relkind = 'r' 
    AND relpages > 0 
  ORDER BY pg_total_relation_size(oid) DESC 
  LIMIT 20; 

2. Use this query to obtain a list of the top 20 largest indexes in the current database and their parent tables:

SELECT indexrelid::REGCLASS::TEXT AS index_name, 
        indrelid::REGCLASS::TEXT AS table_name, 
        pg_size_pretty( 
          pg_relation_size(indexrelid) 
        ) AS total_size 
  FROM pg_index 
 ORDER BY pg_relation_size(indexrelid) DESC 
 LIMIT 20; 

3. Use this query to find the top 20 most active tables ...