Choose hosting that gives sustained IOPS, low network latency, and local NVMe. Prefer managed distributed SQL or sharded Postgres for linear write scale. Tune WAL, batching, and fsync. Run 10–30 minute write tests before any cutover.
Quick comparison table
Below is a compact, practical comparison focused on sustained write behavior and cost tradeoffs.
| Provider |
Storage type |
Typical sustained IOPS |
Local NVMe? |
Typical p99 write latency |
Best fit |
| AWS (EBS gp3 / Nitro NVMe) |
gp3 / local NVMe |
Up to 16,000 IOPS per gp3 volume |
Yes (Nitro instances) |
~5–50ms (varies with load) |
Enterprise SaaS, multi-AZ |
| GCP (PD-SSD / Local SSD) |
PD-SSD / local NVMe |
Provisioned up to tens of thousands IOPS |
Yes |
~5–40ms |
Low-latency regional apps |
| OCI (Bare metal & block) |
Local NVMe, block |
High sustained IOPS on bare metal |
Yes |
~2–30ms |
Cost‑sensitive high IOPS |
| Hetzner / Linode |
Local NVMe |
Solid single-node IOPS (cost-effective) |
Yes |
~3–30ms |
SMB SaaS, predictable budgets |
| Managed distributed SQL |
Cloud-backed distributed store |
Linear write scaling across nodes |
No (abstracted) |
~10–200ms (depends on consensus) |
Apps that need horizontal write scale |
Sustained IOPS and p99 write latency under concurrency determine real write throughput more than advertised peak IOPS. Measure a 10–30 minute steady-state write test to understand real limits.
Managed cloud: when to pick it
Managed cloud services suit teams that need compliance, backups, and less ops work. Managed services cut routine tasks and give built-in PITR and snapshots. Managed stacks still need architecture changes for high write scale.
Strengths of managed DBs
Managed providers give backups, point-in-time recovery, and multi-AZ failover. This reduces the time teams spend on routine tasks. Managed services also handle minor scaling operations automatically.
Limitations in write-heavy setups
Single-writer limits or centralized WAL can block write scale. The error most frequent at this point is trusting advertised throughput without testing tail latency. Many managed offerings require sharding or a distributed layer for linear write scale. Keep these tradeoffs in mind.
When managed makes sense
Choose managed when SRE capacity is limited or compliance matters. Choose managed when multi-region replication and automated failovers are priorities. A typical case: a mid-size SaaS moved from a single RDS instance to Citus. The move halved p99 latency.
Self-hosting gives direct access to local NVMe, CPU cores, and network layout. This control often lowers p99 latency and cuts cost per sustained IOPS. Self-hosted setups demand capacity planning and robust runbooks.
Local NVMe gives the best tail latency for writes. Bare metal removes virtualization jitter seen on shared hosts. Cost per IOPS is usually lower on dedicated hardware.
Operational tradeoffs
Self-hosted stacks need backups, PITR, and failover built by the team. This raises operational burden and risk if procedures remain incomplete. The most common error in migrations is underestimating replication bandwidth during cutover.
When self-hosting is right
Pick self-hosting when low p99 latency and cost control matter most. Pick it when the team can run snapshots and failover drills. For multi-tenant SaaS, per-tenant isolation on bare metal removes noisy-neighbor issues.
Consider these operational costs before choosing self-hosting.
Distributed SQL and NewSQL services
Distributed SQL removes single-writer limits by spreading writes across nodes. These services handle replication and consensus. They increase write latency in exchange for availability and scale.
CockroachDB, PlanetScale, and Spanner-like services give horizontal write scale. Vitess enables sharded MySQL with mature tooling. Citus turns Postgres into distributed SQL with shard control.
Operational complexity and consistency
Distributed SQL handles replication and failover automatically. This comes at the cost of higher p99 latency for strongly consistent commits. This approach works when the app tolerates a small latency rise.
When distributed SQL is best
Choose distributed SQL when writes must scale linearly and teams want to avoid manual sharding. Choose it when cross-node replication bandwidth exists and multi-region replication is required. A common case: high-write global SaaS that needs regional failover.
How to choose based on your situation
Decide by mapping required sustained IOPS and p99 latency to provider capabilities and cost. Build a small matrix with throughput, latency, cost per IOPS, and compliance needs. Run a short benchmark on each candidate provider before committing.
Concrete decision criteria
Score each option on sustained IOPS, p99 latency, storage locality, HA model, compliance, and ops burden. Pick the lowest-cost option that meets throughput and p99 targets. If targets exceed single-node limits, pick distributed or sharded architectures.
Quick capacity thresholds
Aim for p99 latency under 100ms for good UX. Plan sustained IOPS per shard by hardware type. For extreme loads aim for over 50k sustained IOPS across the cluster. Validate these numbers with real tests.
Sample mapping to team profiles
Founders with small teams should favor managed distributed SQL for less ops. Small engineering teams with ops skill should favor managed DB with sharding or self-hosted bare metal. Large teams with SRE should choose self-hosted or OCI bare metal for cost-effective IOPS.
Review team goals before choosing an approach.
Architectures that improve write throughput
Sharding, CQRS, and ingestion pipelines reduce central commit contention and spread write load. The app must do batching, idempotency, and backpressure to avoid overloading storage. This works well when combined with per-tenant isolation.
Sharding patterns
Range or hash sharding splits data to spread writes. Tenant-based sharding isolates noisy tenants. Resharding needs careful planning and rolling cutovers.
CQRS and ingestion pipelines
Move heavy writes into append-only streams like Kafka or Kinesis. Consumers apply writes to the main DB at a controlled speed. This reduces direct load and gives manageable backpressure.
Caching, batching and connection pooling
Batch multiple commits into single transactions when possible. Use PgBouncer or ProxySQL to pool connections and avoid DB socket overload. Group commit lowers per-transaction fsync cost.
A practical rule: measure p99 write latency and sustained IOPS during a ten-minute steady-state test. If p99 exceeds 100ms, implement batching, add local NVMe, or move to sharding.
Client
Concurrent requests and retry logic affect write bursts.
App Layer
Batching, idempotency, and connection pooling reduce DB commits.
Queue/Stream
Kafka/Kinesis smooths spikes and enables controlled consumer writes.
Storage
Local NVMe yields lowest p99; network block storage varies under concurrency.
Reproducible write-benchmark recipes and tuning
Run pgbench and fio tests with realistic concurrency to measure sustained writes and tail latency. Capture p50, p95, and p99, plus CPU steal and disk queue depths. Use the examples below to reproduce results across providers.
Pgbench write-heavy example
Create a custom SQL file to force writes and run many clients. Use this command as a baseline for stress tests.
Pgbench -i -s 50 mydb
pgbench -c 200 -j 8 -T 900 -f custom_insert.sql mydb
Clients set the number of concurrent sessions. Threads map to CPU cores. Duration helps reach steady state.
Fio for raw disk behavior
Use fio for sustained write IOPS under concurrency. The example below tests 4k random writes and captures latency percentiles.
[global]
rw=randwrite
bs=4k
ioengine=libaio
iodepth=64
numjobs=8
runtime=600
name=randwrite
[fio-test]
filename=/dev/nvme0n1
Measure IOPS and latency percentiles from fio output. Compare these to pgbench to isolate DB versus disk bottlenecks.
Postgres tuning snippets
These settings improve write throughput with clear tradeoffs. Do not disable fsync in production.
Shared_buffers = '25%'
wal_level = replica
synchronous_commit = off
wal_compression = on
commit_delay = 1000 # microseconds
commit_siblings = 5
checkpoint_timeout = 15min
Turning synchronous_commit off raises throughput but weakens durability. Use commit_delay and group commit to batch fsync calls. Test changes and roll back if latency or durability changes look worse.
Kernel and TCP hints
Adjust dirty memory thresholds and TCP settings to reduce background flush spikes. Use these sysctl tweaks on DB servers.
Vm.dirty_ratio = 10
Vm.dirty_background_ratio = 2
net.core.somaxconn = 1024
net.ipv4.tcp_fin_timeout = 30
Collect metrics before and after each change. This works well in theory; in practice, run tests and roll back when needed.
Postgres WAL and fsync tuning is core to reducing p99 write latency under sustained IOPS. It deserves focused, practical treatment.
- Start by profiling WAL behavior with wal_writer_stats and pg_stat_bgwriter while running a short heavy test. Increase wal_buffers to a few megabytes, for example 8–32MB, to reduce WAL allocation stalls. Enable wal_compression when payloads are large and tune wal_writer_delay if WAL accumulation causes spikes.
- Lowering wal_writer_delay shortens intervals between WAL flush attempts. This raises WAL IOPS. Use commit_delay and commit_siblings to favor group commit. For example set commit_delay to 200–1000 microseconds and tune commit_siblings to match concurrency. Use synchronous_commit = on for critical transactions and synchronous_commit = local when asynchronous replica persistence is acceptable. Treat synchronous_commit = off as a last resort because it trades durability for throughput.
- At the OS level prefer a wal_sync_method that matches kernel and storage, either fdatasync or pwrite. Test O_DIRECT versus buffered I/O for each workload.
- On local NVMe instance stores you may see much lower p99 write latency than on provisioned block storage.
Always validate changes with a sustained I/O benchmarking run. Verify WAL metrics like wal_write, wal_flush, and fsync waits improve without adding data risk.
A reproducible benchmarking harness ties pgbench, fio, and OS counters into actionable sustained-IOPS and tail-latency conclusions. Automate a 10–30 minute scenario that runs three steps. Step one runs fio randwrite (4k, iodepth 64, numjobs matching DB cores) against the raw device. Step two runs pgbench with a realistic custom SQL workload across a sweep of clients and threads. Step three collects iostat, blktrace, sar, vmstat, and /proc/diskstats every five seconds to capture queue depth and await metrics.
Compare fio random 4k IOPS to pgbench write TPS to quantify DB overhead. Look for divergence that points to CPU, WAL, or fsync bottlenecks. Include a small orchestration script that ramps clients, waits for steady state, collects metrics, and emits a summary.
The summary should list sustained IOPS, avg queue depth, p50/p95/p99 write latency, and CPU steal. Use these reproducible numbers to compare providers rather than trusting peak IOPS claims.
With benchmarks in hand, prepare migration steps.
Migration runbook and IaC snippets
A staged migration reduces downtime and data loss risk. The runbook below covers dry-run, dual-write, catch-up, cutover, and rollback. Run a dry-run at least one week before production cutover.
Pre-migration steps
Run baseline benchmarks on source and target. Verify schema compatibility and shardability. Confirm replication bandwidth and snapshot times.
Live migration steps
- Provision target infra and enable logical replication.
- Start initial copy and enable ongoing replication.
- Run write traffic through a throttled path or dual-write mode.
- Validate catch-up with WAL replay metrics.
- Cut writes to target when lag is acceptable.
- Monitor p99 latency closely for 24–72 hours after cutover.
Commands for postgres logical
CREATE PUBLICATION app_pub FOR ALL TABLES;
CREATE SUBSCRIPTION app_sub CONNECTION 'host=primary hostaddr=... User=replicator' PUBLICATION app_pub;
For large datasets use pg_basebackup for base image and logical replication for final catch-up.
hcl
resource "aws_ebs_volume" "db" {
availability_zone = "us-east-1a"
size = 1000
type = "gp3"
iops = 16000
throughput = 1000
}
Kubernetes StatefulSet pattern for local
apiVersion: apps/v1
kind: StatefulSet
metadata:
name: db
spec:
serviceName: db
replicas: 3
template:
spec:
containers:
- name: postgres
volumeMounts:
- name: data
mountPath: /var/lib/postgresql/data
volumeClaimTemplates:
- metadata:
name: data
spec:
storageClassName: local-nvme
resources:
requests:
storage: 500Gi
In heavy migrations, the runbook must include explicit backpressure and dual-write controls so cutover does not spike WAL or p99 latency. Use a three-phase approach. Phase A does a bulk copy with pg_basebackup and verifies initial sync. Phase B enables dual-write behind a feature flag or API gateway and applies rate limiting to slow ingestion. Phase C quiesces writes, waits for replication lag zero, promotes the target, and switches DNS to the target.
Keep a tested rollback path. If rollback is needed, re-enable the old primary and reject writes to the target. Or use pg_rewind when timelines diverge. Instrument the process with scripted checks and explicit abort thresholds.
Confirm rollback plans before cutover.
Cost and provider decision matrix
Example cost math
If an instance costs $1.50 per hour and provisioned IOPS adds $0.10 per 1k IOPS per month, then 100k sustained IOPS across a cluster adds material cost. Compare provider IOPS pricing to self-hosted server cost.
Vendor notes and a source
AWS documents gp3 characteristics and limits. Teams should consult the official guide before provisioning. AWS EBS volume types
What nobody tells you
Many guides repeat peak IOPS numbers without showing sustained tail-latency under concurrency. The data point often missed is storage queue depth and fsync waits. These show up only at the p99 under realistic client concurrency. AWS gp3 supports up to 16,000 IOPS per volume, but actual sustained p99 latency depends on workload and setup.
