maintenance queries with PostgreSQL

helpful queries

October 29, 2021

Most of these queries are just copied from "the internet".

Find queries running for more than 5 hours - long-running queries:

>$ postgres=# SELECT
  pid, application_name,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 hours' order
by duration desc;

Find idle in transaction queries:

>$ postgres=# SELECT *
FROM pg_stat_activity
WHERE state like 'idle_%';

For the following commands reduce the output to the pids only by replacing * by pid. This will list each pid of the idle_in_transaction queries.

Other variations use state != 'idle'.

In order to stop them, try to run:

pg_cancel_backend(): This behaves like a graceful termination of the query. It returns true and can take a while to actually finish.

SELECT pg_cancel_backend(<pid>);

pg_terminate_backend(): This behaves like a "hard kill".

SELECT pg_terminate_backend(<pid>);

Find blocking queries:

>$ postgres=# SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid =
ANY(pg_blocking_pids(activity.pid));

Find locks with table names and queries:

>$ postgres=# SELECT 
    relname AS relation_name, 
    query, 
    pg_locks.* 
FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid
JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid;

Find missing indices:

This checks if there are more sequence scans than index scans. If the table is small, it gets ignored, since Postgres prefers sequence scans for them.

>$ postgres=# SELECT
  relname AS TableName,
  to_char(seq_scan, '999,999,999,999') AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999') AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999') AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

Find unused indices:

If index_scans is 0 (or close to 0) you can drop those indexes. But be careful, as maybe those indexes are for unique purposes.

>$ postgres=# SELECT s.relname AS table_name,
       indexrelname AS index_name,
       i.indisunique,
       idx_scan AS index_scans
FROM   pg_catalog.pg_stat_user_indexes s,
       pg_index i
WHERE  i.indexrelid = s.indexrelid;