Databases on NVMe, in London

Why disk matters more than cores

Most database slowness is waiting. Waiting on a page read that missed the cache, on an fsync at commit, on a checkpoint flushing dirty buffers. NVMe-backed storage cuts each of those waits, which shows up as steadier commit latency and faster index builds. CPU only becomes the constraint once the data fits in memory and queries are doing real work.

Run the database in London if your application servers and users are in the UK. Every query is a round trip; keeping it inside the same city, or the same VM, removes the largest fixed cost you have.

PostgreSQL starting point

Install from the PGDG repository for current releases. On Debian or Ubuntu:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-16

Then adjust a few settings in postgresql.conf for an 8 GB machine running little else:

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_compression = on
max_connections = 100

Lowering random_page_cost tells the planner random reads are cheap, which is true on NVMe and changes plan choices for the better. Keep max_connections modest and put PgBouncer in front if your application opens many short connections.

MySQL and MariaDB

The single most important knob is the buffer pool. On the same 8 GB box:

[mysqld]
innodb_buffer_pool_size = 5G
innodb_log_file_size = 1G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
bind-address = 127.0.0.1

O_DIRECT avoids caching pages twice, once in InnoDB and once in the kernel. Raise innodb_io_capacity so background flushing keeps up with what the disk can actually do.

Redis

bind 127.0.0.1 -::1
protected-mode yes
maxmemory 1gb
maxmemory-policy allkeys-lru
appendonly yes
appendfsync everysec

Use allkeys-lru for a cache, noeviction for a queue or anything you cannot lose. Append-only files with per-second fsync are cheap on fast storage and save you from losing a session store on reboot.

Keep it off the internet

Exposed database ports are scanned within minutes of an IP going live. Defaults that hold up:

• Bind to loopback. Applications on the same VM connect via 127.0.0.1 or a Unix socket.
• For remote app servers, open the port in the portal firewall for those specific addresses only.
• Require TLS for remote connections: hostssl lines in pg_hba.conf, or require_secure_transport=ON in MySQL.
• Use scram-sha-256 authentication in Postgres and long random passwords everywhere.
• Alternatively, connect app servers over a WireGuard tunnel and never expose the port at all.

Backups you can restore

Use layers. Each covers a failure the others miss.

1. Portal snapshots before upgrades and schema changes, for fast rollback of the whole VM.
2. Scheduled portal backups for routine recovery points.
3. Logical dumps shipped elsewhere: pg_dump -Fc appdb | zstd > appdb-$(date +%F).dump.zst, then copy off the server with rclone or restic.
4. Point-in-time recovery for data you cannot afford to lose by the hour: WAL archiving with pgBackRest or WAL-G, or MySQL binary logs.

Once a month, restore the latest dump into a scratch database and run a row count against production. A backup that has never been restored is a hope, not a plan.

Sizing

Leave at least 25 percent of the disk free. Vacuum, index rebuilds and large migrations need scratch space, and a full disk stops a database harder than any other failure.