Skip to content
zefıly
← All posts

Tuning PostgreSQL 18 on a small server: the settings that matter

4 min read

PostgreSQL's defaults are written for a machine with very little memory. The handful of settings worth changing on a 2 to 8 GB server, what is new in version 18, example values, and how to check that a change helped. Defaults read from Ubuntu 26.04.

PostgreSQL starts with careful defaults: it must run on almost anything. On a server with a few gigabytes of memory, five or six settings decide most of the performance, and the rest can stay as they are.

We read the defaults on a fresh Ubuntu 26.04.1 system on 12 November 2026, where apt install postgresql gives PostgreSQL 18.6.

In short

  • Raise shared_buffers from 128 MB to about a quarter of the memory.
  • Set effective_cache_size to about half to three quarters of the memory. It reserves nothing; it only informs the planner.
  • On SSD storage, lower random_page_cost from 4 to about 1.1.
  • Be careful with work_mem: it applies per sort, per connection.
  • Keep max_connections low and use a pooler, not hundreds of connections.
  • New in 18: asynchronous I/O is on by default (io_method = worker), and new clusters have data checksums.

The defaults we found

Setting Default on Ubuntu 26.04
shared_buffers 128MB
effective_cache_size 4GB
work_mem 4MB
maintenance_work_mem 64MB
max_connections 100
random_page_cost 4
effective_io_concurrency 16
io_method worker
io_workers 3
max_wal_size 1GB
data_checksums on
listen_addresses localhost
password_encryption scram-sha-256

Check your own with:

SHOW shared_buffers;

Where to put changes

On Ubuntu, do not edit postgresql.conf. Put your values in a file of your own, which package updates leave alone:

/etc/postgresql/18/main/conf.d/10-tuning.conf
sudo systemctl restart postgresql

shared_buffers and max_connections need a restart; most others only SELECT pg_reload_conf();.

The settings, one by one

shared_buffers: PostgreSQL's own cache. 128 MB is the same on a 1 GB server and a 64 GB server. Use about 25% of the memory if the server runs only the database, less if PHP and a web server share it.

effective_cache_size: an estimate of how much memory is available for caching in total, including the operating system's file cache. It allocates nothing. Too low, and the planner avoids indexes it should use. Note the default of 4 GB: on a 2 GB server that is already too optimistic.

work_mem: memory for one sort or hash operation. A complex query can use it several times, and each connection has its own. 100 connections × 3 operations × 32 MB is close to 10 GB. Raise it modestly, and set it higher only for the sessions that need it:

SET work_mem = '128MB';

maintenance_work_mem: used by VACUUM, CREATE INDEX and restores. These run rarely and one at a time, so 256 MB to 1 GB is safe and speeds them up clearly.

random_page_cost: how expensive the planner thinks a random read is, compared with a sequential one. The default of 4 comes from spinning disks. On SSD or NVMe set 1.1, and index scans are chosen when they should be.

max_connections: each connection is a process with its own memory. A PHP application rarely needs 100. If you need many clients, put PgBouncer in front and keep this number low.

max_wal_size: with many writes, 1 GB causes frequent checkpoints. 2 to 4 GB smooths them out, at the cost of disk space and a slightly longer crash recovery.

Example values

For a server where PostgreSQL shares the machine with a web application:

2 GB 4 GB 8 GB
shared_buffers 384MB 768MB 2GB
effective_cache_size 1GB 2500MB 5GB
work_mem 4MB 8MB 16MB
maintenance_work_mem 128MB 256MB 512MB
max_connections 40 60 100
random_page_cost 1.1 1.1 1.1

These are starting points. If the database has the machine to itself, give shared_buffers a quarter of the memory.

What is new in PostgreSQL 18

  • Asynchronous I/O. io_method = worker is the default: reads are handed to I/O worker processes (io_workers = 3) instead of being done one after another by the query's own process. Sequential scans and vacuum benefit. With many cores and fast storage you can raise io_workers.
  • Data checksums by default. New clusters verify each page when it is read, so silent corruption on disk is noticed. A cluster upgraded from an older version keeps its old setting.
  • effective_io_concurrency = 16 by default, where older versions had 1.

Check that it helped

Do not tune blind. Turn on the statistics extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

(with shared_preload_libraries = 'pg_stat_statements' and a restart), then look at what costs the most:

SELECT calls, round(total_exec_time) AS ms, left(query, 60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

One missing index usually matters more than every setting on this page. EXPLAIN (ANALYZE, BUFFERS) on the top query tells you.

With Zefily

Zefily installs PostgreSQL 18 on your server and its Autotune sizes the database and PHP for the server's memory and cores, within fixed safe bounds. You can see and change the values on the server's performance page. Databases, users and copies between servers are managed from the panel. See Performance and Databases.

Frequently asked questions

What should shared_buffers be in PostgreSQL?

About a quarter of the server's memory when PostgreSQL has the machine to itself, less when it shares it with a web application. The default is 128 MB.

Does effective_cache_size use memory?

No. It only tells the query planner how much memory is likely available for caching, including the operating system's file cache.

What is io_method in PostgreSQL 18?

The setting for asynchronous I/O, new in version 18. The default, worker, hands reads to background I/O worker processes; io_workers sets how many.

What should random_page_cost be on SSD?

About 1.1. The default of 4 was chosen for spinning disks and makes the planner avoid index scans.

How many connections should PostgreSQL allow?

As few as the application really needs. Each connection is a process with its own memory; use a pooler such as PgBouncer for many clients.

See MariaDB or PostgreSQL for a PHP application if you are still choosing.

More posts

Black Friday & Cyber Monday

Before you go: lifetime plans are on sale

Pay once for a lifetime plan, or get 30% off the first year of any yearly plan.

Only 100 lifetime plans per plan. When they are gone, they are gone.

See the campaign

Amazing November

See the campaign →