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;
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;
select pg_cancel_backend(procpid);
select pg_terminate_backend(procpid);
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;
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;
select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';
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
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;