Decoding the PostgreSQL Data Lake Landscape: A Comprehensive Analysis of Four Distinct Approaches to "Postgres Reads Iceberg"

Introduction: The Modern Data Dilemma

As organizations increasingly segregate their transactional systems from their analytical data estates, a persistent architectural tension remains: how to bridge the gap between operational PostgreSQL databases and modern analytical formats like Apache Iceberg and Parquet stored in cloud object storage.

When database vendors announce that "PostgreSQL can now read Iceberg," they are rarely talking about the same thing. On September 30, AWS announced that Amazon Aurora PostgreSQL now supports direct querying of Apache Iceberg and Parquet data in data lakes. This announcement joined a crowded field of interpretations. Today, a database vendor claiming PostgreSQL can read Iceberg data could mean at least four entirely different things, sharing roughly as much architectural commonality as the various interpretations of the term "serverless."

To untangle this landscape, one must look past the marketing narratives and examine the underlying execution engines, storage architectures, and—most importantly—the query planners.

Main Facts: The Four Contenders

As of October 2026, four prominent open-source and commercial extensions define the frontier of PostgreSQL-based lakehouse querying:

1. Aurora’s aurora_analytics extension: Built directly into Amazon Aurora PostgreSQL, this tool leverages an embedded DuckDB engine running within the PostgreSQL process space to query Parquet and Iceberg data from Amazon S3 and the AWS Glue Data Catalog.
2. pg_lake from Snowflake: Originally developed by Crunchy Data as Crunchy Bridge for Analytics and open-sourced following Snowflake’s acquisition, pg_lake uses a separate, companion multi-threaded process (pgduck_server) communicating over a local Unix socket to bring analytical muscle to PostgreSQL alongside true transactional support.
3. ColdFront from pgEdge: Positioned as a tiered-storage solution, ColdFront automatically archives older partitions of a PostgreSQL table into Iceberg format using a cron-driven Go binary, presenting a unified view to applications via a DuckDB-backed execution model.
4. pg_clickhouse from ClickHouse: A high-performance Foreign Data Wrapper (FDW) that routes analytical queries to an external ClickHouse cluster. While ClickHouse natively reads Iceberg, pg_clickhouse itself acts purely as a PostgreSQL-to-ClickHouse transport layer rather than a direct lakehouse reader.

Despite several of these tools sharing an underlying analytical execution engine (DuckDB), they are far from interchangeable. The fundamental differentiator lies in answering a single, crucial question: When the query executes, which planner is in charge?

Chronology and Evolution of Postgres-Lakehouse Integration

The convergence of relational databases and data lake formats did not happen overnight. It represents a multi-year architectural evolution driven by the explosive growth of object storage and open table formats.

• Early 2024: Crunchy Data begins development on pg_lake, aiming to bridge the gap between operational Postgres workloads and analytical object storage. This work eventually forms the bedrock of Crunchy Bridge for Analytics.
• Late 2024 – Throughout 2025: Ecosystem maturity accelerates. pgEdge develops ColdFront to solve the operational pain point of hot-and-cold data tiering. Meanwhile, ClickHouse refines its FDW (pg_clickhouse) to push complex analytical execution to external columnar engines.
• November 2025: Following its acquisition of Crunchy Data, Snowflake open-sources pg_lake, cementing its place in the broader data community as a decoupled, multi-process lakehouse extension for PostgreSQL.
• September 30, 2026: AWS enters the arena in earnest, announcing native support for Apache Iceberg and Parquet querying within Aurora PostgreSQL via the aurora_analytics extension.
• October 2026: Technical documentation across all four projects reveals distinct architectural trade-offs, particularly regarding query planning, transaction safety, and pushdown execution logic.

Supporting Data: Two Planners, One Query

To understand why these systems behave differently, one must understand the fundamental conflict between PostgreSQL’s query planner and external analytical engines.

The Planner Divide

PostgreSQL’s cost-based planner relies heavily on local statistics gathered via the ANALYZE command—such as pg_class.reltuples, pg_statistic, and associated histograms stored in system catalogs. However, a Parquet file or an Apache Iceberg manifest residing in S3 possesses none of these native PostgreSQL statistics. While object store files maintain their own rich metadata (such as row counts, column bounds, and min/max values in file footers), standard PostgreSQL does not know how to parse or interpret them.

The analytical engines powering these extensions (such as DuckDB or ClickHouse) have their own sophisticated query planners designed specifically for columnar file formats. Consequently, every hybrid system must negotiate a division of labor between two distinct planners:

1. Full Query Pushdown: The analytical engine takes the entire statement, including local heap tables, executes the heavy lifting, and returns the final result set.
2. Table-Scan Pushdown (Fallback): PostgreSQL retains control of the overall query plan, treats the lake engine as a remote or foreign data source, and handles joins, sorts, and aggregations locally.

