Postgres queries

postgres.md

Running queries

SELECT pid, age(clock_timestamp(), query_start), usename, state, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

Blocked pids

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;

kill running query

select pg_cancel_backend(procpid);

kill idle query

select pg_terminate_backend(procpid);

Index creation progress

SELECT
  age(clock_timestamp(), query_start),
  a.query,
  p.phase,
  round(p.blocks_done / NULLIF(p.blocks_total::numeric,0) * 100, 2) AS "% done",
  p.blocks_total,
  p.blocks_done,
  round(p.tuples_done / NULLIF(p.tuples_total::numeric,0) * 100, 2) AS "% tuples done",
  p.tuples_total,
  p.tuples_done,
  ai.schemaname,
  ai.relname,
  ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;

Biggest indexes

select schemaname as table_schema,
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as data_size,
    pg_size_pretty(pg_indexes_size(relid)) as index_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
      as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
         pg_relation_size(relid) desc
limit 100;

Size of indexes in a table

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';

Size of index with bloat fragmentation (slow to query)

CREATE EXTENSION pgstattuple;

select indexname,
  pg_size_pretty(pg_total_relation_size(indexname::regclass)) as total, 
  pg_size_pretty(pg_relation_size(indexname::regclass)) as size,
  t.*
from pg_indexes
 join lateral (select * from pgstatindex(indexname)) t on true
where tablename = 'my_table';

Table bloat https://wiki.postgresql.org/wiki/Show_database_bloat

Vacuum progress

SELECT
  p.phase,
  round(p.heap_blks_scanned / NULLIF(p.heap_blks_total::numeric,0) * 100, 2) AS "% scanned",
  round(p.heap_blks_vacuumed / NULLIF(p.heap_blks_total::numeric,0) * 100, 2) AS "% vacuumed",
  p.num_dead_tuples,
  round(p.num_dead_tuples / NULLIF(p.max_dead_tuples::numeric,0) * 100, 2) as "% dead max",
  a.query,
  p.*
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid;