PostgreSQL ships with deliberately conservative defaults so it can start on almost any machine. On a dedicated database server those defaults leave most of the RAM and disk bandwidth unused. In this tutorial you will size the main memory, write-ahead log (WAL), query planner, connection and autovacuum settings for PostgreSQL 16 on Ubuntu 24.04, apply them in a separate drop-in file, and use pg_stat_statements to check whether they actually helped.

The example values target a dedicated server with 8 GB of RAM, 4 vCPUs and SSD/NVMe storage. The guide explains the formula behind each one so you can scale it to your own hardware.

Prerequisites

To follow this guide you need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS, used mainly as a database server.
  • PostgreSQL 16 installed from the Ubuntu repositories (sudo apt install postgresql).
  • A non-root user with sudo privileges.
  • A recent backup of your databases, and ideally a staging server where you can test changes first.

Step 1 - Gathering information about the server

Every setting in this guide depends on how much memory the server has, how many CPU cores it has and whether the disks are SSDs. Check the CPU count and memory first:

nproc
free -h

4
               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       1.1Gi       4.9Gi        64Mi       2.0Gi       6.7Gi
Swap:             0B          0B          0B

Check whether the disks are rotational. A value of 0 in the ROTA column means SSD or NVMe:

lsblk -d -o NAME,ROTA,SIZE

NAME ROTA  SIZE
vda     0  160G

Finally, confirm the PostgreSQL version and cluster name. On Ubuntu, clusters are managed with the pg_lsclusters helper:

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

The rest of the guide assumes cluster 16/main. Adjust the paths if yours differs.

Step 2 - Creating a drop-in configuration file

The main configuration file is /etc/postgresql/16/main/postgresql.conf. Instead of editing it line by line, keep your tuning in a separate file. Ubuntu's postgresql.conf already contains include_dir = 'conf.d', so any .conf file in /etc/postgresql/16/main/conf.d/ is loaded after the main file and overrides it.

Confirm the include directive is present:

grep "^include_dir" /etc/postgresql/16/main/postgresql.conf

include_dir = 'conf.d'			# include files ending in '.conf' from

Save the current values so you can compare them later:

sudo -u postgres psql -Atc "SELECT name || ' = ' || setting || coalesce(unit, '') FROM pg_settings ORDER BY name" > ~/pg_settings_before.txt

Now create the tuning file. The following sections explain each group of settings; add them all to this one file:

sudo nano /etc/postgresql/16/main/conf.d/90-tuning.conf

Keeping everything in one file makes it easy to review, version and revert: delete the file and restart, and you are back to the defaults.

Step 3 - Sizing memory settings

PostgreSQL uses its own buffer cache (shared_buffers) on top of the Linux page cache, plus per-operation memory for sorts and hashes. Add the following block to 90-tuning.conf:

# Memory (8 GB RAM, dedicated server)
shared_buffers = 2GB                 # about 25% of RAM
effective_cache_size = 6GB           # about 75% of RAM, planner hint only
work_mem = 16MB                      # per sort/hash operation, per query node
maintenance_work_mem = 512MB         # VACUUM, CREATE INDEX, ALTER TABLE
huge_pages = try

What each setting does:

  • shared_buffers: the PostgreSQL buffer cache. Start at 25% of RAM. Going above 40% rarely helps because the operating system cache also holds your data. Changing it requires a restart.
  • effective_cache_size: does not allocate anything. It tells the planner how much data is likely cached in total (shared buffers plus page cache), which makes index scans more attractive. Use 50 to 75% of RAM.
  • work_mem: memory for each sort or hash step before it spills to disk. A single complex query can use several multiples of it, and every connection can run such a query, so keep it modest. A rough ceiling is (RAM - shared_buffers) / (max_connections * 3). With 6 GB free and 100 connections that is about 20 MB.
  • maintenance_work_mem: used by VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY. Only a few of these run at once, so it can be much larger than work_mem. 5 to 10% of RAM, up to about 2 GB, is a good range.
  • huge_pages = try: the default. PostgreSQL uses huge pages if the kernel has them reserved and falls back silently if not. It is listed here so you know it exists; reserving huge pages with vm.nr_hugepages is worth doing only when shared_buffers is several gigabytes or more.

Step 4 - Tuning WAL and checkpoints

