MIME-Version: 1.0
References: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
In-Reply-To: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Wed, 4 Sep 2024 17:58:56 +0500
Message-ID: <CAPnRvGvHAC8HjLCZ2uUBCRZ7=fMJnkTPd3cBOchJu-Vn7G2MHg@mail.gmail.com>
Subject: Re: question on audit columns
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000513b6f06214abdb7"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGvHAC8HjLCZ2uUBCRZ7%3DfMJnkTPd3cBOchJu-Vn7G2MHg%40mail.gmail.com>
Precedence: bulk

--000000000000513b6f06214abdb7
Content-Type: text/plain; charset="UTF-8"

Hi,

In your scenario, triggers can add some overhead since they require extra
processing after each update operation. Considering the size of your table
and the high transaction volume, you need to observe that this might
significantly affect performance.


On Wed, 4 Sept 2024 at 17:50, yudhi s <learnerdatabase99@gmail.com> wrote:

> Hello,
> In postgres database , we have all the tables with audit columns like
> created_by_user, created_timestamp,updated_by_user, updated_timestamp. So
> we have these fields that were supposed to be populated by the time at
> which the insert/update operation happened on the database but not at the
> application level. So we are planning to populate the created_by_user,
> created_timestamp columns by setting a default value of "current_timestamp"
> and "current_user" for the two columns,  but no such this is available to
> populate while we do the update of the row, so the only option seems to be
> through a trigger.
>
> So wanted to check with the experts here  ,considering the table will be
> DML heavy table (300M+ transactions will be inserted daily), Is is okay to
> have the trigger for this table for populating all the audit columns or
> should we keep default for  created_by_user, created_timestamp and just
> trigger for the update related two audit column? Basically wanted to see,
> if the default value does the same thing as a trigger or it does something
> more optimally than trigger?
>
> Regards
> Yudhi
>

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

<div dir=3D"ltr"><div>Hi,</div><div><br>In your scenario, triggers can add =
some overhead since they require extra processing after each update operati=
on. Considering the size of your table and the high transaction volume, you=
 need to observe that this might significantly affect performance.=C2=A0 =
=C2=A0<div class=3D"gmail-yj6qo gmail-ajU" style=3D"outline:none;padding:10=
px 0px;width:22px;margin:2px 0px 0px"><br class=3D"gmail-Apple-interchange-=
newline"></div></div><div><div dir=3D"ltr" class=3D"gmail_signature" data-s=
martmail=3D"gmail_signature"><div dir=3D"ltr"><span></span><div><br></div><=
/div></div></div><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Wed, 4 Sept 2024 at 17:50, yudhi s &lt;<a href=3D"m=
ailto:learnerdatabase99@gmail.com">learnerdatabase99@gmail.com</a>&gt; wrot=
e:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr">Hello,<div>In postgres database , we have all the tables with audit col=
umns like created_by_user, created_timestamp,updated_by_user, updated_times=
tamp. So we have these=C2=A0fields that were supposed to be populated by th=
e time at which the insert/update operation happened on the database but no=
t at the application level. So we are planning=C2=A0to populate the created=
_by_user, created_timestamp columns by setting a default value of &quot;cur=
rent_timestamp&quot; and &quot;current_user&quot; for the two columns,=C2=
=A0 but no such this is available to populate while we do the update of the=
 row, so the only option seems=C2=A0to be through a trigger.=C2=A0</div><di=
v><br></div><div>So wanted to check with the experts here=C2=A0 ,considerin=
g the table will be DML heavy table (300M+ transactions will be inserted da=
ily), Is is okay to have the trigger for this table for populating all the =
audit columns=C2=A0or should=C2=A0we keep default for=C2=A0 created_by_user=
, created_timestamp and just trigger for the update related two audit colum=
n? Basically wanted to see, if the default value does the same thing as a t=
rigger or it does something more optimally than trigger?</div><div><br></di=
v><div>Regards</div><div>Yudhi</div></div>
</blockquote></div>

--000000000000513b6f06214abdb7--