MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Fri, 26 Apr 2024 01:55:49 +0530
Message-ID: <CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ@mail.gmail.com>
Subject: How you make efficient design for CDC and book marking
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007131980616f19839"
Archived-At: <https://www.postgresql.org/message-id/CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ%40mail.gmail.com>
Precedence: bulk

--0000000000007131980616f19839
Content-Type: text/plain; charset="UTF-8"

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

--0000000000007131980616f19839
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<br>My understanding is that the replication tools n=
ormally 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 o=
pen 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 understand=
ing is correct here.<br><br>Thus, in absence of such replication tool(may b=
e because of the additional cost associated etc) if someone wants to find t=
he delta changes (insert/update/delete) in a database as efficiently as pos=
sible and move those to the target database in a continuous data streaming =
setup, I can only think of below option....<br><br>i.e maintaining audit co=
lumns like create_timestamp/update_timestamp columns in every source table =
so that they can be utilized to get the CDC for Insert and Update statement=
s and also for bookmarking. But to find the delta for the deletes , there i=
s 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=
.<br><br>Want to know from experts, if there exists any other way to have t=
hese manual CDC and book marking more efficient for such continuous delta d=
ata movement scenarios?<br><div><br></div><div>Regards</div><div>Lok</div><=
/div>

--0000000000007131980616f19839--