Building a PostgreSQL data warehouse | POSETTE: An Event for Postgres 2025

Summary

Marco Slot describes an 18-month effort to make PostgreSQL usable as a data warehouse by combining PostgreSQL extensions, DuckDB’s vectorized query engine, and Apache Iceberg tables stored in object storage.

Why PostgreSQL needs a different approach for analytics

PostgreSQL is primarily optimized for OLTP: many concurrent, short transactions that read or update small numbers of rows. Analytics workloads instead scan large amounts of data, often using only a subset of columns, and benefit from high scan throughput.

The talk contrasts common OLTP and OLAP designs:

• Storage: OLTP systems commonly store rows together and use indexes to find individual rows. Analytical systems often use compressed, column-oriented storage, allowing queries to read only the columns they need.
• Execution: Row-at-a-time processing can incur function-call and memory-allocation overhead. Vectorized engines process batches of values, which can improve CPU-cache use, reduce overhead, and take advantage of CPU instructions that operate on multiple values at once.
• Query planning: OLTP favors fast planning; analytics can spend more effort finding an efficient plan for a complex query.

Architecture: PostgreSQL extensions, DuckDB, and Iceberg

Rather than replace PostgreSQL, the design uses its extension APIs to add analytics features. Extensions can intercept commands, writes, and query-planning steps, and route relevant work to another engine.

• DuckDB provides vectorized query execution. PostgreSQL and DuckDB are combined in a hybrid query plan: PostgreSQL handles some parts, while DuckDB handles the largest query fragments it can support.
• Apache Iceberg provides the table format. Iceberg metadata in object storage tracks a table’s schema, history, partitioning, and Parquet files. PostgreSQL can serve as the Iceberg catalog and keep track of the current table state.
• Parquet and caching: Data is stored in columnar Parquet files in object storage, with a local NVMe cache. Because Iceberg files are immutable, the system can cache them aggressively.
• Modular extensions: The implementation is organized as multiple extensions, including a mix of C and Rust extensions and open-source and closed-source components.

The resulting product, Crunchy Data Warehouse, is PostgreSQL with extensions for Iceberg tables and querying data-lake files such as CSV and JSON.

Table operations and limitations

Iceberg tables can use many PostgreSQL features, including sequences, generated columns, triggers, constraints, and transactions. The speaker says indexes and foreign keys are not supported for these tables.

Writes create Parquet and metadata files in object storage. Inserting millions of rows can take only a few seconds, but a single-row insert can take about a second because it must write files; batching writes is therefore preferable. Updates use DuckDB to find matching rows, record deletions in Iceberg position-delete files, and write new rows to Parquet. Periodic compaction—described as running every 10 minutes—combines small or heavily deleted files.

Performance claims and benchmarks

The speaker reports that Iceberg tables with DuckDB and caching were often 10–100 times faster than ordinary PostgreSQL tables for analytical queries on the same server.

Reported benchmark results include:

• ClickBench: Iceberg tables were up to 50–60 times faster than PostgreSQL tables in the tested setup. The speaker says caching contributed significantly, though Parquet can also avoid fetching unneeded data.
• TPC-H: The tested workload ran 14 times faster with Iceberg, despite tuning PostgreSQL and adding indexes. The speaker notes that the difference grows at larger scale factors.

These figures are presented as results from the team’s tests, not as universal guarantees.

Use cases and demo

• Log analytics without a separate ETL pipeline: Tables can point to CSV, JSON, or supported text files in blob storage. An Iceberg table can then hold selected or extracted fields for faster querying with PostgreSQL.
• Incremental file processing: The open-source pg_incremental extension can repeatedly run a command with changing parameters—for example, processing new files as they appear. The speaker describes setting up the log pipeline in three commands, with successful batches not retried and failed work retried.
• Replication from operational PostgreSQL: A recently released extension-based feature replicates data from a PostgreSQL publication into Iceberg tables. It can create the destination tables, copy existing data, and then apply inserts, updates, deletes, and truncates in micro-batches. Small files are later compacted.

In the demo, an analytical query on a synthetic operational database with about one billion rows took roughly four minutes on the source database. After replicating the data to Iceberg, the same query took about 300 milliseconds. The speaker cautions that this example had not yet incorporated updates at the time of the fast query. A subsequent write test showed Iceberg catching up in batches rather than continuously.

Takeaway

The talk presents PostgreSQL extensions as a way to combine PostgreSQL’s SQL interface and transaction capabilities with DuckDB’s analytical execution and Iceberg’s data-lake storage. PostgreSQL can act as both the warehouse interface and Iceberg catalog, while tools such as Spark can also access the Iceberg data through that catalog and read directly from object storage.

Main speaker/source

• Marco Slot, Crunchy Data — conference speaker and presenter of the product design, benchmarks, and demo.