AWS recently announced the general availability of pgactive on RDS for PostgreSQL. The replication extension for PostgreSQL supports asynchronous active-active replication for streaming data between database instances, enhancing resiliency and flexibility.
Based on the open-source BDR project, pgactive provides node synchronization, replication lag metrics, and different automatic conflict resolution strategies: last update wins, first update wins, or customizable rules.
Supporting up to 16 writer instances, asynchronous active-active replication allows applications designed for eventual consistency to write to two or more RDS instances, maintaining availability throughout different database operations and reducing write latency for workloads distributed in multiple regions. Jonathan Katz, principal product manager - technical at AWS, explains:
A fundamental component of active-active replication is logical replication. Logical replication uses a data format that allows external systems to interpret changes before applying them to a target database. This lets the target system perform additional actions, such as detecting and resolving write conflicts or converting the statement into something that is supported in the target database software.
By default, the extension logs all conflicts and uses the last-update-wins strategy, accepting the changes from the transaction with the latest timestamp. Aakash Muthuramalingam, database reliability engineer at Mydbops, comments:
AWS has just unveiled a game-changing feature for PostgreSQL users (...) This innovation introduces a groundbreaking approach to database replication, allowing you to achieve unparalleled levels of performance, resilience, and scalability.
In an asynchronous active-active replication deployment, the cluster doesn’t have a single source of truth as multiple databases can accept changes and replicate them to other nodes. Katz warns:
pgactive is not a drop-in solution for all applications. Applications that use pgactive for an active-active database cluster must make specific design decisions to ensure they can operate safely. Even if you are routing write traffic to a single pgactive instance, you must make sure your application is architected to support an active-active replication topology.
In the last year, RDS for PostgreSQL introduced support for other extensions, including pgvector, plrust, h3-pg, hypopg, tcn, and seg, with new updates for pg_tle to handle custom datatypes. While Amazon recently highlighted their team's contributions to open-source databases, the community needs more convincing and some developers question if pgactive will be open-sourced.
The extension is available on database instances running PostgreSQL 15.4-R2 and higher in all AWS regions. To enable the feature, the rds.enable_pgactive parameter should be set to 1 in the DB parameter group. Creating the extension in the database automatically enables rds.logical_replication and track_commit_timestamp, and sets wal_level to logical.