# Local write forwarding in Aurora PostgreSQL *Local (in-cluster) write forwarding* allows your applications to issue read/write transactions directly on an Aurora Replica. The write commands are then forwarded to the writer DB instance to be committed. You can use local write forwarding for your applications that have occasional writes and require *read-after-write consistency*, which is the ability to read the latest write in a transaction. Without write forwarding, your applications must fully split all read and write traffic, maintaining two sets of database connections to send the traffic to the appropriate endpoint. Read replicas receive updates asynchronously from the writer instance. In addition, because replication lag can differ among read replicas, achieving global read consistency across all replicas is difficult. You must transact any reads requiring read-after-write consistency on the writer database instance. Alternatively, you would need to develop complex custom application logic to take advantage of multiple read replicas for scalability while ensuring consistency. With write forwarding, you avoid the need to split those transactions or send them exclusively to the writer instance. You also don't have to develop complex application logic to achieve *read-after-write consistency* consistency. Local write forwarding is available in every Region where Aurora PostgreSQL is available. It is supported in the following Aurora PostgreSQL versions: + 16.4 and higher 16 versions + 15.8 and higher 15 versions + 14.13 and higher 14 versions Local write forwarding is used to forward writes from in-Region replicas. To forward writes from a global replica, see [Using write forwarding in an Amazon Aurora global database](aurora-global-database-write-forwarding.md). **Topics** + [Limitations and considerations of local write forwarding in Aurora PostgreSQL](aurora-postgresql-write-forwarding-limitations.md) + [Configuring Aurora PostgreSQL for Local write forwarding](aurora-postgresql-write-forwarding-configuring.md) + [Working with local write forwarding for Aurora PostgreSQL](aurora-postgresql-write-forwarding-understanding.md) + [Monitoring local write forwarding in Aurora PostgreSQL](aurora-postgresql-write-forwarding-monitoring.md) # Limitations and considerations of local write forwarding in Aurora PostgreSQL The following limitations currently apply to local write forwarding in Aurora PostgreSQL: + Local write forwarding is not supported with RDS Proxy. + Certain statements aren't allowed or can produce stale results when you use them in Aurora PostgreSQL with write forwarding. In addition, user defined functions and user defined procedures aren't supported. Thus, the `EnableLocalWriteForwarding` setting is turned off by default for DB clusters. Before turning it on, check to make sure that your application code isn't affected by any of these restrictions. + The following kinds of SQL statements aren't supported with write forwarding: **Note** These statements can be implicitly used by you in your application or inferred by the PostgreSQL protocol. For example, PL/SQL exception handling can result in the use of SAVEPOINT, which is not a supported statement. + `ANALYZE` + `CLUSTER` + `COPY` + Cursors – Cursors aren't supported, so make sure to close them before using local write forwarding. + Data definition language (DDL) statements + `GRANT`\$1`REVOKE`\$1`REASSIGN OWNED`\$1`SECURITY LABEL` + `LISTEN / NOTIFY` + `LOCK` + `SAVEPOINT` + `SELECT INTO` + `SET CONSTRAINTS` + Sequence updates: `nextval()`, `setval()` + `TRUNCATE` + Two-phase commit commands: `PREPARE TRANSACTION`, `COMMIT PREPARED`, `ROLLBACK PREPARED` + User defined functions and user defined procedures. + `VACUUM` You can consider using the following SQL statements with write forwarding: + A DML statement might consist of multiple parts, such as an `INSERT ... SELECT` statement or a `DELETE ... WHERE` statement. In this case, the entire statement is forwarded to the writer DB instance and run there. + Data manipulation language (DML) statements, such as `INSERT`, `DELETE`, and `UPDATE`. + `EXPLAIN` statements with the statements in this list. + `PREPARE` and `EXECUTE` statements. + `SELECT FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }` statements. # Configuring Aurora PostgreSQL for Local write forwarding Using the following sections, you can enable local write forwarding for your Amazon Aurora PostgreSQL DB cluster, configuring consistency levels, and managing transactions with write forwarding. ## Enabling local write forwarding By default, local write forwarding isn't enabled for Aurora PostgreSQL DB clusters. You enable local write forwarding at the cluster level, not at the instance level. ### Console Using the AWS Management Console, select the **Turn on local write forwarding** check box under **Read replica write forwarding** when you create or modify a DB cluster. ### AWS CLI To enable local write forwarding with the AWS CLI, use the `--enable-local-write-forwarding` option. This option works when you create a new DB cluster using the `create-db-cluster` command. It also works when you modify an existing DB cluster using the `modify-db-cluster` command. You can disable local write forwarding by using the `--no-enable-local-write-forwarding` option with these same CLI commands. The following example creates an Aurora PostgreSQL DB cluster with local write forwarding enabled. ``` aws rds create-db-cluster \ --db-cluster-identifier write-forwarding-test-cluster \ --enable-local-write-forwarding \ --engine aurora-postgresql \ --engine-version 16.4 \ --master-username myuser \ --master-user-password mypassword \ --backup-retention 1 ``` You then create writer and reader DB instances so that you can use write forwarding. For more information, see [Creating an Amazon Aurora DB cluster](Aurora.CreateInstance.md). ### RDS API To enable local write forwarding using the Amazon RDS API, set the `EnableLocalWriteForwarding` parameter to `true`. This parameter works when you create a new DB cluster using the `CreateDBCluster` operation. It also works when you modify an existing DB cluster using the `ModifyDBCluster` operation. You can disable local write forwarding by setting the `EnableLocalWriteForwarding` parameter to `false`. ### Enabling local write forwarding for database sessions The `apg_write_forward.consistency_mode` parameter is a DB parameter and DB cluster parameter that enables write forwarding. You can specify `SESSION`, `EVENTUAL`, `GLOBAL`, or `OFF` for the read consistency level. To learn more about consistency levels, see [Consistency and isolation for local write forwarding in Aurora PostgreSQL](#aurora-postgresql-write-forwarding-isolation). The following rules apply to this parameter: + The default value is `SESSION`. + Local write forwarding is available only if you set `apg_write_forward.consistency_mode` to `EVENTUAL`, `SESSION`, or `GLOBAL`. This parameter is relevant only in reader instances of DB clusters that have local write forwarding enabled. + Setting the value to `OFF` disables local write forwarding in the session. ## Consistency and isolation for local write forwarding in Aurora PostgreSQL You can control the degree of read consistency on a read replica. You can adjust the read consistency level to ensure that all forwarded write operations from your session are visible in the read replica before any subsequent queries. You can also use this setting to ensure that queries on the read replica always see the most current updates from the writer DB instance. This is so even for those submitted by other sessions or other clusters. To specify this type of behavior for your application, you choose the appropriate value for the session-level parameter `apg_write_forward.consistency_mode`. The `apg_write_forward.consistency_mode` parameter has an effect only on read replicas that have local write forwarding enabled. **Note** For the `apg_write_forward.consistency_mode` parameter, you can specify the value `SESSION`, `EVENTUAL`, `GLOBAL`, or `OFF`. By default, the value is set to `SESSION`. Setting the value to `OFF` disables write forwarding. As you increase the consistency level, your application spends more time waiting for changes to be propagated to read replicas. You can choose the balance between lower latency and ensuring that changes made in other locations are fully available before your queries run. With each available consistency mode setting, the effect is as follows: + `SESSION` – A session on a read replica that uses local write forwarding see the results of all changes made in that session. The changes are visible regardless of whether the transaction is committed. If necessary, the query waits for the results of forwarded write operations to be replicated to the current reader DB instance. It doesn't wait for updated results from write operations performed in other sessions within the current DB cluster. + `EVENTUAL` – A session on a read replica that uses local write forwarding might see data that is slightly stale due to replication lag. Results of write operations in the same session aren't visible until the write operation is performed on the writer DB instance and replicated to the read replica. The query doesn't wait for the updated results to be available. Thus, it might retrieve the older data or the updated data, depending on the timing of the statements and the amount of replication lag. + `GLOBAL` – A session on a read replica sees changes made by that session. It also sees all committed changes from both the writer DB instance and other read replicas. Each query might wait for a period that varies depending on the amount of session lag. The query proceeds when the read replica is up-to-date with all committed data from the writer DB instance, as of the time that the query began. **Note** The global consistency mode impacts the latency of queries executed within a session. It will perform a wait even when the session has not sent any write queries. + `OFF` – Local write forwarding is disabled. In sessions that use write forwarding, you can use the `REPEATABLE READ` and `READ COMMITTED` isolation levels. However, the `SERIALIZABLE` isolation level isn't supported. For more information about all the parameters involved with write forwarding, see [Default parameter settings for write forwarding](aurora-postgresql-write-forwarding-understanding.md#aurora-postgresql-write-forwarding-params). ## Transaction access modes with write forwarding If the transaction access mode is set to read only, local write forwarding isn't used. You can set the access mode to read write only while you’re connected to a DB cluster and session that has local write forwarding enabled. For more information on the transaction access modes, see [SET TRANSACTION](https://www.postgresql.org/docs/current/sql-set-transaction.html). # Working with local write forwarding for Aurora PostgreSQL Using the following sections, you can check if a database cluster has local write forwarding enabled, view compatibility considerations, and see configurable parameters and authentication setup. This information equips you with the details to utilize local write forwarding feature in Aurora PostgreSQL effectively. **Note** When a writer instance in a cluster using local write forwarding is restarted, any active, forwarded transactions and queries on reader instances using local write forwarding are automatically closed. After the writer instance is available again, you can retry these transactions. ## Checking if a DB cluster has local write forwarding enabled To determine that you can use local write forwarding in a DB cluster, confirm that the cluster has the attribute `LocalWriteForwardingStatus` set to `enabled`. In the AWS Management Console, on the **Configuration** tab of the details page for the cluster, you see the status **Enabled** for **Local read replica write forwarding**. To see the status of the local write forwarding setting for all of your clusters, run the following AWS CLI command. **Example** ``` aws rds describe-db-clusters \ --query '*[].{DBClusterIdentifier:DBClusterIdentifier,LocalWriteForwardingStatus:LocalWriteForwardingStatus}' [ { "LocalWriteForwardingStatus": "enabled", "DBClusterIdentifier": "write-forwarding-test-cluster-1" }, { "LocalWriteForwardingStatus": "disabled", "DBClusterIdentifier": "write-forwarding-test-cluster-2" }, { "LocalWriteForwardingStatus": "requested", "DBClusterIdentifier": "test-global-cluster-2" }, { "LocalWriteForwardingStatus": "null", "DBClusterIdentifier": "aurora-postgresql-v2-cluster" } ] ``` A DB cluster can have the following values for `LocalWriteForwardingStatus`: + `disabled` – Local write forwarding is disabled. + `disabling` – Local write forwarding is in the process of being disabled. + `enabled` – Local write forwarding is enabled. + `enabling` – Local write forwarding is in the process of being enabled. + `null` – Local write forwarding isn't available for this DB cluster. + `requested` – Local write forwarding has been requested, but is not yet active. ## Default parameter settings for write forwarding The Aurora cluster parameter groups include settings for the local write forwarding feature. Because these are cluster parameters, all DB instances in each cluster have the same values for these variables. Details about these parameters are summarized in the following table, with usage notes after the table. | Parameter | Scope | Type | Default value | Valid values | | --- | --- | --- | --- | --- | | apg\$1write\$1forward.connect\$1timeout | Session | seconds | 30 | 0–2147483647 | | apg\$1write\$1forward.consistency\$1mode | Session | enum | Session | SESSION, EVENTUAL, GLOBAL, and OFF | | apg\$1write\$1forward.idle\$1in\$1transaction\$1session\$1timeout | Session | milliseconds | 86400000 | 0–2147483647 | | apg\$1write\$1forward.idle\$1session\$1timeout | Session | milliseconds | 300000 | 0–2147483647 | | apg\$1write\$1forward.max\$1forwarding\$1connections\$1percent | Global | int | 25 | 1–100 | The `apg_write_forward.max_forwarding_connections_percent` parameter is the upper limit on database connection slots that can be used to handle queries forwarded from readers. It is expressed as a percentage of the `max_connections` setting for the writer DB instance. For example, if `max_connections` is `800` and `apg_write_forward.max_forwarding_connections_percent` is `10`, then the writer allows a maximum of 80 simultaneous forwarded sessions. These connections come from the same connection pool managed by the `max_connections` setting. This setting applies only on the writer DB instance when the cluster has local write forwarding enabled. Use the following settings to control local write forwarding requests: + `apg_write_forward.consistency_mode` – A session-level parameter that controls the degree of read consistency on a read replica. Valid values are `SESSION`, `EVENTUAL`, `GLOBAL`, or `OFF`. By default, the value is set to `SESSION`. Setting the value to `OFF` disables local write forwarding in the session. To learn more about consistency levels, see [Consistency and isolation for local write forwarding in Aurora PostgreSQL](aurora-postgresql-write-forwarding-configuring.md#aurora-postgresql-write-forwarding-isolation). This parameter is relevant only in reader instances that have local write forwarding enabled. + `apg_write_forward.connect_timeout` – The maximum number of seconds the read replica waits when establishing a connection to the writer DB instance before giving up. A value of `0` means to wait indefinitely. + `apg_write_forward.idle_in_transaction_session_timeout` – The number of milliseconds the writer DB instance waits for activity on a connection that's forwarded from a read replica that has an open transaction before closing it. If the session remains idle in transaction beyond this period, Aurora terminates the session. A value of `0` disables the timeout. + `apg_write_forward.idle_session_timeout` – The number of milliseconds the writer DB instance waits for activity on a connection that's forwarded from a read replica before closing it. If the session remains idle beyond this period, Aurora terminates the session. A value of `0` disables the timeout. ## rdswriteforwarduser The `rdswriteforwarduser` is a user that we will use to establish a connection between the read replica and the writer DB instance. **Note** `rdswriteforwarduser` inherits its CONNECT privileges to customer databases via the PUBLIC role. If the privileges for the PUBLIC role are revoked, you will need to GRANT CONNECT privileges for the databases you need to forward writes to. # Monitoring local write forwarding in Aurora PostgreSQL Using the following sections you can monitor local write forwarding in Aurora PostgreSQL clusters, including relevant CloudWatch metrics and wait events to track performance and identify potential issues. ## Amazon CloudWatch metrics and Aurora PostgreSQL status variables for write forwarding The following Amazon CloudWatch metrics apply to the writer DB instances when you use write forwarding on one or more read replicas. | CloudWatch Metric | Units and description | | --- | --- | | `AuroraLocalForwardingWriterDMLThroughput` | Count (per second). Number of forwarded DML statements processed each second by this writer DB instance. | | `AuroraLocalForwardingWriterOpenSessions` | Count. Number of open sessions on this writer DB instance processing forwarded queries. | | `AuroraLocalForwardingWriterTotalSessions` | Count. Total number of forwarded sessions on this writer DB instance. | The following CloudWatch metrics apply to each read replica. These metrics are measured on each reader DB instance in the DB cluster with local write forwarding enabled. | CloudWatch Metric | Unit and description | | --- | --- | | `AuroraForwardingReplicaCommitThroughput` | Count (per second). Number of commits in sessions forwarded by this replica each second. | | `AuroraForwardingReplicaDMLLatency` | Milliseconds. Average response time in milliseconds of forwarded DMLs on replica. | | `AuroraForwardingReplicaDMLThroughput` | Count (per second). Number of forwarded DML statements processed on this replica each second. | | `AuroraForwardingReplicaErrorSessionsLimit` | Count. Number of sessions rejected by the writer DB instance because the limit for max connections or max write forward connections was reached. | | `AuroraForwardingReplicaOpenSessions` | Count. The number of sessions that are using local write forwarding on a replica instance. | | `AuroraForwardingReplicaReadWaitLatency` | Milliseconds. Average wait time in milliseconds that the replica waits to be consistent with the LSN of the writer DB instance. The degree to which the reader DB instance waits depends on the apg\$1write\$1forward.consistency\$1mode setting. For information about this setting, see [Configuration parameters for write forwarding in Aurora PostgreSQL](aurora-global-database-write-forwarding-apg.md#aurora-global-database-write-forwarding-params-apg). | ## Wait events for local write forwarding in Aurora PostgreSQL Amazon Aurora generates the following wait events when you use write forwarding with Aurora PostgreSQL. **Topics** + [IPC:AuroraWriteForwardConnect](#apg-waits.ipcaurorawriteforwardconnect) + [IPC:AuroraWriteForwardConsistencyPoint](#apg-waits.ipcaurorawriteforwardconsistencypoint) + [IPC:AuroraWriteForwardExecute](#apg-waits.ipc:aurorawriteforwardexecute) + [IPC:AuroraWriteForwardGetGlobalConsistencyPoint](#apg-waits.ipc:aurorawriteforwardgetglobalconsistencypoint) + [IPC:AuroraWriteForwardXactAbort](#apg-waits.ipc:aurorawriteforwardxactabort) + [IPC:AuroraWriteForwardXactCommit](#apg-waits.ipc:aurorawriteforwardxactcommit) + [IPC:AuroraWriteForwardXactStart](#apg-waits.ipc:aurorawriteforwardxactstart) ### IPC:AuroraWriteForwardConnect The `IPC:AuroraWriteForwardConnect` event occurs when a backend process on the read replica is waiting for a connection to the writer DB instance to be opened. **Likely causes of increased waits** This event increases as the number of connection attempts from a read replica to the writer node increases. **Actions** Reduce the number of simultaneous connections from a read replica to the writer node. ### IPC:AuroraWriteForwardConsistencyPoint The `IPC:AuroraWriteForwardConsistencyPoint` event describes how long a query from a node on the read replica will wait for the results of forwarded write operations to be replicated to the current Region. This event is only generated if the session-level parameter `apg_write_forward.consistency_mode` is set to one of the following: + `SESSION` – queries on a read replica wait for the results of all changes made in that session. + `GLOBAL` – queries on a read replica wait for the results of changes made by that session, plus all committed changes from both the writer DB instance and read replica. For more information about the `apg_write_forward.consistency_mode` parameter settings, see [Configuration parameters for write forwarding in Aurora PostgreSQL](aurora-global-database-write-forwarding-apg.md#aurora-global-database-write-forwarding-params-apg). **Likely causes of increased waits** Common causes for longer wait times include the following: + Increased replica lag, as measured by the Amazon CloudWatch `ReplicaLag` metric. For more information about this metric, see [Monitoring Aurora PostgreSQL replication](AuroraPostgreSQL.Replication.md#AuroraPostgreSQL.Replication.Monitoring). + Increased load on the writer DB instance or read replica. **Actions** Change your consistency mode, depending on your application's requirements. ### IPC:AuroraWriteForwardExecute The `IPC:AuroraWriteForwardExecute` event occurs when a backend process on the read replica is waiting for a forwarded query to complete and obtain results from the writer node of the DB cluster. **Likely causes of increased waits** Common causes for increased waits include the following: + Fetching a large number of rows from the writer node. + Increased network latency between the writer node and read replica increases the time it takes the read replica to receive data from the writer node. + Increased load on the read replica can delay transmission of the query request from the read replica to the writer node. + Increased load on the writer node can delay transmission of data from the writer node to the read replica. **Actions** We recommend different actions depending on the causes of your wait event. + Optimize queries to retrieve only necessary data. + Optimize data manipulation language (DML) operations to only modify necessary data. + If the read replica or writer node is constrained by CPU or network bandwidth, consider changing it to an instance type with more CPU capacity or more network bandwidth. ### IPC:AuroraWriteForwardGetGlobalConsistencyPoint The `IPC:AuroraWriteForwardGetGlobalConsistencyPoint` event occurs when a backend process on the read replica that's using the GLOBAL consistency mode is waiting to obtain the global consistency point from the writer node before executing a query. **Likely causes of increased waits** Common causes for increased waits include the following: + Increased network latency between the read replica and writer node increases the time it takes the read replica to receive data from the writer node. + Increased load on the read replica can delay transmission of the query request from the read replica to the writer node. + Increased load on the writer node can delay transmission of data from the writer node to the read replica. **Actions** We recommend different actions depending on the causes of your wait event. + Change your consistency mode, depending on your application's requirements. + If the read replica or writer node is constrained by CPU or network bandwidth, consider changing it to an instance type with more CPU capacity or more network bandwidth. ### IPC:AuroraWriteForwardXactAbort The `IPC:AuroraWriteForwardXactAbort` event occurs when a backend process on the read replica is waiting for the result of a remote cleanup query. Cleanup queries are issued to return the process to the appropriate state after a write-forwarded transaction is aborted. Amazon Aurora performs them either because an error was found or because an user issued an explicit `ABORT` command or cancelled a running query. **Likely causes of increased waits** Common causes for increased waits include the following: + Increased network latency between the read replica and writer node increases the time it takes the read replica to receive data from the writer node. + Increased load on the read replica can delay transmission of the cleanup query request from the read replica to the writer node. + Increased load on the writer node can delay transmission of data from the writer node to the read replica. **Actions** We recommend different actions depending on the causes of your wait event. + Investigate the cause of the aborted transaction. + If the read replica or writer DB instance is constrained by CPU or network bandwidth, consider changing it to an instance type with more CPU capacity or more network bandwidth. ### IPC:AuroraWriteForwardXactCommit The `IPC:AuroraWriteForwardXactCommit` event occurs when a backend process on the read replica is waiting for the result of a forwarded commit transaction command. **Likely causes of increased waits** Common causes for increased waits include the following: + Increased network latency between the read replica and writer node increases the time it takes the read replica to receive data from the writer node. + Increased load on the read replica can delay transmission of the query request from the read replica to the writer node. + Increased load on the writer node can delay transmission of data from the writer node to the read replica. **Actions** If the read replica or writer node is constrained by CPU or network bandwidth, consider changing it to an instance type with more CPU capacity or more network bandwidth. ### IPC:AuroraWriteForwardXactStart The `IPC:AuroraWriteForwardXactStart` event occurs when a backend process on the read replica is waiting for the result of a forwarded start transaction command. **Likely causes of increased waits** Common causes for increased waits include the following: + Increased network latency between the read replica and writer node increases the time it takes the read replica to receive data from the writer node. + Increased load on the read replica can delay transmission of the query request from the read replica to the writer node. + Increased load on the writer node can delay transmission of data from the writer node to the read replica. **Actions** If the read replica or writer node is constrained by CPU or network bandwidth, consider changing it to an instance type with more CPU capacity or more network bandwidth.