Postgres Firefighting

Prerequisites

You need schema admin permissions on the Fly Managed Postgres instance. As of April 2026, the fly-user role has:

Capability	Available
pg_stat_activity (all connections)	Yes
pg_terminate_backend	Yes
pg_locks	Yes
pg_stat_monitor	Yes
pg_stat_replication	Yes
pg_stat_user_tables (vacuum stats)	Yes
ALTER SYSTEM / pg_reload_conf	No
CREATE EXTENSION	Untested

Note

Connect via PgBouncer as usual. Most diagnostic queries work through PgBouncer, including pg_terminate_backend. If you hit issues with system function resolution, connect directly to the Postgres instance via fly postgres connect -a <postgres-app-name>.

1. Getting a Picture of Database State

Current Queries

See what’s running right now — active queries, how long they’ve been running, and who’s connected:

SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

To include idle connections (useful for seeing total connection count):

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Blocked Queries

Find queries that are waiting on locks held by other queries:

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    now() - blocked.query_start AS blocked_duration,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocking.query_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid AND NOT blocked_locks.granted
JOIN pg_locks blocking_locks
    ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE blocked.pid != blocking.pid;

If this returns rows, you have a blocking chain. The blocking_pid is the one holding the lock.

Current Locks

See all locks and whether they’ve been granted:

SELECT l.locktype, l.mode, l.granted, l.pid, a.usename, a.query, a.state, a.wait_event_type
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
ORDER BY l.granted, a.state;

Filter to just the contentious ones (locks not yet granted):

SELECT l.locktype, l.relation::regclass, l.mode, a.pid, a.query, now() - a.query_start AS waiting
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

2. Recovering from Bad Situations

Killing Queries with pg_terminate_backend

Step 1: Identify what to kill. Use the blocked queries query above to find the blocking_pid. Or find long-running queries:

SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '1 minute'
ORDER BY query_start;

Step 2: Verify before killing. Check what the blocking connection is doing — it might be a legitimate long migration or backfill:

SELECT pid, usename, application_name, client_addr, state, query,
       now() - xact_start AS transaction_duration,
       now() - query_start AS query_duration
FROM pg_stat_activity
WHERE pid = <suspect_pid>;

Step 3: Terminate the connection.

-- Kill a single connection
SELECT pg_terminate_backend(<pid>);

-- Returns true if the signal was sent successfully

Caution

pg_terminate_backend kills the entire connection, not just the current query. If you only want to cancel the running query without dropping the connection, use pg_cancel_backend(<pid>) first — it’s less disruptive. If the query doesn’t stop after a few seconds, escalate to terminate.

To kill multiple connections matching a pattern (e.g., all idle connections from a specific user):

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'fly-user'
  AND state = 'idle'
  AND now() - state_change > interval '10 minutes';

Evaluating Bloat and Vacuum Issues

Check which tables have the most dead tuples (rows deleted or updated but not yet reclaimed):

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

If dead_pct is high (>20%) and last_autovacuum is old, autovacuum may be falling behind. Check if vacuum is currently running:

SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

Check table size vs bloat estimate:

SELECT
    schemaname || '.' || relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_dead_tup,
    n_live_tup,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Note

Since we don’t have ALTER SYSTEM access, we can’t tune autovacuum thresholds. If vacuum is consistently falling behind, file a support request with Fly to adjust autovacuum_vacuum_cost_delay or per-table autovacuum settings.

Replication Lag

If the application is experiencing stale reads (if using a read replica):

SELECT pid, usename, application_name, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

All LSN values should match and lag columns should be NULL or very small. If replay_lsn is behind sent_lsn, the replica is falling behind.

Query Performance (pg_stat_monitor)

We have pg_stat_monitor installed (a superset of pg_stat_statements). Find the slowest queries:

SELECT queryid, calls, mean_exec_time, max_exec_time, total_exec_time, query
FROM pg_stat_monitor
ORDER BY mean_exec_time DESC
LIMIT 20;

Find the most-called queries (potential N+1 candidates):

SELECT queryid, calls, mean_exec_time, total_exec_time, query
FROM pg_stat_monitor
ORDER BY calls DESC
LIMIT 20;