## Intro Capacity planning for PostgreSQL means deciding how much CPU, memory, disk I/O, storage, and connection headroom your database needs today and as your workload grows. It is not a one-time spreadsheet exercise. It is an ongoing operational practice: measure what you have, compare it with what your queries and clients actually use, then make small, reversible changes and verify the result. This guide is written for developers, DevOps engineers, and technical founders who run PostgreSQL themselves. It connects sizing decisions to real commands, expected output, failure signals, and recovery steps. Every example uses read-only checks first, explicit placeholders instead of secrets, and a verification command before you consider a change successful. The goal is operational safety: observe before changing, limit the blast radius, use placeholders instead of secrets, verify the result, and document how to recover if the expected state is not reached. ## Version and Environment Inventory Before you change any parameter, know exactly what you are running. Start with the PostgreSQL version and the operating system, because capacity behavior changes between major releases. For example, PostgreSQL 14 introduced significant improvements to connection scaling, while version 13 and earlier behave differently under many short-lived connections. Run these read-only commands as the postgres operating system user or any role with sufficient privileges: SELECT version(); Expected output (your version string will vary): PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.0, 64-bit Check the shared memory and resource limits imposed by the operating system: cat /proc/sys/kernel/shmmax cat /proc/sys/kernel/shmall ulimit -n # open file descriptors per process If your database runs inside Docker, verify the container limits rather than the host limits. For example: docker inspect --format '{{.HostConfig.Memory}}' docker inspect --format '{{.HostConfig.NanoCpus}}' Record these values in a runbook. They anchor every later sizing decision: if the OS only allows 1 GB of shared memory, setting shared_buffers to 4 GB will not work. Blast radius and recovery: none of these commands modify state. If you accidentally run a write command instead of a read command, stop immediately and restore from your latest backup. Always connect with a read-only role for inventory checks where possible. For example: CREATE ROLE readonly_user WITH LOGIN PASSWORD 'replace_with_strong_password'; GRANT CONNECT ON DATABASE your_database TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user; Then use readonly_user for all observation commands in this guide. ## Safe Configuration Path PostgreSQL capacity tuning usually starts with a few high-impact parameters in postgresql.conf . The safe path means: - Read the current settings. - Estimate what the new value should be based on actual workload metrics. - Change one parameter at a time. - Reload or restart as required. - Verify the change took effect and did not cause errors. ### Current Settings Query the live database for the parameters you intend to change: SELECT name, setting, unit, context FROM pg_settings WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem', 'max_connections'); Expected output looks like: name | setting | unit | context ----------------------+---------+------+------------ max_connections | 100 | | postmaster shared_buffers | 16384 | 8kB | postmaster work_mem | 4096 | kB | user maintenance_work_mem | 65536 | kB | user effective_cache_size | 524288 | 8kB | user Note the unit column. shared_buffers is in 8 kB pages, so 16384 means 128 MB. work_mem is in kB, so 4096 means 4 MB. ### Sizing Example for shared_buffers A common rule of thumb is to set shared_buffers to 25 percent of the system RAM for a dedicated database server, but not more than 8 GB on older PostgreSQL versions due to checkpoint and double-buffering overhead. On PostgreSQL 12 and later, larger values are usually safe, but start conservative. Suppose your server has 16 GB of RAM. 25 percent is 4 GB. In postgresql.conf : shared_buffers = 4GB This change requires a restart because the context is postmaster . Before restarting, check the current value again and make sure no other changes are pending. ### Sizing Example for work_mem work_mem controls how much memory each sort or hash operation can use before spilling to disk. It is per operation, not per query or per connection. A query with several sorts can use several times work_mem . Set it too high and many concurrent connections can exhaust RAM. A starting value for an OLTP workload with 100 max connections and 16 GB RAM: - Keep shared_buffers at 4 GB. - Leave about 2 GB for the OS and other processes. - PostgreSQL shared memory plus per-backend overhead: roughly 10 to 20 percent of total RAM for safety. - Remaining for work_mem and caches: about 10 GB. - Average sorts per query: 2 to 3. - Concurrent active queries: assume 20 at peak. Calculation: 10 GB / (20 active queries x 3 operations) = 10 GB / 60 = 170 MB per operation. That is too high for most systems; a more typical value is 4 to 16 MB. If sorting 10 MB of data can stay in memory with 16 MB work_mem, most queries will be fast without risking memory exhaustion. work_mem = 16MB This change takes effect at the next command for a session, or immediately if you reload and reconnect. To apply to all sessions without restart, run: sudo systemctl reload postgresql Then verify: SHOW work_mem; Expected output: work_mem ---------- 16MB ### Sizing Example for max_connections max_connections is a hard limit on concurrent client connections. Increasing it without enough memory can cause swapping and slow performance. Instead of raising it high, consider using a connection pooler like PgBouncer in production. If you must raise it, estimate per-connection memory: work_mem plus temp_buffers plus stack and other overhead, typically 2 to 10 MB per connection. With 100 connections, 5 MB average, you need about 500 MB. With 500 connections, that rises to 2.5 GB. max_connections = 300 This change requires a restart. Plan for a maintenance window. Verify after restart: SHOW max_connections; ### Blast Radius and Recovery Always back up your postgresql.conf before editing: cp /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.backup-$(date +%Y%m%d) If PostgreSQL fails to start after a change, check the log file (usually in /var/log/postgresql/ or via journalctl -u postgresql ). Restore the previous file from backup and restart: sudo cp /etc/postgresql/15/main/postgresql.conf.backup-YYYYMMDD /etc/postgresql/15/main/postgresql.conf sudo systemctl restart postgresql ## Verification and Diagnostics Verification is not just checking that a parameter changed. You must confirm that the change had the intended effect on performance or resource use, and did not introduce new problems. ### Connection Count and Pooling To see current connections and whether you are near the limit: SELECT count(*) AS current_connections, max_conn FROM (SELECT count(*) FROM pg_stat_activity) AS current_connections, (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') AS max_conn; Expected output: current_connections | max_conn ---------------------+---------- 45 | 300 If you consistently run above 80 percent of max_connections , consider a pooler or reduce idle session timeouts. ### Cache Hit Ratio Shared buffer cache hit ratio indicates how often PostgreSQL finds data in memory rather than reading from disk. The standard query: SELECT sum(heap_blks_read) AS heap_read, sum(heap_blks_hit) AS heap_hit, round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS cache_hit_ratio FROM pg_statio_user_tables; Aim for a ratio above 99 percent for an OLTP workload. If it is lower, consider increasing shared_buffers or adding RAM, but also verify that the workload is not scanning huge tables unnecessarily. ### Slow Queries and Index Usage Check for queries that are slow or not using indexes: SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5; Requires the pg_stat_statements extension: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; If a query appears with high mean_time and a large number of rows, run EXPLAIN on it: EXPLAIN (ANALYZE, BUFFERS) SELECT ...; Look for Seq Scan on large tables where an index would help. Add an index and verify the query plan changes to Index Scan or Bitmap Index Scan . ### Disk Usage and Growth Monitor database size and table growth: SELECT pg_database_size('your_database'); -- or SELECT pg_size_pretty(pg_database_size('your_database')); Track growth over time with a simple query: SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; If a table grows by more than 20 percent per month, plan for vacuum and possibly partitioning before you run out of disk. ## Failure Modes and Recovery Knowing what can go wrong makes capacity planning less scary. Here are common failure modes and how to recover. ### Out of Disk Space If pg_database_size approaches the filesystem limit, PostgreSQL may stop accepting writes, and in the worst case, the server can crash and refuse to restart until space is freed. Recovery steps: - Check disk usage: df -h and du -sh /var/lib/postgresql if the data directory is there. - Remove old WAL files if you have a working archive: pg_archivecleanup or rely on your backup tool. - VACUUM FULL or CLUSTER a bloated table to reclaim space, but note that it takes an exclusive lock and may cause downtime. - Increase storage or move the data directory to a larger volume. - Verify with df -h and SELECT pg_size_pretty(pg_database_size('your_database')); . ### Connection Exhaustion When max_connections is reached, new clients receive the error FATAL: sorry, too many clients already . The application fails, even though the database itself is not out of memory or CPU. Recovery steps: - Identify idle connections: SELECT pid, usename, state, query_start FROM pg_stat_activity WHERE state = 'idle'; - Terminate idle connections older than a threshold: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '10 minutes'; - Set an idle timeout at the application or pooler level. - If the workload legitimately needs more connections, consider raising max_connections but first add memory or a connection pooler like PgBouncer. ### Memory Exhaustion from work_mem If work_mem is too high, many concurrent sorts can cause the operating system to swap heavily or invoke the OOM killer. Recovery steps: - Watch vmstat 1 and free -h for swap usage. - If swap is high, reduce work_mem immediately. - Reload: sudo systemctl reload postgresql - Verify: SHOW work_mem; and monitor memory usage for a few minutes. ### WAL Archive Failure If continuous archiving fails, WAL files accumulate and disk fills up. Recovery steps: - Check pg_stat_archiver for failures: SELECT * FROM pg_stat_archiver; - Fix the archive command or storage. - If necessary, temporarily set archive_command = '/bin/true' to allow the server to continue, but this breaks point-in-time recovery until fixed. Do this only as a last resort and with full awareness of the impact. ## Operations Checklist Use this checklist before, during, and after any capacity change. Assign an owner and a review cadence for each item to make sure nothing is forgotten.
ItemOwnerFrequencyDone?
Record PostgreSQL version and OS limitsDatabase Ops Lead (e.g., Priya Shah)Monthly or after any upgrade[ ]
Review current connections vs max_connectionsApplication Owner (e.g., Tom Nguyen)Weekly[ ]
Check cache hit ratio for user tablesDatabase Ops LeadWeekly[ ]
Check top 10 tables by size and growth rateData Engineer (e.g., Aisha Patel)Monthly[ ]
Verify backup and restore drillDatabase Ops LeadQuarterly[ ]
Test connection pooler failoverDevOps Engineer (e.g., Carlos Mendez)Quarterly[ ]
Review slow query log and pg_stat_statementsApplication OwnerWeekly[ ]
Validate disk space projections for next 6 monthsInfrastructure Manager (e.g., Li Wei)Monthly[ ]
Audit parameter changes and rollback plansChange Advisory Board (single decision maker: Priya Shah)Before every production change[ ]
For each row, the owner is a specific person, not a team. If you are a solo developer, the owner is you. The frequency is a reminder to revisit the item, not a strict audit. Mark [ ] when done. ## Common Pitfalls and How to Avoid Them ### 1. Setting shared_buffers Too High Why it happens: A rule of thumb says 25 percent of RAM, so people set it to 50 percent or more, expecting better performance. The problem: The operating system also caches files, and PostgreSQL may double-cache data in shared buffers and OS cache. Too much shared memory can reduce the OS cache effectiveness and increase checkpoint write spikes. How to avoid: Start at 25 percent of RAM. Benchmark before and after. Increase only if the cache hit ratio improves and checkpoints remain manageable. On PostgreSQL 12+, you can try larger values but watch pg_stat_bgwriter for checkpoint stats. ### 2. Ignoring Connection Overhead Why it happens: Developers assume each connection is lightweight. They open a connection per request or per thread and set max_connections to 1000. The problem: Each backend process consumes memory and CPU. Context switching between hundreds of processes can degrade performance more than the database work itself. How to avoid: Use a connection pooler. PgBouncer in transaction mode can handle thousands of client connections with only tens of actual PostgreSQL backends. Measure active vs idle connections regularly. ### 3. Not Monitoring Table Bloat Why it happens: Autovacuum runs silently, and DBA attention goes to slow queries, not table size. The problem: Dead tuples accumulate, tables and indexes bloat, queries slow down, and disk space fills up even though logical data size is unchanged. How to avoid: Schedule regular monitoring of dead tuples and vacuum progress. Use extensions like pgstattuple or query pg_stat_user_tables for n_dead_tup . Set autovacuum thresholds appropriate to your churn rate. Run manual VACUUM on high-churn tables during low traffic. ### 4. Forgetting to Test Recovery Why it happens: Backup scripts exist, but nobody restores from them until disaster strikes. The problem: Backups may be corrupt, incomplete, or not restorable due to missing WAL archives. A capacity failure that requires restore becomes a full outage. How to avoid: Perform a quarterly restore drill. Time the restore. Verify data integrity with checksums. Document the exact steps and expected recovery time objective (RTO) and recovery point objective (RPO). Treat a backup as untested until a restore succeeds. ### 5. Changing Multiple Parameters at Once Why it happens: A blog post suggests a group of settings, and the operator copies all of them. The problem: If performance degrades, you cannot tell which parameter caused it. Rollback is harder and may introduce new issues. How to avoid: Change one parameter at a time. Wait at least one full business cycle (24 hours) to observe workload patterns. Use ALTER SYSTEM SET for individual changes and keep a change log. Example: ALTER SYSTEM SET work_mem = '16MB'; SELECT pg_reload_conf(); Then verify and monitor before moving to the next parameter. ## Conclusion PostgreSQL capacity planning is not a one-time configuration. It is a loop: measure, estimate, change one variable, verify, and recover if needed. The exact numbers vary by workload, but the discipline does not. Start with the Version and Environment Inventory in this guide. Record your current settings and OS limits. Then pick one low-risk change: adjust work_mem or add an index to a slow query. Use the verification queries to see the effect. Set up a weekly review of connections and cache hit ratio. Assign an owner to each checklist item, and schedule a quarterly restore drill. A reliable capacity plan makes failure visible, protects sensitive values, limits changes to the intended resource, and defines recovery verification before an incident forces the decision. With the concrete commands and examples above, you can keep your PostgreSQL database performant and avoid the most common capacity mistakes.