PostgreSQL High Availability Cookbook - Second Edition

How to do it...

Follow these steps to learn a little about the database:

1. Use this query to get 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 get 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 ...