Engineering & Deep Dives

• 1: Porting pg_mooncake and duckdb_mooncake to DuckDB 2.0, pg_duckdb 1.2.0, and pgrx 0.19.3
• 2: Mooncake is joining Databricks!
• 3: Mooncake: a real-time storage engine for Apache Iceberg
• 4: No, Iceberg won’t fix your data stack.
• 5: but, my Postgres (+ lakehouse) is free
• 6: HTAP is Dead
• 7: What does Analytics in Postgres really mean?
• 8: How to improve DuckDB performance on Parquet
• 9: Clickbench says Postgres is a great analytics database
• 10: wth are S3 tables
• 11: pg_mooncake 🤝 drizzle: typed SDKs for your analytic workloads
• 12: pg_mooncake + Neon: serverless analytics in Postgres
• 13: pg_mooncake: Fast Analytics in Postgres with Columnstore Tables and DuckDB
• 14: Mooncake: next-gen search and analytic systems
• 15: Reflections from the Iceberg Meetup
• 16: Things we learnt

1 - Porting pg_mooncake and duckdb_mooncake to DuckDB 2.0, pg_duckdb 1.2.0, and pgrx 0.19.3

When Mooncake Labs paused public updates to pg_mooncake after v0.2.0, the extension was pinned to an earlier DuckDB 1.3.x / pg_duckdb snapshot and pgrx 0.12. Meanwhile, the PostgreSQL ecosystem moved forward to PostgreSQL 18, pgrx 0.19.3, and pg_duckdb 1.2.0 (which embeds DuckDB v2.0.0).

This article walks through the engineering changes required to bring the entire Mooncake stack — pg_mooncake v0.3.0, duckdb_mooncake v0.0.2, and moonlink v0.0.1 — onto DuckDB 2.0.0, pg_duckdb 1.2.0, and pgrx 0.19.3.

Architecture Recap: How the Three Repositories Fit Together

Understanding the build and runtime boundaries between the three repositories explains why upgrading DuckDB touches both C++ and Rust across multiple crates:

┌──────────────────────────────────────────────────────────────────────┐
│                         PostgreSQL Process                           │
│                                                                      │
│  ┌─────────────────────────┐          ┌───────────────────────────┐  │
│  │    pg_mooncake.so       │          │      pg_duckdb.so         │  │
│  │  (Rust / pgrx 0.19.3)   │          │  (C++ / DuckDB v2.0.0)    │  │
│  │                         │          │                           │  │
│  │  • mooncake_worker      │          │  • DuckDB planner/engine  │  │
│  │    (walsender +         │          │  • Loads                  │  │
│  │     embedded moonlink)  │◄────────►│    duckdb_mooncake        │  │
│  │  • Exports              │  dlsym   │    storage extension      │  │
│  │    mooncake_get_        │  + IPC   │  • Scans Iceberg Parquet  │  │
│  │    postgres_lsn()       │  socket  │    + Moonlink Puffin      │  │
│  └─────────────────────────┘          └───────────────────────────┘  │
└──────────────────────────────────────────────────────────────────────┘

1. pg_mooncake is a pgrx Rust extension that manages mooncake.create_table(dst, src), spawns the mooncake_worker background process, streams logical replication changes into an embedded moonlink engine, and compiles duckdb_mooncake into the same shared library.
2. pg_duckdb embeds libduckdb (v2.0.0) inside PostgreSQL and intercepts queries referencing columnstore tables.
3. duckdb_mooncake registers a DuckDB StorageExtension (mooncake) that resolves columnstore tables via Moonlink’s UNIX domain socket (mooncake.sock), waits for the target PostgreSQL WAL LSN, and delegates file scans to DuckDB’s parallel parquet_scan reader alongside an in-memory Puffin deletion-vector reader.

1. Adapting duckdb_mooncake to DuckDB 2.0.0 C++ APIs

DuckDB 2.0.0 (d54cbbe3df17e8e5ce68312f483b4155836112aa) introduced several breaking C++ API changes across its catalog, storage extension, and filter pushdown layers:

StorageExtension Registration (StorageExtension::Register)

In DuckDB 1.x, extensions mutated config.storage_extensions["mooncake"] directly. In DuckDB 2.0, storage extensions are registered via StorageExtension::Register(config, "mooncake", make_shared_ptr<MooncakeStorageExtension>()).

TableCatalogEntry::GetColumns() & GetTypes()

MooncakeTableEntry inherits from TableCatalogEntry. We aligned its constructor and column projection helpers with DuckDB 2.0’s ColumnList and LogicalType APIs while preserving the virtual __row_id (UBIGINT) pseudo-column used by Moonlink deletion vectors.

TableFilterSet Iterator Refactoring

In DuckDB 1.x, TableFilterSet exposed a direct std::unordered_map<idx_t, unique_ptr<TableFilter>> filters member. In DuckDB 2.0, TableFilterSet encapsulates its filter map and provides range-based iteration yielding (FilterIndex, const TableFilter &) entries.

While porting MooncakeScanFunction::mooncake_scan_bind to the new iterator API, we uncovered and fixed a subtle bug in IS NULL / IS NOT NULL filter pushdown:

• When a query projected a subset of columns (for example, SELECT b FROM t_col WHERE a IS NOT NULL AND b > 10), CONJUNCTION_AND and comparison filters remapped their column index from the scan projection index to the physical Parquet table column index (column_ids[filter_col_idx]), but IS_NULL and IS_NOT_NULL filters previously passed the unmapped scan index directly to the underlying Parquet filter set.
• Under DuckDB 2.0, an out-of-bounds or mismatched filter column index on parquet_scan caused a segmentation fault (SIGSEGV) during SELECT count(*) FROM (SELECT DISTINCT c, a, b FROM t WHERE c IS NULL AND b > 2).
• Remapping IS_NULL and IS_NOT_NULL filter indices through column_ids resolved the crash completely.

2. Wiring LSN-Consistent Snapshot Reads Across pg_mooncake and duckdb_mooncake

Because pg_mooncake ingests heap writes asynchronously via PostgreSQL logical replication, a SELECT issued immediately after an INSERT or COMMIT must wait until moonlink has ingested WAL up to the current flush position.

In v0.3.0, we completed the cross-library LSN bridge:

1. In pg_mooncake (src/lib.rs), we exported an extern "C" symbol:

   #[no_mangle]
   pub extern "C-unwind" fn mooncake_get_postgres_lsn() -> u64 {
       unsafe {
           if pg_sys::RecoveryInProgress() {
               0
           } else {
               pg_sys::GetXLogFlushRecPtr()
           }
       }
   }

2. In duckdb_mooncake, the storage extension resolves mooncake_get_postgres_lsn at runtime via dlsym(RTLD_DEFAULT, "mooncake_get_postgres_lsn") (falling back to 0 when duckdb_mooncake is loaded inside a standalone DuckDB CLI session outside PostgreSQL).
3. When binding a scan on a columnstore table, duckdb_mooncake passes that LSN to moonlink_scan_table_begin(...), which blocks until Moonlink’s WAL consumer reaches the target LSN.

3. Automatic REPLICA IDENTITY FULL on Partitioned Tables

PostgreSQL logical replication requires REPLICA IDENTITY FULL on tables that do not have a primary key (or where Moonlink needs the full before-image of updated/deleted tuples). When a user mirrors a partitioned table via CALL mooncake.create_table('sales_col', 'sales'), setting REPLICA IDENTITY FULL only on the parent partitioned table does not propagate to existing leaf partitions.

pg_mooncake 0.3.0 now uses pg_sys::find_all_inheritors(relid, pg_sys::AccessExclusiveLock as i32, std::ptr::null_mut()) during mooncake.create_table to set REPLICA IDENTITY FULL on the root table and all existing child partitions in a single atomic operation.

4. End-to-End Verification

The adapted stack was validated across two clean Linux ARM64 environments:

1. Ubuntu 24.04.5 LTS ARM64 (mooncake-u24a-pg18, PostgreSQL 18.6)
2. Rocky Linux 10.2 ARM64 (mooncake-el10a-pg17, PostgreSQL 17.11)

All 58 Moonlink table_handler unit tests, all 3 SQL regression suites (small, partition, unsupported), the runtime pg_duckdb 1.2.0 version guard, and 13 post-restart persistence and Iceberg metadata checks passed with zero failures.

2 - Mooncake is joining Databricks!

Thirteen months ago, we started Mooncake to bring the data intelligence stack that powers giants like OpenAI to every vibe-coded app and developer. From first prompt to IPO.

Today, we’re joining forces with Databricks. The mission continues: make Lakebase and the Lakehouse feel like one.

For a decade, we thought the final form of a database was one that could do it all. Fast updates, transactions, analytics. For database people, this was HTAP. It never quite landed. Zhou and Cheng built it. Pranav tried to sell it. It was hard.

Rebuild both sides, and the end product compromises on both.

Well, what if you didn’t? 🥮

People (and now agents) already have a default OLTP: Postgres. And they love it.

They already have a Lakehouse. And they know how to pull intelligence from it with their engines of choice (Apache Spark, Snowflake, DuckDB, Trino) or their agents (Databricks Agent Bricks).

On both sides, the winners are clear. The architecture is cloud-first. The community is behind it. For the first time in databases, the two estates have clear anchors.

So… what if they just worked together?

For the developer

Your apps start on Postgres. As soon as you hit PMF, that same data is ready for analytics and AI with best-in-class Lakehouse tools… No extra pipelines, no CDC. Postgres is just synced to your Lakehouse.

And all that intelligence can be served with your apps with just Postgres and the pg_mooncake extension.

For the enterprise

For modern enterprises, Postgres + Lakehouse is already the stack. Data engineers move data between them (ETL, rETL); and more engineers manage and optimize the Lakehouse.

With Mooncake, the whole stack just works out of the box; without needing a Debezium + Kafka + Flink stack. 🙂

For the agent

We see two kinds of data <-> agent interactions:

1. Agents that build apps on Postgres (Lovable, Replit).
2. Agents that analyze and understand data (Agent Bricks).

Now, they can work together:

• On the same data.
• At agent scale, with 10,000s running at the same time.
• Without manual & human ETL pipelines connecting the two 🙂

Thank you, and Day 0 again for us

To everyone who believed (users, design partners, investors, open source contributors): thank you.

pg_mooncake will stay open source, alive, and a core part of the community. We’ll keep pushing to get it into every Postgres.

With Databricks, Mooncake now holds both estates: Postgres (Lakebase) and the Lakehouse. And we’re fired up for the experience we can deliver to customers and agents.

At last, the two sides feel like one.

– Zhou, Cheng, Pranav, Nolan, Hao, Paul. 🥮

3 - Mooncake: a real-time storage engine for Apache Iceberg

This technical overview assumes familiarity with Apache Iceberg, an open table format for analytical datasets. Iceberg is part of the general “Lakehouse” movement, where users:

1. Store large datasets using open file formats (Parquet + Metadata) in Object Storage
2. Query them directly by engines like Spark, Trino, Flink, and DuckDB using SQL
3. Get the benefits of ‘database features’ like schema evolution & time-travel
4. Scale practically infinitely, and aren’t locked into a vendor

Iceberg was built for slow-changing data + batch analytics & processing. That world is pretty clear and only improving with engines getting better at reading open formats.

Real-time workloads (user-facing analytics, stream-processing) typically require a separate purpose-built system altogether—e.g., ClickHouse, Druid, SingleStore—so users maintain two pipelines and manually move data between them.

What if we brought the openness and flexibility of the Lakehouse to real-time? There are two key requirements:

1. Don’t sacrifice any part of the Lakehouse: the integrations, the ecosystem.
2. Bring real-time data + fast queries, without destroying Iceberg performance / state.

Introduction

Mooncake is a real-time storage engine built on Iceberg for real-time workloads. It brings high-rate ingest, fast queries, and indexing into Iceberg without giving up the openness & flexibility of the Lakehouse:

1. Fast ingest: streaming writes with sub-second mirroring from relational databases.
2. Fast queries: continuous table optimization with smart caching.
3. Indexing: low-latency search and primary key lookups.

Mooncake’s architecture, indexing layer, and write-aware optimizations turn Iceberg into a platform capable of powering user-facing analytics, search, serving, and HTAP workloads—things that previously required purpose-built real-time systems.

Architecture

                         DuckDB
                         Spark
                       Snowflake
                    (Iceberg-rest)
                    ▲              ▲
                    │              │
                    │              │
             ┌──────┴───moonlink───┴───────┐
             │  ┌───────────────────────┐  │
             │  │                       │  │
Postgres ───►│  │┌ ─ ─ ─ ─ ┐ ┌ ─ ─ ─ ─ ┐│  │
             │  │                       │  │
Kafka    ───►│  ││  index  │ │  cache  ││  │
             │  │                       │  │
Events   ───►│  │└ ─ ─ ─ ─ ┘ └ ─ ─ ─ ─ ┘│  │
             │  │                  nvme │  │
             │  └───────────────────────┘  │
             └─────────┬─────────┬─────────┘
                       ▼         ▼
                 ┌───────Iceberg───────┐
                 │         s3          │
                 │┌───────┐ ┌─────────┐│
                 ││       │ │         ││
                 ││ index │ │ parquet ││
                 ││       │ │         ││
                 │└───────┘ └─────────┘│
                 │                     │
                 └─────────────────────┘

The core of Mooncake is moonlink, a Rust library designed for real-time CDC and streaming into Iceberg.

Moonlink augments an Iceberg table with an in-memory Arrow buffer, a positional delete log, and hybrid in-memory / NVMe-resident / object-store indexes to absorb high-frequency updates. Execution engines read the union of buffer + Iceberg for a fully up-to-date view—without waiting for Iceberg metadata commits.

Inserts

• Rows are written to an Arrow buffer; data is efficiently flushed to Parquet when enough rows have accumulated.
• In-memory indices are built during insertion and flushed as an object-store-native version on Parquet flush.

Deletions

• Moonlink maintains primary key indexes for all rows.
• Deletions update the positional deletion log using these indexes.
• Periodically, deletion logs are converted to Iceberg deletion vectors (Puffin).

Reads

• Moonlink writes Iceberg for any engine to read.
• Moonlink exposes a union-read interface (duckdb_mooncake) that combines its in-memory state with Iceberg files so engines can access the most current table state.
• Mooncake exposes an index-read interface to execute index lookup queries for key lookup and search.

Core Technology Deep Dive

3.1 Write-aware table optimization

Union-read of up-to-date version Iceberg forces a trade-off between freshness and write-amplification: to get fresher data, users write snapshots very frequently, creating too many small Parquet and metadata/manifest files. Compactions add even more snapshots, and the spec makes it hard to prune intermediate versions and recycle unused files.

Mooncake solves this by writing Iceberg metadata periodically, while exposing an Iceberg-compatible API to “read the latest version” before it’s committed—generating up-to-date metadata and data files on the fly. Result: sub-second freshness for reading the table.

Index + Deletion Vector for update/delete Iceberg wasn’t optimized for high-throughput update/delete (CDC) or upserts (streams). Prior approaches batch into expensive global merges or rely on equality deletes (hurting read performance and causing small files). Moonlink maintains an index that covers both the in-memory buffer and Iceberg to locate positions of deleted records, then applies deletions via Iceberg deletion vectors.

Gracefully handling write vs. compaction conflicts Open table formats handle conflicting commits naively—streaming updates often collide with background maintenance (sort/compaction). Mooncake builds mapping structures from old files to new files during maintenance and remaps in-flight updates/deletes, so no transaction needs to fail.

3.2 Iceberg-native indexing

Index storage

• Index file metadata stored as Iceberg Puffin files.
• Index files stored with or near the table’s data.
• Each index file covers one or more data files.
• Background merges form an LSM of indexes.

Query interface & integration

• Indexes point to data via (file_id, offset)—no extra primary key needed.
• APIs:
  • Build(DataFile) -> IndexFile
  • Merge([IndexFiles]) -> IndexFile(s)
  • Query(IndexFile, query) -> [(DataFile, offset, [optional payload])]

Index implementations

