Types of Change Data Capture Postgres

Postgres is a platform that performs multiple tasks such as data warehouse analytics and OLTP workloads. So, what are the benefits of Change Data Capture Postgres?

First, once data warehouses and other downstream systems are kept in sync with PostgreSQL, change events can be captured in real-time. Next, as changes linked to it are processed only, Change Data Capture Postgres reduces the load on Postgres. Finally, access to the changes made to Postgres can be efficiently implemented without changing the application code.

Click here for more on Change Data Capture Postgres

Types of Postgres CDC

# Change Data Capture Postgres – Trigger-based

Here, users can identify Insert, Delete, and Update changes taking place in the table of interest. A row has to be inserted into a change table to create a changelog for every change identified, and all change events are stored in the audit.logged actions. Postgres CDC only stores events that are captured within the database and where change events have to be linked to other data warehouses, the table in Postgres that has the changes have to be queried repeatedly.

On the downside, changes have to be made to the Postgres database for triggers to work effectively. 

# Change Data Capture Postgres - Query-based

In this process, Postgres is queried repeatedly using the timestamp column in it with the column showing the last time a row had changed. The downside is that unless it is soft deletions, it cannot capture Delete changes.

# Change Data Capture - Logical Replication-based

In this process, logical replication is made to the configuration file and automatically implemented by a decoding plugin. The downside is that it is not supported by versions older than 9.4