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
DatabaseConnectionsand idle-in-transaction sessions, which hold locks and block vacuum.
Enlarge diagram
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 EXCLUSIVElock blocks everything queued behind it. Find blockers withpg_stat_activityandpg_blocking_pids(), and setstatement_timeoutandidle_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 intostorage-fulland stops writes. - Watch replication slots: an inactive logical replication slot makes the primary keep WAL forever and can fill the disk.