MediumIngesting Postgres Shards into a Redshift Data Warehouse with Kafka and Debezium | by Afnan Shahid | Jul, 2026 | TouchBistro EngineeringSitemapOpen in appSign up<br>Sign in

Medium Logo

Get app<br>Write

Search

Sign up<br>Sign in

TouchBistro Engineering

Solving hard challenges for amazing restaurants!

Featured

Ingesting Postgres Shards into a Redshift Data Warehouse with Kafka and Debezium

Afnan Shahid

12 min read·<br>3 days ago

Listen

Share

Background<br>At TouchBistro, our primary data source is the Point of Sale (POS) application, an edge application responsible for taking orders, managing tables, and handling other operations that happen in physical restaurants. A single location can run several POS clients at once, and data flows from the POS to our backend as JSON payloads over a REST API.<br>When a payload arrives, two things happen in parallel. The data is written to a sharded Postgres system that supports our operational use cases, exposing fine-grained entities like bills and shifts to both TouchBistro users and third parties. At the same time, to support analytical use cases, we ran that same payload through a separate pipeline: the raw JSON was written to an S3 bucket, an SQS job picked it up and hydrated it with additional context, turned it into CSV, and dropped it back in S3, where several AWS Lambda functions running every five minutes upserted the data into Redshift, our analytical store.

Press enter or click to view image in full size

Our legacy lambda-based ETL architecture

That second pipeline was brittle and hard to maintain. Something as routine as adding a new column meant touching application code at several stages of the pipeline, and a failure anywhere in the chain could silently leave analytics data stale without anyone noticing until a report looked wrong. It was a lot of moving parts and application code to solve what is, at heart, a simple problem: getting operational data into an analytical store.<br>The data was already sitting in Postgres. So we asked ourselves: What if we skipped all the hydration machinery and replicated the shards downstream to Redshift directly, using change data capture (CDC)?<br>CDC Primer<br>Change data capture is a technique for tracking row-level changes: every insert, update, and delete, as they happen in a database.The naive approach is to poll: query the tables on a schedule and diff the results. That’s slow, adds load to the database, and can miss changes that happen between polls. Log-based CDC avoids this. Every transactional database already keeps a write-ahead log (the WAL in Postgres) that records every change before it’s committed, primarily for crash recovery and replication. CDC reads from that log.<br>The tool we ended up reaching for is Debezium, an open-source, log-based CDC platform built on top of Kafka Connect. A Debezium source connector reads a database’s transaction log, turns each committed change into an event, and streams it to a Kafka topic. Because it’s a passive reader of a log the database already maintains, it captures every change in order with minimal overhead on the source, rather than hammering it with queries. From Kafka, those change events can fan out to whatever downstream systems need them. The piece that does that fanning-out is a sink connector: where a source connector reads changes into Kafka, a sink connector reads them back out and writes them to a target system, in our case, Redshift.<br>The False Starts<br>We didn’t want to build something ourselves if we didn’t have to, so we evaluated a few existing solutions first.<br>Fivetran and ZeroETL: We first considered Fivetran, but the cost was prohibitive and given the data engineering expertise we’d already built in-house, it was hard to justify it.<br>Next, we explored ZeroETL, a brand-new AWS offering aimed squarely at this problem: it replicates data from an Aurora PostgreSQL setup into Redshift. Unfortunately, it didn’t work with our sharded architecture. ZeroETL would have replicated every table in each of our ten physical shards as its own table in Redshift, which would have left us needing yet another process to merge the shards back into single logical tables, which is exactly the kind of complex machinery we were trying to get rid of. On top of that, it didn’t integrate with our observability tooling (we rely on Datadog), and it became apparent to us that the product was still in its infancy and hadn’t been battle-tested at scale.<br>Streaming: When exploring Debezium, the most direct implementation, a sink connector that writes straight into Redshift, with the JDBC Sink Connector, was the first thing we tried, and the first thing we abandoned.<br>The biggest limitation surfaced quickly, the sink connector doesn’t natively support a Redshift dialect. We tried using the Postgres dialect first, since Redshift speaks the Postgres wire protocol and is forked from PostgreSQL 8.0.2. But the dialect broke down in the upsert mode. In upsert mode, the connector’s Postgres dialect...