public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lok P <[email protected]>
To: pgsql-general <[email protected]>
Subject: How you make efficient design for CDC and book marking
Date: Fri, 26 Apr 2024 01:55:49 +0530
Message-ID: <CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ@mail.gmail.com> (raw)

Hello,
My understanding is that the replication tools normally rely on the
database transaction logs to find the CDC/delta
changes(Insert/Update/Delete) for tables and then move those delta changes
to the target system/databases. Whatever may be the source database (It
might be open source postgres or aws RDS). And never done though, but i am
assuming , manually scanning the DB logs must not be easy ones and also
might not be given access to DB logs because of security reasons too. Hope
my understanding is correct here.

Thus, in absence of such replication tool(may be because of the additional
cost associated etc) if someone wants to find the delta changes
(insert/update/delete) in a database as efficiently as possible and move
those to the target database in a continuous data streaming setup, I can
only think of below option....

i.e maintaining audit columns like create_timestamp/update_timestamp
columns in every source table so that they can be utilized to get the CDC
for Insert and Update statements and also for bookmarking. But to find the
delta for the deletes , there is not much option but to have row level
triggers created on the base table which will populate another audit table
with the deleted rows, and this is going to crawl if we get a lot of
deletes(in millions) on the source tables.

Want to know from experts, if there exists any other way to have these
manual CDC and book marking more efficient for such continuous delta data
movement scenarios?

Regards
Lok

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: How you make efficient design for CDC and book marking
  In-Reply-To: <CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox