PostgreSQL on a dedicated server: memory, WAL, backups, access

What the engine asks of the hardware

PostgreSQL has no fixed hardware minimum worth quoting; its requirements follow the working set. Three resources decide throughput: memory for the pages queries touch daily, storage latency for WAL flushes and cache misses, and cores for concurrent backends, since each connection is its own process and one query uses one core unless the planner chooses a parallel plan.

effective_cache_size allocates nothing. It is a planner estimate: a higher value makes index scans look cheaper. shared_buffers is real shared memory and needs a restart to change.

Memory arithmetic on a 64 GB and a 256 GB machine

On 64 GB, 25% gives 16GB of shared_buffers, and an effective_cache_size around 48GB tells the planner that most of the remainder is page cache. On 256 GB the same rule gives 64GB; the manual expects little gain past 40%, because PostgreSQL also reads through the kernel cache and data would sit in both.

work_mem is the setting that exhausts RAM. It applies to each sort or hash operation, a complex query runs several at once, and many sessions run concurrently. With the default max_connections of 100 and three sort nodes each, 64MB of work_mem can claim about 19GB in the worst case. Raise it per role or per session for reporting queries rather than globally, or put a connection pooler in front so fewer backends exist.

WAL, fsync and synchronous_commit

Every commit waits for its WAL records to reach durable storage, so commit latency is the array's flush latency. That is why the controller matters as much as the drives: a write cache that survives power loss lets the flush return early without lying about durability.

• fsync = off can cause unrecoverable corruption after a power failure or OS crash. The manual limits it to data you can recreate, such as the initial load of a new cluster. Never in production.
• full_page_writes stays on for the same reason: a torn page cannot be repaired from row-level WAL.
• synchronous_commit = off is the safe trade. A crash can lose the last transactions reported as committed, never more than three times wal_writer_delay (200ms by default), but the database stays consistent. It can be set per transaction, so an event log can use it while payments keep on.

Consumer SATA and many SSDs have volatile write-back caches. The reliability chapter of the manual shows how to check them with hdparm -I or sdparm --get=WCE, and pg_test_fsync measures what a flush really costs on the array.

Choosing the array and the machine

Our SQL dedicated server builds are defined by array and memory, which is the order PostgreSQL cares about. All three run the Linux images PostgreSQL is packaged for (AlmaLinux 10, Ubuntu 26.04, Debian 13), with root, single tenant, no hypervisor between the engine and the disks.

RAID 10 has no parity penalty on small random writes; RAID 5 or 6 trades that for capacity and suits reporting databases that mostly read. The largest controller takes 24 drives and our x86 machines take up to 2 TB of memory, so a custom build can follow the data. Measured differences between drive types are on NVMe vs SSD vs HDD.

Our backup agent cannot back up a software RAID array yet. On the mirrored NVMe build, the base backups and WAL archive described below are your backups.

Installation and first configuration

1. Install from the distribution, or from the PostgreSQL Apt Repository if you need a newer major version than Ubuntu ships.
2. Find the files the server reads with SHOW config_file; and SHOW hba_file; in psql.
3. Set memory and WAL parameters with ALTER SYSTEM, which writes postgresql.auto.conf, then restart for shared_buffers to apply.
4. Edit pg_hba.conf, reload, and check pg_hba_file_rules for errors.

sudo apt install postgresql
sudo -u postgres psql

-- example for a 64 GB machine
ALTER SYSTEM SET shared_buffers = '16GB';
ALTER SYSTEM SET effective_cache_size = '48GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET listen_addresses = 'localhost,10.0.0.5';
-- then restart the postgresql service

Backups with point-in-time recovery

pg_dump gives a logical copy of one database at one moment. Recovery to any second between backups needs continuous archiving: a base backup plus every WAL segment written since. Set archive_mode = on (wal_level must be replica or logical; replica is the default) and an archive_command that returns zero only when the copy succeeded, preferably to storage that is not this machine.

# postgresql.conf (the manual's minimal example)
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

# compressed tar base backup with progress
pg_basebackup -D backup -Ft -z -P

# at restore time, with recovery.signal in the data directory
restore_command = 'cp /mnt/server/archivedir/%f %p'

pg_basebackup connects over the replication protocol, so its role needs the REPLICATION attribute, pg_hba.conf must allow a replication connection, and max_wal_senders must leave room. Keep every archived segment back to the oldest base backup you might restore, and test a restore before you rely on one.

Network exposure and pg_hba.conf

Out of the box listen_addresses is localhost, so nothing outside the machine can connect. Add only the interface your application servers use. Authentication is then decided by pg_hba.conf, read top to bottom: the first line that matches the connection type, database, user and address is used, with no fall-through if it fails, and no match means rejection.

• Use scram-sha-256; it is already the default password_encryption. MD5 passwords are deprecated.
• Allow single /32 addresses for application hosts, not 0.0.0.0/0.
• Use hostssl lines once ssl = on, so queries and results are encrypted in transit; scram-sha-256 already keeps the password itself off the wire.
• Apply changes with SELECT pg_reload_conf(); and inspect pg_hba_file_rules.

A line such as host appdb app 192.168.12.10/32 scram-sha-256 admits one user to one database from one host. Every server here sits on our own network, AS19624, with one IPv4 included; see our five cities to place the database next to its application.

When a dedicated server is the wrong answer

For one small database a Cloud VPS arrives with PostgreSQL already installed and costs less. High availability needs a second server for a streaming replica; one machine, however large, is a single failure domain. If PostgreSQL backs an application such as self-hosted Odoo, size both on the same box or split them once the database's working set competes with the application for memory.