PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

As the postgres user on a suitable PostgreSQL cluster, follow these steps to find horizontal scalability candidates:

1. Execute the following query while connected to any database:

WITH db AS ( 
  SELECT d.datname AS database_name, 
         d.xact_commit + d.xact_rollback AS transactions, 
         d.tup_inserted + d.tup_updated  
                        + d.tup_deleted AS writes, 
         sum(s.calls) AS queries 
    FROM pg_stat_database d 
    LEFT JOIN pg_stat_statements s ON (s.dbid = d.datid) 
   WHERE d.datname NOT IN ( 
           'template0', 'template1', 'postgres' 
         ) 
   GROUP BY 1, 2, 3 
) 
SELECT *  
  FROM db 
 WHERE db.transactions > 10000000 
    OR db.writes > 100000000 
    OR db.queries > 100000000; 

2. Create the following view in the candidate database with this SQL statement:

CREATE OR REPLACE VIEW v_shard_candidates ...