Leaving Aurora for Self-Managed PostgreSQL

Managed databases are good at hiding how databases work. That is the product. You get a connection string and an autoscaling knob, and the machine underneath stops being your problem.

Right up until it is. On the database I am moving, Aurora Serverless v2 hit its capacity ceiling repeatedly, and the bill scaled with the ceiling. At that point "the machine is not your problem" stops being true, and the only way to make a good decision about leaving is to understand what the managed layer was doing for you, in enough depth to do it yourself or consciously decide not to.

This is the playbook I built for that decision. It is written to be reusable: the specific database is one worked example, and every section teaches the piece of the system you need to reason about your own. If you finish it, you should be able to look at any PostgreSQL workload and answer four questions. What does it ask of a machine? What did the managed platform hide? What do I measure before I commit? How do I prove the new box under load before production traffic touches it?

The decision frame first

Before any technical detail, the posture. Every choice below follows from three rules I set at the start:

Measure before buying. No hardware commitment until a candidate box has replayed real traffic. On-demand pricing only until then.

Durability before data. Backups and WAL archiving are configured and tested before the first byte of production data lands on the new machine, not after.

One reversible step at a time. Restore a snapshot, replay, compare, and only then plan the cutover. At every step Aurora is still running and untouched.

None of that is clever. It is the difference between a migration and a gamble, and it shapes what you need to learn. You cannot measure what you do not understand, so the rest of this article is the understanding.

Part 1: What a database asks of a machine

PostgreSQL is a process that turns disk pages into query results. Almost every performance question reduces to four resources, and a self-managed migration forces you to size all four explicitly, where Aurora sized them for you.

CPU: cores versus clocks versus generation

PostgreSQL uses one backend process per connection. A single query runs on one core unless the planner decides to parallelise it, which it does for large scans and aggregates but not for the short OLTP statements that dominate most transactional workloads. So for OLTP, single-core speed matters as much as core count, and the two trade against each other in the instance catalogue.

Then there is the generation problem. "64 vCPUs" on a five-year-old server and "64 vCPUs" on this year's part are not the same amount of work per second. The honest way to compare is to normalise to a reference: pick a benchmark you trust for your access pattern, or take the vendor's stated generational uplift and treat it as an estimate, not a fact.

This mattered in the worked example. The Aurora cluster reported its ceiling in ACUs, Aurora's own capacity unit, on a platform version that AWS describes as roughly 30% faster than its predecessor without naming the silicon. Translating "peak ACU" to "how many cores of a specific x86 or ARM part" is not arithmetic, it is an estimate with a correction factor, and the correction pushed the core count up. That single correction eliminated every cheaper machine on my shortlist. Knowing that the number was an estimate is what stopped me from buying the wrong box.

Memory: three consumers, one pool

PostgreSQL memory has three big consumers, and they compete.

Shared buffers is PostgreSQL's own page cache. Pages are read from disk into it and modified there. A page that is in shared buffers is served without touching storage. On Aurora the size is chosen for you and moves with the instance. On the cluster I measured it had reached 153 GB at peak. That number became the floor for the new machine's RAM, because a cache that shrinks on migration day is a latency regression you will feel immediately.

The operating system page cache. PostgreSQL reads and writes data files through the kernel, so the kernel keeps its own cache of the same files. Pages can live in both, which is why a common rule of thumb sets shared buffers around a quarter of RAM and leaves the rest for the OS. Aurora has no equivalent, because its storage layer is remote. On a self-managed box, the OS cache is a real, useful, and unconfigurable second tier, and the RAM you "leave free" is not free at all.

Per-operation working memory. work_mem is the budget each sort or hash operation gets before spilling to a temporary file on disk. It is per operation, not per connection, so one complex query can use it several times over. The default is 4 MB. On the cluster I measured, work_mem was still 4 MB and the lifetime temp file volume was 48 TB. That is 48 TB of sort and hash work that went through disk instead of memory. It says the default was wrong for this workload, and it says the new box needs RAM headroom for a much higher work_mem multiplied by realistic concurrency.

Add those three up, with margin, and you have a RAM floor. For the worked example: 153 GB of cache plus headroom for working memory and the OS cache put the floor at 192 GiB and the plan at 256.

Storage: where a managed database differs most

