Postgres utils

postgres.sql

######################
## Count estimate

SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname='table_name';

######################
## Table size in disk
# source: https://stackoverflow.com/a/2596678/375888

# Simple:

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

-- `public` schema:
SELECT pg_size_pretty(pg_total_relation_size('"public"."<table>"'));

# Detailed:

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;
        
# DB size:

SELECT pg_size_pretty(pg_database_size('<database name>'));

-- or

SELECT
    pg_size_pretty(sum( pg_table_size(table_name) )) AS table_size,
    pg_size_pretty(sum( pg_indexes_size(table_name) )) AS indexes_size,
    pg_size_pretty(sum( pg_total_relation_size(table_name) )) AS total_size
FROM (
    SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
    FROM information_schema.tables
) AS all_tables;

# Replication lag:

SELECT
  pg_is_in_recovery() AS is_slave,
  pg_last_wal_receive_lsn() AS receive,
  pg_last_wal_replay_lsn() AS replay,
  pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced,
  (
   EXTRACT(EPOCH FROM now()) -
   EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp())
  )::int AS lag;
  
  
######################
## Long running queries / locks / stuck:
# Thanks @agius 

# pg_stat_activity: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
# pg_locks: https://www.postgresql.org/docs/current/view-pg-locks.html

# Slow queries:

SELECT S.pid, age(clock_timestamp(), S.query_start) stmt_age, S.usename, L.mode, L.locktype, L.granted, S.query
FROM pg_stat_activity S
LEFT JOIN pg_locks L on S.pid = L.pid 
WHERE S.state='active' AND S.query NOT LIKE 'SELECT S.pid, age(%'
  AND age(clock_timestamp(), S.query_start) > '1 minute'
ORDER BY stmt_age DESC
LIMIT 100;

# Locked queries:

SELECT S.pid, age(clock_timestamp(), query_start), usename, L.mode, L.locktype, L.granted, query
FROM pg_stat_activity S 
INNER JOIN pg_locks L on S.pid = L.pid 
WHERE query NOT LIKE 'SELECT S.pid, age(%'
ORDER BY age(clock_timestamp(), query_start) DESC
LIMIT 100;

# More on the following: https://www.postgresql.org/docs/current/functions-admin.html
# Cancel a job:
SELECT pg_cancel_backend(< S.pid here >);

# Unplug / kill -9 a job (think twice before running this one):
SELECT pg_terminate_backend(< S.pid here >);

# Also: https://github.com/AdmTal/PostgreSQL-Query-Lock-Explainer