Common symptoms

  • Queries that used to complete in milliseconds now take seconds or minutes
  • The application throws connection timeout errors or "connection refused"
  • Users report prolonged loading screens or intermittent errors
  • pg_stat_activity shows an unusually high number of connections in active state
  • Nightly report jobs don't finish within their scheduled window
  • The server shows high CPU usage, disk I/O or active swap memory
  • PostgreSQL logs record many slow queries (log_min_duration_statement)

Business risks

  • Direct revenue loss if the sales or payment platform is affected
  • SLA breach with customers or integration partners
  • Escalation to critical incident if connection saturation causes total rejection (FATAL: sorry, too many clients)
  • Chain locks leaving business transactions in an inconsistent state
  • Management reports and KPIs unavailable when needed

Detailed technical checklist

  • 1. Check for blocked sessions Identifies which process is blocking others. A long-waiting session can cascade to dozens of connections.
  • 2. Review tables with high dead tuple count A high n_dead_tup with a null or very old last_autovacuum indicates autovacuum is not processing the table.
  • 3. Verify autovacuum configuration If autovacuum_vacuum_cost_delay is too high (above 20ms), autovacuum runs too slowly on high-activity tables.
  • 4. Review unused indexes Unused indexes waste space, slow writes and confuse the planner. Candidates to remove if idx_scan < 50 and the table has been active for months.
  • 5. Check buffer cache hit ratio A hit ratio below 95% indicates PostgreSQL is reading too much from disk. May point to insufficient shared_buffers or table bloat.
  • 6. Verify key memory parameters Recommended shared_buffers: 25% of RAM. work_mem multiplies by the number of active connections; high values can cause OOM.
  • 7. Review active locks (not just blocked ones) View all waiting locks to detect if there is a conflict on a specific table.
  • 8. Analyze the execution plan of the slow query Use EXPLAIN (ANALYZE, BUFFERS) on the query identified in pg_stat_statements. Look for "Seq Scan" on large tables or unexpected "Hash Join".
  • 9. Check checkpoint pressure If checkpoints_req is high compared to checkpoints_timed, the system is writing WAL faster than it can checkpoint. Consider increasing max_wal_size.
  • 10. Run manual ANALYZE if statistics are stale If last_autoanalyze is more than 24 hours old on high-activity tables, statistics are outdated and the planner is choosing suboptimal plans.
SQL 
-- Active sessions and how long they have been running
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 10;

When to escalate to a specialist DBA

  • Active connections exceed 85% of max_connections and the application starts receiving errors
  • Chain locks don't resolve on their own within 10 minutes
  • The database has stopped accepting new connections (FATAL: sorry, too many clients)
  • Logs show PANIC, FATAL or out of memory errors
  • The server reaches active swap or there are OOM killer processes in OS logs
  • The internal team cannot identify the root cause within 30–60 minutes with the business affected

Frequently asked questions

Why did PostgreSQL suddenly slow down without code changes?

The most common causes without code changes are: tables that exceeded the autovacuum threshold with many dead tuples, stale statistics causing the planner to choose an inefficient plan, or a change in data volume making an index no longer selective. Also frequent: backups filling the disk force PostgreSQL to write to a full filesystem, degrading I/O.

Should I restart PostgreSQL to fix slowness?

Generally, no. Restarting terminates active sessions and may temporarily resolve stuck locks, but it doesn't fix the root cause. If the slowness is due to lack of vacuum or stale statistics, it will return. Restart should be reserved for emergencies (zombie processes, OOM) and always after a prior analysis of the current state.

What does autovacuum do and why does performance suffer if it doesn't run?

Autovacuum cleans old row versions (dead tuples) generated by UPDATE and DELETE, and updates query planner statistics. If it doesn't run frequently enough, tables accumulate bloat (wasted space), the planner makes poor decisions and indexes become less efficient. On tables with high concurrency or many DELETE/UPDATE, it's usually necessary to tune autovacuum_vacuum_scale_factor and autovacuum_vacuum_cost_delay.

How do I know if the problem is the network, the application or the database?

If pg_stat_activity shows queries with high execution time (query_start column far from the current time), the bottleneck is in the database. If queries finish quickly but the application is slow, the problem may be in the connection pool, data serialization or the network. If pg_stat_activity shows many sessions in idle in transaction state, the application code is not closing transactions correctly.

Does increasing shared_buffers always improve performance?

Not necessarily. The general recommendation is 25% of available RAM. Beyond 40% it can hurt performance by displacing the OS page cache, which PostgreSQL also uses to read data. Changes to shared_buffers should be made with real measurement before and after; a poorly sized change can make the problem worse.

Related resources