logical replication Archives

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.

Tech Optimizer

April 8, 2026

PostgreSQL logical replication: How to replicate only the data that you need

Many organizations using PostgreSQL face challenges in replicating data across different logical databases while controlling shared information. PostgreSQL 15 introduces native logical replication with column-level and row-level filtering within publications, allowing precise control over data transfer between PostgreSQL databases. Logical replication operates on a publish-subscribe model, where the source database logs changes to the Write-Ahead Log (WAL) and applies publication filters to transmit only the relevant data to the target database. Two filtering modes are available: column-level filtering allows users to specify which columns to replicate, while row-level filtering uses WHERE clauses to determine which rows to include. Both filtering modes can be combined in a single publication. The SQL syntax for creating publications and subscriptions is consistent across various PostgreSQL environments, with specific configurations required for enabling logical replication. Use cases for logical replication include multi-tenant SaaS data isolation, regional data distribution for ecommerce, PCI DSS compliance in financial services, development and testing environments, and retail inventory management. Prerequisites for implementing logical replication include using PostgreSQL 15 or later, ensuring network connectivity, having a user with replication privileges, enabling logical replication on the source instance, and monitoring WAL retention. Logical replication has limitations, such as the need for a replica identity for UPDATE and DELETE operations, the non-replication of DDL changes and sequences, and potential performance overhead during initial synchronization and ongoing replication. After testing, it is advisable to clean up by removing replication resources to prevent unnecessary costs.

Tech Optimizer

March 29, 2026

PostgreSQL cost model: estimating the cost of flushing temp buffers to disk

The project focuses on enabling temporary tables in PostgreSQL's parallel query execution by benchmarking sequential writes and reads of temporary buffers. Functions were introduced to measure buffer flush operations, and tests were conducted to estimate the cost of flushing temporary table buffer pages to disk. Findings indicate that sequential writes are about 30% slower than reads, leading to a proposed cost model. Temporary tables have limited parallel usage due to their function as relational variables and the complexity of parallel workers accessing the leader process's local state. Two enterprise PostgreSQL distributions already support parallel operations on temporary tables, and discussions on logical replication of DDL highlight the need for improved tooling for temporary tables. A significant challenge is that temporary buffer pages are local to the leader process, and if not flushed to disk, parallel workers cannot access them. A comment from 2015 suggests flushing the leader's temporary buffers to disk before parallel operations to allow workers to scan the table in parallel. However, this raises concerns about the cost of writing buffers versus the benefits of parallelism. The benchmarking toolkit includes modifications to PostgreSQL to facilitate measurement of local buffers. Two key commits added statistics infrastructure to track allocated and dirty local buffers and UI functions to manage buffer operations. The methodology involves creating a temporary table, flushing buffers, measuring I/O, and evaluating scan overhead. The tests reveal that the cost of writing a temporary table approximates the cost of a sequential page, leading to a proposed formula for flushing costs. The results show that larger buffer sizes lead to higher write overhead and increased variability. A comprehensive formula for pre-flushing temp buffers is suggested, factoring in the number of dirtied and allocated local buffers. Next steps include adding planner flags for temporary objects, implementing executor support for worker access to temporary table storage, introducing a temp-buffer flush operation, and integrating the cost model into the planner for parallel scans of temporary tables.

Tech Optimizer

March 11, 2026

Netflix Automates RDS PostgreSQL to Aurora PostgreSQL Migration Across 400 Production Clusters

Netflix has developed an internal automation platform to migrate Amazon RDS for PostgreSQL databases to Amazon Aurora PostgreSQL, reducing operational risks and downtime for nearly 400 production clusters. The platform allows service teams to perform migrations through a self-service workflow while ensuring processes like replication validation and rollback safeguards are maintained. Database access is managed through a platform-managed layer using Envoy, which standardizes mutual TLS and abstracts database endpoints, enhancing security and efficiency. The migration process starts with creating an Aurora PostgreSQL cluster as a read replica of the source RDS instance, initialized from a storage snapshot and continuously replaying write-ahead log (WAL) records. Validation checks are performed to ensure the replica can handle peak write throughput before cutover. For change data capture workloads, the system coordinates the state of replication slots and pauses CDC consumers to prevent excessive WAL retention. The Enablement Applications team at Netflix successfully migrated databases for device certification and partner billing workflows, addressing issues like elevated replication lag due to inactive logical replication slots. As replication lag decreases, the system enters a controlled quiescence phase, adjusts security rules, and reboots the source RDS instance. Once all transactions are processed and the Aurora replica is ready, it is promoted to a writable cluster, and traffic is rerouted. Rollback capabilities are prioritized, allowing redirection back to the original RDS instance if validation checks fail or anomalies are detected post-promotion. This setup enables seamless restoration without redeployment, and CDC consumers can resume from recorded slot positions if needed.