• Bucket hash table: high-cardinality hash index used to turn raw deletes into positional deletes. Immutable, hash-ordered SSTable-like format mapping 64-bit hashes to storage locations; partitions via high-order bits; space-efficient, cache-friendly, fast streaming merges.
• Bucket hash table + inverted index: low-cardinality hash index powering GIN/full-text search (inverted index per data file; mergeable bucket table to inverted-index offsets).
• Vector index: built per data file and merged across files; clustered/IVF variants for object-store-native performance.

3.3 Managed buffer and cache

Mooncake is object-store-native with efficient use of local memory & NVMe (similar philosophy to SlateDB, Turbopuffer, and WarpStream).

Object-store-native WAL The object store is the source of truth. Mooncake nodes are mostly stateless: they write WALs directly to the object store and use them on recovery—no multi-replica Raft/Paxos clusters. You can view Mooncake as a database that writes its snapshots as Iceberg snapshots.

Streaming writes to Arrow buffer For low-latency writes, the latest data is buffered before Parquet. An in-memory Arrow buffer lets us append rows and modify fields, flush directly to Parquet/Iceberg, and also send uncompressed Arrow/Parquet over the network for real-time engines. Combining buffer + index, Mooncake can handle ~1M updates on a single machine.

Read & write-through cache Caching matters for both performance and S3 cost. Fresh Parquet, deletion vectors, and indexes land on NVMe before async upload to the object store, yielding <5 ms local writes and reads.

Deployment & Solutions

4.1 pg_mooncake

HTAP, real-time analytics, and sync to Iceberg, all inside Postgres. A Postgres extension that runs Moonlink in a background worker and embeds DuckDB as the execution engine (MIT licensed).

CALL mooncake.create_table('T_mooncake', 'T_heap') creates a columnar version of a Postgres table that stays in sync with the heap with sub-second delay and strong consistency.

4.2 Stand-alone moonlink

Fast & simple replication from Postgres to Iceberg. Stateless Rust engine that reads logical replication or HTTP/RPC ingestion and writes directly to Iceberg, keeping query-ready tables with sub-second latency and minimal overhead.

TL;DR: Mooncake enables workloads that traditionally require specialized databases (like Elasticsearch for search, ClickHouse for analytics, or vector databases for AI) to run directly on your Iceberg data lake—unifying real-time and analytical workloads on a single storage layer.

4 - No, Iceberg won’t fix your data stack.

Life is expectations vs reality. Here is one for Apache Iceberg – a favorite amongst the data community.

Expectations: Iceberg is the solution to all my data pains

See, the core of Iceberg is pretty neat:

1. Keep all data in object storage in open file formats like Parquet.
2. Fool any execution engine (DuckDB, ClickHouse, Spark, Snowflake, etc.) into thinking these files are a table and query them with SQL directly.
3. [Ongoing progress] Get exceptional performance on these tables, without having to load data into a system-specific format.

And with clever design you get:

1. Interoperability across execution engines. You don’t need to load data into a proprietary format to get great performance.
2. ACID transactions + schema evolution.
3. True separation of storage and compute.
4. Time travel + versioning.

Iceberg was initially built by/for BIG DATA teams at Netflix, Apple, et al. More recently, it’s being built for you and me (see Supabase Analytics Buckets + Tiger Lake). Vendors are now putting in the work to create a great experience within their platforms.

Great. So I, the user, (1) own data in object store, (2) am not locked into any vendor, and (3) can query it without loading data into a specific system. That should be it.

Reality: Iceberg is only cool if you’re willing to build your own Snowflake (BYOS)

For Apple and Netflix, it’s WELL worth the hundreds of engineers to build their own Snowflake or Databricks on raw cloud storage. For you and me, I’m really not sure.

See, you and I start on Iceberg and expect it to JUST work, like Postgres or ClickHouse. I insert/upsert data, load files, and query it. It’s not that simple:

1. Metadata overhead: Iceberg tracks snapshots, manifests, manifest lists, and table-metadata JSONs. A one-row update triggers four S3 writes (each hundreds of ms). A regular RDBMS on SSD handles this in <1 ms. The result of all these metadata updates is commonly known as the “small file explosion”. And it’s up to the user to manage the maintenance of these files. This is a WILD concept for users coming from the RDBMS world where all of this is on ‘auto-pilot’.

2. Limited write concurrency: Iceberg uses optimistic concurrency. Each writer tries to commit new metadata, assuming no conflict. If two collide, one fails and retries. This guarantees serializability, but at the cost of write throughput. To contrast: Iceberg handles a few commits per minute on the same table without collisions. An RDBMS can handle thousands of transactions per second: each with full isolation, indexing, and durability.

3. Read / Write performance tradeoffs: From the Snowflake Iceberg docs: “performance primarily depends on how efficiently the Parquet files are written”. If another engine writes sub-optimally, Snowflake’s query speed suffers. Investing in Iceberg requires investing in partitioning, sorting, and compaction jobs to get good performance. The classic is the OPTIMIZE operation (e.g., via Spark or Trino) to merge small files, at additional compute cost. This extra maintenance tax is well worth it at Netflix scale. But for you and me – we’re just used to a cloud DW that just works.

4. Batch + stale data: Iceberg was built for slow-changing datasets. Think huge fact tables with heavy batch writes, not high-concurrency row updates. It lacks real indexes / streaming. You still need a separate real-time columnar system.

So, who’s fixing Iceberg, and how?

• AWS / R2: “We’ll host your files. The rest is your problem.” They give you CDC connectors, a basic catalog, and occasional compaction if you pay for it. That’s it. File explosion? Your bill. You want fast queries? Run your own optimization jobs.
• Snowflake: “It’s Iceberg-compatible — as long as we own it.” You can read external Iceberg tables. But if you want write access, performance, or optimizations? Convert it to a Snowflake-managed Iceberg table — which is basically Snowflake with an Iceberg API skin. For example, Snowflake writes 16MB Parquet files, which works perfectly with their storage nodes and disk cache. Imagine the performance without them (the recommended size for other engines is usually 256MB to 1GB).
• DuckLake (DuckDB devs): “The spec is wrong. Let’s rethink metadata.” Instead of dozens of JSON/Avro files in S3, store metadata in an actual embedded database (DuckDB, SQLite). Way faster planning, better concurrency. Essentially it is challenging the Iceberg design so the engine can just read from object store.
• Hudi / Paimon: “The whole Iceberg format is too broken. We built a new one.” Event-stream-driven companies like Uber and Alibaba have different use cases, so they introduced these new formats. They bake compaction, indexing, and streaming ingest into the format. Hudi has table services; Paimon uses LSM trees.

What if Iceberg just had regular database ergonomics?

To the outsider, database and big data engineers might look ‘same-same’; but we look at the same spec very differently.

Big data people (the Iceberg OGs) see ’table’ and think JSON metadata files, compaction pipelines, eventually consistent catalog state, maybe Spark will clean it up later.

As a database kernel person, I immediately think indexing, concurrency, transaction isolation, hot writes, query optimization. I keep wondering…. What if we just ‘copied’ the obvious stuff from real databases and layered it around Iceberg? What if we stopped treating it like a format to worship, and started treating it like a backend storage layer for a proper RDBMS?

Let’s set some bare-minimum expectations, things even SQLite does without breaking a sweat:

1. Concurrent Writes + Streaming Ingest That Works

Stop the “single writer” nonsense. No more retry storms. No more fear of write conflicts on concurrent jobs. Let multiple writers append, update, and even delete data — without overwriting each other or bloating S3 with metadata garbage.

2. Automatic Optimization, That Never Conflicts with Writes

Background compaction should just happen. And it should never require a lock or cause a conflict with a user query or write. Optimizing a table shouldn’t be a “maintenance window” — it should be invisible, online, constant.

3. Built-in Indexing and Caching, Not an Afterthought

Not everything needs to be a billion-row scan. Let me define an index — zonemap, Bloom, bitmap, anything — and store it with the table, queryable by any engine.

The Crazy Part? It’s Actually Not That Hard.

This isn’t impossible. It’s not even particularly original. We’ve been doing this for decades in RDBMS land:

• We can have a simple WAL to mark committed logs.
• We can have a primary key index to apply updates and deletes instead of full scans.
• We can save the ‘compaction’ operation state, and remap the deletions that happened in between.
• We can also just index the Parquet files and write them as Puffin blobs.

