DEV Community

Aniketh Deshpande
Aniketh Deshpande

Posted on

5

Change Data Capture - PostgreSQL

Change Data Capture is the concept of recording the changes in the database table fields.

  • It is very helpful in use cases where we want to track creation, updation, deletion of records in the table.

  • We might want to use this information to make changes in other databases or notify customers or notify other services.

  • Example:

    1. Save a copy of this data in a warehouse post transform.
    2. Trigger notification service to notify users about this change.
    3. Cache the data.

CDC In Postgres

  • Using Notify/Listen
    • NOTIFY provides a mechanism for interprocess communication between the database and the service that is LISTENing to this notification channel.
    • One or more services could be listening to this notification channel.
    • Name of this channel is usually the name of the database. However the user is free to set suitable names for these channels.
    • Any change in the table is captured by the DB and a trigger is initiated, which calls a function that formats the message to notify.
    • This usually contains the table name and the payload string.
    • The listening server registers to the channel and gets the message from the DB.
    • The service can then use this message and perform operations on it.

Pros:

  • Simple to implement. Use a trigger and a function to notify. Implement a listen service.

Cons:

  • Weak reliability. There is always a risk of loss of message especially when the listening service is down. Messages in the queue do not persist.

  • Using Debezium

    • Debezium is an open source tool used for capturing changes in the database tables based on the WAL (write ahead log).
    • The tool provides connectors to connect to a variety of databases.
    • The source connector is used to capture changes in the source database.
    • The sync connector is used to sync data directly in the destination database.

Pros:

  • The changes are persistent as they can be streamed to kafka. Hence highly reliable.

Cons:

  • Debezium does not take into account changes in the schema, users need to update the schema changes. Otherwise there would be data loss.

Thank you
Aniketh Deshpande

Image of Stellar post

From Hackathon to Funded - Stellar Dev Diaries Ep. 1 🎥

Ever wondered what it takes to go from idea to funding? In episode 1 of the Stellar Dev Diaries, we hear how the Freelii team did just that. Check it out and follow along to see the rest of their dev journey!

Watch the video

Top comments (1)

Collapse
 
rudu1 profile image
Rudram •

This article is really helpful.

Image of Stellar post

Check out Episode 1: How a Hackathon Project Became a Web3 Startup 🚀

Ever wondered what it takes to build a web3 startup from scratch? In the Stellar Dev Diaries series, we follow the journey of a team of developers building on the Stellar Network as they go from hackathon win to getting funded and launching on mainnet.

Read more

đź‘‹ Kindness is contagious

Dive into this informative piece, backed by our vibrant DEV Community

Whether you’re a novice or a pro, your perspective enriches our collective insight.

A simple “thank you” can lift someone’s spirits—share your gratitude in the comments!

On DEV, the power of shared knowledge paves a smoother path and tightens our community ties. Found value here? A quick thanks to the author makes a big impact.

Okay