Tech Optimizer

February 14, 2026

PostgreSQL vs MySQL: Which Database is Better in 2026?

PostgreSQL 17 introduces significant enhancements, including refined memory management in VACUUM processes, improved SQL/JSON capabilities with features like JSON_TABLE(), advancements in logical replication and parallel processing, and overall increased efficiency for handling complex queries and large datasets. MySQL, under Oracle's stewardship, focuses on performance, reliability, and cloud integration, with updates unveiled at the HeatWave Summit in 2025 that support both transactional and analytical workloads in a unified system, enabling real-time analytics without data transfer to separate tools.

Tech Optimizer

January 29, 2026

Aurora PostgreSQL 13 Deadline: Four Upgrade Paths to Beat February Cutoff

Standard support for Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL version 13 will end on February 28, 2026. PostgreSQL 13 will be deprecated by the community in November 2025, ceasing to receive bug fixes or security patches. AWS recommends upgrading to newer versions, such as 16 or 17, which offer significant performance enhancements and improved security. PostgreSQL 17 can achieve up to twice the write throughput and consumes 20 times less memory during vacuum operations. Version 16 introduces pg_stat_io for detailed I/O statistics, while version 14 includes a vacuum emergency mode. Aurora-specific enhancements in version 14.9 and later can lead to faster query latency and reduced costs. Version 14 introduces new roles for access control, and version 15 revokes certain permissions. Major upgrades in logical replication include automatic slot synchronization in version 17 and support for parallel apply in version 16. Transitioning between major versions requires careful examination of catalog changes, as some views and configuration parameters will evolve. Extensions must be verified, as most do not auto-upgrade. An in-place major version upgrade can be performed via the AWS Console or CLI, with downtime varying based on database size. AWS recommends snapshot-based testing beforehand. The CLI command can check valid upgrade targets, leading from version 13 to 14, 15, 16, or 17. Preparation involves validating instance classes and dropping replication slots. Amazon RDS Blue/Green deployments allow for near-zero downtime by synchronizing production with a staging environment, enabling application testing before traffic switching. This feature is supported from Aurora PostgreSQL version 13.12 onward. Logical replication through pglogical offers flexibility for minimal downtime, while AWS DMS supports homogeneous migration with Change Data Capture. Extended Support is available for a fee, providing up to three years of security patches. Best practices include replicating production environments in staging, conducting load tests, and validating queries against new catalogs. Recent minor releases, including Aurora PostgreSQL 17.6 and 16.10, showcase ongoing improvements. Engaging AWS Support is advisable for complex setups to ensure seamless transitions before the deadline.

Tech Optimizer

January 28, 2026

Strategies for upgrading Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL from version 13

Standard support for PostgreSQL version 13 will end on February 28, 2026. Upgrading to newer PostgreSQL versions can enhance database performance and introduce new features. Notable enhancements in recent versions include: - Vacuum emergency mode (v14+) to manage old row versions. - Improved I/O performance (v17) with better write throughput. - Enhanced query optimization (v17+) for IN clauses and parallel BRIN index builds. - Memory efficiency improvements (v17) reducing vacuum memory usage. - Advanced monitoring features like pg_stat_io (v16+) and pg_wait_events (v17+). - Logical replication improvements such as failover support and slot migration (v17+). - Developer experience enhancements including JSONB subscripting (v14+) and SQL/JSON JSON_TABLE (v17+). - Security enhancements with new roles for access control (v14+) and maintenance tasks (v17+). For Amazon Aurora PostgreSQL-Compatible, upgrading to versions v14.9+, v15.4+, v16.1+, and higher can yield performance optimizations, including faster query latency and cost savings. Changes in system catalog views and configuration parameters have occurred in PostgreSQL versions 14 to 17, impacting application compatibility. Upgrade strategies include in-place upgrades, blue/green deployments, logical replication, and using AWS Database Migration Service (AWS DMS). If an immediate upgrade is not possible, Amazon RDS Extended Support offers up to three years of continued security patches and bug fixes beyond the standard support end date.

