Opscraft
Database operations · Lesson 4 of 6 · 3 min read

Performance and capacity

Connections

Each PostgreSQL connection is a separate process using memory, so thousands of connections hurt even when idle. Signs of trouble: FATAL: sorry, too many clients already or remaining connection slots are reserved.

  • Size application pools deliberately: total connections = pods × pool size per pod. Scaling a Deployment from 10 to 50 pods with a pool of 20 means 1,000 connections.
  • Use a pooler: PgBouncer (transaction pooling lets many clients share few server connections) or RDS Proxy.
  • Watch DatabaseConnections and idle-in-transaction sessions, which hold locks and block vacuum.
Enlarge diagram50 pods × pool 201,000 client connectionsRDS Proxy /PgBouncertransaction poolingPostgreSQL100 server connections
Figure 1. Connections multiply with pods. A pooler lets many clients share a few database connections.
Swipe sideways to see all of it, or tap to open it full screen.

Slow queries and locks

  • Find what's expensive with pg_stat_statements (PostgreSQL) or the slow query log and Performance Schema (MySQL), and on AWS with Performance Insights / CloudWatch Database Insights, which show load by query and by wait event.
  • Read plans with EXPLAIN (ANALYZE, BUFFERS): sequential scans on large tables usually mean a missing index; huge row estimate errors mean stale statistics (ANALYZE).
  • Locks: a long-running transaction or a migration waiting for an ACCESS EXCLUSIVE lock blocks everything queued behind it. Find blockers with pg_stat_activity and pg_blocking_pids(), and set statement_timeout and idle_in_transaction_session_timeout.
SELECT pid, state, now() - xact_start AS age, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY age DESC NULLS LAST LIMIT 10;

Storage, vacuum and growth

  • PostgreSQL never overwrites rows in place: updates and deletes leave dead rows that autovacuum cleans up. If vacuum can't keep up (long transactions, too-low settings), tables bloat and queries slow down.
  • Autovacuum also prevents transaction ID wraparound; if it falls far behind, PostgreSQL eventually refuses writes to protect data. Alert on the age of the oldest transaction ID.
  • On RDS, use gp3 storage with provisioned IOPS and throughput as needed, enable storage autoscaling, and alert on FreeStorageSpace, read/write latency and IOPS against the limit. Running out of storage puts an RDS instance into storage-full and stops writes.
  • Watch replication slots: an inactive logical replication slot makes the primary keep WAL forever and can fill the disk.