Helpful Postgres Queries

As with any performance advice, the only way to really improve things is to try things out and measure.

Find long-running queries

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  usename,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE 
	state = 'active' and 
    (now() - pg_stat_activity.query_start) > interval '1 minute';

Find long-running queries

Cancel a query

SELECT pg_cancel_backend(13075); -- replace 13075 with the pid

Cancel a long running query

List indexes

SELECT    CONCAT(n.nspname,'.', c.relname) AS table,
          i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
          pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
          pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c 
JOIN      pg_index x ON c.oid = x.indrelid
JOIN      pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE     c.relkind = ANY (ARRAY['r', 't'])
AND       n.oid NOT IN (99, 11, 12375)

List all indexes and their size

Credit to Percona

Locks taken

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

List all locks taken

Credit to Postgresql wiki

Blocking queries

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0

All queries that are blocking another one

Credit to StackOverflow

Test Table

Sometimes it's really helpful to create a smaller version of a real table to understand really long-running queries. We're lucky that our primary table activity_stream doesn't use external foreign keys, so we can make a smaller copy of it to try things out. Though with any performance work, always be sure any changes scale up to the 'real' version as expected.

CREATE TABLE narrator.activity_stream_debug (LIKE narrator.activity_stream INCLUDING INDEXES);

INSERT INTO narrator.activity_stream_debug
SELECT
*
FROM
    narrator.activity_stream
WHERE
    ts > '2021/05/01'::date;