PostgreSQL tuning for small servers: 1 GB to 8 GB

On a database server with 1 to 8 GB of memory, five settings decide almost everything: shared_buffers, work_mem, max_connections, maintenance_work_mem and the checkpoint pair. The rest of postgresql.conf is either fine at its default or a rounding error next to a missing index. If you change nothing else after reading this, set shared_buffers to about a quarter of the memory, keep work_mem small, keep max_connections low, and let autovacuum run harder than it does out of the box.

The reason tuning matters more on a small server than a large one is that the failure mode is different. A big machine that is slightly misconfigured is slower than it could be. A small machine that is misconfigured runs out of memory, and running out of memory is not a slowdown - the kernel stops the process. Everything below is really about spending a fixed memory budget on purpose.

One caveat before the numbers: how you apply a change depends on your host. A server you administer gives you postgresql.conf and a shell. A managed database may give you the superuser account and ALTER SYSTEM, or a panel, or a fixed configuration you cannot alter at all. Find out which of those you have before planning a change around it, and keep the session-level options in mind, because those work everywhere.

What the defaults assume#

PostgreSQL ships with a configuration that will start on almost any machine, including one with 256 MB of memory. That is a deliberate choice by the project and it means the defaults are not a recommendation.

random_page_cost = 4.0 is the clearest example. It encodes the assumption that a random read costs four times a sequential one, which was true of the spinning disks of 2005 and is wildly wrong on NVMe. Left alone, it pushes the planner towards sequential scans where an index would have been faster. What NVMe actually changes covers the hardware side of that gap.

Memory: the three settings that decide everything#

Think of the server's memory in three pots: what PostgreSQL reserves once at startup, what each query may take while it runs, and what the operating system keeps as file cache. Overspend on the first two and the third disappears, then the kernel starts killing things.

`shared_buffers` is allocated once and shared by every backend. A quarter of the machine's memory is the long-standing rule and it holds well up to about 8 GB. Going higher rarely helps on a small server, because the operating system is caching the same files anyway and doubling up wastes the memory twice.

`work_mem` is the one that bites. It is not per connection: it is per sort, hash join or hash aggregate node, and a single complex query can have several of them, each entitled to the full amount. Ten connections running a three-sort query each with work_mem = 64MB can ask for nearly 2 GB. Keep the global value small and raise it for the one report that needs it:

BEGIN;SET LOCAL work_mem = '64MB';SELECT ... ;  -- the monthly aggregateCOMMIT;

`maintenance_work_mem` is used by VACUUM, CREATE INDEX and ALTER TABLE, and by only a few processes at a time (autovacuum workers each take up to autovacuum_work_mem, which defaults to this value). It is the cheapest setting to raise, and raising it is what makes index builds and vacuums finish in minutes instead of hours.

`effective_cache_size` allocates nothing at all. It tells the planner roughly how much of your data is likely to be in cache somewhere, and a realistic value makes index scans look as cheap as they really are. Half to two thirds of total memory is a fair guess on a dedicated database server.

Here is a starting point, not a truth. Test against your own workload.

Set effective_cache_size alongside them at roughly 60% of the server's memory: 512MB, 1GB, 2500MB, 5GB and 9GB for the rows above.

On RE:NODE, reaching the memory limit stops the container and restarts it clean rather than letting it swap, which is kinder to the rest of the machine and merciless about an over-optimistic work_mem. The panel's console graphs memory, CPU and disk against the plan's limits, so the number you should be sizing against is visible before you change anything.

Connections are a memory setting#

Every PostgreSQL connection is an operating-system process with its own memory. Idle ones are cheap but not free, and busy ones are entitled to work_mem several times over. That makes max_connections a memory decision rather than a capacity one, and the answer on a small server is a surprisingly small number.

A database with two CPU cores cannot usefully run more than a handful of queries at once. Connections beyond that point queue inside PostgreSQL, where queuing is more expensive than queuing in a pool, because each waiting backend still holds memory and a scheduling slot.

• Size the application pool first: 10 to 20 per process covers most web applications.
• Multiply by the number of processes and add background workers. That total is your real demand.
• Set max_connections a little above it, with superuser_reserved_connections (default 3) keeping a seat for you.
• If the total is getting large, put a pooler in front rather than raising the limit.

FATAL: sorry, too many clients already is nearly always a leak or an unbounded pool rather than genuine load. Connection pools and limits is the short version of why more connections make things slower, and the connection parameters themselves are in PostgreSQL remote connections.

