The production architecture
A reliable Postgres CDC to Kafka pipeline has five explicit stages: PostgreSQL logical replication, a unique replication slot, a CDC connector such as Debezium, durable Kafka topics and idempotent downstream consumers. For real-time analytics, a consumer such as Tinybird can ingest the topic, preserve the raw event envelope and build a current-state or history model for low-latency queries.
The setup is often drawn as one arrow, but the operational boundaries matter. PostgreSQL guarantees that a slot can retain WAL needed by its reader. The connector translates database changes and tracks offsets. Kafka provides durable distribution and consumer groups. The destination decides how updates, deletes, ordering and duplicates become queryable data. Reliability comes from the contracts between these stages, not from any single product.
This guide uses Debezium terminology because it makes the mechanics visible. Confluent Cloud's managed Postgres CDC connector follows the same broad path while reducing the infrastructure a team operates. Validate current availability and connector-specific settings in the vendor documentation before production.
Decide the data contract before provisioning
List the source tables, expected change rate, peak transaction size, required operations and acceptable lag. Identify whether consumers need a row's current state, every historical transition or business events derived from changes. CDC captures database facts; it does not automatically produce the clean domain events that an event-driven application would design.
Choose keys deliberately. A stable primary key should be present on each captured table. Decide how deletes will be represented and whether personally identifiable fields should be excluded at the source. Estimate backfill size separately from steady-state throughput: an initial snapshot can be the largest load the pipeline ever sees.
Write recovery objectives. How long can the connector be unavailable before retained WAL threatens source storage? How far behind may analytics become? How will the team rebuild the destination if offsets or schemas become unusable? These answers set storage headroom, retention and alert thresholds.
Configure PostgreSQL and AWS RDS safely
Self-managed PostgreSQL needs wal_level = logical, adequate max_replication_slots and adequate max_wal_senders. Create a least-privilege replication identity and grant it access only to the schemas and tables the connector needs. Use pgoutput unless a specific requirement justifies another plugin.
Create a publication for the selected tables and a unique slot for the connector. Managing them explicitly is often preferable in production because names, ownership and lifecycle stay under infrastructure control. Never casually drop a slot to clear disk: doing so discards the retained position and normally forces a new snapshot or controlled resynchronization.
On Amazon RDS for PostgreSQL, enable the static rds.logical_replication parameter in the DB parameter group and reboot the instance. AWS also documents the rds_replication role for managing logical slots and streaming changes. Review the generated WAL and instance storage after enabling the feature, not only after the connector goes live.
For updates and deletes, confirm replica identity. Primary-key tables usually provide what the connector needs. Use REPLICA IDENTITY FULL only where necessary and load-test the extra WAL it creates.
Configure Debezium or a managed connector
A Debezium PostgreSQL connector needs a database address, replication user, database name, topic prefix, plugin, slot and publication. Filter the table list at the connector or publication level rather than capturing the entire database and discarding most events downstream. Use encrypted network connections and a secret manager; configuration examples should never contain production credentials.
The connector takes or coordinates an initial snapshot according to its snapshot mode, then continues reading from the recorded WAL position. Choose the mode from the lifecycle: a new destination normally needs existing rows, while a replacement consumer may need to resume at a known position. Test snapshot duration, locks and storage rather than assuming the default is harmless for a large table.
Configure heartbeats. A quiet captured database can make a healthy connector appear idle and may delay LSN acknowledgement in some multi-database scenarios. Debezium documents heartbeat records and a heartbeat action query for advancing progress. Monitor both the connector's offset and PostgreSQL's slot views.
Schema history and serialization are part of the contract. JSON without a registry is easy to inspect but can be verbose and permissive. Avro or schema-aware JSON can enforce compatibility but adds registry operation. Pick one format, document compatibility rules and test a real migration such as adding a nullable column.
Design Kafka topics for replay, ordering and consumers
Debezium normally emits a table's changes to a table-specific topic. Partitioning must preserve the ordering needed by the consumer. Using the row key as the Kafka message key keeps changes for one entity in the same partition, while the total order across unrelated rows is normally neither available nor necessary.
Set retention from recovery needs, not from habit. If the analytical sink can be rebuilt from Kafka, retention must cover the maximum expected outage plus operational response time. If Kafka is only transient transport and the raw destination is the replay source, document that distinction. Compaction can help keep the latest keyed record, but it is not an immediate deduplication service and tombstone semantics require testing.
Use separate consumer groups for independent destinations. Within one group, Kafka distributes partitions among instances for scaling; it does not give every instance a complete copy. Track consumer lag per group and alert on sustained growth rather than short snapshot spikes.
Connect Kafka to Tinybird for real-time analytics
Tinybird's Kafka connector consumes a configured topic into a Data Source. Its settings include the Kafka connection, topic, consumer group and offset reset policy, with support for JSON and Avro-style formats. Use a dedicated consumer group and choose earliest or latest only after deciding whether the source must ingest retained history.
Keep a raw CDC Data Source with operation, key, source timestamp and version/LSN fields. Then create a modeled layer. A current-state table can use a versioned replacement strategy and a deletion flag; a change-history table remains append-only. Do not aggregate an update stream as though every version were a new sale or user.
Tinybird notes that background replacement merges are eventual. Queries that require current correctness need an appropriate query-time strategy, and complex aggregates over mutable state may need a snapshot or lambda-style design. The deduplication guide lays out the available patterns.
Validate the complete path
Start with a synthetic table and a traceable set of operations: insert row A, update it twice, insert and delete row B, and commit several changes in one transaction. Record the expected source state and expected event history. Verify the connector envelope, topic key, partition, destination rows and served API result.
Then test failure. Stop the connector while continuing writes and confirm WAL retention and catch-up. Restart a destination consumer from an earlier offset and confirm duplicates do not change results. Introduce a compatible schema change. Pause the destination until lag grows. Finally, test a controlled database failover and measure the gap and recovery steps.
Reconciliation should compare source and modeled destination by count, key ranges and representative hashes or aggregates. A healthy connector status is not proof that every destination table is correct.
Production checklist
- One named publication and one unique slot per independent connector.
- Alerts for retained WAL, free source storage and inactive slots.
- Connector offsets and schema history stored durably.
- Kafka retention longer than the agreed recovery window.
- Stable keys and explicit delete/tombstone behavior.
- Idempotent destination writes and measurable duplicate rate.
- Runbooks for snapshot, resnapshot, failover and schema rejection.
- End-to-end freshness metric from source commit to served query.
FAQ
Does Postgres CDC require Kafka?
No. A CDC client can deliver directly to another system. Kafka is valuable when several consumers, durable replay, buffering or an existing streaming platform justify the extra component.
Can Tinybird read Postgres WAL directly?
The architecture documented here uses a CDC tool to read PostgreSQL logical changes and publish them to Kafka-compatible infrastructure, which Tinybird consumes. Tinybird is the analytical destination and API layer, not the WAL reader in this path.
What should Kafka's offset reset policy be?
Use earliest when a new group must ingest retained history and latest when it should start only from new records. Confirm the choice against the snapshot and recovery plan before creating the consumer group.
Sources checked September 5, 2026: Debezium PostgreSQL connector documentation, AWS RDS logical replication guidance, PostgreSQL logical-decoding documentation and Tinybird's Kafka and deduplication guides.