public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khan Muhammad Usman <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: question on audit columns
Date: Wed, 4 Sep 2024 13:17:28 +0000
Message-ID: <DBAPR01MB6840804A91ABB765B0A07EACF29C2@DBAPR01MB6840.eurprd01.prod.exchangelabs.com> (raw)
In-Reply-To: <CAEzWdqfUWE=fuR5k1rd1iQx4K1-LceW3razY8f_tb9YONAZRMQ@mail.gmail.com>
References: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
<CAPnRvGvHAC8HjLCZ2uUBCRZ7=fMJnkTPd3cBOchJu-Vn7G2MHg@mail.gmail.com>
<CAEzWdqfUWE=fuR5k1rd1iQx4K1-LceW3razY8f_tb9YONAZRMQ@mail.gmail.com>
Yes this would be the better approach.
Sent from Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: yudhi s <[email protected]>
Sent: Wednesday, September 4, 2024 6:10:04 PM
To: Muhammad Usman Khan <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: question on audit columns
On Wed, Sep 4, 2024 at 6:29 PM Muhammad Usman Khan <[email protected]<mailto:[email protected]>> wrote:
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 <[email protected]<mailto:[email protected]>> 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
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?
view thread (5+ 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], [email protected]
Subject: Re: question on audit columns
In-Reply-To: <DBAPR01MB6840804A91ABB765B0A07EACF29C2@DBAPR01MB6840.eurprd01.prod.exchangelabs.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