You keep the open format, the decoupled compute, the time travel, the engine interoperability. But you get actual database ergonomics. You get performance without handholding. You get operations that make sense.

Iceberg doesn’t need to be replaced. It just needs help thinking like a database.

That’s 🥮. Starting with Moonlink: sub-second Postgres-to-Iceberg sync that just works :).

5 - but, my Postgres (+ lakehouse) is free

Disclaimer: Your Postgres and Lakehouse are free and open too. You don’t need to be (or spend like) a giant to use them.

Why Postgres + Lakehouse?

Reason #1: If you believe in AI, this is the agent stack

Agents need memory. Both working memory and longer-term knowledge.

Working memory has to be instant, reliable, and accurate, just like app state. What’s the best place to store it? Postgres. That’s true for both apps and agents. This state is typically tied to an individual user, tenant, or agent.

Longer-term knowledge is different. The raw inputs are massive, messy, and shared across users, tenants, and agents—logs, LLM inputs and outputs, interactions. The goal is to ‘get smarter’.

What’s the best way to store these raw inputs? A lakehouse. Infinitely scalable, object-store-centric, and open to the best tools/engines for processing, analytics, search, MLOps, all available on the same data. And you’re only paying for what you’re using.

The classic “data engineering” pipeline was about extracting insights and intelligence from app data to make apps smarter, stickier, and better. The modern data stack was built around this flow:

App data → Bronze (raw) → Silver (processed) → Gold (modelled) → Serve the insight/intelligence to Apps

Turns out, the same flow applies to agents:

Agent context → Raw memory → Knowledge → Better context

Postgres → Lake → Postgres; it’s full circle.

Reason #2: The industry needs a new OLTP winner… there’s A LOT of shareholder value at stake

OLTP is the new battlefield, and differentiator, for the giants and the hyperscalers.

When Snowflake first came out, everyone was pretty amazed: a cloud-native data warehouse where, no matter how complex the query or how large the dataset, you got results. It just worked.

But 10 years later, much of the tech behind Snowflake is being commoditized:

1. Separation of storage and compute, now table stakes with object stores and smart caching.
2. Vectorized execution, now runnable on your laptop with DuckDB.

This made the new lakehouse architecture inevitable: put data and metadata in object storage, and query it with a vectorized engine.

Sure, it’s not as fast as Snowflake. Not as fancy. Not the same magical out-of-the-box experience. But the DIY-Snowflake tradeoffs are real.

The analytics market is no longer black and white—where warehousing either worked or didn’t, and there was clear value to capture as a provider. Now, it feels incremental. Commoditized.

So how do you 10x shareholder value as an analytics company? You look next door—at OLTP.

For 40 years, Oracle, SQL Server, and DB2 seemed unbeatable for tier-1 workloads, while MySQL dominated tier-3. But things are slowly changing:

1. Hardware is getting better
2. The cloud is getting better
3. MySQL got bought by Oracle
4. Postgres is improving at a rapid pace
5. OLTP workloads aren’t getting bigger

Suddenly, Postgres is good enough for tier-2—even some tier-1 workloads. It’s easy to get behind: open-source, reliable, extensible, and fast-shipping compared to the MySQL crowd.

This is just another ‘Zhou opinion’, right?

Err, let’s see about that:

• Microsoft: Fabric (lakehouse) has SQL Server database in it with mirroring already; guess what will come next? A Postgres.
• Google: AlloyDB (Postgres) + BigQuery. Both will connect through data in Iceberg.
• Amazon: Aurora (increasingly Postgres-native) and RDS, with S3 Tables, Glue, SageMaker. S3 is becoming an Iceberg-backed lakehouse itself. Firehose is already connecting them.
• Supabase: Check their recent (great) blog: Open Data Standards: Postgres, OTel, Iceberg.
• ClickHouse: After acquiring PeerDB, Postgres is now the #1 source for ClickPipes, and they’re working on deep Iceberg integration where ClickHouse becomes an execution engine.

Postgres + Iceberg is everyone’s horse in the race. And the win comes from a great developer experience between the two. This isn’t so much about bolting analytics (lakehouse) onto operational systems, sacrificing both. We’ve learnt this from the HTAP market.

This is more about getting the best-in-class OLTP (likely Postgres), and the best-in-class OLAP (likely lakehouse) to work independently (not sacrificing each other), but together. And the combined OLTP + OLAP market is to win. Play ball.

Postgres + Lakehouse will always stay free

While the giants first spend billions getting a Postgres, and then gluing it into their fortress (lakehouse), pg_mooncake is already on the shelf with a sub-second Postgres-to-Iceberg sync.

It’s also MIT licensed 🙂

6 - HTAP is Dead

This blog is inspired by Jordan Tigani’s blog titled “Big Data is Dead”. Jordan and I actually spent some time building an HTAP database at SingleStore.

The good old days (’80s)

Back in the ’80s, one relational database did everything. Transactions (OLTP) during the day and reports after hours (OLAP). Databases like Oracle V2 and IBM DB2 ran OLTP and OLAP on the same system; largely because data sets still fit on a few disks and compute was costly.

Nobody called it Hybrid Transactional/Analytical Processing (HTAP); it was simply the database.

The great divide (’90s)

As businesses had more data and asked tougher questions, the database began to show its limits.

See, transactional and analytical workloads pull in opposite directions. OLTP requires microsecond inserts and single-row lookups, while OLAP demands full-table scans and large-scale aggregates. This created constant contention; analytics consuming I/O and cache needed for latency-sensitive transactions, and vice versa.

The solution? Isolate the workloads. By the early 2000s, the Great Divide had begun.

The storage split (2000s)

A key technical driver behind this divide was storage architecture. OLTP systems optimized for row-based storage (fast writes + point queries). While OLAP systems chose columnar storage for efficient scans and aggregations.

By the mid-2000s, this split had become industry standard. Database pioneer Michael Stonebraker marked this shift in his paper, “One Size Fits All”: An Idea Whose Time Has Come and Gone. The database started breaking up into specialized engines.

OLTP and OLAP both ditched SQL (2000s–2010s)

Horizontal scaling pushed OLTP and OLAP even further apart.

Early distributed OLTP databases (NoSQL engines like MongoDB) dropped SQL and analytical capabilities entirely. On the analytics side, we saw the adoption of MapReduce and Data Lake architectures (Hadoop/HDFS): trading traditional RDBMS properties like strict consistency for massive throughput.

The unexpected reconciliation (2010s)

In the 2010s, two distinct database movements gained momentum:

1. NewSQL (Spanner, CockroachDB, Vitess). OLTP should remain SQL-based.
2. Cloud Data Warehouses (Redshift, Snowflake). OLAP should be on SQL systems with stronger consistency guarantees.

On paper, these systems served very different workloads. But under the hood, they shared a lot: distributed, MPP-style execution, and SQL. OLTP and OLAP systems, in isolation, had converged on many of the same architectural principles. There was one big difference: storage engines.

We asked ourselves: what if you could combine both row and columnstore storage engines in a single database?

Voilà, HTAP (2014)

In 2014, Gartner introduced the term HTAP (Hybrid Transactional and Analytical Processing): the next big DB architecture.

The goal was to close the gap between operational and analytical systems. This was a necessity for emerging workloads like pricing, fraud detection, and personalization. Even at the business level, decision makers wanted now’s data. Early HTAP systems showed it could be done. Well, mostly…

SingleStoreDB combined an in-memory rowstore, a disk-based columnstore, and a vectorized execution engine—supporting fast scans, seeks, filters, aggregations, and updates in a single system. Over time, we found that with modern hardware, the columnstore alone could handle a surprising number of OLTP-style queries, including point lookups and low-latency access patterns.

TiDB took a different route, pairing its TiKV rowstore with a separate columnar engine based on ClickHouse—maintaining two copies of the data to serve both workloads.

So that should be it, right? ’70s data nirvana, alas.

The Cloud Data Warehouse was the only winner (2020s)

Cloud data warehouses have clearly won. The NewSQL movement stalled… And HTAP? It never got the attention it deserved. Despite real technical progress, it remained pre-product market fit.

1. It’s really, really hard to replace someone’s OLTP system. Take DB-Engines’ word for this: Oracle and SQL Server still sit at #1 and #3.

