Lakebase Postgre updating Delta Table.

[rkhbo3003]

I am using Postgre for OLTP processing for POS application.Lag is reduced a lot, however when there is updation on Postgre table, I need to sync back to delta table. There is one way from delta table sync table (read only). Any design pattern sas to keep delta table and postgre table in sync.

[balajij8]

@rkhbo3003 

You can use Lakebase Change Data Feed (CDF) for it. It is the pattern for this Postgres to Delta ingestion path. It continuously streams every INSERT, UPDATE and DELETE from the Lakebase Postgres write-ahead log (WAL) directly into Unity Catalog Delta tables with short latency. You don't need additional external CDC tooling or maintain custom streaming clusters. You can enable REPLICA IDENTITY FULL on the Postgres tables and start the feed directly from the Lakebase UI. POS transactions land automatically in Delta as lb_pos_transactions_history appended with standard CDC metadata columns (_pg_change_type, _pg_commit_lsn and _pg_commit_timestamp).

It completes a fully managed bi-directional sync loop with zero custom ETL overhead combined with the existing Delta-to-Postgres synced tables

OLTP Writes (Postgres) - POS terminals write to writable Postgres tables for millisecond latency transaction processing. OLTP to Delta (Lakebase CDF) WAL changes stream asynchronously to Delta history tables for analytics and downstream consumption.
Delta to Postgres (Synced Tables) - Curated reference data such as product catalogs, pricing and customer dimensions replicates back to Postgres for fast, read-only lookups

Lakebase CDF only generates append only change logs (lb_<table_name>_history). It does not maintain an inplace and current state replica table out of the box. To maintain a live current-state table such as pos_transactions_current, you can use the CDC history table and process the change feed downstream. You can run a periodic MERGE statement on a scheduled job every few minutes, or create an auto updating Lakeflow Streaming Table / Materialized View that applies the CDC events continuously. It provides an immutable audit trail in the _history table while delivering an up-to-date snapshot for operational analytics. You can check the bi directional sync here

More details here

[srini_ve]

If you are using Databricks Lakebase Postgres, I think Lakebase CDF is a good fit for this use case.

Since the POS application is already writing to Postgres for OLTP processing, I would keep Postgres as the system of record and use Lakebase CDF to asynchronously propagate the INSERT, UPDATE, and DELETE changes into Delta.

The overall pattern would be:

POS Application → Lakebase Postgres → Lakebase CDF → Delta

And for the reverse direction:

Delta / Unity Catalog → Lakebase Synced Tables → Lakebase Postgres

One important point is that these are two separate capabilities rather than a single bidirectional replication feature. CDF provides an append-only change history in Delta, so if you also need a current-state Delta table, you would process the CDF events downstream using a Lakeflow pipeline/streaming approach or a MERGE.

For this use case, it should significantly reduce the need for custom CDC tooling and polling-based table comparisons.

The main things I would consider before using it in production are that Lakebase CDF is currently Public Preview and the source tables need REPLICA IDENTITY FULL enabled.

For a POS workload where PostgreSQL needs to remain responsive for OLTP while Delta is used for analytics.