Running Databases on Cloud Infrastructure Without Regret

Every other tier in a modern application is disposable. The database is not. It holds state, it is the hardest thing to move, and it is where an undersized decision produces the longest-lasting pain.

It deserves more deliberate treatment than the rest of the stack, and it usually gets less.

Memory is the first constraint

A database's performance is governed largely by whether its working set — the data actually being read and written — fits in memory.

When it does, reads are served from the buffer pool at memory speed. When it does not, every miss becomes a disk read, and latency rises by orders of magnitude.

Practical guidance:

• PostgreSQL: set shared_buffers to roughly 25% of system RAM, and let the operating system page cache use the rest. effective_cache_size should reflect total available cache, typically 50–75% of RAM.
• MySQL / InnoDB: innodb_buffer_pool_size at 60–70% of RAM on a dedicated database server.
• Measure the hit ratio. Below about 99% on a read-heavy workload means you are short of memory.

Adding memory is almost always cheaper than the engineering time spent optimising around its absence.

Storage is the second

Once the working set exceeds memory, storage latency becomes the throughput ceiling. Two mistakes are common:

1. Choosing the wrong tier. Standard SSD is not adequate for a write-heavy production database.
2. Choosing a volume that is too small. On several platforms, throughput and IOPS scale with provisioned capacity. A 20 GB volume may be throttled regardless of its tier.

This is precisely where a provider with a storage-first architecture earns its price. A UpCloud account with MaxIOPS storage delivers consistent low latency without forcing you into a larger instance purely to obtain disk throughput — a coupling that exists on several competing platforms. The storage guide covers the tiers in detail.

Diagnosing it is straightforward: if %wa in top is high while user CPU is low, the disk is the constraint and adding cores will change nothing.

Connections are the third

Connection limits cap concurrency long before CPU does, and this surprises people repeatedly.

Each PostgreSQL connection is a process consuming several megabytes. A default max_connections of 100 is not a suggestion — exceeding it produces refused connections, not queuing.

The answer is a connection pooler: PgBouncer for PostgreSQL, ProxySQL for MySQL. A pooler lets hundreds of application connections share a much smaller set of database connections, and it is one of the highest-value additions available to a busy application.

Managed or self-hosted

The honest version of this decision:

Choose managed when you have no one who owns database operations, when point-in-time recovery matters, when you want automated failover, or when patching and version upgrades would otherwise not happen. The premium buys work you would otherwise do badly or not at all.

Choose self-hosted when you need an extension or configuration the managed service does not permit, when the cost difference is material at your scale, or when you genuinely have database operations capability on the team.

The failure mode is choosing self-hosted for cost reasons and then not doing the operational work. An unbacked-up, unpatched, unmonitored database is cheaper right up until it is catastrophically expensive.

Replication and failover

• Streaming replication gives a hot standby. Asynchronous is the default and means the standby may lag; synchronous guarantees no data loss at a latency cost.
• Read replicas offload read traffic, but the application must tolerate replication lag. Reading your own write from a lagging replica is a classic bug.
• Automatic failover needs an orchestrator — Patroni, repmgr, or a managed service's own mechanism. Replication alone does not fail over.

Do not build automatic failover until you have working backups. The order matters.

Backups that actually work

1. Automated, scheduled, off the database host. A backup on the same volume is not a backup.
2. Sent to object storage in a different failure domain.
3. Retention aligned to a written recovery point objective, not to a guess.
4. Restore-tested on a schedule. An untested backup is a hypothesis.
5. Point-in-time recovery where the data justifies it — WAL archiving for PostgreSQL, binary logs for MySQL.

Point four is the one that gets skipped, and it is the one that determines whether the other four mattered. The backup guide covers the routine.

Frequently asked questions

How much RAM does my database need? Enough for the working set plus overhead. Measure the buffer pool hit ratio; if it is below about 99% on a read-heavy workload, add memory.

Should the database have a public IP? No. Private networking only, reachable from the application tier's security group. This is the single most valuable configuration decision available.

Is one large instance or several smaller ones better? For the primary database, one correctly sized instance. Databases scale up more readily than out. Read replicas scale reads; writes stay on the primary.

How often should I test restores? Quarterly at minimum, and after any change to the backup process.

The sizing guide covers measuring the instance itself.