Database Capacity Planning — Vocabulary and Communication

Databases have a max_connections limit (PostgreSQL default: 100, configurable). Each connection uses memory (5–10MB). At connection limit, new connections fail immediately. Solutions: connection pooling (PgBouncer reduces connections to database), increasing the limit (requires more RAM), or connection management in the application.

In PostgreSQL's MVCC model, UPDATE and DELETE leave dead row versions in the table. VACUUM reclaims space logically (marks dead space reusable) but does not return space to the OS unless VACUUM FULL is run (which locks the table). Bloat monitoring is essential for capacity planning.

IOPS (Input/Output Operations Per Second) measures how many read/write operations a storage system can handle per second. OLTP databases are typically IOPS-bound (many small random reads/writes). Database capacity planning requires estimating peak IOPS from the workload and selecting storage that can sustain them.

Vertical scaling (scale up): add CPU cores, RAM, faster NVMe — easy but limited by single-server maximums and expensive at the top end. Horizontal scaling (scale out): add read replicas for read traffic, shard writes across multiple nodes — more complex but theoretically unlimited.

Storage tiers in databases: hot tier (NVMe SSD — highest IOPS, highest cost — for active transactional data), warm tier (SATA SSD — moderate IOPS/cost), cold tier (HDD/object storage — lowest cost — for archived data rarely queried). Choosing correctly reduces costs while maintaining required performance.