2. Most workloads don’t need distributed OLTP. Hardware got faster and cheaper. A single beefy machine can handle the majority of transactional workloads. Cursor and OpenAI are powered by a single-box Postgres instance. You’ll be just fine.

3. Cloud-native architectures favored shared-disk, not shared-nothing. While NewSQL systems demanded fast local storage (and even in-memory durability), cloud platforms pushed toward object storage and elastic compute.

4. OLTP and OLAP are owned by different teams. OLTP is owned by product engineering; OLAP belongs to the data team. The incentives rarely align. No one gets promoted for “consolidating the stack”.

Your data stack forms the HTAP database (Today)

The cloud also started the move away from tightly coupled warehouses toward modular lakes built on object storage. In trying to escape the traditional warehouse/database, data teams started assembling their own custom systems, made of ‘best-in-class’ building blocks:

1. OLTP systems and stream processors as the WAL
2. Open table formats like Iceberg serve as the storage engine
3. Query engines like Spark and Trino for execution
4. Real-time systems like ClickHouse or Elastic function as indexes

Even in today’s disaggregated data stack, the need remains the same: fast OLAP queries on fresh transactional data. This now happens through a web of streaming pipelines, cloud data lakes, and real-time query layers.

It’s still HTAP; but through composition instead of consolidation of databases. It comes down to questions like:

1. How do I apply the WAL to my storage engine? AKA: How do I CDC from my OLTP system to the data lake efficiently?

2. Can I build a lower-cost index on my data lake, and keep it in sync? AKA: How do I ingest real-time data into the lake? Or how do I query Lake data with Postgres or Elastic functionality?

The HTAP challenge of our time comes down to making the lakehouse real-time ready.

After spending my best 10 years first starting and then rescuing it, HTAP as a monolithic database is dead. But let the spirit live on. 🥮

7 - What does Analytics in Postgres really mean?

Few areas in databases are hotter right now than extending Postgres to support analytics queries. This isn’t a particularly novel area either –– there have been plenty of attempts in the last decade:

Here is what it means to our customers –– and why the problem remains largely unsolved, despite our first attempt with pg_mooncake.

Level 0: I just run analytics queries on my Postgres tables

You’ll be surprised how far this can go with proper indexes, statistics, query tuning, and materialized views—people really overthink this.

However, as data volume and query complexity grow, the system starts to fall apart—indexes hurt write performance, materialized views can’t keep up, and certain queries become too slow to execute.

Eventually, you reach a point where you just need to store data in a columnar format. Lucky you.

Level 1: I want to use psql to query my columnar data

This resonates with product engineers because it simplifies their life. They can manage a familiar client and leverage an ORM that’s already set up. Plus, they can join analytics tables with operational tables directly in Postgres. There are two schools of thought here:

1. pg_duckdb (embedded DuckDB in Postgres)

Query columnar files (Parquet, CSV, etc.) with the embedded DuckDB execution, then stream the results back to Postgres.

2. Postgres FDW (often the ClickHouse FDW)

Query ClickHouse tables from Postgres, but queries execute in ClickHouse itself.

In both cases, queries are executed by a vectorized query engine. The key difference lies in where the analytic query is executed—either within your Postgres instance or on external compute.

There’s also an assumption here: your data already exists in a columnar format (e.g., Parquet files or ClickHouse tables).

Spoiler: It doesn’t.

Level 2: I want to write and manage my columnar data with Postgres

Instead of setting up an ETL tool and introducing a specialized analytics database as the destination, I just want to use Postgres.

This is what we solved with pg_mooncake v0.1 by introducing columnstore tables. You can create and transactionally write, delete, and update rows in columnstore tables using Postgres just like with a regular table. Queries on these tables are executed with DuckDB. The 2024 Pavlo review highlights our innovation here.

This approach is similar to that of Crunchy Data Warehouse. Both use open data formats (Iceberg, Delta Lake, Parquet) for columnar storage, enabling elite performance (Top 10 in ClickBench) without any vendor lock-in on data.

We were very happy with the initial version of pg_mooncake. But it’s not enough.

Level 3: I need the columnstore to be up-to-date with my Postgres heap table

The user story here is “My app does all kinds of inserts, updates, deletes; and I need to serve analytics on up-to-date data.” This request is very common amongst app-facing analytics. There are two approaches here:

1. A magical ‘operational columnstore’ in Postgres.

A columnstore table that handles ACID transactions, indexing, updates, deletes, prepared statements, triggers, and arbitrary query shapes may sound like magic, but it’s possible.

At SingleStore, we built a hybrid transactional-analytical processing (HTAP) system where the columnstore was the source of truth. The reality is, it took us a decade of work –– and still wasn’t ‘OLTP’ enough.

We thought pg_mooncake could do the same in Postgres, but adding full operational capabilities as an extension proved tough. Ultimately, we’d be competing with Postgres itself—its native heap tables are too powerful, with so many features and extensions already built around them (see the discussion on Hacker News).

2. Replicate Postgres tables into a specialized analytics system.

For example: ClickPipes + ClickHouse.

Postgres provides robust and efficient CDC (change data capture) at the WAL level, ensuring reliable, low-latency updates. These changes can be applied to an OLAP system like ClickHouse.

ClickHouse handles the append-only case by buffering inserts in memory and periodically flushing them. Great.

But it’s still not enough. With concurrent updates and deletes, ClickHouse ends up storing multiple versions of the same row. Now, it’s on you to define deduplication logic to clean up these duplicates at merge time. Good luck with that.

You really need a columnstore that can keep up with Postgres-like OLTP as the source

So far, it’s still an unsolved problem.

You want the properties of a modern columnstore—open formats on object storage—but you also need it to handle OLTP concurrency and data updates. And you’ll want to write to it and manage it directly in Postgres.

Think about it—this is a crazy requirement… a list of immutable files on disk or blob store that needs to be rapidly updated.

That is exactly what we built in pg_mooncake v0.2 / v0.3 with Moonlink. 🥮⭐

8 - How to improve DuckDB performance on Parquet

Last week, we ran ClickBench on Postgres and ranked in the top 10. We showed that Postgres can be extended with pg_mooncake to support serious analytics workloads.

In the announcement, we got a lot of questions on how pg_mooncake outperforms DuckDB on Parquet files even though internally, it embeds DuckDB in Postgres and stores data in Parquet format.

See also the discussion on X / Twitter.

The best part: pg_mooncake does not modify DuckDB at all…

A quick recap of the pg_mooncake design

pg_mooncake is a Postgres extension that adds column-oriented storage and vectorized execution for fast analytics:

1. Data in columnstore tables is stored in Parquet files
2. Table metadata (which Parquet files are present) is stored in Postgres with transactional guarantees
3. Queries on columnstore tables are executed using DuckDB

We recommend the full architecture read.

How can an Elephant catch a Duck?

At first glance, it seems unlikely that a system which routes Postgres queries through DuckDB to scan Parquet files could outperform DuckDB scanning those files directly.

And on ClickBench, this holds true for plain wrappers: pg_duckdb (10.21x) exhibits significant overhead compared to DuckDB (Parquet, partitioned) (6.76x).

Surprisingly, despite relying on pg_duckdb, pg_mooncake (6.03x) is approximately 1.5x faster than pg_duckdb and even outperforms DuckDB (Parquet, partitioned) itself.

So, Why Does pg_mooncake Outperform DuckDB?

pg_mooncake leverages Postgres to store table metadata, eliminating the need to query an external catalog (as DuckDB would require when scanning external Iceberg or Delta Lake tables). Instead, it directly queries the Postgres table to identify which Parquet files to scan, reducing overhead.

Additionally, pg_mooncake stores detailed Parquet metadata for each file in Postgres, including column statistics for every row group. These statistics, such as MIN/MAX values for each column, are aggregated to optimize scans. When querying a columnstore table, pg_mooncake uses these statistics to filter out data files and row groups that cannot produce matches.

For instance, in a query like:

... WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' ...

any row groups or segments outside this date range are skipped. By avoiding unnecessary I/O, this optimization dramatically enhances query performance.

Since parsing Parquet metadata is computationally expensive, pg_mooncake further minimizes overhead by maintaining an in-memory cache for Parquet metadata, resulting in an additional 5% speedup in query execution.

