Bug: Aurora PostgreSQL does not perform logical decoding when synchronous_commit = off

Question

When PostgreSQL is configured with `logical_replication = 1` and `synchronous_commit = off`, we see differing behavior between Aurora PostgreSQL and locally run PostgreSQL on Ubuntu. With locally run PostgreSQL, the following commands will return transaction data successfully:
```
select * from pg_create_logical_replication_slot('test', 'test_decoding', false, false);
create table planet (name text);
insert into planet (name) values ('earth');
select * from pg_logical_slot_peek_changes('test', null, null);
```
```
 lsn | xid | data
-----+-----+------
 0/4003188 | 8278 | BEGIN 8278
 0/4003188 | 8278 | table public.planet: INSERT: name[text]:'earth'
 0/40031F8 | 8278 | COMMIT 8278
(3 rows)
```
But with Aurora PostgreSQL, no transaction data shows up:
```
 lsn | xid | data
-----+-----+------
(0 rows)
```
According to the official PostgreSQL docs, the transaction should have been flushed and available in under 600 ms. However, with Aurora PostgreSQL we've seen it take hours, or never show up to logical consumers at all.

This forces us to use `synchronous_commit = on` if we want to implement a functional CDC pipeline.

Accepted Answer

Hi,

Our blog post discusses this, a link below is provided. [1]

> If you disable synchronous_commit, PostgreSQL doesn’t try to ensure durability of every transaction. Instead, PostgreSQL writes committed transactions to the WAL file in groups. This can offer performance benefits at the risk of losing transactions. You should be very careful when changing this parameter from its default value. Disabling synchronous_commit compromises durability of your DB instance and can lead to data loss. Aurora PostgreSQL uses a log-based storage engine to persist all modifications. Every commit is sent to six copies of data; after it’s confirmed by a quorum of four, Aurora PostgreSQL can acknowledge the commit back to client. If you disable synchronous_commit, every commit requested by client doesn’t wait for the four out of six quorum. Ideally, you shouldn’t disable this parameter because that compromises the durability benefits offered by Aurora PostgreSQL.

This is something that is being reviewed with regards to logical replication, but at this time Aurora Postgres greatly depends on `synchronous_commit = on` in order to protect the integrity of the data.

References:
[1] https://aws.amazon.com/blogs/database/amazon-aurora-postgresql-parameters-part-2-replication-security-and-logging/

Answer

We found that switching `synchronous_commit` back `on` did not impact our job queue throughput, so all is well. :)

Answer

Thank you for the explanation. We will discuss and benchmark `synchronous_commit = on`.

Answer

In PostgreSQL, including RDS PostgreSQL, disabling `synchronous_commit` means committing transactions without ensuring durability for every transaction, potentially improving performance but risking data loss. In Aurora PostgreSQL, disabling this parameter doesn't wait for the four out of six quorum confirmations, compromising durability benefits. Aurora PostgreSQL employs a log-based storage engine, confirming commits after a quorum of four copies, ensuring durability.

Disabling the synchronous_commit parameter in Aurora PostgreSQL is not recommended because it compromises the durability benefits provided by Aurora's log-based storage engine. When synchronous_commit is disabled, commits requested by the client won't wait for the required quorum of four out of six copies to confirm, increasing the risk of data loss. Aurora PostgreSQL relies on synchronous commit to ensure the integrity and durability of transactions, making it crucial not to disable this parameter for the safety of your database instance.

References:
 =============

[+] https://aws.amazon.com/blogs/database/amazon-aurora-postgresql-parameters-part-2-replication-security-and-logging/

Bug: Aurora PostgreSQL does not perform logical decoding when synchronous_commit = off

References:

関連するコンテンツ