---
topic: system-design
author: Crashtech Editorial
published: Nov 14, 2026 · read: 5 min
---

From Sharded Postgres to a Data Lake: CDC, Hudi and Update-Heavy Workloads

Why warehouses struggle when most writes are updates, and how Debezium, Kafka, Hudi and Spark replaced 480 hourly connectors for 200 billion rows.

– –

Most data engineering diagrams assume rows are mostly inserted. In an editing product, the opposite is true: people change existing content constantly. That single fact decides whether your analytics pipeline is cheap and fresh or slow and expensive.

What they were storing

Everything in the editor is a block: text, headings, images, pages, lists. All blocks share one table structure, with parent links that form a tree. Hundreds of terabytes of compressed data, spread across Postgres shards: 32 physical instances with 15 logical shards each in 2021, then 96 instances with 5 logical shards each in 2023, always 480 logical shards in total. Adding hardware did not mean re-sharding the data.

Design one: a warehouse and a connector per shard

The first dedicated analytics stack was a simple ELT pipeline: a managed ingestion tool read each shard’s write-ahead log hourly, landed 480 raw tables in the warehouse, and a merge step combined them. It worked until three things bit.

Operational burden 480 connectors
Monitoring that many connectors, and re-syncing them every time Postgres was re-sharded, upgraded or maintained, created heavy on-call load.
Wrong shape for the writes update-heavy
Users edit existing blocks far more often than they add new ones. Warehouses favour inserts, so merging updates grew slower and costlier.
Logic outgrew SQL tree computation
Features like search and AI needed denormalised blocks with permissions. A block’s permissions are inherited up a tree to the workspace, so they must be computed by traversal, which is painful in standard SQL at billions of rows.
The deciding factor requirements
The third problem mattered most. Even a cheap, stable warehouse could not do the transformation the product needed.

Design two: a lake with stages

The goals: store raw and processed data at scale, ingest update-heavy data quickly and cheaply, and support denormalised data for search and AI.

Postgres shards --(Debezium CDC)--> Kafka --(Hudi Deltastreamer)--> S3 raw (Hudi tables)
                                                                     |
                                                      Spark: transform, denormalise, enrich
                                                                     |
                                                     S3 processed --> search, analytics, AI

The decisions, and why

  1. S3 as the lake

    Cheap, proven at scale, and the managed Postgres service could export snapshots straight into it, which made bootstrapping tables easy.

  2. Spark as the engine

    Beyond SQL, it offers user-defined functions for logic like tree traversal. PySpark covers most jobs for accessibility, Scala Spark handles heavy ones, and everything runs distributed.

  3. Incremental ingestion, with snapshots as the exception

    Applying changes continuously takes minutes to a couple of hours and is cheaper. A full snapshot dump took over ten hours and about twice the cost, so snapshots are reserved for bootstrapping.

  4. CDC through Kafka, then Hudi

    Debezium reads Postgres changes into Kafka. Of the open table formats considered (Hudi, Iceberg, Delta Lake), Hudi performed best for an update-heavy workload and integrated natively with Debezium messages.

  5. Raw first, process after

    Land the data untouched, as a single source of truth that simplifies debugging. Transform and enrich afterwards, and send only clean, business-critical results to downstream systems.

Why raw-first pays for itself

When a transformation has a bug, you re-run it over the raw lake. If you had transformed on the way in, the only fix is going back to production, which is slow, risky and sometimes impossible.

Advertisement

Making it scale

Connectors and topics. One Debezium connector per Postgres host, running on Kubernetes. One Kafka topic per table, shared by all 480 shards, so downstream ingestion maintains one stream per table instead of 480.

Hudi tuning for updates. Copy-on-write tables with the upsert operation, partitioned by the Postgres shard scheme, sorted by the change’s log position, and a bloom-filter index so Hudi can locate which files hold a given key without scanning.

Spark tuning for skewed shards. Small shards are loaded fully in memory, large shards use disk-based reshuffling, and the 480 shards are processed in parallel with multi-threading.

Bootstrapping a new table without gaps

The classic trap is snapshot plus stream with a hole between them. The safe order:

  1. Start capturing changes first

    Point the CDC connector at the table so changes begin accumulating in Kafka.

  2. Take a snapshot from a known timestamp

    Use the managed export-to-S3 feature to save the table as of that moment.

  3. Load the snapshot into the table format

    A Spark job reads the export and writes Hudi tables.

  4. Replay the log from the snapshot timestamp

    Configure the stream consumer to start from that timestamp, so changes made during snapshotting are applied. Upserts make any overlap harmless.

Results, and what they enabled

The project ran from spring to autumn of 2022. Reported outcomes: over a million dollars saved in 2022, with proportionally more in the following years, ingestion time down from over a day to minutes or hours, the ability to re-sync within 24 hours without overloading the databases, and headroom for data doubling every six to twelve months. The denormalised, permission-aware data it produced is what made the AI features possible.

Adapting it to a smaller shop

Your situationTake this
One Postgres, analytics on a replica works fineDo not build a lake. Use a replica or a warehouse
Warehouse merges are slow and costlyCDC into a table format that supports upserts
Many shards, many connectorsFewer connectors per host, one topic per table
Derived data keeps getting correctedKeep raw, transform in re-runnable stages
New table onboarding is error-proneScript the capture, snapshot, load, replay sequence

Do

Treat the shape of your writes as an architecture input. Measure update ratio first, keep a raw copy, make every stage re-runnable, and verify row counts and checksums after each bootstrap.

Don't

Pick the lake format by brand. An append-optimised warehouse and an upsert-optimised table format are different tools, and the workload picks the winner.

Takeaway

Analytics stacks break when the data’s write pattern does not match the storage’s design. For update-heavy products, capture changes from the database log, apply them as upserts in a table format built for it, keep the raw data, and do the heavy denormalisation in an engine that can run real code. Then fresh data is cheap, and the hard computations are possible.

Advertisement

Frequently asked questions

Why is an update-heavy workload hard for a data warehouse?

Warehouses are optimised for appending large batches and scanning columns. Frequent small updates to existing rows force expensive merges or rewrites of the files holding them. Content tools where users edit existing items far more than they create new ones hit this wall quickly, in both speed and cost.

What do Hudi, Iceberg and Delta Lake have in common?

All three are open table formats that add transactions, upserts and schema management on top of files in object storage. They differ in how they handle updates, indexing and engine integration. Hudi was designed with upsert-heavy ingestion from change streams as a core use case.

Why ingest raw data into the lake before transforming it?

A raw copy is a single source of truth that you can reprocess when logic changes or a bug is found, without going back to the production database. Transform and denormalize in later stages, and send only clean, business-critical results downstream.

How do you bootstrap a new table into a CDC-fed lake?

Start the change connector first so changes accumulate in the log, take a consistent snapshot of the table from a known timestamp, load it into the lake format, then replay the log from that timestamp. Snapshot plus replay from the same point leaves no gap and no duplicates.

Sources & further reading

/* Comments */