Useful Postgres Queries

pg.sql

-- active connections
SELECT application_name, state, age(now(), xact_start) age, query FROM pg_stat_activity WHERE state <> 'idle';

SELECT application_name, pid, state, age(now(), xact_start) age,
  left(regexp_replace(query,E'[\\n\\r]+',' ','g'), 100)
  FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age;

-- bloated tables
select relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup,
  n_dead_tup, round(n_dead_tup::numeric/n_live_tup, 2) as bloat,
  date_trunc('second',last_autoanalyze) as last_autoanalyze,
  date_trunc('second',last_autovacuum) as last_autovacuum,
  date_trunc('second',now()) as now
  from pg_stat_all_tables where n_live_tup > 1000000 and n_dead_tup > 10000
  and round(n_dead_tup::numeric/n_live_tup, 2) > 0.02 order by bloat;

-- speed up autovacuuming
-- https://blog.2ndquadrant.com/autovacuum-tuning-basics/
ALTER TABLE my_table SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 10,
  autovacuum_naptime = 15
);

ALTER TABLE my_table SET (autovacuum_vacuum_cost_delay = 10);

-- instantly make column NOT NULL (without validation)
-- https://www.postgresql.org/message-id/CA%2BTgmoY9jpTW-Xi3GuL6G9-GiaTgR7FASutQd-fnRqwQTx%2B_xw%40mail.gmail.com
UPDATE pg_catalog.pg_attribute SET attnotnull = TRUE WHERE attrelid='my_table'::regclass::oid AND attname = 'my_column';

-- programatically check if column is NOT NULL
SELECT attnotnull FROM pg_attribute
  JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
  WHERE pg_class.relname = 'my_table' AND pg_attribute.attname = 'my_column';
  

-- View locks 
SELECT a.datname, c.relname, l.tuple, l.transactionid, l.mode, l.GRANTED, a.usename,
       a.query_start, age(now(),a.xact_start) AS xact_age, age(now(),a.query_start) AS query_age, a.pid 
  FROM  pg_stat_activity a
  JOIN pg_locks l ON l.pid = a.pid
  JOIN pg_class c ON c.oid = l.relation
  WHERE a.query_start < NOW() - INTERVAL '1 seconds'
  ORDER BY a.query_start;

-- table size
SELECT table_name, row_estimate,
    pg_size_pretty(total_bytes) AS TOTAL, pg_size_pretty(index_bytes) AS INDEX,
    pg_size_pretty(toast_bytes) AS TOAST, pg_size_pretty(table_bytes) AS TABLE
FROM (SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
    SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
    , c.reltuples AS row_estimate
    , pg_total_relation_size(c.oid) AS total_bytes
    , pg_indexes_size(c.oid) AS index_bytes
    , pg_total_relation_size(reltoastrelid) AS toast_bytes
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE relkind = 'r') a
) a;

-- table size more verbose
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a ORDER BY total_bytes DESC;