MIME-Version: 1.0
References: <CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ@mail.gmail.com>
In-Reply-To: <CAKna9Vax9VahVdYNtbVEkKN6bcTYB84CkdqhD07Qh5Gog4tPFQ@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Fri, 26 Apr 2024 11:30:34 +0530
Message-ID: <CAEzWdqdsBZxiFSqOvJtej32u=jPXv-DM3_hfaBfi0zTyWH-Mrg@mail.gmail.com>
Subject: Re: How you make efficient design for CDC and book marking
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000c7f5c0616f9a0fa"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqdsBZxiFSqOvJtej32u%3DjPXv-DM3_hfaBfi0zTyWH-Mrg%40mail.gmail.com>
Precedence: bulk

--0000000000000c7f5c0616f9a0fa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

If you are worried about the audit trigger and also an additional audit
table to hold all the rows , then you may get an additional flag added to
your base table deleted_flag and when delete happens, you just need to
update that flag through your code manually, but not physically delete the
record from the base table (if you want to avoid the performance impact of
the row trigger on the base tables). So it will make the main table bulky
but will have lesser impact to your incoming data load to base or main
tables.

On Fri, Apr 26, 2024 at 1:56=E2=80=AFAM Lok P <loknath.73@gmail.com> wrote:

> 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 change=
s
> 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 a=
m
> 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. Hop=
e
> my understanding is correct here.
>
> Thus, in absence of such replication tool(may be because of the additiona=
l
> 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 th=
e
> 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 tabl=
e
> 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
>

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

<div dir=3D"ltr">If you are worried about the audit trigger and also an add=
itional audit table to hold all the rows , then you may get an additional f=
lag added to your base table deleted_flag and when delete happens, you just=
 need to update that flag through your=C2=A0code manually, but not physical=
ly delete the record from the base table (if you want to avoid the performa=
nce impact of the row trigger on the base tables). So it will make the main=
 table bulky but will have lesser impact to your incoming=C2=A0data load to=
 base or main tables.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Fri, Apr 26, 2024 at 1:56=E2=80=AFAM Lok P &lt;<a h=
ref=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">He=
llo,<br>My understanding is that the replication tools normally rely on the=
 database transaction logs to find the CDC/delta changes(Insert/Update/Dele=
te) for tables and then move those delta changes to the target system/datab=
ases. 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=
.<br><br>Thus, in absence of such replication tool(may be because of the ad=
ditional cost associated etc) if someone wants to find the delta changes (i=
nsert/update/delete) in a database as efficiently as possible and move thos=
e to the target database in a continuous data streaming setup, I can only t=
hink of below option....<br><br>i.e maintaining audit columns like create_t=
imestamp/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 book=
marking. But to find the delta for the deletes , there is not much option b=
ut 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 w=
e get a lot of deletes(in millions) on the source tables.<br><br>Want to kn=
ow from experts, if there exists any other way to have these manual CDC and=
 book marking more efficient for such continuous delta data movement scenar=
ios?<br><div><br></div><div>Regards</div><div>Lok</div></div>
</blockquote></div>

--0000000000000c7f5c0616f9a0fa--