Why still stick to Parquet?

We’re often asked this question. If raw performance were the only consideration, using a custom memory format would allow us to squeeze out the last bit of performance.

However, we believe in the openness that the Lakehouse architecture brings. Postgres isn’t designed to manage every aspect of your data lifecycle, and this approach enables seamless multi-engine interoperability.

Most importantly, we demonstrated that you can achieve great performance even with Postgres and Parquet.

There are plenty of optimizations left for DuckDB (and pg_mooncake)

While running ClickBench, we identified several areas of improvement for both DuckDB and pg_mooncake.

We’re excited to work on these optimizations with our community, contribute upstream changes to DuckDB, or implement them as DuckDB OptimizerExtension hooks.

We’d love to make DuckDB on Parquet even faster; making open formats even more a reality.

🥮

9 - Clickbench says Postgres is a great analytics database

TL;DR: We spent a few months optimizing PostgreSQL and made it to the Top 10 on ClickBench, a benchmark typically dominated by specialized analytics databases.

What’s more, all compute is within Postgres, and all tables are managed directly by PostgreSQL—it’s not a simple wrapper. This is the story of pg_mooncake.

What Is ClickBench?

ClickBench is the definitive benchmark for real-time analytics databases, originally designed to showcase the performance of ClickHouse. It evaluates databases on their ability to handle real-world analytics workloads, including high-volume table scans and complex aggregations.

Historically, ClickHouse and other purpose-built analytics databases have led this benchmark, while general-purpose databases like Postgres/MySQL have lagged behind by 100x. But we wanted to challenge that perception—and Postgres delivered.

How to Build Analytics in Postgres?

When most people think of PostgreSQL, they think of a rock-solid OLTP database, not a real-time analytics powerhouse. However, PostgreSQL’s extensibility makes it uniquely capable of punching above its weight class.

Here’s how we approached the challenge:

1. Build a PG Extension

We leveraged PG’s extensibility to build pg_mooncake as a native PG extension.

2. Storage Format: Columnstore

For analytics workloads, a columnstore format is essential. ClickBench workloads typically involve wide tables, but queries only access a small subset of columns:

• In a row store (like a PostgreSQL heap table), reading a single column means jumping through rows.
• In a columnstore, reads are sequential, which is faster (and it also enables better compression and execution on compressed data).

3. Vectorized Execution

To enhance query execution speed, we embedded DuckDB as the execution engine for columnstore queries. This means across the execution pipeline, data is processed in batches instead of row by row, enabling SIMD, which is a lot more efficient for scans, group-bys, and aggregations.

4. Table Metadata & Management Directly in PostgreSQL

Efficient metadata handling is critical for real-time analytics, since fixed latency matters. Instead of fetching metadata or statistics from storage formats like Parquet, we store them directly in PG:

• This enables faster query planning.
• It also allows for advanced features like file skipping, significantly improving performance.

More details on the architecture.

What Does It Mean?

PostgreSQL is no longer just an OLTP workhorse. With careful tuning and engineering, it’s capable of delivering analytics performance on par with specialized databases while retaining the flexibility and ecosystem advantages of PostgreSQL.

After building advanced data systems for a decade, part of my core belief is: we can make the data stack a lot simpler.

pg_mooncake is MIT licensed, so if you don’t believe it, give it a try.

🥮

10 - wth are S3 tables

Amazon announced table buckets during re:Invent last year. An S3 bucket that’s also a table? What does that mean?

TL;DR: S3 tables allow Iceberg tables to exist without a catalog. Let me explain.

Iceberg vs Delta Lake

What? I thought Iceberg and Delta are just table formats? Why are we comparing the way they’re implemented?

A key difference between Iceberg and Delta Lake is “Delta tables can exist without a catalog, but Iceberg cannot”, due to their metadata design:

1. Delta Lake relies on the file system to get ACID in the metadata layer.

The metadata layer in _delta_log is an infinite list of JSON files. If a directory contains a Delta table, you just need to read the files (based on file names) in _delta_log, one by one, to get the current state of the table.

You don’t need any extra information or ‘catalog’ to read/write to them.

2. Iceberg, on the other hand, is storage agnostic, and relies on a catalog to get ACID.

The metadata layer is a reversed linked list of snapshot nodes (from SNAPSHOT N+1, you can find SNAPSHOT N, but not vice versa).

You need an external ‘catalog’ to atomically insert and retrieve the last snapshot.

Wait, what if the file system can consistently ‘remember’ the snapshot pointer and atomically swap it? Bingo, we got an S3 table bucket.

How does writing to S3 table buckets actually work?

I spent some time on S3 table docs so you don’t have to. P.S. it’s hard to understand, even DeepSeek struggles.

We’ll compare writing to an S3 Table with a ’normal’ Iceberg catalog:

1. [Both] Engine writes data_files (Parquet)
2. [Both] Engine writes Iceberg metadata files
3. [Catalog Only] Request the catalog to commit
4. [S3 Table Only] Call S3 API UpdateTableMetadataLocation to perform the commit

Yep, that’s the only difference.

What are the benefits of S3 tables then?

I am sure Amazon is doing a lot of work on performance (both on compaction and bucket layout).

But the functionality difference is:

S3 tables allow you to read and write any file to the bucket, but not list files.

I thought it was dumb at first, but it actually makes sense.

It means the only way to ‘probe’ a table bucket is to read the metadata-location specified by the bucket. So by design, any user can only read files that are part of the table, because there is no way to find any other file.

So, who should use S3 tables?

I think the use cases might be very different from what people think.

For the enterprise-scale lakehouse, it actually makes sense to have a proper centralized external catalog and table maintenance services. And S3 Tables are still more expensive than regular S3.

I don’t see much use of S3 tables there.

To me, S3 Tables are great for smaller teams and lightweight analytics, potentially embedded into other workflows. It gives you Iceberg without managing an external catalog.

Here’s my take: the first engine to write S3 Tables is Spark, and the second will be Postgres.

🥮

11 - pg_mooncake 🤝 drizzle: typed SDKs for your analytic workloads

A thing that surprised us in the pg_mooncake launch was how much developers cared about ORMs for their analytical workloads. We saw two typical requests:

1. “I have a bunch of Parquet files. I want full ORM support over them.”
2. “I use Drizzle/Prisma for my transactional database. I also want to use it for my analytics and dashboards.”

We’ve always believed that columnar storage in Postgres must look and feel like a regular heap table. It’s where we innovated with pg_mooncake columnstore tables; you can run transactions, updates, deletes, and joins with heap tables.

A nice bonus of native columnstore in Postgres is your favorite ORM works out of the box. In this blog post, we’ll use Drizzle.

Let’s build a simple analytics app on a Reddit dataset, and run all analytics queries using Drizzle.

1. Create a pg_mooncake columnstore table

We’ll be using a Reddit dataset from Hugging Face. First, create a pg_mooncake columnstore table (see the v0.1 syntax and v0.3 mooncake.create_table reference):

CREATE TABLE reddit_comments (
    author TEXT,
    body TEXT,
    controversiality BIGINT,
    created_utc BIGINT,
    link_id TEXT,
    score BIGINT,
    subreddit TEXT,
    subreddit_id TEXT,
    id TEXT
) USING columnstore;

Next, copy the data from Hugging Face into reddit_comments (v0.1 Load Data guide). Here we just copy 1 Parquet file (15M rows):

COPY reddit_comments FROM 'hf://datasets/fddemarco/pushshift-reddit-comments/data/RC_2012-01.parquet';

Great. Loading data from Hugging Face in Postgres is pretty simple now. And fast too.

2. Set up Drizzle schema for the columnstore table

After we set up the Drizzle PostgreSQL driver for our Postgres instance, we can set up the schema for our columnstore table:

// schema.ts
export const redditComments = pgTable("reddit_comments", {
  id: text("id").primaryKey(),
  author: text("author"),
  body: text("body"),
  controversiality: bigint("controversiality", { mode: "number" }),
  created_utc: bigint("created_utc", { mode: "number" }),
  link_id: text("link_id"),
  score: bigint("score", { mode: "number" }),
  subreddit: text("subreddit"),
  subreddit_id: text("subreddit_id")
});