This is the part that changes your mental model, so it gets its own section below. The short version: Aurora replaced your disk with a distributed storage service. When you leave, you get a disk back, and every property of that disk matters. Latency per write, whether it is local or network-attached, what happens to it when the instance is stopped, and whether the operating system's promise of an atomic write matches the size of a database page.

Network

Two things. Replication traffic to a standby, which is roughly your WAL rate. And the round trip from the application, which on a managed service you never chose and on a self-managed box you place deliberately, in the same zone as the workers that hit it hardest.

Part 2: How a write reaches disk

Everything about PostgreSQL durability, checkpoint tuning, and the Aurora difference comes from one sequence. Learn it once and the parameter names stop being magic.

The simple model

Three places are involved when a transaction changes a row:

1. Shared buffers. The page holding the row is modified in memory. It is now dirty.
2. The write-ahead log. A small record describing the change is appended to a sequential file and flushed to disk. Only after that flush does the transaction report success.
3. The data files. The dirty page is written to its real location later, in the background.

The write-ahead log is the durability mechanism. If the server dies after step 2 and before step 3, recovery replays the log onto the data files. Small sequential writes now, random page writes later, and a crash in between is survivable. That trade is most of why PostgreSQL performs the way it does.

Checkpoints

Dirty pages cannot stay dirty forever. A checkpoint is the moment PostgreSQL guarantees every dirty page from before a certain point has been written to the data files, and writes a marker into the WAL saying so. After a checkpoint, WAL older than the marker is no longer needed for crash recovery, and recovery after a crash only has to replay from the last checkpoint forward.

Two settings decide when checkpoints happen. checkpoint_timeout, default five minutes, is the maximum time between them. max_wal_size, default 1 GB, is how much WAL is allowed to accumulate before one is forced. Whichever limit arrives first triggers the checkpoint. A third setting, checkpoint_completion_target, default 0.9, spreads the page writes across most of the interval so the checkpoint is a steady background load rather than a burst.

The trade is explicit in the PostgreSQL WAL configuration chapter: shorter intervals mean faster crash recovery, because less WAL has to be replayed, and more I/O, because of what comes next.

Full page writes and torn pages

A PostgreSQL page is 8 KB. Most storage promises atomic writes of 4 KB or smaller. If power fails halfway through writing a page, the disk can hold the first half of the new version and the second half of the old one. That is a torn page, and WAL replay cannot repair it, because a WAL record describes a change to a page and applying a change to a half-wrong page yields a fully wrong page.

PostgreSQL's defence is full_page_writes. When it is on, which is the default, the first modification of any page after a checkpoint writes the entire 8 KB page into the WAL, not just the change. Recovery restores that whole image and replays later changes on top. The parameter reference is blunt about the alternative: turning it off "might lead to either unrecoverable data corruption, or silent data corruption, after a system failure."

Now connect the two mechanisms. Every checkpoint resets the "first modification since checkpoint" bookkeeping, so every page touched in the next interval gets logged in full again. Frequent checkpoints multiply full-page images. This is write amplification, and it is the reason a 60-second checkpoint interval on a busy database is a bad idea on real disks: you pay 8 KB of WAL per touched page per minute, on top of the checkpoint flush itself.

Worked example: what one small update actually writes

Take a table with three indexes and rows of about 80 bytes. You update one integer column on one row. That sounds like a hundred-byte write. Immediately after a checkpoint, on default settings, here is what actually reaches the WAL:

Write	Rough size	Why it exists
New heap tuple	~100 bytes	MVCC writes a new row version instead of editing in place
Full page image of the heap page	8 KB	First touch of that page since the checkpoint
Index entries, three indexes	~150 bytes	Each index needs a pointer to the new tuple
Full page images of three index pages	24 KB	The same first-touch rule, once per index page

Roughly 32 KB of write-ahead log for an 80-byte change. That is a 400x amplification, and it is not a bug. It is the price of being able to survive a torn page.

The number that makes it bearable is what happens next. Update a second row on the same heap page inside the same checkpoint interval and the full page images are gone: you pay only the ~250 bytes of tuple and index records. So amplification is front-loaded per page per interval, which is exactly why interval length is the lever. Halve the interval and you double how often you pay the 32 KB.

You can watch this directly. pg_stat_wal counts full page images separately:

SELECT wal_records,
       wal_fpi,
       pg_size_pretty(wal_bytes) AS wal_total,
       stats_reset
FROM pg_stat_wal;

