The Three Main Approaches To Postgres CDC

 Before going into the types of Postgres CDC, let us briefly understand the two as individual entities.

PostgreSQL or Postgres is an open-source relational database and is widely used for carrying out multiple functions such as data warehousing, OLTP workloads, and business analytics. 

Change Data Capture (CDC) is a software design pattern. It tracks and monitors changes made to a database and takes the necessary action based on those changes.

When you combine the two – Postgres CDC – users get several benefits

Now, let us go into detail about the three types of Postgres CDC.

# Trigger-based Postgres CDC

Here it is possible to identify changes such as Insert, Update, and Delete in the table of interest. A changelog can be created for each change by inserting a row into a change table. In this type of Postgres CDC changes captured are stored in PostgreSQL only.

The downside here is that the performance of Postgres is adversely affected as triggers increase the execution time of the original statement.

# Query-based Postgres CDC

In this model, a timestamp column shows when a row has been last changed in the tracked database. Here, unlike the previous type, Postgres CDC captures only Insert and Update events and not Delete changes. 

The downside here is that this approach requires repeated refreshes of the tracked tables and hence, resources are wasted if there are no changes.

# Logical Replication-based Postgres CDC

This type of Postgres CDC quickly replicates data between different PostgreSQL instances, even though they might be running on different systems. There is a write-ahead log on a disk that reflects all change events like Delete, Update, and Insert.

The downside here is that versions of PostgreSQL older than 9.4 do not support logical replication.