Gonçalo Cabrita

Postgres

#databases

Best practices

Snippets

Query management

SELECT round(total_exec_time*1000)/1000 AS total_exec_time,
       round(total_plan_time*1000)/1000 AS total_plan_time,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 2;

Check long running queries

SELECT pid,
       now() - pg_stat_activity.query_start AS duration,
       query,
       state,
       wait_event,
       wait_event_type,
       pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state = 'active';

Blockers of queries

SELECT blockers.pid,
       blockers.usename,
       blockers.query_start,
       blockers.query
FROM pg_stat_Activity blockers
INNER JOIN
  (SELECT pg_blocking_pids(pid) blocking_pids
   FROM pg_stat_Activity
   WHERE pid != pg_backend_pid()
     AND query LIKE 'ALTER TABLE%' ) my_query ON blockers.pid = ANY(my_query.blocking_pids);

Kill query

SELECT pg_cancel_backend(pid);

Kill query (force)

SELECT pg_terminate_backend(pid);

Check ongoing vacuums

SELECT p.pid,
       now() - a.xact_start AS duration,
       coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
       CASE
           WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
           WHEN a.query ~*'^vacuum' THEN 'user'
           ELSE 'regular'
       END AS MODE,
       p.datname AS DATABASE,
       p.relid::regclass AS TABLE,
       p.phase,
       pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
       pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
       round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
       round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
       p.index_vacuum_count,
       round(100.0 * p.num_dead_tuples / p.max_dead_tuples, 1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid)
ORDER BY now() - a.xact_start DESC;

Check table sizes

SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog',
                      'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 40;

Check which tables are aging

SELECT c.oid::regclass,
       age(c.relfrozenxid),
       pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind IN ('r',
                  't',
                  'm')
  AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC
LIMIT 20;