A healthy write-heavy database shows wal_fpi far below wal_records. If full page images approach the record count, your checkpoints are too frequent for your write pattern, and lengthening the interval will cut WAL volume outright.

Sizing checkpoints from a number, not a guess

The only sane input for checkpoint settings is the WAL rate: bytes of WAL per hour under real load. With that number:

• Pick a checkpoint interval you can tolerate for crash recovery, commonly 15 to 30 minutes for a write-heavy primary.
• Set max_wal_size above the WAL generated in that interval, with margin, so time triggers checkpoints rather than volume. On a database writing several gigabytes an hour, that is tens of gigabytes, not the 1 GB default.
• Keep checkpoint_completion_target at 0.9 and watch the checkpointer statistics for checkpoints that were forced by volume rather than time.

Worked through with a number. Suppose the replay measures 6 GB of WAL per hour at peak:

Step	Arithmetic	Result
WAL per minute	6 GB / 60	100 MB
Target checkpoint interval	chosen for recovery time	15 minutes
WAL per interval	100 MB x 15	1.5 GB
max_wal_size with 2x headroom	1.5 GB x 2	3 GB

At the 1 GB default, that database would checkpoint every ten minutes on volume alone, never reaching the timeout, and every one of those checkpoints would re-arm the full page image tax from the previous section. Set to 3 GB, time triggers the checkpoints and volume becomes the safety net, which is the behaviour you want.

Then verify rather than trust. The checkpointer statistics tell you which trigger fired:

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

num_timed should dominate. A rising num_requested means volume is still forcing checkpoints and max_wal_size is too small for the interval you picked. On older PostgreSQL versions the same counters live in pg_stat_bgwriter under different names, so check which view your version exposes.

You read the WAL rate from pg_stat_wal. Unless you are on Aurora, where you cannot, and that is the subject of the next part.

The commit path: synchronous_commit and fsync

fsync on means PostgreSQL asks the operating system to physically flush WAL to the disk before reporting a commit. Turning it off is a documented way to lose the database on power failure. Leave it on. synchronous_commit is the subtler knob: with it on, the commit waits for the WAL flush; with it off, the commit returns immediately and the flush happens within about three times wal_writer_delay, 600 ms by default. Off does not risk corruption, only the loss of the last few hundred milliseconds of acknowledged transactions on a crash. Whether that is acceptable is a business question with a technical answer, and on a migration the baseline is to keep it on and measure the cost before relaxing it.

Part 3: What Aurora was doing for you

Aurora is PostgreSQL with a very different storage and operational model around it. The engine still generates WAL records. The difference is what happens to them.

Amazon's own description of the storage engine is that the database "only sends log records to the storage nodes and those writes are done in parallel", and that on the storage side "log records are first coalesced and then used to create data pages." The compute instance never writes a data page to a disk it owns. Materialising pages from the log is the storage service's job, replicated across three availability zones.

Once you hold that picture, the parameter group of an Aurora cluster reads very differently. On the cluster I measured:

full_page_writes    = off
checkpoint_timeout  = 60s
max_wal_size        = 1GB

On stock PostgreSQL those three lines describe corruption risk plus a checkpoint storm. On Aurora they are fine, and now you can see why. There is no local data file for a torn page to happen in, so full-page images have nothing to protect. A "checkpoint" does not flush dirty pages from the engine, so its frequency costs almost nothing. The values are accurate for that machine, and catastrophic for any other.

That generalises into the single most useful rule for a migration:

Parameter type	Examples	Transfers?
Describes the workload	autovacuum aggressiveness, work_mem observations, extension list, index usage	Yes, copy and then tune
Describes the physical write path	full_page_writes, checkpoint_timeout, max_wal_size, wal_buffers	No, derive from measurement on the new machine
Describes durability semantics	synchronous_commit, fsync	Keep the strict setting as baseline, measure before relaxing

The second thing Aurora does is hide the measurement you need most. pg_stat_get_wal(), the function behind pg_stat_wal, returns an error on Aurora saying it is not supported. Monitoring tools like pgmetrics and the Datadog agent carry explicit "skip WAL stats on Aurora" branches for exactly this reason. In my cluster pg_current_wal_lsn() was not usable either without turning on logical replication, and I found no CloudWatch metric that reports WAL bytes for Aurora PostgreSQL.

