Start with the PostgreSQL Bottleneck
Imagine a slow reports page on a VPS while the rest of the application remains available. Memory pressure, disk waits, locks, connection churn or the query plan could be responsible. Correlate database, application and operating-system measurements before choosing a setting to change.
This guide connects four tuning decisions: shared_buffers, work_mem, WAL/checkpoints and PgBouncer pooling. A configuration value is useful only when it fits the workload, memory budget and recovery requirements. A larger cache or connection limit does not automatically improve throughput.
PostgreSQL behavior and examples below are checked against version 17 documentation; PgBouncer configuration follows current 1.26 documentation. Verify the installed versions before adapting them. Numeric examples describe a 4 GiB VPS primarily allocated to PostgreSQL, with measured headroom for the operating system, local pooler and any co-located application. They are candidates for evaluation, not universal defaults.
Keep a consistent PostgreSQL backup, a tested restore procedure and the previous configuration before a production change. The guide to offsite backups with Restic or Borg to S3-compatible storage covers remote copies. Copying a live PostgreSQL data directory with a generic file tool does not by itself produce a consistent database backup; prepare it with a PostgreSQL-aware method first.
Budget Memory and Connections Together
Available CPU, RAM and storage behavior depend on the VPS plan and hosting architecture. Measure sustained I/O latency and memory pressure instead of assuming that every VPS has the same limits or contention. Count all services on the machine, not just the database process.
Reserve space for shared buffers, backend processes, concurrent query operations, maintenance and usable filesystem cache. Connection behavior affects that budget: many short-lived connections add setup work, while too many active backends can compete for memory and CPU. A pool limits some concurrency but adds a queue when its backends are busy.
Keep the workload model explicit: expected active queries, heavy-report overlap, parallel workers and maintenance jobs. Revisit it when a new feature or scheduled task changes the mix. Peak concurrency matters even when the daily average is low.
shared_buffers is PostgreSQL’s shared cache for table and index pages. The operating system has a separate filesystem cache, so a PostgreSQL buffer miss is not necessarily a physical disk read. Allocating more shared memory can help a cache-constrained workload but leaves less room for other uses; compare latency and memory pressure under representative load.
PostgreSQL’s approximately 25% starting suggestion applies to a dedicated database server with at least 1 GB RAM. In the 4 GiB example, 1 GiB goes to shared_buffers. The remaining 3 GiB must cover query memory, backend and background processes, maintenance, the OS, filesystem cache and other services. Reduce the database budget when the application needs more memory.
effective_cache_size is the planner’s estimate of disk cache available to a single query, considering PostgreSQL buffers and the usable portion of kernel cache. It reserves no memory. Account for overlapping cached pages and concurrent queries. The 2 GiB candidate below assumes meaningful usable kernel cache in addition to the 1 GiB shared buffers; replace that estimate with one supported by measurements.
Use the actual configuration file reported by SHOW config_file. shared_buffers and max_connections require a server restart. The connection limit below is an illustrative ceiling, not a target for active queries; the PgBouncer example later caps its single database alias at 25 backends, leaving room that must be budgeted for direct connections and administrative reserves.
# postgresql.conf excerpt: measured 4 GiB budget
shared_buffers = 1GB
# Planner estimate only; no memory reservation
effective_cache_size = 2GB
max_connections = 50
The PostgreSQL resource settings reference explains memory allocation and parameter scope. Compare effective values after applying a change; editing a file or successfully reloading it does not prove a restart-only setting is active.
Account for work_mem Multipliers and Spills
work_mem is a base budget per query operation, not one allocation per query or connection, and is not all reserved in advance. Several sort or hash operations can be active in one plan. Hash operations use work_mem multiplied by hash_mem_multiplier, whose PostgreSQL 17 default is 2. Parallel workers have individual operation budgets, and concurrent queries multiply the total.
For illustration, 20 concurrent queries with two hash operations each, 4 MiB work_mem and a hash multiplier of 2 imply about 320 MiB for those operations without parallelism. This is not a total server-memory bound: other plan nodes, workers, backend overhead, cache and maintenance also consume memory. Raising one report to 128 MiB changes its potential footprint substantially; verify its plan and concurrency before doing so.
Check temporary-file activity and the query plan before increasing memory. An index or query change may avoid a spill without a larger global limit. Separate query and maintenance budgets; with the example below, three autovacuum workers can use about 192 MiB of working memory in addition to process overhead. Autovacuum runs throughout the workload cycle.
# Illustrative budget inputs, not aggregate RAM limits
work_mem = 4MB
maintenance_work_mem = 256MB
autovacuum_work_mem = 64MB
autovacuum = on
A spilling report may benefit from a targeted increase, but measure its result and total memory impact. The pg_stat_statements reference describes query-level statistics. Enabling the module requires shared_preload_libraries and a restart, query-ID calculation, and CREATE EXTENSION in a database where its views are needed; merely querying the view does not enable collection.
Compare interval changes in pg_stat_database.temp_files and temp_bytes, accounting for counter resets. For one reviewed report, use SET LOCAL inside an explicit transaction. The example contains no report query; 128 MiB is only a value to evaluate. Its effect ends at COMMIT or ROLLBACK. A plain session SET does not automatically revert at transaction end and should not be used as persistent client state through transaction pooling.
BEGIN;
SET LOCAL work_mem = '128MB';
-- Run the reviewed report query here, within this transaction.
COMMIT;
Separate database roles and bounded pools can isolate batch work. Role defaults take effect at login, so changing them does not update already-open pooled server sessions; coordinate connection recycling when applying new defaults. Do not leave an explicit transaction idle.
Balance WAL, Checkpoints and Durability
Write-ahead logging requires the relevant WAL records to reach durable storage before changed data pages are written there. It does not mean every in-memory change waits for disk first. Keep fsync and full_page_writes protection in place while tuning; recovery depends on the storage stack honoring its write guarantees.
Frequent checkpoints can add data-file I/O and full-page WAL images. A larger max_wal_size or longer checkpoint_timeout may reduce checkpoint frequency, but needs disk headroom and can lengthen crash recovery. max_wal_size is a soft limit, not a storage cap, and WAL volume can trigger a checkpoint before the timeout. Archive failures or replication retention can also accumulate WAL.
Evaluate the following values against write volume, free space and recovery goals. replica records information needed for physical replication and archiving, but does not configure a backup or point-in-time recovery by itself. Preserve logical where logical decoding requires it; changing wal_level requires a restart.
# postgresql.conf: workload-dependent candidates
wal_level = replica
wal_compression = on
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 256MB
# Retain commit durability; no universal few-millisecond loss claim
synchronous_commit = on
wal_compression = on means pglz compression of full-page WAL images in PostgreSQL 17, not all WAL records; compare CPU cost and WAL volume. synchronous_commit = on waits for local durable WAL and, when configured, synchronous-standby confirmation. off may lose recently acknowledged transactions after a crash; its loss window is not universally a few milliseconds. remote_write still waits for local durability, but the standby’s filesystem write is not the same as survival of its operating-system crash. The setting applies at transaction commit, not to an individual table.
Keep autovacuum progressing across busy and quiet periods. Each worker has an autovacuum_work_mem budget; at -1 it inherits maintenance_work_mem. Account for concurrent manual maintenance as well. Review dead tuples, long transactions, vacuum progress and transaction-ID age before changing table thresholds or vacuum cost limits. Deferring required cleanup can create a larger later workload.
Correlate checkpoint timing, WAL growth, temporary files, disk latency and application errors. The guide to monitoring with Prometheus, Grafana and Uptime Kuma provides infrastructure context. PostgreSQL 17 exposes checkpoint statistics through pg_stat_checkpointer; check view differences when using another version.
Configure PgBouncer for a Defined Access Model
An application that repeatedly opens short-lived connections can spend unnecessary time establishing database sessions. PgBouncer reuses server connections and can limit their count. It does not fix an expensive query or lock wait; when all backends are busy, clients wait for capacity. Measure queue delay alongside database execution time and application timeouts.
Session pooling retains a backend for the client session. Transaction pooling returns it after each transaction, so the next transaction may use another backend. Check dependencies on session SET/RESET, LISTEN, session advisory locks and preserved temporary tables. Use session pooling or a separate direct path where required. Protocol-level named prepared statements require supported PgBouncer behavior and a positive max_prepared_statements; SQL PREPARE is not the same feature. server_reset_query is not used in transaction mode, so DISCARD ALL does not make unsupported session state safe.
The following is an excerpt for an existing service where application clients, PgBouncer and PostgreSQL communicate over IPv4 loopback on the same machine. Provision appdb and a minimally privileged appuser first, with a SCRAM-capable client. Reconcile PostgreSQL’s existing HBA rules so the first matching rule for appdb/appuser from 127.0.0.1/32 uses scram-sha-256; do not replace the complete HBA file. Preserve the service’s startup, log and permission configuration.
; Existing service configuration excerpt; local clients only
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir =
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 25
server_idle_timeout = 300
max_prepared_statements = 100
Provision auth_file using this quoted field format; the placeholders are not a usable verifier:
"appuser" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
An authorized administrator must securely provision the exact PostgreSQL SCRAM verifier into the file, including identical salt and iterations. Independently hashing the same password is insufficient. Restrict file access to the PgBouncer service account and authorized administrators; do not expose its contents in logs or shell history. Omitting a forced user/password from the database definition is intentional: verifier-based backend authentication also requires SCRAM client authentication and the same user. Test a new client connection end to end before redirecting application traffic.
default_pool_size is per user/database pair; reserve_pool_size adds capacity after reserve_pool_timeout. For the single appdb/appuser pool here, 20 normal plus 5 reserve backends fit the max_db_connections limit of 25 for this PgBouncer database alias. max_client_conn = 100 limits clients, not active database queries. PostgreSQL max_connections must accommodate all pooler instances, aliases, user pools, direct clients and reserved administrative slots. OS file-descriptor needs include both client and server connections. The 100 prepared-statement limit is an illustrative memory/performance tradeoff, not an application compatibility guarantee.
Read the PgBouncer documentation for the installed version. This example disables Unix sockets and listens only on 127.0.0.1:6432. Remote clients need a separate network-access and certificate-verifying TLS design for the relevant connection legs. Keep INI comments on separate lines; a semicolon after a value is not a comment. Do not ignore an unsupported startup parameter unless its behavior is deliberately accounted for.
Check Query Plans and Storage Assumptions
random_page_cost and seq_page_cost express the planner’s relative I/O cost assumptions. Lowering the random cost can encourage index scans, but an SSD label alone does not justify a global value. Compare plans across representative queries and the actual cache/storage behavior before changing these illustrative candidates:
# Optional planner experiment only; not enabled by this excerpt
# random_page_cost = 1.1
# seq_page_cost = 1.0
Distinguish PostgreSQL buffer reads from physical disk reads, which the OS cache may satisfy. Monitor available memory and active paging, not just whether swap contains any pages. If pressure rises, identify concurrent query, maintenance and application demand before reducing a particular setting. Pooling can bound some backend concurrency but cannot impose a total server-memory cap.
For a hypothetical scheduling change, move a batch report away from peak web traffic and compare contention under otherwise similar conditions. The query may take the same CPU and I/O overall while overlapping less critical work. This is a workload-management decision whose result still needs measurement.
Use a Repeatable Tuning Workflow
Choose one suspected bottleneck, the evidence that supports it and the metric expected to improve. Record a rollback threshold before making the change. This avoids attributing a result to whichever of several simultaneous changes seems most plausible.
Collect baseline query times, active connections, pool waits, temporary-file growth, WAL volume, checkpoints and disk latency. Include a complete relevant job cycle. The guide to building VPS monitoring can help organize the measurements; choose database-specific metrics as well.
Calculate candidate memory and connection budgets before testing. For a pool change, verify application compatibility and authentication. For WAL changes, include disk headroom and recovery implications. Classify each parameter by whether it needs a restart, reload or session change; plan the operational effect explicitly.
Change one bounded item and compare equivalent traffic, data and cache conditions. Include peak demand and scheduled work before drawing a conclusion. Keep the tested version and settings with the measurements so a later application change does not invalidate the comparison unnoticed.
Use pg_stat_statements to find queries responsible for substantial total work, then inspect their plans and data distribution. Plain EXPLAIN plans a statement; EXPLAIN ANALYZE actually executes it, including side effects. Evaluate costly or modifying statements in a controlled environment. An index or rewrite must be assessed for read benefit, write cost and correctness.
Keep deployment and rollback procedures specific to the changed component. The rsync, symlink and systemd deployment guide can inform application release handling, but switching an application symlink does not eliminate a PostgreSQL restart or roll back database state.
Review application logs, database logs and metrics together after each change. The centralized logging guide provides context; check the support status of the collector you deploy. Restrict access and retention because query logs can contain sensitive values.
Verify Effective Settings and Diagnostic Coverage
The earlier excerpts belong to one measured configuration; do not install multiple conflicting copies. The following optional diagnostics illustrate additional visibility. Logging every temporary file or autovacuum action can create substantial volume, and duration logging can expose SQL text. Set access, storage and retention policy before enabling them; review the needed scope after investigation.
# Optional diagnostic settings in postgresql.conf
log_checkpoints = on
# Uncomment only for a reviewed investigation scope:
# log_autovacuum_min_duration = 0
# log_temp_files = 0
# log_min_duration_statement = 500
# Separate autovacuum tuning candidates, only after measurement:
# autovacuum_vacuum_cost_limit = 200
# autovacuum_naptime = 10s
After the required restart or reload, compare effective values and pending_restart. The following read-only SQL examines settings and a small activity sample; it does not establish performance improvement or show the PgBouncer client queue.
SHOW config_file;
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'work_mem',
'wal_level', 'checkpoint_timeout');
SELECT pid, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY pid
LIMIT 5;
Use the PostgreSQL resource settings reference to check the installed version’s parameter meanings. A successful configuration reload does not apply postmaster-context values; verify that required restarts and new-session defaults have taken effect.
Keep Maintenance and Release Ownership Clear
Assign owners for database maintenance, pool configuration and application compatibility tests. A performance change can cross all three areas; the person interpreting its result needs to know what was actually deployed.
Schedule heavy manual maintenance according to its locking behavior, resource use and service requirements. An off-peak start does not prove it finishes before peak traffic. Budget any maintenance_work_mem increase for concurrent jobs and distinguish manual work from continuously needed autovacuum.
For repeated analytics, consider materialized views or pre-aggregation where acceptable data freshness and refresh cost justify them. Record the resulting staleness and maintenance requirements. Reducing repeated computation is useful only if the application still receives correct, sufficiently current results.
Record which alerts indicate overdue maintenance, prolonged transactions, pool saturation or disk/WAL pressure. Test notification delivery. A checkpoint coinciding with a latency spike is a hypothesis to investigate, not proof of causation.
Keep database and application rollback steps separate. The application deployment guide covers release mechanics; database configuration changes need their own reload/restart checks, connection handling and recovery plan.
Write a short change record with versions, parameter scope, expected effect, observed result and rollback outcome. Include when role defaults required server-connection recycling. This lets later investigation distinguish a workload change from an unapplied or overridden setting.
Common Decision Errors
A single spilling query does not justify raising global work_mem. Check its plan and concurrency first; a transaction-local experiment narrows exposure but still needs a budget. A higher cache estimate likewise changes planner assumptions without creating memory.
A larger pool is not evidence of more throughput. Compare queue time, database execution time and total request latency; moving the queue into PostgreSQL can worsen contention. Keep the total backend budget within memory and administrative-access requirements.
A larger WAL threshold is not a disk-capacity safeguard. Investigate checkpoint triggers, archive failures and replication retention separately. Compression affects full-page images and consumes CPU; retain it only when the measured tradeoff fits the workload.
Autovacuum is not merely optional off-hours work. Investigate why cleanup is falling behind and preserve transaction-ID maintenance. Adjust memory, cost or table thresholds only with evidence about the current bottleneck and all concurrent work.
Choose the Next Experiment from Evidence
Select the smallest change that addresses the measured constraint. A report spill suggests a query-plan and memory investigation; connection churn suggests a pooling evaluation; checkpoint pressure suggests a WAL and storage review. Avoid changing all three before knowing which one limits the application.
Keep the version-appropriate PostgreSQL resource documentation and PgBouncer documentation alongside the change record. The guides to centralized logs and VPS monitoring can help organize supporting observations.
Judge the Result Against the Whole Budget
A successful change meets the application’s latency and throughput requirements within its RAM, connection and storage budget while preserving the required durability. Compare representative load, confirm effective settings and retain a usable rollback. No sequence of three tweaks or fixed observation period guarantees improvement.
Expand a tested change only after its relevant workload cycle, including scheduled jobs, has been observed. Recheck the budget when data volume, application behavior, software versions or hosting resources change.
Before production rollout, confirm the recovery path using a consistent PostgreSQL backup and restore test. The guide to offsite backups covers the separate storage copy; having a remote file set alone does not establish database recoverability.
