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_buffersfrom 128 MB to about a quarter of the memory. - Set
effective_cache_sizeto about half to three quarters of the memory. It reserves nothing; it only informs the planner. - On SSD storage, lower
random_page_costfrom 4 to about 1.1. - Be careful with
work_mem: it applies per sort, per connection. - Keep
max_connectionslow 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 = workeris 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 raiseio_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 = 16by 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.