Looking at your chart, the pattern is very telling — p50 (median) stays near 0ms while p99 spikes to 30s. That means most requests are fine, but a small subset are extremely slow. Combined with your other clues, here's what's almost certainly happening:

Most Likely Cause: Missing Index + Table Bloat / Lock Contention

The fact that the same query takes 1s for some users and 30s for others is the key diagnostic signal. This almost never happens with business logic bugs — it's a database access pattern issue.

1. Missing or Skipped Index (Most Probable)

If your query filters by something user-specific (e.g. WHERE user_id = $1 or WHERE organization_id = $1), Postgres might be doing a sequential scan for users whose data spans many rows, but an index scan for users with few rows.

-- Run this to find slow queries

SELECT query, mean_exec_time, calls, total_exec_time

FROM pg_stat_statements

ORDER BY mean_exec_time DESC

LIMIT 20;

-- Check if your query is using an index or seq scan

EXPLAIN (ANALYZE, BUFFERS) SELECT ... your actual query here ...;

Watch for Seq Scan on large tables — that's your culprit.

1. Lock Contention / Waiting Queries

The transient nature (sometimes fast, sometimes 30s) strongly suggests lock waits. A background job, migration, or write-heavy operation may be holding a lock, and queries queue up behind it.

-- See what's currently blocked

SELECT

pid,

now() - pg_stat_activity.query_start AS duration,

query,

state,

wait_event_type,

wait_event

FROM pg_stat_activity

WHERE state != 'idle'

ORDER BY duration DESC;

Look for wait_event_type = 'Lock'. If you see it, find what's holding the lock:

SELECT * FROM pg_locks WHERE NOT granted;

1. Connection Pool Exhaustion (Very Common in Express + Postgres)

If your pool is maxed out, new queries queue and wait for a free connection — exactly matching your transient, p99-only spike pattern. The median is fine because most requests get a connection quickly, but under burst traffic, some wait.

// Check your pool config — common mistake is leaving it at default (10)

const pool = new Pool({

max: 20, // tune this

idleTimeoutMillis: 30000,

connectionTimeoutMillis: 2000, // set this so you FAIL FAST instead of waiting 30s

});

Add this to see pool pressure in real time:

pool.on('connect', () => console.log('pool connect, total:', pool.totalCount));

pool.on('acquire', () => console.log('pool acquire, idle:', pool.idleCount, 'waiting:', pool.waitingCount));

If waitingCount is ever > 0, you've found it.

1. Autovacuum / Table Bloat

If a table has high write/delete churn, dead tuples accumulate and sequential scans get slower over time — and non-uniformly across users depending on data distribution.

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 10;

If n_dead_tup is very high on your key tables, run VACUUM ANALYZE your_table; manually and see if it helps.

Suggested Order of Investigation

1-Check pg_stat_activity during a slow period — look for lock waits

2-Log pool.waitingCount — if it's ever > 0, that's your smoking gun

3-Run EXPLAIN ANALYZE on your heaviest queries — look for seq scans

4-Check pg_stat_user_tables for dead tuple bloat