3. Start querying your data model with full type safety

For example, to get subreddit statistics:

// routes.ts
const stats = db
  .select({
    subreddit: redditComments.subreddit,
    commentCount: sql<number>`count(${redditComments.id})`,
    avgScore: sql<number>`avg(${redditComments.score})::float`,
    avgControversiality: sql<number>`avg(${redditComments.controversiality})::float`,
    totalScore: sql<number>`sum(${redditComments.score})`
  })
  .from(redditComments)
  .groupBy(redditComments.subreddit)
  .orderBy(desc(sql`count(${redditComments.id})`))
  .limit(10);

You can write fast analytics queries with syntax you’re comfortable with.

4. Get started with pg_mooncake today

Check out our friends at Drizzle. It’s never been easier to use your ORM for your analytics.

We’re now an official Drizzle sponsor:

We’re excited to take on projects like One Dollar Stats together. Stay tuned for more.

12 - pg_mooncake + Neon: serverless analytics in Postgres

pg_mooncake launched in beta two months ago. It adds columnar storage and a vectorized engine to Postgres.

Over the last two months, with our customers, we’ve realized we accidentally built the best serverless database for analytics. Here’s the story:

1. pg_mooncake 🤝 Neon storage engine

Neon innovated significantly on its storage engine.

There’s a great thread by Nikita on this. This storage engine is how Neon can achieve true separation of storage & compute. This leads to an exceptional DX around branching, scale-to-zero, read replicas, instant provisioning… All of these are goodies of a true serverless Postgres.

pg_mooncake’s architecture also involves true separation of storage and compute. Data is stored in object store (local file system or S3), and only metadata is stored in Postgres.

This is the Lakehouse architecture. The Lakehouse is how data in some of the largest companies in the world is stored and analyzed in a cost-efficient and serverless manner.

TL;DR on the Lakehouse: store all data in object store in a universal format. Query this data directly as a Pandas dataframe, with Spark, or with DuckDB in a serverless way.

pg_mooncake’s goal is to bring this flexibility, scalability, and ‘serverless’ analytics to every Postgres application.

2. Scale everything to zero

Combining Neon’s storage and our analytics architecture –– you get complete separation of storage and compute for transactional and analytical workloads, while keeping all table metadata in Postgres.

With this serverless architecture, and combining analytical and transactional tables in one system, we’ve seen customers do cool things like:

1. Separate and scale analytical workloads separately from transactional workloads.
2. Sync changes from heap tables into columnstore tables using a separate compute.
3. Join columnstore tables with heap tables for BI and dashboards.
4. And finally, run entire analytics services that scale to zero.

Customers like Inkeep ship their application, fast BI dashboards, and their analytics services entirely in Postgres. Each of their services scales independently, while all data and table metadata stays in Postgres. It’s pretty neat.

3. ‘Scale to zero’ analytics with Python

This is where it gets fun.

pg_mooncake writes data in the standard analytics formats Iceberg and Delta to your object store. These act as metadata layers over Parquet files that make data files ‘feel’ like a table to any query engine (Pandas, Polars, Spark, DuckDB).

A common workload we’ve seen is pg_mooncake customers querying and transforming columnstore tables using Python. It’s very simple to create a dataframe from your columnstore table (see Cloud Storage and Iceberg Export for more info).

We’ve also seen requests for running semantic query engines like LOTUS on Postgres.

4. Get started today!

Read our Getting Started guide for creating your first columnstore table in Postgres.

Hint: it’s easy :)

13 - pg_mooncake: Fast Analytics in Postgres with Columnstore Tables and DuckDB

pg_mooncake brings columnstore tables with DuckDB execution to Postgres for fast analytics.

It supports transactional inserts, updates, and deletes, just like regular Postgres tables. Data is written with Delta Lake (and Apache Iceberg in v0.2+) table metadata, allowing it to be queried outside of Postgres.

Why another Postgres analytics extension?

Postgres is the most popular database these days, but it’s bad at analytics. Numerous attempts have been made to address this, but with limited success so far. Fast analytics require a distinct set of techniques, such as columnar storage, vectorized execution, and late materialization, etc. Early projects like Citus & Timescale added columnar storage, but there was no good vectorized execution to leverage the format.

A decade ago, this would have been a massive project, but today we have DuckDB.

For the unfamiliar, think of DuckDB as SQLite for analytics. It is designed to be embedded in a host process, in our case, Postgres. It’s incredibly fast for analytics, ranking among the tops on ClickBench. It has extensions to use external compute to scale beyond a single node. Even better, its SQL syntax follows Postgres closely, making the integration easier.

Recently, we’ve seen a trend to embed DuckDB inside Postgres. Extensions like pg_duckdb and pg_analytics are designed for querying external columnar storage in Postgres, but they can’t write out data in those formats via transactional insert/update/deletes.

pg_mooncake brings a columnstore table with both storage and compute that can leverage the format.

How pg_mooncake was built

The first decision to make is the choice of storage format. While a proprietary format could be optimized for best performance, an open format allows sharing across engines - like a mooncake meant to be shared with friends. pg_mooncake chooses Parquet as its file format, with added table metadata from open formats like Iceberg and Delta Lake. This ensures that a mooncake table is directly readable by most engines.

Table operations can be viewed as operations on these Parquet files. INSERT and COPY will create new Parquet files, UPDATE and DELETE will remove corresponding Parquet files and create new ones with modifications. Yes, deleting one row from a table with a one million row Parquet file could mean writing a new one with 999,999 rows. In the future we will introduce deletion vectors to solve it by marking rows as deleted without actually rewriting the file.

At its core, pg_mooncake is a TAM for columnstore table interface within Postgres + a storage extension for Parquet files within DuckDB. Table metadata, including addition and deletion of Parquet files, is stored inside a Postgres table for transactional consistency. Queries involving columnstore tables are routed from Postgres to DuckDB and the results are streamed back to Postgres via pg_duckdb.

This integration is made possible because both DuckDB and Postgres are highly extensible, allowing users to implement their own storage, planner, and executor. Even their native tables are implemented using the same mechanisms, enabling third-party tables to achieve the same functionality with minimal overhead.

Query Lifetime

Let’s walk through the lifetime of a query.

When a SQL query comes to Postgres, Postgres parses it and generates a plan for it. When the query involves columnstore tables, it is treated as an analytics query and will be executed entirely using DuckDB and the results will be streamed back to Postgres. Minor query rewrites are applied to bridge the SQL syntax differences.

A DuckDB storage extension is added to interact with our storage format, implemented similarly to DuckDB’s own storage. Physical operators like TableScan, Insert, Update, Delete are implemented for our storage format. Moreover, pg_duckdb implements read from Postgres regular tables, allowing our columnstore tables to join with Postgres regular tables within DuckDB.

Transactional Updates

To support DELETE and UPDATE, an implicit row_id column is populated during the TableScan, consisting of (file_id, row_offset) to identify the rows efficiently. The physical delete operator tracks all the row_ids marked for deletion and, upon completion, reads all undeleted rows from affected Parquet files and writes them to new Parquet files. The physical update operator follows a similar approach: it streams the updated rows to a new Parquet file, remembers the row_ids to delete, and deletes the original rows.

All the table metadata, including addition and deletion of Parquet files, are stored inside a Postgres table. This allows for transactional insert/update/delete. DuckDB takes a Postgres snapshot in addition to the SQL query to run, and reads all metadata from that snapshot to ensure consistency.

What’s Next

We commit to make pg_mooncake better and production-ready. Features like compaction, secondary index and constraints, and partitioned tables are on our roadmap.

As a company, Mooncake Labs aims to build a lakehouse for developers. Our goal is to simplify the data stack with clean Postgres and Python interfaces and pg_mooncake is a start in this direction.

🥮

14 - Mooncake: next-gen search and analytic systems

We are Zhou, Cheng, and Pranav. And we’ve spent the last decade building databases at MemSQL (now SingleStore).

We love complex systems, deep-tech engineering, and naturally – physics 🙂 Data systems, much like physics, are built on certain principles. The principles need to be unanimous and durable.

Building systems that are simply ’technically’ better is futile. The success of a data system depends on the principles it embodies, and changing them along the way is extremely difficult. Closed-source companies can’t go to open source and expect adoption. Databases can’t just abandon their storage format and be effective stateless engines.

