NewApp Digest
search bot

logical replication

How we pushed CDC into Postgres, and turned replication into clockwork
Tech Optimizer
July 24, 2026

How we pushed CDC into Postgres, and turned replication into clockwork

Making data from transactional databases accessible to analytical databases is essential in modern data architecture, but it faces challenges such as fragile tooling, high costs, and complex operations. Snowflake has developed a Postgres service that addresses these issues by reimagining Postgres replication. Postgres is a strong operational database, but its change data capture (CDC) capabilities need improvement. Many data pipelines are fragile due to complexities in managing continuous data flow, schema changes, snapshots, and failures. To enhance user experience with Snowflake Postgres, a complete reinvention of the replication process was necessary. A new feature called data mirroring is in public preview, allowing resilient data replication into Snowflake with low cost, minimal lag, and transactional consistency. This feature directly pushes changes from Postgres into Apache Iceberg™ tables in transactional batches, which are then automatically applied to Snowflake tables without extra infrastructure. Change data capture records changes from a transactional database for replay on another system, primarily using logical decoding in Postgres. This method decodes WAL records into logical operations, but the client bears the responsibility for subsequent steps like backfilling, managing schema changes, and handling failures. Built-in logical replication in Postgres only addresses some complexities. A limitation of logical decoding is that the external system does not recognize the state of Postgres, making it difficult to detect schema changes or correlate snapshots with changes. The solution is to push changes from Postgres into a data lake, specifically into Iceberg tables using compressed Parquet format, with object stores like Amazon S3 as the destination. The mirroring process uses a new Postgres extension called snowflake_cdc, which continuously pushes batches of changes into per-table change logs and a meta log. This extension understands the Postgres environment, coordinating schema changes and complex transactions while ensuring alignment between snapshots and changes.
Google Cloud: PostgreSQL community contribution updates
Tech Optimizer
July 10, 2026

Google Cloud: PostgreSQL community contribution updates

Google Cloud actively participates in the PostgreSQL ecosystem by supporting community-driven events and contributing to open-source initiatives. Recent key events include: - **PGConf.dev 2026**: Featured strategic discussions on logical replication and global index architecture, with a consensus to adopt a deparsing-based approach for DDL replication. Dilip Kumar presented on global indexes. - **PGConf India 2026**: Attracted over 580 participants, featuring various sessions including keynotes and technical talks by Google Cloud contributors. - **PGDay Paris & PGDay France 2026**: Matt Cornillon was involved in organizing PGDay France, with sessions led by him and Yves Colin. - **PGDay FOSDEM 2026**: Focused on AI-assisted workflows in PostgreSQL development, with a technical talk by Matt Cornillon. - **PGConf Belgium 2026**: The session was selected as supplementary material for a database exam, indicating student engagement. - **Nordic PG Day 2026**: Google participated as a Partner-level sponsor and hosted a dedicated booth. - **Swiss PGDay 2026**: Featured a demonstration on processing vectors in PostgreSQL. - **Postgres Conference 2026 San Jose**: Google sponsored the event, with Vikas Arora discussing PostgreSQL adaptations for AI workloads. Community leadership roles included: - Dilip Kumar on the Program Committee for PGConf.dev 2026 and the Paper Selection Committee for PGConf India 2026. - Matt Cornillon on the organization committee for PGDay France, and Yves Colin on the Program Committee. Acknowledgment was given to various contributors for their dedication to PostgreSQL conferences.
Logical replication improvements in Amazon RDS for PostgreSQL 18
Tech Optimizer
July 8, 2026

Logical replication improvements in Amazon RDS for PostgreSQL 18

PostgreSQL logical replication has operational challenges, such as the absence of generated columns in the replication stream, replication conflicts causing apply worker halts, and lingering replication slots consuming write-ahead log (WAL) files. PostgreSQL 18 introduces enhancements to address these issues, including the ability to include STORED generated columns in the replication stream with a new publication parameter, detailed conflict counters in pg_stat_subscription_stats, automatic invalidation of idle replication slots, and the ability to toggle two-phase commit settings on live subscriptions. To set up a test environment, two PostgreSQL 18 instances (one publisher and one subscriber) are required, along with an EC2 instance for executing commands. The publisher and subscriber must have specific configurations for logical replication, including enabling logical replication and setting appropriate parameters. PostgreSQL 18 allows the replication of STORED generated columns by using the publish_generated_columns parameter. The subscriber must define these columns as regular columns. The new version also enhances conflict monitoring by providing detailed counters for different types of replication conflicts. Parallel streaming is now the default behavior for subscriptions, improving efficiency. The two-phase commit feature can now be modified on existing subscriptions without needing to recreate them. PostgreSQL 18 also introduces automatic cleanup of idle replication slots, which can be configured to invalidate unused slots after a specified duration. Considerations for implementation include ensuring that the subscriber's column types match those of the publisher for generated columns, the requirement for track_commit_timestamp for certain conflict counters, and the fact that sequences are not replicated. Cleanup of replication resources is recommended after testing to prevent unnecessary costs and WAL accumulation.
The database storage problem is solved. Here’s what comes next.
Tech Optimizer
June 25, 2026

The database storage problem is solved. Here’s what comes next.

