The Functioning of the Postgres CDC

Postgres or PostgreSQL as it is known is a widely-used open-source relational database with a wide range of use cases such as data warehouse and analytics to carry out OLTP workloads. Change Data Capture or CDC is software that monitors changes made to source databases to enable performing certain actions based on those changes.

Now, taken together, there are many benefits of the Postgres CDC. First, by keeping data warehouses and other downstream systems in sync with PostgreSQL, change events can be captured in real-time. Next, CDC reduces the load on PostgreSQL as only the changes made to the data are processed. Finally, Postgres CDC helps in the seamless implementation of use cases that require access to changes made in PostgreSQL without modifying the application code. 

Types of Postgres CDC

• Trigger-based Postgres CDC: Changes such as Insert, Delete, and Update taking place in the table of interest can be identified. For each change, a row is inserted into a changing table, thereby building a changelog. However, this type of Postgres CDC stores events that are captured inside PostgreSQL only.  
• Query-based Postgres CDC: In this method, PostgreSQL has to be recurringly queried when the schema of the tracked database has a timestamp column that indicates the last time that a row has been changed. Query-based Postgres CDC cannot capture Delete and is limited to Update and Insert events.  
• Logical replication-based Postgres CDC: This system can quickly replicate data between different PostgreSQL instances on various systems. It is a write-ahead log on disk that comprises all events that change the data in the PostgreSQL database such as Insert, Update, and Delete.

These are the different types of Postgres CDC.