We have strong beliefs about what is happening and what is correct. These principles make the Mooncake.

We’re grateful for the support from Khosla Ventures, Nikita Shamgunov, Jordan Tigani, Barry McCardel, Amir Haghighat, Paul Copplestone, Roger Luo, and many others.

Thank you for believing in us and our principles.

1. Software is built by the ‘product engineer’

There is a disconnect between the interfaces, jargon, and stack built by the data community, and how teams actually ship software today. And this is our opportunity. There are two types of teams today:

a. Teams that have overengineered their stack (more pipelines than customers)

These teams have built a lot of infrastructure to ship some product. Each piece introduces specific APIs, schemas, and types. The flow often resides in the mind of a single engineer.

A common setup we’ve seen is Postgres + PostHog + Dagster + Fivetran + dbt + BigQuery for some very basic insights on product usage.

Despite our decade in databases, we’d struggle to set this up—how will your SWE agent handle it? Also, when are you actually working on your product instead of your stack?

b. Teams that would benefit from some data sanity

These teams have shipped a lot of product on shoestring infrastructure—typically a Postgres table, ad-hoc files (CSV, Parquet, JSONL), Python scripts, and Lambda functions to hold it all together.

I like these teams, but they would benefit from storing data in tables (Iceberg and Delta) instead of ad-hoc files. They wouldn’t have to sift through hundreds of time-stamped Parquet files to find missing values or wrangle with dataframes to stitch their files and tables together.

Unfortunately, these teams often don’t know that this technology exists, and it’s hard to blame them. There is so much jargon, it’s hard to get started, and there is opportunity cost of setting up all this infra. Also, have you tried writing to an Iceberg table? Good luck!

2. Database engines are on the path to being commoditized

We’ve seen every database implement the same optimizations, mimicking one another. The database pie has been sliced so thin that we’re left with generic marketing terms like ‘real-time!’ and ‘analytics!’

This is why we strongly believe in open table formats like Iceberg and Delta. A single copy of data with stateless execution engines aligns incentives and creates a positive-sum game.

3. Agents need separation of storage and compute

Separation of storage and compute has historically created delightful ‘for human’ experiences: serverless, branching, read replicas, scale to 0, lower costs.

It’s also compelling for agents. With chain-of-thought / o1-type reasoning, you’ll expect LLMs to do more than just make function calls with fixed knowledge bases. Agents will write and update tables, run complex transformations with Python.

There are three pieces to the Mooncake; Postgres and Python are all you will need

First, get 1000x faster analytics directly in Postgres with pg_mooncake columnstore tables. These columnstore tables are just like a regular Postgres table.

“We’re really excited about Mooncake bringing easy-to-use columnstore tables to Postgres… Mooncake is exactly what we needed to modernize our data infrastructure all while staying within Postgres.”

— Max Muoto

Next, you can query these tables outside of Postgres with Polars, DuckDB, and Pandas with 0 dataframe wrangling or formatting.

It’s day 61, and there are a lot of systems to beat 🙂

We have a long journey ahead, but the principles won’t change (hopefully).

🥮

15 - Reflections from the Iceberg Meetup

Last month, I spoke at the Iceberg meetup at Snowflake HQ. Public speaking has never been my favorite, and knowing Snowflake CEO Sridhar and Tabular CEO Ryan were in the audience did not help.

It was day 18 of starting Mooncake Labs, and I spoke about a general indexing framework for Iceberg. You can find the details here. Here were some of my learnings:

1. Great products are a reflection of its community.

I spent the last decade building a proprietary database. Every time a feature request came in, engineering and product leaders would spend hours prioritizing its impact. Almost every time, it boiled down to one question: How much ARR? I watched my best engineers spend months building awkward hooks and extensions that did not fit the bigger picture. I often thought, Just ask the customer to make a PR :) The end product of this type of prioritization process tends to look like:

The Iceberg community feels very different. There’s a clear end goal, with contributors and users working together. Developer experience is at the center, which feels refreshing—like the right way to build things people love.

I’m excited to build in the open and bring a community around Mooncake. Successful products reflect the people behind them—both builders and users.

2. Holy sh*t it’s early days.

While much of the conversation focused on pushing the frontier of Iceberg—metadata caching, v3 format, optimized deletion files—there was also a lot of focus on the basics. I was often asked: “What is a catalog, and why do I need it?”

And, in talking with some AI developers and product engineers, these conversations feel even more foreign.

“wtf is Iceberg && what is the lake?”

These are developers with a bunch of files on S3 who would benefit from table semantics – they feel the pain of managing files, processing with Python, and serving processed datasets to apps. They should care about the lakehouse, but many don’t even know what it means.

It’s clear I live in a narrow bubble within the Twitter data stratosphere. The most sophisticated companies are consolidating around open table technologies, but there’s still a long way to go before they become standard tools for every product engineer.

First, the interfaces, syntax, and terminology need to be tailored to how modern applications are built. Setting up the infrastructure shouldn’t require a dozen data engineers.

Hopefully, Mooncake can play a role in accelerating that journey 🙂

3. Freakish performance is on the horizon. And then there will only be one.

Some of my friends at Databricks SQL, DuckDB, Snowflake, and StarRocks are optimizing their systems for open tables. I’ve seen how this typically unfolds—it starts with a 2x improvement and quickly compounds, leading to significant gains.

Stateful systems will always have a place, particularly in OLTP or HTAP. However, it’s hard not to envision a future where ML/AI processing, analytics (both backend and user-facing), and ad-hoc queries are fully stateless on these open tables.

I’m most excited about the work being done in adaptive query execution (PVLDB, 17(12): 3947 - 3959, 2024). This has been a long-standing interest of mine, but I was never able to convince people to build it effectively in my past experience. It’s great to see it being implemented on the lakehouse better than in most data warehouses!

–– In many ways, the era of open tables is similar to this AI era. There’s a lot of promise—some have already capitalized on it and won big. But it’s still very early days 🥮

16 - Things we learnt

The title should really be “Things we learnt, the hard way”.

As we start this new journey, we spent some time thinking about the axioms of data today. These are things we (or no one for that matter) can dispute.

1. If Postgres can do it, Postgres will do it

We’ve seen this one plenty of times now – it started with JSON, then moved to geo-spatial (PostGIS), and now we’re seeing it with vector search (pgvector).

Performance used to be the battle. It was a battle we played a lot in our past lives. It was a battle we also won. But somehow, we never won the war.

In our past lives, we built the fastest general-purpose vector database (amongst others); we even wrote some papers on it (SingleStore-V: PVLDB, 17: 3772 - 3785, 2024). But somehow, that wasn’t enough.

pgvector with its pretty average QPS and throughput –– won.

Why? It’s good enough, and it’s just there.

Never fight gravity. Postgres is gravity.

2. We are in innings 0 of the open table format era. Play ball.

Open table formats (Iceberg/Delta/Hudi) seem to be all the rage. Tabular didn’t hurt. Apparently, the world’s data is now suddenly all open. And seemingly, this happened in the last 6 months.

How? We were recently at the Iceberg meetup in San Francisco. We even gave a talk (more on that later). We were alongside Sridhar Ramaswamy, Ryan Blue, and some of the most skilled data engineers in the world. The meetup was 30 people –– largely representing two main customers. Tiny by SF standards. What’s all the hype about?

Open table formats haven’t eaten the world. Not even close, and definitely not yet. But they sure will. Open tables mean not much to not many just yet. But this will change. It really feels like the right architecture. Not just from an engineering standpoint. It is an architecture that can unite the community.

And when the community is united and builds deep conviction about a technology, you can bet some epic things are going to happen.

That’s what these open tables will do. The Avengers are being formed around open table formats. Are you going to fight it?

Entropy and change in the data landscape are constants

In our short tenure, we’ve witnessed:

• The Hadoop ecosystem rise and crumble
• MySQL dethroned
• The birth of terms like “big data” and, now somehow, “small data”

We sure could be in another one of these hype cycles. Maybe Postgres and open table formats don’t eat the world. But they do put the developer at its center and provide them an A+ experience. That is very exciting.

Developers always win. 🥮