← PostgreSQL Blog
PostgreSQL Emergency Kit: 10 SQL Queries to Save Your Production Database
PostgreSQL Emergency Kit: 10 SQL Queries to Save Your Production Database
When production is on fire, you don’t need a story :) you need working code. The following 10 queries aren’t fancy theories; they are battle-tested, refined SQL commands that deliver immediate results.
Table of Contents
- Deep Lock Analyzer: Who is blocking whom? (Recursive)
- Smart Size Inspector: Table vs Index Breakdown
- The Smart Sniper: Tiered Idle Connection Canceler (
pg_cancel_backend) - Mass Extinction: Force Kill Idle Sessions (
pg_terminate_backend) - Dead Tuple Detective: Bloat Check
- The Fixer: Manual Vacuum
- Unused Index Finder: Performance Tuning
- Active Queries Snapshot: Real-time Load
- Idle Duration Monitor: Who is sleeping?
- Cache Hit Ratio: Health Check
1. The Deep Lock Analyzer
Standard blocking queries often fail to show the full picture. This recursive query identifies the entire chain of dependency — showing exactly who is holding the lock and who started the traffic jam.
SELECT
bl.pid AS blocked_pid ,
ba.usename AS blocked_user ,
bl2.pid AS blocking_pid ,
ba2.usename AS blocking_user,
ba.query AS blocked_query,
ba2.query AS blocking_query
FROM
pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity ba ON ba.pid = bl.pid JOIN pg_catalog.pg_locks bl2 ON ( bl.locktype = bl2.locktype
AND bl.database IS NOT DISTINCT FROM bl2.database
AND bl.relation IS NOT DISTINCT FROM bl2.relation
AND bl.page IS NOT DISTINCT FROM bl2.page
AND bl.tuple IS NOT DISTINCT FROM bl2.tuple
AND bl.virtualxid IS NOT DISTINCT FROM bl2.virtualxid
AND bl.transactionid IS NOT DISTINCT FROM bl2.transactionid
AND bl.classid IS NOT DISTINCT FROM bl2.classid
AND bl.objid IS NOT DISTINCT FROM bl2.objid
AND bl.objsubid IS NOT DISTINCT FROM bl2.objsubid
AND bl.pid <> bl2.pid ) JOIN pg_catalog.pg_stat_activity ba2 ON ba2.pid = bl2.pid
WHERE
NOT bl.granted
AND bl2.granted;2. The Smart Size Inspector
When the disk alert fires, you need to know what is consuming space immediately. This query breaks down usage by Table Data vs. Index Data.
SELECT
table_name ,
pg_size_pretty(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS total_size,
pg_size_pretty(pg_table_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS table_size,
pg_size_pretty(pg_indexes_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS indexes_size
FROM
information_schema.tables
WHERE
table_schema = 'public' -- Change to your schema
ORDER BY
pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) DESC;3. The Smart Sniper (Soft Kill)
Action: pg_cancel_backend (Cancels query, keeps connection). This script uses tiered logic. It becomes more aggressive as the connection count rises. ( Connection numbers are only sample)
- Low load (>450 conn): Cancels queries idle for >15 mins.
- Critical load (>900 conn): Cancels queries idle for >3 mins.
WITH idle_connections AS
(
SELECT
pid,
now() - state_change AS idle_duration
FROM
pg_stat_activity
WHERE
state = 'idle'
AND datname = 'your_db_name' )
SELECT
pg_cancel_backend(pid)
FROM
idle_connections
WHERE
(
SELECT
count(*)
FROM
idle_connections) > 450
AND (
(
(
SELECT
count(*)
FROM
idle_connections) > 900
AND idle_duration > interval '3 minutes')
OR (
(
SELECT
count(*)
FROM
idle_connections) > 750
AND idle_duration > interval '5 minutes')
OR (
(
SELECT
count(*)
FROM
idle_connections) > 600
AND idle_duration > interval '10 minutes')
OR (
(
SELECT
count(*)
FROM
idle_connections) > 450
AND idle_duration > interval '15 minutes') );4. Mass Extinction (Hard Kill)
Action: pg_terminate_backend (Terminates session/connection). Use this only when the pool is completely saturated and soft kill isn't enough.
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
state = 'idle'
AND now() - state_change > interval '20 minutes'
AND datname = 'your_db_name';5. Dead Tuple Detective
Performance degrading slowly? Autovacuum might be failing. This identifies tables with the highest number of dead tuples (bloat).
SELECT
relname ,
n_dead_tup ,
n_live_tup ,
last_vacuum,
last_autovacuum
FROM
pg_stat_all_tables
WHERE
schemaname = '*****'
AND n_dead_tup > 0
ORDER BY
n_dead_tup DESC;6. The Fixer (Manual Vacuum)
Don’t wait for the autovacuum scheduler. Run this manually to reclaim space immediately.
-- Replace with your schema.tablename
VACUUM VERBOSE ANALYZE public.target_table;7. Unused Index Finder
Indexes speed up reads but slow down writes. An index with zero scans is technical debt.
SELECT
schemaname ,
relname ,
indexrelname,
idx_scan
FROM
pg_stat_user_indexes
WHERE
schemaname = '******'
AND idx_scan = 0
ORDER BY
pg_relation_size(indexrelid) DESC;8. Active Queries Snapshot
When CPU hits 100%, check what is running right now (excluding idle sessions).
SELECT
pid ,
usename ,
application_name ,
client_addr ,
now() - query_start as duration,
state ,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
duration DESC;9. Idle Duration Monitor
Inspect the “Sleepers” holding connections open without doing work.
SELECT
pid ,
usename ,
application_name ,
state ,
now() - state_change as idle_time,
query
FROM
pg_stat_activity
WHERE
state = 'idle'
AND datname = 'your_db_name'
ORDER BY
idle_time DESC;10. Cache Hit Ratio
Is your database running from RAM (fast) or Disk (slow)? Target should be 99%+.
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit ,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;Automate or Die
These queries are lifesavers for manual intervention. However, Engineer doesn’t watch a terminal all day.
If you want to visualize these metrics 24/7 and set up automatic alerts, check out my guide on Monitoring PostgreSQL Cluster: Server, Database and Logs.