Install PostgreSQL with Sensible Tuning

A default PostgreSQL installation is intentionally conservative — tuned to run on a 256 MB virtual machine from 2005. On any real server you will leave significant performance on the table unless you adjust a handful of key parameters. This guide installs the latest PostgreSQL release from the official PGDG repositories, then applies evidence-based tuning for shared_buffers, work_mem, effective_cache_size, and WAL behaviour.

Install PostgreSQL from PGDG Repositories

Distro-packaged PostgreSQL is often one or two major versions behind. Use the official PostgreSQL Global Development Group (PGDG) repos to get the current release.

Debian / Ubuntu

sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  | sudo gpg --dearmor -o /usr/share/keyrings/pgdg.gpg
echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] \
https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-16

Fedora / RHEL 9 / Rocky Linux 9

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16-contrib
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

Arch Linux

sudo pacman -S postgresql
sudo -u postgres initdb --locale=C.UTF-8 -D /var/lib/postgres/data
sudo systemctl enable --now postgresql

On Debian/Ubuntu the package ships with a default cluster already initialised and the service already enabled. Verify it is running before continuing:

sudo systemctl status postgresql

Locate postgresql.conf

All tuning lives in postgresql.conf. Its path varies by distro and major version.

• Debian/Ubuntu: /etc/postgresql/16/main/postgresql.conf
• RHEL/Fedora/Rocky: /var/lib/pgsql/16/data/postgresql.conf
• Arch: /var/lib/postgres/data/postgresql.conf

If you are unsure, ask PostgreSQL itself:

sudo -u postgres psql -c 'SHOW config_file;'

Many production setups drop overrides into conf.d/ rather than editing the main file directly. That approach is cleaner for config management. Check whether your install includes a conf.d include:

grep 'include_dir' /etc/postgresql/16/main/postgresql.conf

If it does, create a dedicated tuning file:

sudo touch /etc/postgresql/16/main/conf.d/tuning.conf
sudo chown postgres:postgres /etc/postgresql/16/main/conf.d/tuning.conf

Memory Tuning

shared_buffers

shared_buffers is PostgreSQL's dedicated buffer cache — shared across all connections. The standard starting point is 25 % of total RAM. On a system with 16 GB RAM, set it to 4 GB. PostgreSQL also relies on the OS page cache, so setting this above 40 % typically shows diminishing returns.

# Determine your total RAM in MB
awk '/MemTotal/ {printf "%d MB\n", $2/1024}' /proc/meminfo

Then set the value in postgresql.conf (or tuning.conf):

shared_buffers = 4GB          # 25 % of 16 GB RAM example

On Linux, shared_buffers values above 2 GB require that huge_pages be considered. If your kernel's shmmax is too low, PostgreSQL will refuse to start. Check and raise it if needed:

cat /proc/sys/kernel/shmmax
# If lower than your shared_buffers value in bytes, raise it persistently:
echo 'kernel.shmmax = 8589934592' | sudo tee -a /etc/sysctl.d/90-postgres.conf
sudo sysctl -p /etc/sysctl.d/90-postgres.conf

work_mem

work_mem is the memory each sort or hash operation can use before spilling to disk. The danger is that a single query can open many sort nodes simultaneously, and each connection can run multiple queries. On a busy server with 100 connections each running a complex query, even a modest work_mem multiplies quickly.

A pragmatic formula: (Total RAM - shared_buffers) / (max_connections × 2). For 16 GB RAM, 4 GB shared_buffers, and 100 max_connections that gives roughly 60 MB — a reasonable default for an OLTP workload.

work_mem = 64MB               # adjust based on formula above
max_connections = 100         # set this deliberately; do not leave it unbounded

For analytics-heavy workloads with fewer long-running queries, raising work_mem to 256 MB or higher is worthwhile. For OLTP with many short connections, prefer a connection pooler (PgBouncer) and keep work_mem lower.

effective_cache_size

effective_cache_size does not allocate memory. It is a planner hint: how much total memory (PostgreSQL buffers + OS page cache) can the planner assume is available for caching data? A higher value encourages the planner to favour index scans over sequential scans. Set it to roughly 75 % of total RAM.

effective_cache_size = 12GB   # 75 % of 16 GB RAM example

WAL and Checkpoint Tuning

Write-Ahead Log settings have a large impact on both write throughput and crash recovery time.

wal_buffers

The default of -1 (auto: 1/32 of shared_buffers, capped at 16 MB) is usually fine. For write-heavy workloads, 64 MB is a safe ceiling:

wal_buffers = 16MB            # or 64MB for heavy write workloads

checkpoint_completion_target

Checkpoints flush dirty pages to disk. Spreading that I/O over a longer fraction of the checkpoint interval reduces latency spikes. The default is 0.9, which is already sensible in PostgreSQL 14+; leave it unless you have reason to change it.

checkpoint_completion_target = 0.9

min_wal_size and max_wal_size

max_wal_size controls how large the WAL can grow between checkpoints (default: 1 GB). Raising it reduces checkpoint frequency at the cost of longer recovery time after a crash. For a write-heavy workload with fast storage, 4 GB is reasonable:

min_wal_size = 1GB
max_wal_size = 4GB

synchronous_commit

By default PostgreSQL waits for WAL to be flushed to disk before confirming a transaction. Setting synchronous_commit = off allows up to wal_writer_delay ms (default 200 ms) of data loss in a crash, but can double write throughput. This is acceptable for session-level analytics or queues, but think carefully before applying it cluster-wide.

# Cluster-wide: only if data loss of ~200ms on crash is acceptable
synchronous_commit = off

# Or per-session / per-transaction for specific use cases:
# SET LOCAL synchronous_commit = off;

Apply Changes and Verify

Most memory and WAL parameters require a full service restart. A reload is not sufficient for shared_buffers.

# Debian/Ubuntu
sudo systemctl restart postgresql@16-main

# RHEL/Fedora/Rocky
sudo systemctl restart postgresql-16

# Arch
sudo systemctl restart postgresql

Verify your settings took effect inside psql:

sudo -u postgres psql -c "
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'shared_buffers','work_mem','effective_cache_size',
  'wal_buffers','max_wal_size','synchronous_commit'
);
"

You should see your new values reflected in the setting column. shared_buffers and wal_buffers are reported in 8 kB blocks; divide by 128 to convert to MB.

Troubleshooting

• PostgreSQL fails to start after raising shared_buffers: The most common cause is shmmax being too low (see the kernel section above) or huge_pages = try failing silently. Check journalctl -u postgresql and /var/log/postgresql/ for the exact error.
• Out-of-memory kills after raising work_mem: You have set work_mem too high relative to the number of concurrent connections and query complexity. Reduce it by half and monitor with pg_stat_activity.
• Settings appear unchanged after restart: You may be editing the wrong postgresql.conf. Run SHOW config_file; inside psql to confirm the path, and ensure no conf.d file is overriding your changes.
• Frequent checkpoints despite raising max_wal_size: Check pg_stat_bgwriter — if checkpoints_req is high, writes are triggering checkpoints before the timeout. Consider raising max_wal_size further or investigating write amplification.