Tech Optimizer

December 1, 2025

Risks of Manual PostgreSQL Starts in Patroni Clusters: Downtime Warnings

Patroni is an open-source tool for managing PostgreSQL clusters, automating failover and replication. Manual starting of PostgreSQL services within an active Patroni cluster can lead to severe disruptions, including data integrity issues and availability risks. Patroni uses a distributed consensus system, often with etcd or Consul, to manage cluster state and leader elections. Manual interventions can confuse this process, resulting in multiple nodes believing they are the primary, which can cause conflicting writes and potential data loss. Real-world incidents have documented outages due to manual starts, such as promoting a replica node to leader status inadvertently. This disrupts Write-Ahead Logging (WAL) synchronization, leading to divergent transaction logs. Database administrators are advised to use Patroni's built-in commands for service management and implement role-based access controls to prevent unauthorized manual actions. Monitoring solutions are crucial for early detection of anomalies. Simulating failure scenarios in staging environments can help prepare teams for real incidents. Ongoing advancements aim to enhance Patroni's safeguards against manual overrides, with future iterations potentially incorporating AI-driven anomaly detection.

Tech Optimizer

November 7, 2025

PostgreSQL 13’s Sunset: Why Database Pros Must Upgrade Before the Lights Go Out

Organizations using PostgreSQL 13 must upgrade before its end-of-life on November 13, 2025, as this will result in the cessation of security patches, bug fixes, and official support. Continuing to use an unsupported version exposes systems to vulnerabilities, which can lead to data breaches and compliance challenges. The last minor release for PostgreSQL 13 was 13.21 in May 2025. Upgrading to newer versions, such as PostgreSQL 16 or 17, offers performance improvements and enhanced features. Strategies for upgrading include using tools like pg_upgrade, pg_dump/pg_restore, and logical replication to minimize downtime. Compatibility issues may arise due to deprecated functions in PostgreSQL 13, necessitating code reviews. Managed services like those from Percona can provide support beyond EOL. The costs of not upgrading can be significant, with potential downtime from security breaches often exceeding migration expenses.

Tech Optimizer

November 7, 2025

Reinventing PostgreSQL for the Next Generation of Apps

Enterprises are modernizing their databases due to latency issues, the need for global uptime, and licensing pressures. PostgreSQL is emerging as a preferred alternative to traditional database solutions like Oracle and SAP, focusing on reliability, control, and efficient management of modern workloads, including AI and edge applications. pgEdge, an open-source Postgres vendor, emphasizes that migration to PostgreSQL is about ensuring flexibility and avoiding vendor lock-in. PostgreSQL's governance model, independent of any single company, is a significant advantage, particularly in light of licensing audits and forced upgrades. PostgreSQL has a large developer community and is a mainstream enterprise technology, with tools like pgAdmin widely used. Concerns about operational burdens with open source are addressed by extensions like pgEdge, which enhance PostgreSQL's capabilities for high availability and seamless multi-cloud deployment. pgEdge operationalizes PostgreSQL for distributed use, automating upgrades, backups, and point-in-time recovery, leading to a lower total cost of ownership compared to proprietary models. Modern applications require edge-native databases to operate close to users, reducing latency. pgEdge supports multimaster PostgreSQL across geographically distributed clusters, allowing local read and write capabilities while maintaining data consistency. It builds on PostgreSQL’s logical replication without forking it, ensuring compatibility and consistency. pgEdge facilitates database automation on Kubernetes, managing backups, recovery, and upgrades, making it easier for platform teams. Organizations can start with a simple setup and scale to a multiregion architecture as needed, using the same PostgreSQL stack throughout. pgEdge offers container builds that cater to enterprise needs for geospatial intelligence and AI workflows. As AI applications increasingly run at the edge, pgEdge provides the necessary performance and coherence for edge-native AI. PostgreSQL's SQL compatibility and ACID compliance ease the migration process from systems like Oracle and SAP. The extensive user base across various sectors simplifies hiring PostgreSQL expertise. Combining PostgreSQL with pgEdge offers a strategic modernization pathway for enterprises needing reliable, Kubernetes-native operations and AI-ready extensions, freeing them from vendor lock-in and high licensing costs. This integration transforms PostgreSQL into a globally distributed, cloud-native control plane for data, benefiting architects, CFOs, and developers alike.