Telling the planner what your storage is#

These cost nothing and change which plan the planner picks. On NVMe:

random_page_cost = 1.1effective_io_concurrency = 200default_statistics_target = 100

random_page_cost = 1.1 says random reads are barely more expensive than sequential ones, which is close to true on flash. It is the single most effective one-line change on a server whose data mostly fits in cache, because it stops the planner preferring a full table scan to an index it has.

effective_io_concurrency lets the planner assume several reads can be in flight at once, which helps bitmap heap scans. default_statistics_target raises how much detail ANALYZE collects per column; leave it at 100 globally and raise it per column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 on a column with a skewed distribution that the planner keeps misjudging.

Two more, both about small-server realities. jit is on by default since PostgreSQL 12 and helps long analytical queries while adding compile time and memory to short ones; on a small OLTP database, measure it, and turning it off is a reasonable default. If your plan buys less than a full core of CPU, set max_parallel_workers_per_gather = 0: parallel workers on a hard CPU throttle just divide the same slice into more pieces and add coordination. Once the plans look wrong, the tool is EXPLAIN ANALYZE, not more guessing.

Checkpoints, WAL and writes#

Every change is written to the write-ahead log first, and periodically a checkpoint flushes the modified pages to the data files. Checkpoints that come too often turn a small write workload into a stream of full-page writes, because the first change to a page after a checkpoint writes the whole page to WAL.

checkpoint_timeout = 15minmax_wal_size = 2GBmin_wal_size = 512MBcheckpoint_completion_target = 0.9wal_compression = on

Spreading checkpoints further apart reduces write amplification at the cost of a longer recovery after a crash. Fifteen minutes is a sane middle for a small server. max_wal_size is a ceiling on how much WAL may accumulate between checkpoints, and 2GB on a 20 GB disk is comfortable; it is not a disk reservation, just an upper bound before a checkpoint is forced.

checkpoint_completion_target has defaulted to 0.9 since PostgreSQL 14, which is the value you want anyway: it spreads the flush across 90% of the interval instead of dumping it. wal_compression = on trades a little CPU for noticeably less WAL, which matters when the disk is 20 GB rather than 2 TB.

The setting people are tempted by is synchronous_commit. Turning it off makes commits return before the WAL is flushed, which is a real speed-up for write-heavy workloads and means a hard crash can lose the last fraction of a second of committed transactions. It does not corrupt the database, and that distinction is the whole argument: it is acceptable for analytics and event ingest, and not acceptable for anything involving money. fsync = off and full_page_writes = off are different in kind - they can leave an unrecoverable database, and there is no workload on a production server where they are worth it.

Autovacuum on a small server#

The default autovacuum thresholds wait until 20% of a table is dead before cleaning it. On a small server that is the wrong trade: a big vacuum on a busy table is far more disruptive than frequent small ones, and the dead rows are occupying memory and cache you do not have.

autovacuum_vacuum_scale_factor = 0.05autovacuum_analyze_scale_factor = 0.02autovacuum_vacuum_cost_limit = 1000autovacuum_naptime = 30slog_autovacuum_min_duration = 1s

That combination cleans at 5% dead rows instead of 20%, re-analyses more eagerly so the planner's statistics stay honest, and raises the cost limit so a worker is not throttled to a crawl. The one table that takes the most writes usually deserves its own settings rather than a global change:

ALTER TABLE app.sessions SET (  autovacuum_vacuum_scale_factor = 0.01,  autovacuum_vacuum_cost_delay = 0);

Never turn autovacuum off. The full account of what it does, why it falls behind and what bloat costs you is in Postgres vacuum and bloat.

Timeouts and limits worth setting#

Defaults here are all "unlimited", which means one bad query can hold a lock, fill the disk with temporary files, or keep a transaction open long enough to stop autovacuum cleaning anything. These four lines prevent more small-server incidents than any amount of memory tuning:

statement_timeout = 30sidle_in_transaction_session_timeout = 60slock_timeout = 5stemp_file_limit = 2GB

