Essential PostgreSQL Queries Everyone Should Know! 🚀
PostgreSQL is a powerful open-source RDBMS, but managing and optimizing it requires the right queries. Here’s a collection of must-know PostgreSQL queries to monitor performance, troubleshoot locks, manage space, and optimize indexing.
📌 1. Check Tablespace Size
SELECT pg_size_pretty(pg_tablespace_size(‘pg_default’));
🔹 Why? Helps track tablespace utilization to prevent storage issues.
📌 2. Find Active Sessions & Running Queries
SELECT pid, datname, usename, application_name, client_hostname, query_start, query, state
FROM pg_stat_activity
WHERE state = ‘active’;
🔹 Why? Identifies currently running queries to track database load.
📌 3. Detect Slow & Long-Running Queries
SELECT pid, usename, query_start, now() – query_start AS query_time, query
FROM pg_stat_activity
WHERE now() – query_start > INTERVAL ’10 minutes’;
🔹 Why? Identifies queries running for too long, causing performance degradation.
📌 4. Identify Blocking Sessions
SELECT activity.pid, 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));
🔹 Why? Finds sessions blocking other transactions.
📌 5. View Locks & Queries on Locked Tables
SELECT relname AS table_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;
🔹 Why? Helps diagnose deadlocks and waiting transactions.
📌 6. Kill Long-Running Queries
🚀 Graceful Cancel:
SELECT pg_cancel_backend();
🚀 Force Termination:
SELECT pg_terminate_backend();
🔹 Why? Cancels problematic queries without restarting PostgreSQL.
📌 7. Monitor High CPU Usage Queries
SELECT total_time / 1000 / 3600 AS total_hours, calls, query
FROM pg_stat_statements
ORDER BY total_hours DESC LIMIT 10;
🔹 Why? Identifies queries consuming excessive CPU.
📌 8. View Database Sizes
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
🔹 Why? Helps track disk usage across all databases.
📌 9. Check Cache Hit Rates (Should Be ≥ 0.99)
SELECT sum(heap_blks_read) AS heap_read, sum(heap_blks_hit) AS heap_hit,
(sum(heap_blks_hit) – sum(heap_blks_read)) / sum(heap_blks_hit) AS ratio
FROM pg_statio_user_tables;
🔹 Why? Ensures efficient memory usage instead of excessive disk I/O.
📌 10. Detect Unused Indexes
SELECT relname AS table_name, indexrelname AS index_name, idx_scan, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelname::regclass) DESC;
🔹 Why? Identifies unnecessary indexes that waste space.
📌 11. Check for Table Bloat (Wasted Space)
WITH bloat_info AS (
SELECT current_database(), schemaname, tablename,
ROUND((relpages::FLOAT / otta)::NUMERIC, 1) AS bloat_ratio,
CASE WHEN relpages < otta THEN 0 ELSE bs * (relpages – otta) END AS wasted_bytes
FROM (SELECT … FROM pg_class JOIN pg_namespace ON …) AS inner_query
)
SELECT * FROM bloat_info ORDER BY wasted_bytes DESC;
🔹 Why? Helps reduce wasted space and optimize table performance.
📌 12. Monitor CPU & Memory Usage from PostgreSQL
CREATE EXTENSION file_fdw;
CREATE SERVER fileserver FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE loadavg (one text, five text, fifteen text, scheduled text, pid text)
SERVER fileserver OPTIONS (filename ‘/proc/loadavg’, format ‘text’, delimiter ‘ ‘);
SELECT * FROM loadavg;
🔹 Why? Enables in-database monitoring of system performance.