PostgreSQL 17 replication performance and logic

I conclude that PostgreSQL 17 provides superior flexibility for complex workloads through its updated vacuuming and logical replication features. PostgreSQL 17 introduces a new internal memory structure for vacuum called TidStore. This structure uses a radix tree to store tuple IDs. This optimization allows the process to use up to 20 times less memory than previous versions. This change also removes the 1GB limit that existed for the TidStore array. The updated vacuum process makes scanning dead tuples faster. The release also replaces the max_dead_tuples and num_dead_tuples columns in the pg_stat_progress_vacuum view with max_dead_tuple_bytes and dead_tuple_bytes. This change reflects the move toward tracking vacuum progress in bytes rather than in tuples. PostgreSQL 17 also combines WAL records when rows are frozen during vacuuming. This reduces the total size of the WAL, which helps the server and its replicas. The release also improves logical replication by removing the requirement to drop logical replication slots during major engine version upgrades. This change simplifies the management of high-availability workloads.

The Mechanics of Data Distribution

PostgreSQL provides two primary methods for data distribution: logical replication and streaming replication. Streaming replication sends Write-Ahead Log (WAL) records from a primary server to a replica. This method uses a filesystem-level block replication approach. It replicates all changes to every database object, which makes it an all-or-nothing solution. Because it uses WAL, streaming replication provides high performance and prevents data loss if the primary and receiving servers do not crash at the same time. This method can capture changes at up to one terabyte per second. Engineers use streaming replication to create backups or to distribute database workloads across several machines for read-only formats.

Logical replication provides a different approach by copying data at the SQL level. This method uses a publish and subscribe model. Users can replicate specific tables, rows, or columns. This provides more control over what data moves between servers. Engineers use logical replication to merge multiple databases into one central database for analytics or to distribute database subsets between multiple databases. However, logical replication fails to copy sequences, large objects, materialized views, partition root tables, or foreign tables. It only supports Data Manipulation Language (DML) operations. Because logical replication only supports Data Manipulation Language operations and lacks support for DDL or schema changes, database teams must manually define the target schema before they attempt to replicate data between different versions of PostgreSQL.

Amazon RDS provides the pgactive extension for PostgreSQL 15.4-R2 and higher to support active-active replication. This extension allows up to 16 writer instances to accept changes. It uses asynchronous replication to distribute data between database instances. The system uses conflict resolution strategies like last update wins, first update wins, or customizable rules.

Comparing PostgreSQL with MariaDB and MySQL

Teams choosing between PostgreSQL and MariaDB must evaluate their architectural requirements. MariaDB maintains compatibility with MySQL and supports various storage engines. MariaDB is maintained by the MariaDB Foundation and MariaDB Corporation. PostgreSQL uses a process-per-connection model, whereas MySQL uses a thread-based architecture. PostgreSQL uses heap storage, but MySQL InnoDB organizes rows around a clustered primary key index. PostgreSQL relies on VACUUM for cleanup, while InnoDB maintains older versions through undo logs.

PostgreSQL includes JSON_TABLE(), which allows users to query JSON data and present the results as a relational view. This functionality matches the JSON_TABLE() option in MySQL. PostgreSQL also includes the MERGE command, which allows developers to perform conditional updates, inserts, or deletes in one SQL statement. The MERGE command in version 17 now allows the target to be a view. You already know that schema management differs between these systems. Will the increased performance of the TidStore structure satisfy teams with massive vacuuming requirements? PostgreSQL also provides the PostGIS extension for geospatial data.