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

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

On Wed, Sep 4, 2024 at 6:29=E2=80=AFPM Muhammad Usman Khan <usman.k@bitnine=
.net>
wrote:

> Hi,
>
> In your scenario, triggers can add some overhead since they require extra
> processing after each update operation. Considering the size of your tabl=
e
> 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. S=
o
>> 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 th=
e
>> application level. So we are planning to populate the created_by_user,
>> created_timestamp columns by setting a default value of "current_timesta=
mp"
>> and "current_user" for the two columns,  but no such this is available t=
o
>> 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 somethi=
ng
>> more optimally than trigger?
>>
>> Regards
>> Yudhi
>>
>
Thank you so much. So do you mean to say that , we should add default
values for the create_timestamp and create_user_id as current_timestamp and
current_user,  but for update_user_id and update_timestamp , we can ask the
application to update the values manually , whenever they are executing the
update statement on the rows?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Wed, Sep 4, 2024 at 6:29=E2=80=AFPM Mu=
hammad Usman Khan &lt;<a href=3D"mailto:usman.k@bitnine.net">usman.k@bitnin=
e.net</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><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 op=
eration. 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 style=3D"outline:none;padding:10px 0px;width:22px;margin:2px=
 0px 0px"><br></div></div><div><div dir=3D"ltr" class=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"mailto:learnerdatabase99@gmail.=
com" target=3D"_blank">learnerdatabase99@gmail.com</a>&gt; wrote:<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"ltr">Hello,<d=
iv>In postgres database , we have all the tables with audit columns like cr=
eated_by_user, created_timestamp,updated_by_user, updated_timestamp. So we =
have these=C2=A0fields that were supposed to be populated by the time at wh=
ich the insert/update operation happened on the database but not at the app=
lication level. So we are planning=C2=A0to populate the created_by_user, cr=
eated_timestamp columns by setting a default value of &quot;current_timesta=
mp&quot; and &quot;current_user&quot; for the two columns,=C2=A0 but no suc=
h this is available to populate while we do the update of the row, so the o=
nly option seems=C2=A0to be through a trigger.=C2=A0</div><div><br></div><d=
iv>So wanted to check with the experts here=C2=A0 ,considering the table wi=
ll be DML heavy table (300M+ transactions will be inserted daily), Is is ok=
ay 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_tim=
estamp 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?</div><div><br></div><div>Regard=
s</div><div>Yudhi</div></div></blockquote></div></blockquote><div><br></div=
><div>Thank you so much. So do you mean to say that , we should add default=
 values for the create_timestamp and create_user_id as current_timestamp an=
d current_user,=C2=A0 but for update_user_id and update_timestamp , we can =
ask the application to update the values manually , whenever=C2=A0they are =
executing the update statement on the rows?=C2=A0</div></div></div>

--00000000000018c1c006214ae5aa--