Data inconsistency in AWS Amazon Aurora Postgres solved with Local Write Forwarding?

I'm a big fan of Postgres. I'm also a big fan of AWS Aurora Postgres. While working as a consultant optimising databases for clients, I witnessed first hand the amazing scalability that's possible with these two technologies. But it's not all sun and roses.

YouTube has many . The to retain data from recently accessed blocks in volatile memory (RAM). And this is what can lead to inconsistent query results. Consider this scenario:

1. The writer instance receives a query updating a row.


2. The writer sends the update to the storage layer.


3. The storage layer commits the change and returns a success to the writer instance.


4. The writer instance returns to the client that the transaction was successful.


5. A reader instance receives a read-only query for the same row that was just updated.


6. The reader has a large amount of RAM, and that row is already in the page cache, so it skips going to storage and simply returns the row from the page cache.

See the problem? The reader instance skipped the lookup to the backend storage as it believed it already had the latest available data to return. Meanwhile a background process runs between the storage layer and compute layer, which invalidates the page cache blocks when the underlying block has changed. Unfortunately there's a small, but significant, latency to this process which results in this issue. Let's validate that latency.

I'll be using an Aurora Postgres cluster using engine version 16.4, a single writer instance and a single reader instance, both running db.t4g.medium instances. As a test client I'll be using a t3a.micro EC2 instance. The writer, reader and test client are all in different AZs, in the same eu-west-1 region.

For testing I'll be using a simple Python script. The script will perform the following actions:

1. Open database connections to the writer, reader or both, depending on the test scenario.


2. Update a row within a table with a counter starting at 0 and increasing sequentially up to the maximum number of repetitions.


3. Wait an increasing amount of time, starting with no wait and going up to 100 milliseconds.


4. Read the same row back and check if the counter has the new value (consistent), or still has the old value (inconsistent).


5. Repeat steps 2-4 until reaching the maximum number of 10,000 repetitions.

The time taken between steps 2 and 4 will also include the time it takes the script to run, which needs to be taken into consideration. So let's work out how long that will be. Let's run without any delay in step 3, with both writes and reads going to the writer instance. This will be our absolute best case scenario in terms of code latency, and in this scenario, the code takes (M=0.12795ms, SD=0.000155) measured from the successful commit of the transaction in step 2, to the issuing of the read request in step 4 across 100,000 repetitions. Pretty quick, and more than quick enough for our testing.

Now we know how quick we can read data back, we can now start to see if a read immediately after a write will return the data we expect. Here's the results when writing to the writer, and reading from the reader.

should be doing. (Although impossible to see on the chart, we still get a failure rate of 0.02% with a 50 millisecond delay added).

Having previously identified what the issue is likely to be here, the page cache, let's repeat the same test but sending both the updates and the reads to the writer instance.

, which provides horizonal autoscaling of writer instances, but that's a topic for another day as it has some significant design details which need taking into consideration. Putting the Limitless product aside, this means that you will always be limited to a single writer instance within any single Aurora Postgres cluster. So the writer instance should only be used for queries performing updates, with all other queries handled by autoscaling reader instances. But what other option do we have? If we're performing updates and then needing to read that data back with consistency, based on these findings we have to use the writer instance, or add in an artificial delay, don't we?

This was true until the , and once enabled clients can specify the level of consistency they need when using the feature:

OFF: Disabled, updates sent to a reader instance will fail immediately.

SESSION: The default on a cluster with local write forwarding enabled. This means that any changes made within a single session will always be consistent within that session, but may not be consistent in other sessions.

EVENTUAL: This allows for updates to be sent to reader instances for forwarding to the writer instance, but provides absolutely no guarantee that the data will be immediately consistent.

GLOBAL: The sledge hammer setting. This ensures that all updates sent through the session are replicated to all reader instances before the transaction returns.

This should solve our page cache data consistency issue, and it even allows us to set the desired consistency required on a per client basis, which is fantastic. Let's check it works by repeating our previous tests. We'll start with EVENTUAL consistency, where we should still expect to see failures.

No more failures! And finally, let's try with GLOBAL consistency.

Let's look a bit closer at the numbers:

A whopping 43.87% increase in latency compared to not using local write forwarding and this is on an otherwise completely empty, isolated and idle cluster, an entirely unrealistic prospect in the real world.

Now that sounds like a big increase, but the latency figures are still under 10ms across the board. How that scales with a real-life production workload, is entirely dependent on the workload in question. Using load testing tools such as . The chart below shows a reader instance actively forwarding traffic to the writer instance. These new metrics will be really useful as production workloads move onto clusters with local write forwarding enabled, helping to diagnose situations when the feature is causing unexpected bottlenecks.

Overall I would consider local write forwarding a big win, even with the latency penalty shown above. The ability to remove all traffic from the writer instance and throw everything at the readers makes life a lot simpler for developers, without having to worry about consistency issues. I highly recommend people have a play and see how it performs.

If you're interested in the raw data behind this blog post, spot any inaccuracies, or would like to add anything, please do get in touch.