statement_timeout is best set per role rather than globally, so a migration or a nightly report is not killed mid-way: ALTER ROLE app SET statement_timeout = '30s'. idle_in_transaction_session_timeout kills sessions that opened a transaction and wandered off, which is the most common reason vacuum cannot reclaim anything. lock_timeout stops a DDL statement from queueing behind a long read and blocking every writer behind it in turn. temp_file_limit caps what one session can spill to disk when work_mem is not enough, which on a 20 GB volume is the difference between a slow query and a full disk. PostgreSQL 17 added transaction_timeout as well, if you are on a version that has it.

Where settings live, and which need a restart#

There are four places a setting can come from, in increasing order of precedence: postgresql.conf (and any file it includes), postgresql.auto.conf (written by ALTER SYSTEM), per-database and per-role settings, and the session itself.

-- See the value, its unit, and whether changing it needs a restartSELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN ('shared_buffers','work_mem','max_connections','random_page_cost');-- Change one durably, if your host gives you the superuser accountALTER SYSTEM SET random_page_cost = 1.1;SELECT pg_reload_conf();-- Narrower scopes, which need no superuserALTER DATABASE shop SET work_mem = '8MB';ALTER ROLE reporting SET work_mem = '64MB';SET LOCAL work_mem = '64MB';   -- this transaction only

The context column is the one to read. postmaster means a restart: shared_buffers, max_connections, max_worker_processes and anything in shared_preload_libraries. sighup means a reload is enough: work_mem, the autovacuum settings, the planner costs, the timeouts. user means any session can change it for itself.

Whether you get postgresql.conf itself, ALTER SYSTEM, or neither depends on the host, so check what yours lets you change before you write a plan around editing a file. Two habits make this safe wherever you land: change one setting at a time, and take a backup before a restart, because a server that will not start because of a typo in a configuration file is a bad time to discover you do not have one. Restoring is a button on RE:NODE and backup slots come with every tier - testing a restore before you need it makes the case for pressing it once on purpose.

Measuring, before and after#

Tuning without measurement is decoration. Three sources cover nearly everything.

-- The queries actually costing you time (needs the extension installed)CREATE EXTENSION IF NOT EXISTS pg_stat_statements;SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms,       round(total_exec_time::numeric) AS total_ms, queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

pg_stat_statements has to be in shared_preload_libraries, which means a restart to enable it. It is worth the restart: it is the only view that tells you where the time goes rather than where you assume it goes.

log_min_duration_statement = 500mslog_checkpoints = onlog_temp_files = 0log_lock_waits = on

log_temp_files = 0 logs every temporary file, which is how you discover work_mem is too small for a specific query rather than for everything. log_checkpoints (on by default since PostgreSQL 15) tells you whether checkpoints are being forced by max_wal_size instead of by the timeout, which is the signal to raise it. log_lock_waits names the statement that is blocking others.

Finally, pg_stat_activity during an incident, pg_stat_database for cache ratios over time, and pg_stat_io if you are on PostgreSQL 16 or later. Compare the same query before and after each change with EXPLAIN (ANALYZE, BUFFERS), and keep the shape of the load graph in view - reading a server load graph explains why the average hides the thing that hurt. If the numbers say the working set simply does not fit, tuning has run out of room and when to upgrade your plan is the honest next step.

FAQ#

How much should shared_buffers be on a 2 GB server?

About 384 MB, which is a little under a quarter. Larger values leave less for the operating system's cache and for query memory, and on a small server the double caching costs more than the extra hit rate gains.

Why did my server get killed for running out of memory when Postgres was configured correctly?

Almost always work_mem multiplied by concurrency. The setting is per sort or hash node, not per connection, so a handful of complex queries can each claim it several times over. Lower the global value and raise it per session where it is needed.

Do I need a connection pooler on a small database?

If your application already pools connections in-process, usually not. You need one when several application processes each hold a pool and the total would push max_connections into the hundreds, or when serverless-style workers open a connection per request.

Is it safe to turn off synchronous_commit?

It is safe in the sense that it cannot corrupt the database, and unsafe in the sense that a crash can lose the last few committed transactions. Reasonable for event ingest and analytics, not for orders or payments. Never turn off fsync or full_page_writes on anything you care about.

Which settings need a restart rather than a reload?

shared_buffers, max_connections, max_worker_processes, wal_level, listen_addresses, port and shared_preload_libraries. Check the context column of pg_settings: postmaster means restart, sighup means reload.

What should I tune first if the database is slow?

Nothing in this post. Find the slow query with pg_stat_statements, read its plan, and check whether it is missing an index. Configuration recovers percentages; a missing index on a large table costs orders of magnitude.