{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"postgresql-configuration-for-production-the-settings-that-actually-matter-ksgkx","url":"https://zyvop.com/postgresql-configuration-for-production-the-settings-that-actually-matter-ksgkx","title":"PostgreSQL Configuration for Production: The Settings That Actually Matter","subtitle":"The eight PostgreSQL settings that deliver the biggest performance gains on small VPS deployments.","tldr":"PostgreSQL's defaults are designed to run anywhere, not perform well. Learn the 8 configuration settings that matter, realistic connection limits, PgBouncer sizing, a production-ready 4GB VPS configuration, cache verification queries, and the performance problems tuning alone cannot solve.","keywords":["PostgreSQL Node.js performance","PostgreSQL configuration production 2026","random_page_cost SSD","postgresql.conf tuning","shared_buffers PostgreSQL","PostgreSQL VPS configuration","PGTune Node.js","work_mem PostgreSQL"],"entities":["ZyVOP","PostgreSQL Node.js performance","PostgreSQL configuration production 2026","random_page_cost SSD","postgresql.conf tuning","shared_buffers PostgreSQL","PostgreSQL VPS configuration","PGTune Node.js","work_mem PostgreSQL"],"keyTakeaways":["The default PostgreSQL configuration is tuned for compatibility, not performance.","It ships with settings calibrated for a machine with 128MB of RAM in 1996 — shared_buffers = 128MB, work_mem = 4MB, max_connections = 100.","Running these defaults on a modern 4GB VPS means your database is using a fraction of the available memory and hitting connection limits long before the hardware is stressed."],"headings":["Before You Change Anything: Understand Your Hardware","Finding postgresql.conf","The Eight Settings","1. shared_buffers — Postgres's Primary Cache","2. effective_cache_size — Tells the Query Planner About OS Cache","3. work_mem — Memory Per Sort or Hash Operation","4. maintenance_work_mem — Memory for VACUUM, CREATE INDEX, ALTER TABLE","5. checkpoint_completion_target — Spread Write I/O","6. wal_buffers — Write-Ahead Log Buffer","7. random_page_cost — Tell Postgres About Your Storage Type","8. max_connections — Set Lower Than You Think","The Complete postgresql.conf Block","Verifying the Changes","Using PGTune as a Starting Point","What These Settings Do Not Fix"],"outboundLinks":[],"contentText":"The default PostgreSQL configuration is tuned for compatibility, not performance. It ships with settings calibrated for a machine with 128MB of RAM in 1996 — shared_buffers = 128MB, work_mem = 4MB, max_connections = 100. Running these defaults on a modern 4GB VPS means your database is using a fraction of the available memory and hitting connection limits long before the hardware is stressed. Tuning PostgreSQL configuration is not complicated. There are eight settings that account for the vast majority of the performance difference between a default install and a production-ready one. This guide covers all eight with the specific values to use, why they matter, and how to verify they are working. Before You Change Anything: Understand Your Hardware Every tuning recommendation is relative to your server's RAM and CPU count. Know your numbers before setting values: # RAM free -h # or cat /proc/meminfo | grep MemTotal # CPU cores nproc # Storage type (SSD vs HDD matters for random_page_cost) cat /sys/block/sda/queue/rotational # 0 = SSD, 1 = HDDThe examples below are calibrated for a 4GB RAM VPS with 2 CPU cores — a common starting point for self-hosted Node.js apps. Finding postgresql.conf # Show config file location sudo -u postgres psql -c \"SHOW config_file;\" # Typical locations: # Ubuntu/Debian: /etc/postgresql/16/main/postgresql.conf # Docker postgres image: /var/lib/postgresql/data/postgresql.confAfter editing, reload without restarting (most settings apply on reload): sudo systemctl reload postgresql # Or from psql: SELECT pg_reload_conf();Some settings require a full restart (shared_buffers, max_connections, wal_buffers). Check which ones: SELECT name, context FROM pg_settings WHERE name IN ( 'shared_buffers', 'work_mem', 'max_connections', 'effective_cache_size', 'wal_buffers', 'checkpoint_completion_target' ); -- context = 'postmaster' → requires restart -- context = 'sighup' → reload is sufficientThe Eight Settings 1. shared_buffers — Postgres's Primary Cache The amount of memory PostgreSQL uses for caching data pages. The default (128MB) is tiny. The standard recommendation is 25% of total RAM. # Default shared_buffers = 128MB # For 4GB RAM VPS shared_buffers = 1GB # For 8GB RAM VPS shared_buffers = 2GB # For 16GB+ RAM VPS shared_buffers = 4GB # Cap around 4-8GB — OS cache handles the rest above thatWhy 25% and not more? PostgreSQL relies on the operating system's page cache for the rest. Over-allocating shared_buffers can reduce the OS cache available, which is counterproductive. The OS cache is often more efficient at caching frequently accessed data anyway. Requires restart. 2. effective_cache_size — Tells the Query Planner About OS Cache This setting does not allocate memory — it tells the query planner how much total memory is available for caching (Postgres buffer + OS cache combined). The planner uses this to decide whether to use an index scan or a sequential scan. # Default effective_cache_size = 4GB # For 4GB RAM VPS effective_cache_size = 3GB # shared_buffers (1GB) + OS cache (~2GB) # For 8GB RAM VPS effective_cache_size = 6GBSetting this too low causes the planner to avoid index scans it should use. Setting it approximately right (total RAM minus overhead) is sufficient. 3. work_mem — Memory Per Sort or Hash Operation Memory available for sorting and hash join operations. The default (4MB) means Postgres spills to disk for any sort over 4MB. With modern data sizes, this happens constantly. # Default work_mem = 4MB # For a web API workload (multiple concurrent connections) work_mem = 16MB # For a reporting/analytics workload (fewer, heavier queries) work_mem = 64MBCritical caveat: work_mem is per operation, not per query, and not per connection. A complex query with 5 sort steps can use 5 × work_mem. With 20 active connections, total consumption could reach 20 connections × 5 operations × 16MB = 1.6GB. Set this thoughtfully relative to your connection count. The signal that work_mem is too low: EXPLAIN ANALYZE shows \"Sort Method: external merge Disk\" — Postgres is sorting to disk. -- Check if sorts are spilling to disk SELECT query, sort_space_used, sort_space_type FROM pg_stat_statements JOIN pg_sort_stats ON ...; -- Or look for \"external merge Disk\" in EXPLAIN ANALYZE output4. maintenance_work_mem — Memory for VACUUM, CREATE INDEX, ALTER TABLE Used during vacuum, index creation, and table alterations. The default is 64MB. Increasing it makes these operations faster. # Default maintenance_work_mem = 64MB # For 4GB+ RAM maintenance_work_mem = 256MBThis only applies during maintenance operations — it does not affect regular query memory. Set it higher than work_mem since maintenance operations run infrequently and benefit from more memory. 5. checkpoint_completion_target — Spread Write I/O PostgreSQL periodically writes dirty pages to disk (a \"checkpoint\"). By default, it tries to complete this in 50% of the checkpoint interval — which means a burst of write I/O. Setting this to 0.9 spreads writes more evenly across the full interval. # Default checkpoint_completion_target = 0.5 # Recommended checkpoint_completion_target = 0.9The effect: more consistent write latency. Less pronounced write spikes. Generally better for SSD-based systems. 6. wal_buffers — Write-Ahead Log Buffer Memory for buffering WAL (Write-Ahead Log) data before it is written to disk. The default (-1, which means auto-set to 1/32 of shared_buffers) is usually fine after tuning shared_buffers, but setting it explicitly to 16MB avoids a restart if you adjust shared_buffers later. # Default (auto) wal_buffers = -1 # Explicit value — good for write-heavy workloads wal_buffers = 16MBRequires restart. 7. random_page_cost — Tell Postgres About Your Storage Type The query planner uses random_page_cost to estimate the cost of a random disk read. The default (4.0) assumes a spinning disk where random reads are expensive. For an SSD, random reads are nearly as fast as sequential reads. # Default (HDD assumption) random_page_cost = 4.0 # For SSD random_page_cost = 1.1 # For NVMe (fastest) random_page_cost = 1.0With the default value on an SSD, the planner often incorrectly prefers sequential scans over index scans, because it overestimates the cost of the random I/O an index scan requires. Setting this correctly is one of the most impactful changes for index-heavy workloads. Verify your storage type: cat /sys/block/sda/queue/rotational # 0 = SSD, use 1.1 # 1 = HDD, keep at 4.08. max_connections — Set Lower Than You Think The default (100) sounds like enough. But Postgres allocates shared memory proportional to max_connections for things like lock tables and process state — even if those connections are never used. Reducing max_connections to what you actually need frees that memory for the cache. # Default max_connections = 100 # With PgBouncer handling multiplexing (recommended) max_connections = 50 # Actual Postgres connections — PgBouncer handles the rest # Without PgBouncer, single-server app max_connections = 100 # Keep default, but no higher unless neededIf you are running PgBouncer in front of Postgres, max_connections in Postgres can be set to default_pool_size in PgBouncer plus a buffer for superuser connections (typically +10). Requires restart. The Complete postgresql.conf Block A production-ready config for a 4GB RAM, 2-core VPS with SSD storage: # postgresql.conf — 4GB RAM, 2 CPU, SSD # Memory shared_buffers = 1GB effective_cache_size = 3GB work_mem = 16MB maintenance_work_mem = 256MB wal_buffers = 16MB # Planner random_page_cost = 1.1 # SSD effective_io_concurrency = 200 # SSD (use 2 for HDD) default_statistics_target = 100 # More accurate query planning # Write behaviour checkpoint_completion_target = 0.9 min_wal_size = 1GB max_wal_size = 4GB # Connections max_connections = 50 # With PgBouncer; use 100 without # Logging (useful for development, reduce in prod) log_min_duration_statement = 1000 # Log queries over 1 second log_checkpoints = on log_connections = off # Too noisy in prod log_disconnections = off log_lock_waits = on deadlock_timeout = 1s # Autovacuum — keep tables clean autovacuum = on autovacuum_max_workers = 3 autovacuum_vacuum_scale_factor = 0.1 # Vacuum after 10% of rows change (default 20%) autovacuum_analyze_scale_factor = 0.05Verifying the Changes After reloading (or restarting), confirm the settings took effect: -- Verify key settings SELECT name, setting, unit, context FROM pg_settings WHERE name IN ( 'shared_buffers', 'work_mem', 'effective_cache_size', 'random_page_cost', 'max_connections', 'wal_buffers', 'checkpoint_completion_target' ); -- Check if shared_buffers is actually being used SELECT blks_hit, blks_read, round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = 'yourdb'; -- Target: cache_hit_ratio > 99% -- If below 95%, either shared_buffers is too small or your working set exceeds RAMA cache hit ratio below 95% means Postgres is reading from disk frequently — either shared_buffers is too small, your working data set is larger than available RAM, or you have missing indexes causing full table scans. Using PGTune as a Starting Point For any server not covered by the examples above, PGTune generates a baseline configuration: # Or use the web version: https://pgtune.leopard.in.ua # Example output for 8GB RAM, 4 CPUs, SSD, web application: # shared_buffers = 2GB # effective_cache_size = 6GB # maintenance_work_mem = 512MB # checkpoint_completion_target = 0.9 # wal_buffers = 16MB # default_statistics_target = 100 # random_page_cost = 1.1 # work_mem = 6990kB ← tuned for connection count # huge_pages = off # min_wal_size = 1GB # max_wal_size = 4GBPGTune is a good starting point, not a final answer. Validate with pg_stat_database cache hit ratios and EXPLAIN ANALYZE on your actual slow queries. What These Settings Do Not Fix Configuration tuning fixes resource allocation and planner behaviour. It does not fix: Missing indexes — no amount of shared_buffers makes a sequential scan on a million-row table fast N+1 queries — configuration does not reduce round trips Unvacuumed tables with high dead row percentages Poorly written queries with cross joins or non-SARGable predicates Run EXPLAIN ANALYZE on your slow queries before and after tuning. If a query is slow because of a sequential scan on a large table, an index will help more than any configuration change.","contentHash":"sha256:cdf24865cd0c6aef060e346446624a7c21fa7a8d9be9c7883c35126e58b6a409","authorName":"ZyVOP","authorUrl":"https://zyvop.com/author/zyvop","authorSameAs":["https://zyvop.com","https://github.com/zyvop","https://x.com/zyvop1"],"category":null,"tags":["PostgreSQL Node.js performance","PostgreSQL configuration production 2026","random_page_cost SSD","postgresql.conf tuning","shared_buffers PostgreSQL","PostgreSQL VPS configuration","PGTune Node.js","work_mem PostgreSQL"],"audience":"Software engineers and developers building applications with PostgreSQL Node.js performance","tone":"Instructional, practical, code-first","readingTimeMinutes":6,"wordCount":1340,"faqs":null,"primaryTopic":"PostgreSQL Node.js performance","publishedAt":"2026-05-29T07:19:49.385Z","updatedAt":"2026-08-18T23:15:00.373Z","canonicalUrl":"https://zyvop.com/postgresql-configuration-for-production-the-settings-that-actually-matter-ksgkx"}