public inbox for [email protected]help / color / mirror / Atom feed
question on audit columns 5+ messages / 4 participants [nested] [flat]
* question on audit columns @ 2024-09-04 12:49 yudhi s <[email protected]> 2024-09-04 12:58 ` Re: question on audit columns Muhammad Usman Khan <[email protected]> 2024-09-05 17:32 ` Re: question on audit columns Vincent Veyron <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: yudhi s @ 2024-09-04 12:49 UTC (permalink / raw) To: pgsql-general <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: question on audit columns 2024-09-04 12:49 question on audit columns yudhi s <[email protected]> @ 2024-09-04 12:58 ` Muhammad Usman Khan <[email protected]> 2024-09-04 13:10 ` Re: question on audit columns yudhi s <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Muhammad Usman Khan @ 2024-09-04 12:58 UTC (permalink / raw) To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]> 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]> 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 > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: question on audit columns 2024-09-04 12:49 question on audit columns yudhi s <[email protected]> 2024-09-04 12:58 ` Re: question on audit columns Muhammad Usman Khan <[email protected]> @ 2024-09-04 13:10 ` yudhi s <[email protected]> 2024-09-04 14:31 ` Re: question on audit columns Ron Johnson <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: yudhi s @ 2024-09-04 13:10 UTC (permalink / raw) To: Muhammad Usman Khan <[email protected]>; +Cc: pgsql-general <[email protected]> On Wed, Sep 4, 2024 at 6:29 PM Muhammad Usman Khan <[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]> 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? ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: question on audit columns 2024-09-04 12:49 question on audit columns yudhi s <[email protected]> 2024-09-04 12:58 ` Re: question on audit columns Muhammad Usman Khan <[email protected]> 2024-09-04 13:10 ` Re: question on audit columns yudhi s <[email protected]> @ 2024-09-04 14:31 ` Ron Johnson <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Ron Johnson @ 2024-09-04 14:31 UTC (permalink / raw) To: pgsql-general On Wed, Sep 4, 2024 at 9:10 AM yudhi s <[email protected]> wrote: > > On Wed, Sep 4, 2024 at 6:29 PM Muhammad Usman Khan <[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]> 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, > That's the simplest way. But the application can overwrite those fields. > 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? > How strict are the audit requirements? If they're really strict, you might need INSERT and UPDATE triggers that call security defined functions which write into a separate table not accessible by the application. That table would have the application table's PK, created_by_user, created_timestamp, updated_by_user and updated_timestamp. Would that table have a LOT of records? Sure. Would it add overhead? Sure. But the subsequently beefier hardware requirements and care in designing the physical schema (for example, audit tables in a separate tablespace and pg_wal/ on separate disk controllers, or a 10Gb SAN) are the price you pay for strict audit requirements. Of course, if the audit requirements are minimal, then sure, "default values and the application" are Good Enough. -- Death to America, and butter sauce. Iraq lobster! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: question on audit columns 2024-09-04 12:49 question on audit columns yudhi s <[email protected]> @ 2024-09-05 17:32 ` Vincent Veyron <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Vincent Veyron @ 2024-09-05 17:32 UTC (permalink / raw) To: yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]> On Wed, 4 Sep 2024 18:19:47 +0530 yudhi s <[email protected]> wrote: Hi, > 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. > If you can live with the fact that updated_by_user and updated_timestamp get the same values as created_by_user and created_timestamp when inserting the record, then you can do : vv=> create table audit (created_by_user text default current_user, created_timestamp timestamp default now(), updated_by_user text default current_user, updated_timestamp timestamp default now(), data text); CREATE TABLE vv=> insert into audit (data) values ('abc'); INSERT 0 1 vv=> select * from audit; created_by_user | created_timestamp | updated_by_user | updated_timestamp | data -----------------+----------------------------+-----------------+----------------------------+------ vincent | 2024-09-05 19:17:53.446109 | vincent | 2024-09-05 19:17:53.446109 | abc (1 row) --as user postgres update audit set updated_by_user = DEFAULT, updated_timestamp = DEFAULT, data = 'def'; vv=> select * from audit; created_by_user | created_timestamp | updated_by_user | updated_timestamp | data -----------------+----------------------------+-----------------+---------------------------+------ vincent | 2024-09-05 19:17:53.446109 | postgres | 2024-09-05 19:24:01.19186 | def (1 row) -- Bien à vous, Vincent Veyron https://marica.fr/ Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des contrats ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-05 17:32 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-04 12:49 question on audit columns yudhi s <[email protected]> 2024-09-04 12:58 ` Muhammad Usman Khan <[email protected]> 2024-09-04 13:10 ` yudhi s <[email protected]> 2024-09-04 14:31 ` Ron Johnson <[email protected]> 2024-09-05 17:32 ` Vincent Veyron <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox