Using write forwarding with Amazon Aurora Global Database for PostgreSQL

Amazon Aurora combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open-source databases. Amazon Aurora is a global scale relational database service built for the cloud with drop-in MySQL and PostgreSQL compatibility. Amazon Aurora Global Database lets you span your Aurora database across multiple AWS Regions. This helps you recover from a Regional outage in typically a minute, or perform planned Regional rotations to adhere to compliance mandates. It also offers low-latency reads, so that your applications can improve read query latencies by reading data closer to your end-users.

An Amazon Aurora Global Database has one primary Region and up to five secondary Regions. The primary Region serves reads and writes, and the secondary Regions can serve reads. On November 9, 2023, Aurora Global Database for PostgreSQL announced support for write forwarding, expanding the feature to both Amazon Aurora for MySQL-Compatible Edition and Amazon Aurora for PostgreSQL-Compatible Edition databases. The write forwarding feature within Amazon Aurora Global Database for PostgreSQL, enables developers to issue DML commands to readers in a secondary Region and forward them to the primary Region. When writes are forwarded, the updates are applied in the primary Region first and then replicated to the secondary Regions. This verifies that the primary cluster is always the source of truth and has an up-to-date copy of your data. With global write forwarding, there is no need to set up networking among Regions for cross-Region writes. Additionally, developers can perform read after writes with a developer-specified level of consistency that meets the needs of their applications.

In this post, we show you how to enable and use the write forwarding feature within an Aurora Global Database for PostgreSQL with various consistency modes.

Overview of global write forwarding

An application that is using global database secondary Regions to serve fast, local reads to its end-users may also occasionally need to write to the database. Traditionally, to achieve cross-region writes, the application must do the following:

Establish connectivity from the secondary Region to the primary Region.
Split the application’s read and write requests so that reads go to the secondary Region readers and writes go to the primary Region.
Implement custom logic within the application to manage consistency when reading after writing. Amazon Aurora Global Database replicates data asynchronously between Regions with typically subsecond replication lag. Although the replication lag is minimal, it’s still possible for a read request against the secondary Region to miss a write sent by the application to the primary Region until it has replicated to the secondary Region.

Benefits of global write forwarding

The write forwarding feature within Aurora Global Database for PostgreSQL solves the aforementioned challenges and reduces the burden on developers. With global write forwarding, applications can connect to a reader node in the secondary Region and issue both read and write requests. The reads are served directly from the readers in the secondary Region. The writes are automatically forwarded to the writer in the primary Region, where it is updated first and then the changes are replicated to the secondary Regions. Key benefits of write forwarding include the following:

Managed connectivity – Writes are transparently forwarded to the primary cluster. Aurora takes care of the maintenance activities and fully manages the connectivity between the secondary and primary cluster.
No replication conflicts – Because all writes are applied by the single writer in the primary cluster, replication-related update conflicts don’t occur.
Simplicity – You don’t need to split the read and write requests in the application or build complex logic to manage consistency in case of read-after-write requests.
Flexibility – You can choose among several read consistency levels that meet the needs of your application, balancing between consistency and performance.

How global write forwarding works

Aurora Global Database write forwarding works by accepting a write statement at a read replica in a secondary database cluster. Aurora then forwards the identified write statements with the necessary context to the primary cluster. The output from the statement, including warnings and errors, is returned to the secondary cluster’s reader instance, which returns it back to the application. This entire process is transparent to the application. You only need to enable write forwarding for the cluster and verify the appropriate consistency mode is set for your application. The consistency mode for Aurora Global Database for PostgreSQL is governed through the apg_write_forward.consistency_mode parameter in the database cluster parameter group setting. You can also override the consistency mode governed through the parameter by setting the apg_write_forward.consistency_mode variable in an individual session.

Now, let’s discuss the different consistency modes available with write forwarding in Aurora Global Database for PostgreSQL.

Consistency modes

As mentioned before, a write forwarded to the primary cluster takes time to replicate back to the secondary database cluster. If you don’t need read-after-write consistency, then you may continue to the next statement without waiting for the replication to complete and improve read latency. However, if your application requires read-after-write consistency, you need to wait for the replication to complete to make sure that subsequent reads see the recent write. To that end, write forwarding supports the following configurable read consistency modes:

Eventual mode – Eventual consistency mode means the subsequent read requests won’t wait for the writes to replicate back. You may want to use eventual consistency mode when you need to perform writes from the secondary Region if you don’t want to do the heavy lifting of setting the networking between Regions, and your application doesn’t need consistency in case of read-after-write scenarios.
Session mode – Session consistency mode provides that reads after a write in the same session will wait for the replication to complete before processing the read query. This is the default value for the apg_write_forward.consistency_mode parameter. This makes sure that the session sees its own changes, but it’s not guaranteed to see changes issued by other sessions.
Global mode – Global consistency mode makes sure that read queries wait for replication to catch up to the point in time when the read started. This means that the read from the secondary cluster will see the changes committed to the primary cluster up to the point when the read query was started on the secondary cluster. Although this mode provides the strongest read-after-write consistency, it does so at the expense of performance.
Off mode – Off consistency mode turns off write forwarding. You can use the consistency mode to disable write forwarding through the parameter group or intermittently using the apg_write_forward.consistency_mode variable in an individual session. By disabling write forwarding through the parameter group, you can make sure that if your developers need to use write forwarding, they must specify the consistency mode that best meets their application needs.

The default value for the apg_write_forward.consistency_mode parameter is session.

Enable write forwarding in an Aurora Global Database

You can enable write forwarding when adding a new Region to an existing Aurora cluster, or modify an existing Aurora Global Database. There are no reboots required to enable write forwarding for an existing Aurora Global Database cluster. To enable write forwarding for an existing Amazon Aurora Global Database for PostgreSQL, complete the following steps:

On the Amazon RDS console, choose Databases in the navigation pane.
Select Aurora Global Database’s secondary cluster and choose Modify.
In the Read replica write forwarding section, select Turn on read replica write forwarding, then choose Continue.
For Schedule modifications, choose Apply immediately for the modifications to be applied as soon as possible, regardless of the maintenance window setting for the database cluster.

If you do not wish to apply the modifications immediately, then choose Apply during the next scheduled maintenance window under Schedule Modifications, to enable the write forwarding feature during the upcoming maintenance window. There are no reboots or unavailability expected for your primary or secondary global database clusters to enable write forwarding.

Using write forwarding

To demonstrate write forwarding, we have created an Aurora Global Database for PostgreSQL version 15.4 with the primary Region in ap-south-1 and the secondary Region in eu-west-1, as shown in the previous section. For more information about the Region and version availability of the write forwarding feature, refer to Region and version availability of write forwarding in Aurora PostgreSQL. Complete the following steps to use write forwarding:

After connecting to the writer instance (using the cluster endpoint) in the primary Region (ap-south-1), create a new logins table using the following code:
```
pgtest=> CREATE TABLE logins
(
id serial primary key,
name varchar(100) not null,
loginDate timestamp
);
```
Note that the write forwarding feature only forwards DML commands. This means DDL commands such as CREATE TABLE must be run directly in the primary Region. To better understand which statements work with the write forwarding feature, refer to Application and SQL compatibility with write forwarding in Aurora PostgreSQL. For more information about the considerations around isolation and consistency modes with the write forwarding feature, refer to Isolation and consistency for write forwarding in Aurora PostgreSQL.
Connect from the secondary Region (eu-west-1) using the secondary Region’s reader endpoint. Before proceeding further, let’s check the consistency level set for the apg_write_forward.consistency_mode parameter in the parameter group using the following command:
```
/*Check the consistency mode by reading the apg_write_forward.consistency_mode*/

pgtest=> SHOW apg_write_forward.consistency_mode;
 apg_write_forward.consistency_mode 
------------------------------------
 session
(1 row)
```
The parameter value is derived from the database cluster parameter group by all the sessions. The default value for the apg_write_forward.consistency_mode parameter is session. If needed, you can set the desired parameter value for the apg_write_forward.consistency_mode parameter in a session and override its value set in the cluster parameter group only until the end of that session. Modifying the apg_write_forward.consistency_mode parameter value does not necessitate DB instance reboots.
Let’s issue a DML command (INSERT into the logins table) on the secondary Region (eu-west-1). With the write forwarding feature enabled, we can observe how the following DML statement inserts a row in the logins table from the secondary Region:
```
/* Forwarded write from secondary Region and read the rows from logins table */

pgtest=> insert into logins values (default, 'John - login from Dublin (secondary)','11-05-23');

INSERT 0 1
```
We can verify if the write forwarding feature succeeded by reading all the rows in the logins table:
```
pgtest=> select * from logins order by id desc;
 id | name | logindate  
----+--------------------------------------+------------+-------+
 2 | John - login from Dublin (secondary) | 2023-11-05 00:00:00 |
 1 | Adam - login from Mumbai (primary)   | 2023-10-04 00:00:00 |
```
From the preceding output, you can see that when you issue a DML command against the secondary read-only global database cluster, Aurora forwards the DML (INSERT) statement from the secondary read-only Region to the writer in the primary Region. The data is replicated back to the secondary Regions and is then visible from the readers in the secondary Regions.

Now that we have validated the write forwarding feature within the Amazon Aurora PostgreSQL global database, let’s look at how the write forwarding feature works in various consistency modes such as eventual, session, and global individually. To do so, we’re using a basic Python application. The Python application issues a write (to the logins table) to the readers in the secondary Region (eu-west-1). After the write is committed, the application reads and displays the latest five rows from the same logins table.

Eventual consistency mode

Let’s begin by setting the consistency mode to eventual by editing the value of the apg_write_forward.consistency_mode parameter in a custom cluster parameter group that is applied to our Aurora Global Database’s secondary cluster.

After saving the changes, you can verify the consistency mode using the SHOW command (similar to the step shown previously) and confirm if the parameter value changes were applied in the secondary Region.

To demonstrate eventual consistency, a single thread from the Python application is started. In the thread, an INSERT command on the reader endpoint of the secondary Region is issued. After the write gets committed, the latest five rows from the same logins table are read.

Before starting the application, let’s verify the replica lag in the secondary Region. The replication lag gives us a sense of how long it could take for the write committed in the primary Region (ap-south-1) to become available in the secondary Region (eu-west-1). We’ll monitor the replica lag using the AuroraReplicaLag instance-level Amazon CloudWatch metric. For more information about the various other CloudWatch metrics for Aurora, refer Amazon CloudWatch metrics for Amazon Aurora.

Here, in our case, the average AuroraReplicaLag over a 5-minute period in the secondary Region was observed to be 285.97 milliseconds, as highlighted. Because we’re using eventual consistency mode, the Aurora Global Database doesn’t wait for the writes to be replicated back to the readers in the secondary Region. Therefore, as long as the read requests from the application arrive before approximately 200 milliseconds, the forwarded write won’t be visible in the read request’s output. Let’s see this in action:

/* Testing eventual consistency by issuing a read after write from a single thread */

[ec2-user@***]$ python3.9 demo_write_forwarding.py 1
[Thread: thread-1] Starting thread-1 with args: ['demo_write_forwarding.py','1']
[Thread: thread-1] Write committed at time: 11/06 6:23:03.519 with login user: ligiro
[Thread: thread-1] Reading latest 5 row from login table at 11/06 6:23:03.537
╒══════╤══════════════════════════════════════╤═════════════════════╤
│   ID │ Login Detail                         │ Login Date          │
╞══════╪══════════════════════════════════════╪═════════════════════╪
│    2 │ John - login from Dublin (secondary) │ 2023-11-05 00:00:00 │ 
├──────┼──────────────────────────────────────┼─────────────────────┼
│    1 │ Adam - login from Mumbai (primary)   │ 2023-10-04 00:00:00 │
╘══════╧══════════════════════════════════════╧═════════════════════╧
[Thread: thread-1] [start: 11/06 6:23:02.481, end: 11/06 6:23:03.539, timeLapse: 1.0587]

As expected, when using the write forwarding feature with the eventual consistency mode, you can observe that although the write for the user ligiro was committed, it wasn’t visible in the read request that followed the write immediately. The reason behind this behavior is that the replicas in the secondary Region hadn’t caught up yet.

Session consistency mode

Next, we run the same test with the session consistency mode. However, before running the application again, let’s update the parameter value for the apg_write_forward.consistency_mode parameter to session and also confirm the updated parameter value from the secondary Region. When we run the same Python application again, it produces the following result:

/* Testing session consistency by issuing a read after write from a single thread */

[ec2-user@***]$ python3.9 demo_write_forwarding.py 1
[Thread: thread-1] Starting thread-1 with args: ['demo_write_forwarding.py', '1']
[Thread: thread-1] Write committed at time: 11/06 6:31:29.776 with login user: poqohi
[Thread: thread-1] Reading latest 5 row from login table at 11/06 6:31:29.795
╒══════╤════════════════════════════════════════╤════════════════════════════╤
│   ID │ Login Detail                           │ Login Date                 │
╞══════╪════════════════════════════════════════╪════════════════════════════╪
│    4 │ poqohi - login from Dublin (secondary) │ 2023-11-06 06:31:28.750334 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    3 │ ligiro - login from Dublin (secondary) │ 2023-11-06 06:23:02.481080 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    2 │ John - login from Dublin (secondary)   │ 2023-11-05 00:00:00        │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    1 │ Adam - login from Mumbai (primary)     │ 2023-10-04 00:00:00        │
╘══════╧════════════════════════════════════════╧════════════════════════════╧
[Thread: thread-1] [start: 11/06 6:31:28.750, end: 11/06 6:31:29.923, timeLapse: 1.1737]

When using write forwarding with session as the consistency mode, you can see that the write to insert the user’s (poqohi) login information was forwarded and committed in the primary Region. Moreover, the subsequent read within the same session waited for this write to be replicated to the secondary Region before returning the read request results output to the application. As a result, you can see the forwarded write for the newly inserted user (poqohi) in the latest five rows extracted by the Python application.

With session consistency mode, the reads within a particular session (session one) is consistent with the writes from the same session (session one). However, if another session (session two) issued and committed a write in its own session, then it may not be seen by the read request in the other session (session one).

To better understand this behavior, we have demonstrated this scenario in the following code by opening another thread from the same Python application. When the writes are committed from both the threads (Thread-1 and Thread-2) before any of the read requests, it’s possible that some reads requests won’t see the writes sent across the different sessions because consistency is only guaranteed within a session (because the apg_write_forward.consistency_mode parameter is set to session).

/* Testing session consistency by issuing a read after write from two threads */

[ec2-user@ip-172-31-38-77 final]$ python3.9 demo_write_forwarding.py 2
[Thread: thread-1] Starting thread-1 with args: ['demo_write_forwarding.py', '2']
[Thread: thread-2] Starting thread-2 with args: ['demo_write_forwarding.py', '2']
[Thread: thread-2] Write committed at time: 11/06 7:5:45.875 with login user: purigu
[Thread: thread-1] Write committed at time: 11/06 7:5:45.879 with login user: dogufe
[Thread: thread-2] Reading latest 5 row from login table at 11/06 7:5:45.893
╒═══╤═══════════════════════════════════════╤═════════════════════╤
│ ID│ Login Detail                          │ Login Date          │
╞═══╪═══════════════════════════════════════╪═════════════════════╪
│ 5 │ purigu - login from Dublin (secondary)│ 2023-11-06 07:05:44 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 4 │ poqohi - login from Dublin (secondary)│ 2023-11-06 06:31:28 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 3 │ ligiro - login from Dublin (secondary)│ 2023-11-06 06:23:02 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 2 │ John - login from Dublin (secondary)  │ 2023-11-05 00:00:00 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 1 │ Adam - login from Mumbai (primary)    │ 2023-10-04 00:00:00 │
╘═══╧═══════════════════════════════════════╧═════════════════════╧
[Thread: thread-2] [start: 11/06 7:5:44.846, end: 11/06 7:5:45.896, timeLapse: 1.0497]

[Thread: thread-1] Reading latest 5 row from login table at 11/06 7:5:45.898
╒═══╤═══════════════════════════════════════╤═════════════════════╤
│ ID│ Login Detail                          │ Login Date          │
╞═══╪═══════════════════════════════════════╪═════════════════════╪
│ 6 │ dogufe - login from Dublin (secondary)│ 2023-11-06 07:05:44 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 5 │ purigu - login from Dublin (secondary)│ 2023-11-06 07:05:44 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 4 │ poqohi - login from Dublin (secondary)│ 2023-11-06 06:31:28 │
├───┼───────────────────────────────────────┼─────────────────────┼
│ 3 │ ligiro - login from Dublin (secondary)│ 2023-11-06 06:23:02 │ 
├───┼───────────────────────────────────────┼─────────────────────┼
│ 2 │ John - login from Dublin (secondary)  │ 2023-11-05 00:00:00 │  
╘═══╧═══════════════════════════════════════╧═════════════════════╧
[Thread: thread-1] [start: 11/06 7:5:44.848, end: 11/06 7:5:46.277, timeLapse: 1.4289]

As shown in the preceding code, the two threads started concurrently. The write from thread-2 completed first for user purigu, followed by the write from thread-1 for user dogufe. The read from thread-2 was consistent with the write from the same session and therefore contained the user purigu in the session’s read output. However, the read from thread-2 didn’t wait for the write from thread-1, meaning that it didn’t wait for the write from the other thread for the user dogufe to be replicated to the secondary Region, because that write for user dogufe happened outside its session. The read from thread-1, on the other hand, had to wait for its session’s write changes to be replicated to the secondary, which happened after the write from thread-2 (purigu). Therefore, the read from thread-1 saw both writes, which can be seen in the preceding code in the second read output table.

Next, let’s see the output with the global consistency mode.

Global consistency mode