Every change is written to the WAL before it reaches the data files. A checkpoint flushes dirty pages to disk; if checkpoints happen too often, the server writes the same pages over and over and write latency spikes. Add this block:

# WAL and checkpoints
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on
  • max_wal_size is the amount of WAL after which a checkpoint is forced. The default of 1 GB triggers frequent checkpoints on any write-heavy workload. 4 GB is a reasonable starting point; make sure the disk holding /var/lib/postgresql has room for it.
  • checkpoint_timeout sets the maximum time between checkpoints. Longer intervals mean fewer writes but longer crash recovery. 15 minutes is a common compromise.
  • checkpoint_completion_target = 0.9 (the default since PostgreSQL 14) spreads checkpoint writes over 90% of the interval.
  • wal_buffers = 16MB is what the default automatic sizing (-1) picks once shared_buffers is large enough; setting it explicitly documents the value.
  • wal_compression = on compresses full-page images in the WAL, trading some CPU for less WAL volume.

In Step 9 you will check whether checkpoints are triggered by time (good) or by WAL volume (increase max_wal_size).

Step 5 - Adjusting the query planner for SSDs

The planner's cost model still assumes spinning disks, where a random read costs four times a sequential read. On SSDs and NVMe the difference is small, so lower random_page_cost and allow more concurrent I/O. Add:

# Planner (SSD/NVMe storage)
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100
  • random_page_cost = 1.1 makes the planner choose index scans when they are actually cheaper. Leave it at 4.0 only if your data really lives on HDDs.
  • effective_io_concurrency = 200 lets bitmap heap scans prefetch many blocks in parallel, which SSDs handle well.
  • default_statistics_target = 100 is the default. If EXPLAIN ANALYZE shows row estimates that are badly off for a specific column, raise the target for that column only, for example ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;, and run ANALYZE orders;.

Step 6 - Configuring parallel workers and connections

PostgreSQL can split large scans, joins and aggregates across several worker processes. Match the limits to the number of vCPUs:

# Parallelism (4 vCPUs)
max_worker_processes = 8
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2

# Connections
max_connections = 100
  • max_parallel_workers caps the total number of parallel workers; set it to the number of cores.
  • max_parallel_workers_per_gather is how many workers one query node can use. Half the cores leaves room for concurrent queries.
  • max_connections: each connection is a separate process with its own memory. Raising it to 500 or 1000 usually makes performance worse. If your application needs many connections, keep this value around 100 and put a connection pooler such as PgBouncer (sudo apt install pgbouncer) in front of PostgreSQL.

Step 7 - Tuning autovacuum and logging

Autovacuum removes dead row versions and refreshes planner statistics. The default thresholds wait until 20% of a table has changed, which on a table with 50 million rows means 10 million dead rows before any cleanup. Lower the scale factors and let autovacuum work faster:

# Autovacuum
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000
  • The scale factors trigger vacuum after 5% and analyze after 2% of a table changes.
  • autovacuum_vacuum_cost_limit raises how much work autovacuum does before it pauses. The default (200 via vacuum_cost_limit) is sized for old HDDs.

Then add logging that helps you find slow queries and memory spills:

# Logging
log_min_duration_statement = 500ms
log_temp_files = 0
log_lock_waits = on
log_checkpoints = on
log_autovacuum_min_duration = 1s

# Query statistics
shared_preload_libraries = 'pg_stat_statements'
  • log_min_duration_statement = 500ms logs every statement that runs longer than half a second.
  • log_temp_files = 0 logs every temporary file, which tells you when work_mem is too small for a query.
  • shared_preload_libraries loads pg_stat_statements, which records execution statistics for every normalized query. It is included in the Ubuntu postgresql-16 package.

Save and close the file.

Step 8 - Validating and applying the configuration

Before restarting, check that PostgreSQL can parse the file. The pg_file_settings view reads the configuration files from disk and reports errors without applying anything:

sudo -u postgres psql -c "SELECT sourcefile, sourceline, name, error FROM pg_file_settings WHERE error IS NOT NULL;"

An empty result means every line is valid:

 sourcefile | sourceline | name | error
------------+------------+------+-------
(0 rows)

