Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slr2v-001Z4e-Uh for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 14:31:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1slr2u-00AbXL-Iy for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 14:31:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slr2u-00AbVY-3M for pgsql-general@lists.postgresql.org; Wed, 04 Sep 2024 14:31:44 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1slr2r-0007eT-Rm for pgsql-general@postgresql.org; Wed, 04 Sep 2024 14:31:43 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-5e172cc6d97so1796297eaf.1 for ; Wed, 04 Sep 2024 07:31:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725460301; x=1726065101; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=orFYBxKIqTva7GOiHV8xFOIVEMsxfq9elFiWSG9s7iQ=; b=NeneV3KUvjQwA1lPj6ySnt9HFff6nSeKEk3YdD6xecGizt00QnHf/AEjl71ykzFSnw E/YkClqZI7F3Xh+tyzvJY79MBve4GkSGmTc26ii9gO1qKaVGwCc6tpSgqqjBNKh2SBJZ U8UTuIc07A2pmdnuoW27B5pKYgpYn47rQ0mHViFnDgJElQ4Y+D2mH5xGKzsomSjLGfHX 4DgMzSfLK54QnkunJMytz9d2eG4PtjGBFOfP3GGRPnRK358/xLwVV26Le7Jy2ZVc0vR2 Ya2yBpfPjVo+wp8fkNlz8wZL6YmsrAF2yE4rQGkAMnHBqtm14FGsg9/JnWE+WXzq8i73 lsQw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725460301; x=1726065101; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=orFYBxKIqTva7GOiHV8xFOIVEMsxfq9elFiWSG9s7iQ=; b=iF8lN1LUhDu6Uni+Ib+NrvavLXqb5J8SxJyW/syWh24o3tyElrhjNda9/nuIAZDrBt 1rEBAO2lCy5yTbyofAyM4gDdn8KK6XW5mPaSK75RjMSxUuNlb3XY3gNzbMczbU/by6oR T+/Ie9Jx9RbLujXoF8ReHsXIoeCbclFxeLjnjXQHl4XDs2dWq45+DKOAvhq4BJP4bJDi HFANikAap5KLJ9fyYAmo5YPKmdmOZeGYlh4sZ/9AItFUQxJODBqiCbD3XfmdaDWClz4u lD6C5AaC420Q6Z8LxMw9BIBiGunQZVx+u+drDZjps7hZK1T5YVnb33AdaIU4qZLCx6f4 AukQ== X-Gm-Message-State: AOJu0YzJPCz5ETrrPoijxhV4K4zmW3iAocybLtnR83sgi42bUC/D0Lpq CKX6xBfIRg2/pgAgj8+zYknHsHBCtnwpUtfIjPzfhIvKv6cQWsA+uzVRISJPqzMpknEGWYpVQSM G3VjGyupeTE+lCu/Rv+yh3v61tugKfg== X-Google-Smtp-Source: AGHT+IEssPfe5CF2iSpnRdxgns2nAI61AhvUm6HV5c0HaVfKwGsAsfWPuxunn4T5ORDO53EyeakVWYaZcacXEgPd5jc= X-Received: by 2002:a05:6870:2408:b0:25e:1ced:744 with SMTP id 586e51a60fabf-27790367911mr23832164fac.47.1725460300796; Wed, 04 Sep 2024 07:31:40 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Wed, 4 Sep 2024 10:31:29 -0400 Message-ID: Subject: Re: question on audit columns To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004d987206214c0805" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004d987206214c0805 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Sep 4, 2024 at 9:10=E2=80=AFAM yudhi s wrote: > > On Wed, Sep 4, 2024 at 6:29=E2=80=AFPM Muhammad Usman Khan > wrote: > >> Hi, >> >> In your scenario, triggers can add some overhead since they require extr= a >> processing after each update operation. Considering the size of your tab= le >> 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 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 t= he >>> application level. So we are planning to populate the created_by_user, >>> created_timestamp columns by setting a default value of "current_timest= amp" >>> 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 b= e >>> 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 se= e, >>> if the default value does the same thing as a trigger or it does someth= ing >>> 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 a= nd > 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. --=20 Death to America, and butter sauce. Iraq lobster! --0000000000004d987206214c0805 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Sep 4, 2024 at 9:10=E2=80=AFAM yu= dhi s <learnerdatabase99@= gmail.com> wrote:

<= /div>
On We= d, Sep 4, 2024 at 6:29=E2=80=AFPM Muhammad Usman Khan <usman.k@bitnine.net> wrote:<= br>
Hi,

In your scenario, triggers can add some overhead si= nce 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.=C2=A0 =C2=A0
=
=C2=A0
On Wed, 4 Sept 2024 at 17:50, yu= dhi 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=C2=A0fields = that were supposed to be populated by the time at which the insert/update o= peration happened on the database but not at the application level. So we a= re planning=C2=A0to populate the created_by_user, created_timestamp columns= by setting a default value of "current_timestamp" and "curr= ent_user" 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=A0t= o be through a trigger.=C2=A0

So wanted to check w= ith the experts here=C2=A0 ,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=C2=A0or should=C2=A0we = keep default for=C2=A0 created_by_user, created_timestamp and just trigger = for the update related two audit column? Basically wanted to see, if the de= fault value does the same thing as a trigger or it does something more opti= mally than trigger?

Regards
Yudhi
<= /div>

Thank you so much.= So do you mean to say that , we should add default values for the create_t= imestamp and create_user_id as current_timestamp and current_user,=C2=A0

That's the simplest way= .=C2=A0 But the application can overwrite those fields.
=C2=A0
but for update_user_id and update_timestamp , w= e can ask the application to update the values manually , whenever=C2=A0the= y are executing the update statement on the rows?=C2=A0

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.=C2=A0 That t= able would have the application table's PK, created_by_user, created_ti= mestamp, updated_by_user and updated_timestamp.

Wo= uld that table have a LOT of records?=C2=A0 Sure.
Would it add ov= erhead?=C2=A0 Sure.

But the subsequently=C2=A0beef= ier hardware requirements and care in designing the physical schema (for ex= ample, audit tables in a separate tablespace and pg_wal/ on separate disk c= ontrollers, or a 10Gb SAN) are the price you pay for strict audit requireme= nts.

Of course, if the audit requirements are mini= mal, then sure, "default values and the application" are Good Eno= ugh.

-- <= br>
Death to Ame= rica, and butter sauce.
Iraq lobster!
--0000000000004d987206214c0805--