So the platform that makes WAL rate irrelevant for itself also makes it invisible, and WAL rate is the first input to configuring the database you are moving to. The consequence is a method, not a complaint: the first real WAL number comes from the replay box, and every checkpoint setting is a placeholder until it does.

Part 4: The read side, connections, and vacuum

The write path gets the drama. Three quieter areas decide whether the new box feels fast.

Reads and the double cache

A read is served from shared buffers if the page is there, from the OS cache if the kernel has it, and from disk otherwise. The cache hit ratio from pg_stat_database tells you how often you reach disk. On a migration, the target is to preserve that ratio, which means the new shared buffers must be at least the old peak, and the OS cache needs the rest of RAM to do its job. This is why "RAM equal to the Aurora instance" is the wrong comparison: Aurora had no OS cache to feed.

Indexes, and the partition trap

An unused-index audit is standard pre-migration hygiene: every index costs write amplification on every insert and update, and dropping dead ones is free performance. The trap is partitioned tables. Usage statistics are recorded per partition, so an index on the parent can show zero scans while its children are hit constantly. On the cluster I measured, a 41 GB index looked unused at the parent level and turned out to be essential once the child partitions were rolled up. Roll them up before you drop anything.

Connections are processes

Every PostgreSQL connection is an operating system process with its own memory. Aurora absorbs a few hundred idle connections without complaint. On a self-managed box they are a few hundred processes sitting on RAM and file descriptors. The cluster I measured had 186 idle anonymous connections, which is the argument for a connection pooler from day one.

The arithmetic that makes people nervous, correctly, is work_mem multiplied by concurrency. Raise work_mem to 256 MB to stop the temp file spilling from earlier, and the theoretical worst case is that number times the number of concurrent sort and hash operations, which is more than the number of connections because one query can use several at once. Two hundred active connections each running two sorts is 100 GB of working memory on a 256 GB machine, on top of shared buffers.

That worst case almost never happens, and designing as though it cannot happen is how databases fall over at 3am. The mitigation is not a smaller work_mem, because that is what sent 48 TB to disk. It is bounding the multiplier:

Lever	Effect	Cost
PgBouncer, small server pool	Caps concurrent backends regardless of app connections	Session features break, see below
work_mem set per role or per query	Big budget only for the queries that need it	Someone has to identify them
Lower global work_mem, raise in session	Safe default, explicit opt-in	Application changes

The pooler is the one that generalises, because it turns an unbounded number into a number you chose.

PgBouncer in transaction pooling mode assigns a server connection to a client only for the duration of a transaction, so a small pool of real backends serves a large number of application connections. It works by breaking some session assumptions on purpose. Session-level features are not supported through it: SET and RESET, LISTEN, WITH HOLD cursors, PREPARE and DEALLOCATE, session-level advisory locks, and a few others. The pre-migration check is to count how often the application uses each. This one used none: zero LISTEN, zero advisory locks. That made transaction pooling safe. If the count is not zero, session pooling or application changes come first.

Vacuum settings transfer cleanly

The Aurora cluster already ran autovacuum aggressively: a 5 second naptime, 8 workers, and a large work memory. Those numbers were earned by the workload's update rate, and they say nothing about the platform. They copy straight across. If anything, the new box with faster local storage lets vacuum keep up more easily, but the settings are the starting point, not the thing to reinvent.

Part 5: Measuring a workload you cannot fully see

A migration plan is only as good as its measurements, and the measurements come in layers. Here is the sheet I built for the worked example, with the source of each number and how confident it is.

What	Value (this cluster)	Source	Confidence
Logical data size	~570 GB	pg_database_size	Measured
Volume peak and growth	680 GB peak, ~6.3 GB/day	CloudWatch volume metrics	Measured
Cache at peak	153 GB shared buffers	Parameter and instance history	Measured
Temp file volume, lifetime	48 TB	pg_stat_database	Measured
Connection profile	186 idle, 0 LISTEN, 0 advisory locks	pg_stat_activity, code audit	Measured
Capacity ceiling	256 ACU limit hit repeatedly	Serverless scaling history	Measured
WAL rate	Unknown	Not readable on Aurora	Placeholder until replay
Core equivalent at peak	35 to 52 current-generation cores	ACU peak with a platform correction	Estimated

The right-hand column is the important one. A plan that mixes measured, estimated and unknown numbers without labelling them will look precise and be wrong. The single biggest correction in this plan, the platform generation factor, moved an estimate, and it moved the machine choice with it.

The method for the unknowns is a replay. Restore a snapshot of the production database onto the candidate machine, then drive it with captured production traffic at 1x, 1.5x and 2x rate. Read pg_stat_wal, the checkpointer stats, latency percentiles and CPU on a box that can actually report them. The 2x run answers the growth question. The 1x run answers the "is this box enough" question. Both answer the WAL question that Aurora could not.

Part 6: Sizing the machine

With the sheet filled in, sizing becomes a set of explicit gates rather than a shopping trip.

Cores. Take the estimated core equivalent at peak, with its correction, and pick the size that clears the upper bound. Choose the architecture consciously. ARM instances offered the best price per core for this workload; x86 would have been required if I needed a specific disaster-recovery tool that only supported x86. Write that dependency down before it decides for you.

RAM. Shared buffers peak plus working memory headroom plus OS cache. Floor 192 GiB, plan 256 GiB for this database.

Storage. Local NVMe or network-attached block storage is the biggest fork. Local NVMe gives the lowest write latency, which is what a WAL flush on every commit wants, and it disappears when the instance stops, which is what a durability plan has to survive. Network-attached storage survives instance loss and adds latency to every fsync. The worked example chose local NVMe for the primary and made the durability plan carry the risk: continuous WAL archiving plus a base backup tool, with a standby on network-attached storage in another zone as the next step. If your recovery objective cannot tolerate hours, the standby is not the next step, it is part of step one.

The budget. An on-demand budget of roughly $3,000 a month all-in, deliberately on-demand so that a wrong choice costs one month, not one year.

The gates before buying. The candidate has to pass all of these, and they are written down so that "it's probably fine" cannot creep in:

1. The instance size actually exists in the target region and zone. It did not, in the zone I first assumed.
2. The list price is verified against the catalogue, not a blog post.
3. The business has signed off on the recovery time objective that a single box implies.
4. A single-disk fsync latency test on the real NVMe matches expectations.
5. The 1x, 1.5x and 2x replays pass their latency targets.

Part 7: Durability and availability without a managed layer

This is the part where leaving a managed service costs you the most thinking, so it gets the most care.

Backups are WAL plus a base copy. A base backup is a copy of the data files at a point in time. Continuous WAL archiving is every WAL segment since. Together they let you restore to any moment, which is point-in-time recovery. pgBackRest handles the base backups and archive management; pg_receivewal can stream WAL to a second location for a tighter recovery point. Both get configured and tested with a real restore before production data arrives. "Durability before data" is not a slogan, it is an ordering constraint.

Checksums on. Data checksums cost a little CPU and catch silent corruption at read time instead of at restore time. Turn them on when initialising the new cluster; it is far harder later.

A standby is a second copy of the write path. Streaming replication ships WAL to a second server that replays it continuously. Asynchronous replication means the primary does not wait; you can lose the last few transactions on failover. Synchronous replication, via the remote_write or remote_apply values of synchronous_commit, makes the primary wait for the standby, which tightens the recovery point to zero at the cost of commit latency. The choice is the RPO number from the business, expressed as a parameter.

Failover is a procedure before it is a tool. Tools like Patroni automate promotion of a standby. Automating a procedure you have never run by hand is how you get a failover that promotes the wrong node at the worst time. The plan here is deliberate: manual failover drills first, documented, timed, and repeated, then automation once the manual version is boring.

The honest RTO of step one. With a single primary and archived WAL, recovery from instance loss means restoring a base backup and replaying WAL onto a new box. For a database in the hundreds of gigabytes, that is hours. That number was put in front of the business as the cost of the first phase, and the standby was scheduled as the thing that brings it down. Managed services make this trade for you silently. Self-managing means making it out loud.

Part 8: Building and proving the box

The build itself is the least interesting part once the thinking is done, but the order matters.

Infrastructure as code creates the instance, its security boundary and its storage; configuration management installs PostgreSQL 17 onto an XFS filesystem on the local NVMe with checksums enabled and the locale matched to the source, because a locale mismatch is a silent way to break index ordering after a restore. Access goes through a session-manager tunnel rather than a public SSH port. Secrets live in the platform's parameter store, not in a repository.

Then the proving sequence:

1. Synthetic load first. Before any production data, run a synthetic write-heavy load and watch the fsync latency, WAL throughput and checkpoint behaviour on this specific disk. This is where a wrong storage choice shows up cheaply.
2. Durability next. Configure and test backups and WAL archiving. Perform a restore. Time it. That time is your real RTO until a standby exists.
3. Restore the production snapshot and replay captured traffic at 1x, 1.5x and 2x. Fill in the WAL rate and derive the checkpoint settings from it.
4. Add the standby. Run failover drills by hand.
5. Only then plan the cutover window, with Aurora still live and untouched as the rollback.

Every step leaves the previous state intact. If step 3 says the box is wrong, the cost is one month of on-demand pricing and a corrected sheet.

What this actually teaches

Strip the specifics away and the reusable model is short.

A database is a machine plus a write path plus a durability plan. A managed service bundles all three and hides the seams. Leaving one means finding the seams: which parameters describe your workload and travel with it, which describe the platform's physical write path and must be rederived, and which numbers you can measure at the source versus only on a replay. Size from labelled measurements, gate every purchase on tests you wrote down in advance, put durability before data, and make the recovery objective a business decision spoken out loud rather than a default inherited from a vendor.

The distinction that made the whole decision tractable is the one I would hand to anyone starting the same move: separate engine behaviour from platform behaviour. Aurora is PostgreSQL with a different machine underneath. Once you know which parts were the engine and which were the machine, you know exactly what you are taking on.

Continue learning

If the write path now makes sense, the next thing to understand properly is checkpoint tuning under real WAL rates, including how checkpoint_completion_target shapes the I/O curve and what the checkpointer statistics tell you. After that, MVCC and vacuum in depth, because bloat is the slow-motion failure of every write-heavy PostgreSQL. Then streaming replication and failover mechanics, which is where the availability half of this playbook lives. Those three explain most of what a self-managed PostgreSQL will ever do to you.

Sources and further reading

Primary sources this article leans on, and the places to go deeper. In rough order of usefulness.

• PostgreSQL manual: WAL configuration. Checkpoints, full-page writes and the recovery trade-off, from the people who wrote the code. Read this before touching any checkpoint parameter.
• PostgreSQL manual: Reliability. What the database can and cannot promise about your disk, including the torn-page discussion and the caveats about drive write caches.
• PostgreSQL manual: WAL parameters. The reference for full_page_writes, synchronous_commit, fsync, wal_buffers, checkpoint_timeout and max_wal_size.
• PostgreSQL manual: Continuous archiving and point-in-time recovery. The durability model that pgBackRest and pg_receivewal implement.
• PostgreSQL manual: Log-shipping standby servers. Streaming replication, synchronous standbys and the semantics behind remote_write and remote_apply.
• PostgreSQL manual: Routine vacuuming and Concurrency control. MVCC, dead tuples and why autovacuum settings are workload settings.
• PostgreSQL manual: Cumulative statistics. pg_stat_wal, pg_stat_database and the other views used in the measurement sheet.
• The Internals of PostgreSQL by Hironobu Suzuki. The best free deep dive on the buffer manager, WAL and vacuum. Chapters 8 and 9 cover this article's write path at code level.
• Introducing the Aurora Storage Engine from the AWS Database Blog, and the SIGMOD 2017 paper Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases. The log-is-the-database model, from the source.
• PgBouncer features. The table of what transaction pooling breaks. Check your application against it before choosing a pooling mode.
• pgBackRest user guide and Patroni documentation. The two tools that carry the durability and failover halves of Part 7.

FAQ

Is Aurora bad? No. Its storage model removes an entire class of failure and an entire class of tuning. This database outgrew its capacity ceiling and its cost curve. That is a fit problem, not a quality problem.

Can I skip the replay and just size generously? You can, and you will not know your WAL rate, your checkpoint behaviour, or whether your fsync latency is acceptable until production tells you. The replay costs a few days. Production telling you costs an incident.

Why local NVMe if it disappears with the instance? Because commit latency on a write-heavy primary is dominated by WAL flush latency, and local NVMe is the fastest option. The durability plan, continuous WAL archiving now and a standby next, is what makes that acceptable. If the recovery objective cannot tolerate the gap, choose network-attached storage and accept the latency.

Does any of this change for a read-heavy workload? The write-path sections matter less and the cache sections matter more. Size RAM to keep the working set in shared buffers plus OS cache, and lean harder on read replicas. The measurement discipline is identical.