logical replication Archives

Tech Optimizer

June 19, 2026

Looking Ahead to Postgres 19

Postgres has introduced new functionalities, including UPDATE and DELETE FOR PORTION OF, enhancing temporal use cases. The expansion of RANDOM() temporal functions is attributed to Paul Ramsey and Greg Sabino Mullane. Version 19 includes performance improvements in the planner and executor components, with contributions from Tom Lane. Key enhancements include refinements in anti-joins and semi-joins, constant folding optimizations, incremental sorting with append paths, enhanced aggregate processing prior to joins, improved join selectivity computation, and more comprehensive function statistics. These changes allow Postgres to better understand query structures, reducing unnecessary processing. The visibility of memoization in EXPLAIN has improved, sort performance has benefited from radix sort, and foreign key constraint checks have become faster. The COPY FROM command can now utilize SIMD instructions. Postgres 19 offers a range of improvements for application developers, operators, performance enthusiasts, and those building on Postgres, including enhanced graph queries, refined SQL syntax, improved window functions, better upsert behavior, REPACK CONCURRENTLY, advancements in autovacuum, improved monitoring capabilities, and new hooks. The release is still in beta, providing an opportunity for testing applications, migration, extensions, execution plans, and maintenance workflows.

Tech Optimizer

June 7, 2026

Implementing real-time change data capture with Debezium for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL now support native streaming replication, allowing data changes to flow from source databases. Organizations often struggle to capture and propagate these changes in real time without affecting database performance. Traditional ETL pipelines can introduce significant delays, leading to outdated data and missed opportunities. Debezium is an open-source platform for change data capture (CDC) that monitors databases and streams changes to applications or data pipelines, enabling real-time data synchronization. The CDC solution combines PostgreSQL's logical replication with Debezium's change capture framework, focusing on Amazon Aurora for PostgreSQL. To implement this solution, logical replication must be enabled on Amazon Aurora through DB cluster parameter groups. Debezium connectors monitor the database's Write-Ahead Logging (WAL) via logical replication slots, converting transaction logs into structured event streams. Key components of the solution include: - Amazon Aurora for PostgreSQL as the source database with logical replication enabled. - A Debezium PostgreSQL connector running on MSK Connect for change capture. - Amazon MSK for message streaming. - An Amazon EC2 instance for testing and consuming change events. Steps to implement the solution include: 1. Creating an Amazon Aurora for PostgreSQL DB cluster and enabling logical replication. 2. Creating an Amazon MSK serverless cluster. 3. Setting up an Amazon EC2 instance to install Kafka and connect to the database. 4. Creating a custom plugin for Amazon MSK to replicate changes from RDS for PostgreSQL. 5. Uploading the custom plugin to an Amazon S3 bucket. 6. Storing credentials in AWS Secrets Manager and creating an IAM role for MSK Connect. 7. Testing the solution by verifying data replication and monitoring real-time changes. Monitoring can be done through CloudWatch Logs and the MSK Connect console, addressing common issues like replication slot lag and connector failures. To avoid charges, resources created during the implementation should be deleted in the correct order.

Tech Optimizer

June 6, 2026

Implementing real-time change data capture with Debezium for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL support native streaming replication, allowing data changes to flow from source databases. Organizations often struggle with real-time data capture and dissemination, leading to outdated information due to traditional batch ETL pipelines. Debezium is an open-source platform for change data capture (CDC) that streams database changes to applications or data pipelines in real time. The CDC solution utilizes PostgreSQL's logical replication capabilities and Debezium's framework. Key components include Amazon Aurora for PostgreSQL as the source database, a Debezium PostgreSQL connector on MSK Connect, Amazon MSK for message streaming, and an Amazon EC2 instance for testing. To implement the solution, users must enable logical replication on Amazon Aurora, create an Amazon MSK cluster, set up an EC2 instance with Kafka, and create a custom plugin for MSK Connect. The process involves configuring IAM roles and policies, connecting to the database, and creating an Amazon MSK connector to stream changes. Testing the solution includes verifying real-time changes by inserting, updating, and deleting records in PostgreSQL, with events appearing in Kafka. Monitoring tools are available through CloudWatch for troubleshooting issues like replication slot lag and connector failures. To clean up resources after testing, users should delete the MSK Connect connector, custom plugin, Amazon MSK cluster, Aurora PostgreSQL database instance, EC2 instance, IAM role and policy, Secrets Manager secrets, CloudWatch Logs log group, and S3 bucket.

Tech Optimizer

June 6, 2026