shared_buffers, max_connections, max_worker_processes, wal_buffers, huge_pages, autovacuum_max_workers and shared_preload_libraries only take effect after a restart, so restart the service during a maintenance window:

sudo systemctl restart postgresql
sudo systemctl status postgresql@16-main --no-pager

● [email protected] - PostgreSQL Cluster 16-main
     Loaded: loaded (/usr/lib/systemd/system/[email protected]; enabled-runtime; preset: enabled)
     Active: active (running) since Thu 2026-09-24 10:12:41 UTC; 3s ago

If the service fails to start, the reason is in the cluster log:

sudo tail -n 30 /var/log/postgresql/postgresql-16-main.log

Confirm the new values are live and nothing is waiting for a restart:

sudo -u postgres psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_wal_size','random_page_cost','shared_preload_libraries');"

           name           |      setting       | unit | pending_restart
--------------------------+--------------------+------+-----------------
 max_wal_size             | 4096               | MB   | f
 random_page_cost         | 1.1                |      | f
 shared_buffers           | 262144             | 8kB  | f
 shared_preload_libraries | pg_stat_statements |      | f
 work_mem                 | 16384              | kB   | f

shared_buffers is reported in 8 kB pages: 262144 x 8 kB = 2 GB.

For later changes to settings that do not need a restart (for example work_mem, random_page_cost or the autovacuum and logging settings), edit the file and reload instead:

sudo systemctl reload postgresql

Step 9 - Measuring the effect

Tuning without measuring is guesswork. Enable pg_stat_statements in the database your application uses (replace your_database):

sudo -u postgres psql -d your_database -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

After the application has run for a while, list the queries that consume the most total time:

sudo -u postgres psql -d your_database -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 60) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

These are the queries to optimize first, usually with an index or a rewrite rather than more configuration.

Check the buffer cache hit ratio. On an OLTP workload it should be above 99%; if it is much lower and the server has free RAM, shared_buffers can grow:

sudo -u postgres psql -c "SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct FROM pg_stat_database WHERE datname NOT LIKE 'template%';"

Check what triggers checkpoints. In PostgreSQL 16 the counters live in pg_stat_bgwriter:

sudo -u postgres psql -c "SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;"

 checkpoints_timed | checkpoints_req
-------------------+-----------------
               142 |               3

Most checkpoints should be timed. If checkpoints_req grows quickly, WAL fills up before the timeout and max_wal_size should be increased.

Finally, look for queries that spill to disk. With log_temp_files = 0, they appear in the log:

sudo grep "temporary file" /var/log/postgresql/postgresql-16-main.log | tail -n 5

Frequent large temporary files for the same query mean it needs more work_mem (per session or per role with ALTER ROLE report_user SET work_mem = '128MB';) or a better index.

Scaling the values to other servers

Use these starting points and adjust after measuring:

Setting2 GB RAM8 GB RAM32 GB RAM
shared_buffers512MB2GB8GB
effective_cache_size1536MB6GB24GB
work_mem4MB16MB32MB
maintenance_work_mem128MB512MB2GB
max_wal_size1GB4GB16GB

For analytics or reporting workloads with few concurrent users and large queries, lower max_connections, raise work_mem substantially (64 MB to 256 MB) and allow more parallel workers per query.

Troubleshooting

  • PostgreSQL does not start after the change: check /var/log/postgresql/postgresql-16-main.log. A message like could not map anonymous shared memory means shared_buffers is too large for the available RAM. Lower it or remove 90-tuning.conf to return to the defaults.
  • The server runs out of memory under load: work_mem multiplied by active connections is too high. Lower work_mem or max_connections, and use a pooler.
  • A setting does not change: another file may override it. Run sudo -u postgres psql -c "SELECT name, setting, sourcefile FROM pg_settings WHERE name = 'work_mem';" to see which file the active value comes from. Values set with ALTER SYSTEM live in postgresql.auto.conf, which is read last and wins.

Conclusion

You moved PostgreSQL 16 from its conservative defaults to settings sized for the server's RAM, CPUs and SSD storage, applied them in a single drop-in file and confirmed with pg_stat_statements, the cache hit ratio and checkpoint counters that they work. From here, add indexes for the slowest queries in pg_stat_statements, put PgBouncer in front of PostgreSQL if you need hundreds of connections, and set up regular backups with pg_dump before tuning further.