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';
Cancel a query
SELECT pg_cancel_backend(13075); -- replace 13075 with the pidList 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)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;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)) > 0Credit 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;