PostgreSQL vs SQL Server 2026: $0 vs $15K License

PostgreSQL has become the most utilized database for developers for two consecutive years as of 2026, while SQL Server has introduced native AI capabilities and vector search functionalities in its latest release. PostgreSQL is favored for new projects due to its permissive licensing, cross-platform operation, and extensive ecosystem of extensions, whereas SQL Server is preferred by organizations already using Microsoft products for its robust tooling and enterprise support. As of June 2026, SQL Server ranks third in the DB-Engines popularity ranking with a score of 698, while PostgreSQL ranks fourth with a score of 688. Developer adoption trends show that 49% of respondents in the Stack Overflow 2024 Developer Survey use PostgreSQL, compared to 27% for SQL Server. The latest stable version of PostgreSQL is 18.4, released on May 14, 2026, while SQL Server 2025 (version 17.x) became generally available on November 18, 2025. PostgreSQL is free under a permissive license, while SQL Server's Enterprise Edition can cost upwards of ,123 per two-core pack, with an eight-core minimum. PostgreSQL supports various operating systems, including Linux, Windows, macOS, and BSD, while SQL Server operates on Windows and Linux. PostgreSQL uses PL/pgSQL as its primary procedural language, allowing for multiple procedural languages, whereas SQL Server uses T-SQL. PostgreSQL's JSONB type offers rich indexing, while SQL Server added native JSON support in its 2025 release. Both databases are capable of handling high transaction volumes, but PostgreSQL excels in read-heavy scenarios due to its multi-version concurrency control, while SQL Server's columnstore indexes provide advantages for analytical workloads. PostgreSQL supports tables up to 32 TB, while SQL Server can handle a maximum database size of 524 PB. SQL Server's high availability technology includes Always On Availability Groups, while PostgreSQL offers built-in streaming and logical replication. The security features of both databases meet enterprise standards, with PostgreSQL providing a transparent patching process and SQL Server integrating with Microsoft tools. Real-world usage examples include Stack Overflow using SQL Server, while Instagram and Reddit utilize PostgreSQL. Developer sentiment increasingly favors PostgreSQL for new projects, emphasizing flexibility and open-source solutions. For new startups or greenfield projects, PostgreSQL is recommended due to its zero license cost and compatibility. Existing Microsoft enterprises are advised to stick with SQL Server for its integration benefits. Migration from SQL Server to PostgreSQL is becoming common, with a structured approach required for successful transitions.

Tech Optimizer

May 21, 2026

PostgreSQL Flaws Expose Databases to Remote Code Execution and SQL Injection

PostgreSQL has released versions 18.4, 17.10, 16.14, 15.18, and 14.23 to address 11 security vulnerabilities and over 60 bugs. The vulnerabilities affect PostgreSQL versions 14 through 18 and include issues such as remote code execution, SQL injection, and denial-of-service risks. Specific vulnerabilities include: - CVE-2026-6472: Missing authorization in CREATE TYPE allows query hijacking. - CVE-2026-6473: Integer wraparound leads to out-of-bounds writes and server crashes. - CVE-2026-6474: Format string issue leaks server memory. - CVE-2026-6475: Symlink attack allows overwriting arbitrary files. - CVE-2026-6476: SQL injection allows execution of arbitrary SQL as superuser. - CVE-2026-6477: Memory buffer overwrite via libpq lo_* functions. - CVE-2026-6478: Timing attack exposes MD5-hashed passwords. - CVE-2026-6479: SSL/GSS recursion flaw allows denial-of-service. - CVE-2026-6575: Buffer over-read leaks memory data (PostgreSQL 18 only). - CVE-2026-6637: Refint module enables stack overflow and SQL injection, leading to possible RCE. - CVE-2026-6638: SQL injection in REFRESH PUBLICATION via table names. Organizations are advised to upgrade to the latest versions, avoid MD5 password authentication, restrict privileges, audit extensions, and monitor for abnormal activity. PostgreSQL 14 will reach its end-of-life on November 12, 2026.

Tech Optimizer

May 20, 2026

Critical PostgreSQL Flaws Enable Code Execution and SQL Injection

On May 14, 2026, PostgreSQL released critical security updates for versions 18.4, 17.10, 16.14, 15.18, and 14.23, addressing 11 Common Vulnerabilities and Exposures (CVEs) related to stack buffer overflows, SQL injection, memory disclosure, and denial-of-service vulnerabilities. The most critical vulnerability, CVE-2026-6637, has a CVSS score of 8.8 and allows remote, unprivileged database users to execute arbitrary code. Other notable vulnerabilities include CVE-2026-6473, which affects memory allocation leading to potential memory corruption, and CVE-2026-6475, which allows overwriting sensitive OS-level files. Additional vulnerabilities include SQL injection flaws and authentication issues, with various CVSS scores ranging from 3.7 to 7.5. The updates can be implemented without a database dump, and PostgreSQL 14 will reach its end-of-life on November 12, 2026.

Tech Optimizer

May 19, 2026

Critical PostgreSQL Vulnerabilities Enables Code Execution and SQL Injections

The PostgreSQL Global Development Group has released security updates for versions 18.4, 17.10, 16.14, 15.18, and 14.23, addressing 11 vulnerabilities, including critical issues related to arbitrary code execution and SQL injection flaws. All supported branches from 14 through 18 are affected by vulnerabilities, necessitating updates. Database administrators can perform in-place upgrades without needing a dump/restore. Key vulnerabilities include: - CVE-2026-6637: Stack buffer overflow in the refint module allowing arbitrary code execution. - CVE-2026-6472: Privilege bypass and arbitrary SQL execution. - CVE-2026-6473: Potential remote code execution and memory corruption. - CVE-2026-6474: Server memory information leak. - CVE-2026-6475: Arbitrary file overwrite vulnerability. - CVE-2026-6476: SQL injection with superuser execution. - CVE-2026-6477: Client-side code execution risk. - CVE-2026-6478: MD5 credential timing leak. - CVE-2026-6479: SSL/GSS denial-of-service flaw. - CVE-2026-6575: Limited memory disclosure issue. - CVE-2026-6638: SQL injection in logical replication. Organizations using PostgreSQL 14 should upgrade to version 14.23 and plan migration to a newer version before the end-of-life on November 12, 2026. The updates are urgent for deployments exposed to the internet or in multi-tenant environments.

Tech Optimizer

May 18, 2026

Google Pushes AI for PostgreSQL Development

Google is integrating artificial intelligence into PostgreSQL engineering while ensuring engineers remain responsible for their code contributions. This initiative aims to improve productivity and streamline processes, particularly in version upgrades, replication behavior, and production recovery. Sailesh Krishnamurthy, Google Cloud's VP of Databases, emphasized the importance of human oversight in this AI-driven approach. Between July and December 2025, Google's PostgreSQL engineering focused on logical replication, improvements to pg_upgrade, and upstream bug fixes. Logical replication allows selective database change transfers, which is beneficial for migrations and upgrades. The roadmap includes Automatic Conflict Detection and logical replication of sequences to minimize manual synchronization issues. Enterprise teams are particularly interested in these developments due to the challenges posed by write collisions and misaligned sequence values. The demand for PostgreSQL services at Google is increasing due to migration needs from Oracle and SQL Server. Recent data shows PostgreSQL's rising popularity, ranking fourth in the DB-Engines ranking and gaining 8.37 points year over year, while its competitors experienced declines.

Tech Optimizer

May 6, 2026

How Aurora Blue-Green Upgrades Cut Our Postgres Downtime by 50X

Paxos has implemented Blue-Green upgrades to improve database maintenance for asset trading, achieving a 50-fold reduction in downtime from 30-120 minutes to approximately one minute. This upgrade strategy allows for real-time synchronization between a blue cluster handling production traffic and a new green cluster with the updated version. Challenges included issues with CREATE ROLE statements disrupting upgrades due to temporary roles and the necessity to drop replication slots before PostgreSQL 17, which could lead to data loss. Additionally, upon the green cluster becoming primary, it acquires a new cluster ID, requiring updates for clients using RDS IAM authentication. The transition to PostgreSQL 17 is anticipated to resolve the need to drop logical replication slots, preserving Change Data Capture (CDC) continuity.

Tech Optimizer

April 11, 2026

Google Cloud Highlights Ongoing Work on PostgreSQL Core Capabilities

Google Cloud has made technical contributions to PostgreSQL, focusing on advancements in logical replication, upgrade processes, and system stability. Key developments include the evolution of logical replication towards active-active configurations with automatic conflict detection to identify row-level conflicts during replication. This progress has sparked discussions about consistency models in database systems. Enhancements have also expanded logical replication to include sequences, reducing manual synchronization needs. Improvements to pg_upgrade have streamlined large object management and reduced upgrade times, while ensuring WAL data retention and schema constraint preservation. Bug fixes have addressed issues with index pages, extension loading, and WAL flush logic. Future features under development include a structured conflict log for replication and enhancements to parallel data export in pg_dump.