Postgres has been a reliable transactional database for three decades, used for managing customer records and financial transactions. Innovations in the Postgres ecosystem are now focused on minimizing data movement rather than just data storage. The challenge of interoperability is becoming crucial, as organizations seek to share operational data seamlessly across various systems without creating additional copies or pipelines. Many organizations are spending as much effort on data movement as on data storage. Postgres is increasingly viewed as the authoritative system for critical information, and its role is evolving to facilitate better interaction with operational data. Technologies like logical replication and change data capture are enhancing Postgres's integration within data ecosystems. The rise of AI has highlighted the need for real-time access to operational data and has prompted organizations to reconsider the necessity of maintaining multiple copies of the same data. The database industry is shifting focus from optimizing storage to enabling effortless data sharing across systems. Postgres continues to adapt to new workloads and architectural patterns, maintaining its reputation as a stable foundation for operational data while expanding its capabilities through innovative extensions.
Millisecond Latency at Scale: Why Our ML Feature Store Runs on Snowflake Postgres
Tech Optimizer
June 23, 2026

Millisecond Latency at Scale: Why Our ML Feature Store Runs on Snowflake Postgres

Organizations are consolidating their fragmented database environments with Snowflake Postgres, phasing out outdated systems and simplifying multivendor setups without extensive code rewrites. Ericsson migrated four legacy databases to Snowflake Postgres, achieving a 99% reduction in data processing time. SimCorp's transition to Snowflake Postgres resulted in a tenfold increase in disk operation speeds. Sigma Computing provides real-time analytics using Snowflake Postgres, eliminating the need for external systems. BlueCloud supports low-latency transactional workloads and analytics on a single platform. Superblocks enables developers to create full-stack applications using Snowflake CoCo, leveraging SQL tools against live data. Snowflake Postgres is approximately four times faster than Databricks Lakebase and has a 99.95% published uptime SLA. It operates on Postgres 18 and accommodates up to 64 TB of storage, surpassing Lakebase's 16 TB limit. Snowflake Postgres simplifies management with in-place major version upgrades and supports standard logical replication, enhancing flexibility for data movement and integration.
PostgreSQL 18 on Amazon Aurora and Amazon RDS: Security, monitoring, and developer enhancements
Tech Optimizer
June 20, 2026

PostgreSQL 18 on Amazon Aurora and Amazon RDS: Security, monitoring, and developer enhancements

PostgreSQL version 18 has deprecated MD5 password authentication in favor of SCRAM-SHA-256, with a new parameter, md5_password_warnings, enabled by default to log deprecation warnings. It has enhanced monitoring capabilities by adding columns to pg_stat_database and pg_stat_statements to track parallel worker activity, with the default max_parallel_workers_per_gather set to 0 in Aurora PostgreSQL. The pg_stat_subscription_stats view now includes new columns for tracking conflict types in logical replication. Optimizer statistics are automatically transferred during upgrades, while uuidv7() generates timestamp-ordered UUIDs. The default streaming option for CREATE SUBSCRIPTION has changed to parallel, and the idle_replication_slot_timeout parameter automatically invalidates inactive replication slots. Enhancements to the COPY command include REJECT_LIMIT for error tolerance and a silent LOG_VERBOSITY level. OLD and NEW aliases have been introduced in RETURNING clauses for various DML commands.
PostgreSQL 18 on Amazon Aurora and Amazon RDS: Performance enhancements
Tech Optimizer
June 20, 2026

PostgreSQL 18 on Amazon Aurora and Amazon RDS: Performance enhancements

PostgreSQL 18 addresses common performance challenges for users, including managing query performance across composite indexes, diagnosing memory spills in materialized Common Table Expressions (CTEs), and upgrading major versions without plan regressions. Key enhancements include skip scan optimization for multicolumn indexes, improved EXPLAIN functionality, and optimizer statistics that persist through major version upgrades. Skip scan optimization allows PostgreSQL to efficiently utilize multicolumn B-tree indexes even when leading columns are not specified in the WHERE clause, significantly improving query performance. The EXPLAIN command has been enhanced to include buffer statistics by default, providing deeper insights into query execution and resource usage. PostgreSQL 18 also introduces visibility into the storage of materialized nodes in query plans, indicating whether intermediate results were stored in memory or spilled to disk. A new metric, Index Searches, has been added to EXPLAIN ANALYZE output, indicating how many times the database traversed the index tree during query execution. Additionally, Self-Join Elimination (SJE) automatically detects and removes unnecessary inner joins of a table to itself, optimizing query performance. The autovacuum mechanism has been improved with the introduction of autovacuum_vacuum_max_threshold, which caps the number of dead tuples that can accumulate before autovacuum triggers a VACUUM, addressing issues with large tables. The vacuum_truncate parameter provides a server-wide control point to disable VACUUM’s file truncation behavior, reducing locking issues on busy systems. PostgreSQL 18 also separates the allocation of autovacuum worker slots from their usage, allowing for dynamic adjustments to autovacuum_max_workers without requiring a server restart. Finally, new columns in pg_stat_all_tables track cumulative time spent on maintenance operations, providing better insights into maintenance overhead for each table.
Looking Ahead to Postgres 19
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.
Search