SELECT pid, query_start, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state <> 'idle' AND (now() - query_start) > interval '5 minutes';
SELECT a.datname, l.relation::regclass, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l
JOIN pg_stat_activity a
ON a.pid = l.pid
WHERE NOT granted;
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE idx_scan < seq_scan AND n_live_tup > 10000
ORDER BY n_live_tup DESC;
SELECT pg_cancel_backend(pid);
pid
with the process ID from your earlier findings.SELECT schemaname, tablename, pg_size_pretty(total_bytes - COALESCE(index_bytes,0) - COALESCE(toast_bytes,0) - COALESCE(free_space,0)) AS table_bytes
FROM (
SELECT *, pg_total_relation_size(schemaname || '.' || tablename) AS total_bytes
FROM pg_tables
LEFT OUTER JOIN (
SELECT table_schema || '.' || table_name AS table_full_name,
SUM(index_bytes) AS index_bytes, SUM(toast_bytes) AS toast_bytes, SUM(free_space) AS free_space
FROM (
SELECT current_database() AS table_catalog, nspname AS table_schema, relname AS table_name,
c2.relname AS index_name, pg_size_pretty(pg_relation_size(c2.oid)) AS index_size,
pg_size_pretty(pg_total_relation_size(c2.oid)) AS total_size,
pg_size_pretty(pg_relation_size(c2.oid) - pg_relation_size(reltoastrelid)) AS table_bytes,
pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast_bytes,
pg_size_pretty(pg_relation_size(reltoastrelid) - pg_relation_size(reltoastidxid)) AS toast_table_bytes,
pg_size_pretty(pg_relation_size(reltoastidxid)) AS toast_index_bytes,
pg_size_pretty(pg_table_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_bytes,
pg_size_pretty(pg_total_relation_size(relid) - pg_table_size(relid) - pg_indexes_size(relid)) AS free_space
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON c2.oid = i.indrelid
WHERE c.relkind = 'i'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND pg_relation_size(c2.oid) > 0
) AS s1
GROUP BY table_schema, table_name
) AS s2 ON table_full_name = schemaname || '.' || tablename
) AS s3
ORDER BY total_bytes DESC LIMIT 10;
VACUUM (VERBOSE, ANALYZE) tablename;
tablename
with the name of the table to be vacuumed. This command will reclaim storage occupied by dead tuples and update statistics for better query planning.top
, htop
, or vmstat
to monitor the CPU and memory usage in real-time. Also, consider using pg_top
to specifically monitor PostgreSQL processes.These steps are immediate actions a developer can take to investigate and mitigate high CPU/memory usage issues in a Postgres DB.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)