Detailed System Breakdown

1. Aurora: DuckDB in the Building

Available on Aurora PostgreSQL 17.11+ and 18.6+, the aurora_analytics extension embeds DuckDB directly within the PostgreSQL server process.

• Setup & Usage: Enabled via the cluster parameter group, an IAM role, and the CREATE EXTENSION aurora_analytics command, users define read-only foreign tables pointing to S3, AWS Glue, or S3 Tables.
• Execution Behavior: Hybrid queries run within a single process. Under full query pushdown, DuckDB executes the entire plan (including local heap scans via POSTGRES_SCAN). However, execution falls back to table-scan pushdown if the query references constructs DuckDB cannot parse—such as unsupported functions, data types, or non-C collations. A single improperly collated column can silently downgrade performance by shifting planning authority back to PostgreSQL.

2. pg_lake: DuckDB Next Door

Shipped as open-source by Snowflake, pg_lake deliberately avoids embedding DuckDB in-process. Instead, it relies on a separate multi-threaded companion process (pgduck_server) communicating over a local Unix socket.

• Architecture Rationale: This separation respects PostgreSQL’s process-isolation memory and threading boundaries. The background server can consume up to an 80% default memory limit, requiring careful tuning alongside shared_buffers.
• ACID and Writes: Unlike its peers, pg_lake shines in transactional write capabilities. Operations like INSERT, UPDATE, DELETE, and COPY function seamlessly on Iceberg tables within ordinary ACID transactions that can simultaneously touch local heap tables.

3. ColdFront: DuckDB in Your Backend

Developed by pgEdge, ColdFront is explicitly tailored for automated data tiering.

• Tiering Mechanism: An automated cron-driven archiver moves historical partitions of a PostgreSQL table into Iceberg format, leaving a unified view (UNION ALL) that stitches the hot heap table and cold object storage together.
• All-or-Nothing Planning: When a query touches the unified view, pg_duckdb takes over completely. There is no cost-based split between hot and cold tiers; every query goes through DuckDB. While write operations are supported via post-parse hooks, mixed-tier writes require careful configuration regarding crash safety and transaction rollback behavior.

4. pg_clickhouse: The External Transport Layer

pg_clickhouse functions strictly as an FDW linking PostgreSQL to an external ClickHouse cluster.

• The Iceberg Connection: ClickHouse itself possesses robust native capabilities for querying Iceberg via specialized table engines and data lake catalogs. pg_clickhouse, however, does not read Iceberg directly; rather, it passes SQL statements through to ClickHouse.
• Trade-offs: While complex filtering, aggregations, and joins are pushed down effectively to ClickHouse, joining remote ClickHouse tables with local PostgreSQL heap tables forces PostgreSQL to pull raw rows across the wire and perform local joins unless carefully rewritten using Common Table Expressions (CTEs).

Official Responses and Architectural Stances

Project maintainers and corporate backers emphasize distinct design philosophies for their respective tools:

• AWS frames Aurora’s native integration as a frictionless, serverless-friendly analytics bridge: “Hybrid queries run within a single process, offering seamless access to petabytes of data lake storage without requiring complex ETL pipelines or external service management.”
• Snowflake/Crunchy Data emphasizes enterprise-grade safety and separation of concerns: “Embedding multi-threaded engines directly into core PostgreSQL backends introduces severe memory and concurrency risks. A decoupled companion process ensures stability while maintaining robust ACID compliance across relational heaps and open table formats.”
• pgEdge focuses on operational efficiency through transparent tiering: “Database administrators should not have to manually rewrite application queries to archive historical data. ColdFront automates lifecycle management while presenting a unified relational interface to developers.”

Implications for Database Administrators and Enterprise Architects

Selecting the right tool depends entirely on your architectural constraints, operational maturity, and workload characteristics.

The Diagnostic Imperative: Always Check Your Plan

Regardless of which approach an organization selects, engineers must adopt a rigorous diagnostic posture. The single most important command when deploying any hybrid Postgres-lakehouse query is:

EXPLAIN (VERBOSE) SELECT * FROM hybrid_table ...;

If the execution plan reveals a single custom scan node at the top, the analytical engine (such as DuckDB) has successfully taken over planning using rich file metadata. If the plan is dominated by PostgreSQL foreign scan nodes, the database planner is guessing cardinalities blind—frequently resulting in suboptimal join orders, bloated memory consumption, and severely degraded query performance.

By understanding the underlying mechanics of these four paradigms, enterprise architects can successfully harness the analytical power of data lakes without sacrificing the operational reliability of PostgreSQL.