If your application needs the reads to be consistent with writes across all open sessions, you may want to use global consistency mode. To understand the write forwarding feature with global consistency mode, let’s update the value for the apg_write_forward.consistency_mode parameter to global and confirm the updated value from the secondary Region. When we run the same Python application, we get the following output:

/* Testing global consistency by issuing a read after write from two threads */

[ec2-user@ip-172-31-38-77 final]$ python3.9 demo_write_forwarding.py 2
[Thread: thread-1] Starting thread-1 with args:['demo_write_forwarding.py', '2']
[Thread: thread-2] Starting thread-2 with args: ['demo_write_forwarding.py', '2']
[Thread: thread-2] Write committed at time: 11/06 8:30:55.321 with login user: mubomo
[Thread: thread-1] Write committed at time: 11/06 8:30:55.324 with login user: woreto
[Thread: thread-2] Reading latest 5 row from login table at 11/06 8:30:55.339
╒══════╤════════════════════════════════════════╤════════════════════════════╤
│   ID │ Login Detail                           │ Login Date                 │
╞══════╪════════════════════════════════════════╪════════════════════════════╪
│    8 │ mubomo - login from Dublin (secondary) │ 2023-11-06 08:30:54.611970 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    7 │ woreto - login from Dublin (secondary) │ 2023-11-06 08:30:54.631695 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    6 │ dogufe - login from Dublin (secondary) │ 2023-11-06 07:05:44.848925 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    5 │ purigu - login from Dublin (secondary) │ 2023-11-06 07:05:44.846970 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    4 │ poqohi - login from Dublin (secondary) │ 2023-11-06 06:31:28.750334 │
╘══════╧════════════════════════════════════════╧════════════════════════════╧
[Thread: thread-2] [start: 11/06 8:30:54.611, end: 11/06 8:30:55.580, timeLapse: 0. 9683]

[Thread: thread-1] Reading latest 5 row from login table at 11/06 8:30:55.342
╒══════╤════════════════════════════════════════╤════════════════════════════╤
│   ID │ Login Detail                           │ Login Date                 │
╞══════╪════════════════════════════════════════╪════════════════════════════╪
│    8 │ mubomo - login from Dublin (secondary) │ 2023-11-06 08:30:54.611970 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    7 │ woreto - login from Dublin (secondary) │ 2023-11-06 08:30:54.631695 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    6 │ dogufe - login from Dublin (secondary) │ 2023-11-06 07:05:44.848925 │
├──────┼────────────────────────────────────────┼────────────────────────────┼
│    5 │ purigu - login from Dublin (secondary) │ 2023-11-06 07:05:44.846970 │
│    4 │ poqohi - login from Dublin (secondary) │ 2023-11-06 06:31:28.750334 │
╘══════╧════════════════════════════════════════╧════════════════════════════╧
[Thread: thread-1] [start: 11/06 8:30:54, end: 11/06 8:30:55, timeLapse: 0. 9562]

In the preceding example, you can see two threads started concurrently. The key difference here is that the read requests from both threads waited for committed writes across the two sessions to be replicated back to the secondary Region. As a result, you can observe both writes (mubomo and woreto) in the read output. The global consistency mode offers the strongest read-after-write consistency, but it does so at the expense of performance. You can use global consistency mode with the write forwarding feature when your application writes and reads from the secondary Region can afford to slow down in order to meet the strongest consistency requirement.

Summary

In this post, we explored the write forwarding feature of Aurora Global Database for PostgreSQL. Aurora Global Database provides significant benefits for your application in terms of scalability, geographic load balancing, disaster recovery, and cost optimization. Write forwarding maintains that write operations are directed to the primary instance, and read operations can be efficiently distributed to read replicas to improve query performance and scalability. Write forwarding reduces the complexity of application code or the need to deploy a proxy to differentiate between read and write queries. Additionally, developers can now make trade-offs between read consistency and speed by specifying the apg_write_forward.consistency_mode parameter or intermittently setting the variable in an individual session.

Get started with Aurora Global Database and write forwarding today!

About the Authors

Rohan Bhatia is a Product Manager on the Amazon Aurora team based in Seattle, WA.

Rinisha Marar is an Amazon RDS for PostgreSQL Solutions Architect specializing in relational (Amazon RDS and Amazon Aurora PostgreSQL) database services at Amazon Web Services. She helps customers by providing them technical and architectural guidance, solutions, and recommendations on best practices when using AWS services.

Eric Felice is Senior Database Solutions Architect specializing in AWS databases. He provides technical guidance to help customers migrate and modernize using AWS purpose-built database services.