From: Tim Clarke <timc@minerva.info>
To: Adrian Klaver <adrian.klaver@aklaver.com>, Khan Muhammad Usman
	<usman.k@bitnine.net>, yudhi s <learnerdatabase99@gmail.com>
CC: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: question on audit columns
Thread-Topic: question on audit columns
Thread-Index: AQHa/sj/ap6n7lAIDkO0tNqm4cTuiLJHltQAgAADHQCAAAIRAIAAFhaAgAAAQwU=
Date: Wed, 4 Sep 2024 14:48:19 +0000
Message-ID:
 <LO4P123MB4671919E3F0E6914264CEA58A39C2@LO4P123MB4671.GBRP123.PROD.OUTLOOK.COM>
References:
 <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
 <CAPnRvGvHAC8HjLCZ2uUBCRZ7=fMJnkTPd3cBOchJu-Vn7G2MHg@mail.gmail.com>
 <CAEzWdqfUWE=fuR5k1rd1iQx4K1-LceW3razY8f_tb9YONAZRMQ@mail.gmail.com>
 <DBAPR01MB6840804A91ABB765B0A07EACF29C2@DBAPR01MB6840.eurprd01.prod.exchangelabs.com>
 <b683944c-63f2-456f-a423-efe743fc0769@aklaver.com>
In-Reply-To: <b683944c-63f2-456f-a423-efe743fc0769@aklaver.com>
Accept-Language: en-GB, en-US
Content-Language: en-GB
msip_labels:
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=minerva.info;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: LO4P123MB4671:EE_|CWXP123MB3142:EE_
x-ms-office365-filtering-correlation-id: a0f6be8d-239f-44ce-7c24-08dcccf09dd8
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;ARA:13230040|376014|366016|1800799024|38070700018;
x-microsoft-antispam-message-info:
 =?iso-8859-1?Q?I8ReYgsziZortzm54Ag67BJZrz0M0gfl+BNKHnDnUcUYdd2WHQjK7kiJs4?=
 =?iso-8859-1?Q?TLDhJazuc3zElPgkM/R1tqX5Oy5u66B+/V6IWjVSFsl4pm+fXWOjadajTY?=
 =?iso-8859-1?Q?G12JUDqHpYpARWLoypzpjb8C32zqp+bCGp7xS65+m8e2D/xzo+gNA70/SX?=
 =?iso-8859-1?Q?b2W4yoEF5Gou/l+M1mIl9OyvRew28QFO6mAjZPMC2WsxOOsiWUXvTVK9PA?=
 =?iso-8859-1?Q?7I6vPKFKI3ogqm8aS+jr0XC3ddd1eunzPbW7tO1QfJTsuMC6GMZjSD6xVV?=
 =?iso-8859-1?Q?vkDIjtCvsOnD9Frv+R7ZgE9NnPhka4NHupDiQQK+C8Aaqhpss7E3x2PKFT?=
 =?iso-8859-1?Q?d+X2rLIrsJ6FUVrhLpjXOJ5H7w/RTLVgfkQbZ11c9LHIb1GRz+u1X+4VlZ?=
 =?iso-8859-1?Q?EfhE2kjb30FJsSs7vjTXcQofkWCtx0djeCyVto/bG17/PR8pLFc/N/g9LY?=
 =?iso-8859-1?Q?wdZLYNBfHYFPl344rZgaeYKuy57T2mq5jzZIFTEiXljhl/7S1Id7R1xAaP?=
 =?iso-8859-1?Q?eIOrNW2sllqrAvRSWWUM6Dsbqg0KZHGL7Ei4LMekSJLG1rMUDaQ91iyrx3?=
 =?iso-8859-1?Q?7hjRpu859qYYtk6BCWJ6Fz3+HH9GU+O/eOeAlK18J6Q8bzCGnNxf1H1fkd?=
 =?iso-8859-1?Q?H70FXlWqKBLLHZGktW4Ae17mG4TENlJY88KHAyDgQHLqtVBrxxJI6CJmnV?=
 =?iso-8859-1?Q?PdQ7PsNoxZwYOmceRgihD9zl2jRgcYDYeU+bAouzAIeTzU4KcP+5Bh7kvW?=
 =?iso-8859-1?Q?9FUo2zJVi9egJKivJIBQFcNsTffkmuLgLAF/h1Um9oaTCjtCA8WQyHi7Rq?=
 =?iso-8859-1?Q?NFYAxIMS0iJCHkMroUske6saMFeQOsVtYgxFicAFdqCSeeL0FOZaagq+U/?=
 =?iso-8859-1?Q?iAUjeRHUoEMtKP0Q60wZYP6PG9FHXzO+kI34DoyWwjKK8OFsn5+2+eCiRa?=
 =?iso-8859-1?Q?zep0/j/TOdGxml7hILZOSvloe8AntRmIYlUnIteWf2tqVJQZ3bs2BSXG82?=
 =?iso-8859-1?Q?OiOnbvdlg52Lgrf2+p0A/SOBTJLZ5FHZVLihU33RYaAF61hPKGR2RIvB4C?=
 =?iso-8859-1?Q?0Pq1DMX6E/KuBMt9m9h9SaY2tEII7w2oFVqEmj0o0aru/69Sc8cMAqxPTn?=
 =?iso-8859-1?Q?158m1Jpw6xWYZ2r7FDglC88yyo6ej5AV/jllwR1suxmQuiEx3+3oVvOi61?=
 =?iso-8859-1?Q?cXA/OWp4UPvstfawP6KBFB5+ZK+3Gl8jIYsEd7bYoTTRaZ3L2V1cg2A38e?=
 =?iso-8859-1?Q?YV5fZsG5SiiiogpqGYgfHrXa0vG/difuF7vaLYGbeYAWS4NJEkQNiDY6uV?=
 =?iso-8859-1?Q?NjkZC1QGu+9xiy80C8yc9+KbEwQ+/5pJTj17smtaY5SubgQ7MaJitHnnM6?=
 =?iso-8859-1?Q?eOA6T08OAAVYIGmschquJrrGz73XF6I3XwfXD6LIwnOujzy2wIFgf3d1Ul?=
 =?iso-8859-1?Q?3UT/nbZRZyLw3jJ90k1iVMmFZxUD7NpVadSV8Q=3D=3D?=
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:LO4P123MB4671.GBRP123.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(38070700018);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-8859-1?Q?DCiLaJk+fnkuQ1Fq2ydm9IuWLMUAol6Tbl1GnIf5F1ooEGHbedQhF3+Dir?=
 =?iso-8859-1?Q?pf66GFp3k4bSF/6CASr8ezL65u+wri36wp4C7jbKWWCcXtNZRlH6jND8Lj?=
 =?iso-8859-1?Q?sWRXOEYJpB2N+9OhvGqpNFhx6p5ecY159LYZCo51Xz8BZ9N3HAS3UfdNlh?=
 =?iso-8859-1?Q?7c0lEnTNqBZxPY3l9HQSgd99pn2F016jZZXI3JlW0/s1gTuL0Z2TWu+zAH?=
 =?iso-8859-1?Q?+H/WBsfRVdU7IetSYAMDRRy1BqKXwHWsEI5lsHYy1moXgJ80yJrn6kjipg?=
 =?iso-8859-1?Q?Sf7xA7aKYo/Zodg4fMrS5sZqIn5r4icbGN8vWNL5YwQbE909QU5Xrsk7Ks?=
 =?iso-8859-1?Q?zoZ9pivpRgGsaLCtEibI1iyjsdFNL1vNRQ9OX2pPXzCY5BAdkUz94o9iPi?=
 =?iso-8859-1?Q?uZugI4dkxsqGRXMqzhPGc7ueGIAh1J6AxYKLi7jUCKb2SPHoAnKYQt3C9/?=
 =?iso-8859-1?Q?OWg67BsrAGDHJealY3a4mTwA3uNvYIxuToY6q3RvDRq9MVRoZmUf277KqO?=
 =?iso-8859-1?Q?pvFForId11rlPJhVqnx+AfkNDkPV8l8SnW0ypxDzYkJc7XOBi0pzeClv21?=
 =?iso-8859-1?Q?bRwGJHV0raihKqF0Sd9mDUATqb7BQq+xa9Rv5KGcfj/O1nSwF/0c4leCbU?=
 =?iso-8859-1?Q?bLqjCmIB+A7FCFM+pEbCgbcwruCpbr3jgya9P80SoD2UCIwEmHZncyYs/H?=
 =?iso-8859-1?Q?XAab7xNpjxDKMcTUQFz/r/04FEX/grX420E9rrhQTCL+FeEIyv85nFElqH?=
 =?iso-8859-1?Q?Yj+65afZ9LBrgUpzGP6qDMLZwaoYrUDP4tHXCYajO5XuFEoGi51vzyh5aM?=
 =?iso-8859-1?Q?KeJAf8m1QjH0TQb5/FwQTeJbGvMV9iF4lnIE+ePUlPcFZTwBGza2HPBibq?=
 =?iso-8859-1?Q?7FJe4dLWV533WqTIrU68RBohAWyQ2K5PEtPwVrMJMcmzO/S6DharexyDkS?=
 =?iso-8859-1?Q?axKEBhJvb8MnNV5IRtM+3mAn8b60mzJ8U1ecLd6NQw+jSMDYaJMe/K2cV6?=
 =?iso-8859-1?Q?6gvbJdh/uT8XBOpyNw5KK6RFYxCmsgepvLsRoYZzy+OwNHW4tJzvR17EVj?=
 =?iso-8859-1?Q?udl2iTZ/A7uVsFBnbRz5ZaBbrWIdiG89tlfAfDlh8mI4GQEbHK9yIxsqTr?=
 =?iso-8859-1?Q?iuW/JERFGyOhdxBIwChKDm+WX7iNyG6ndVSMPDpt+MJwq/WDj9Tt0PZ0Kn?=
 =?iso-8859-1?Q?u9CjB36Ga1HR5SKhthjII9YMrz93Iam03ghcV+EUn1RTgOPzWd4ZJsvnfW?=
 =?iso-8859-1?Q?9UM0BnXHN8Uvpk5gh1rX8WGXxwJ1Y9HnWoUb2UdJY09HN2ab7jF3NR/y2D?=
 =?iso-8859-1?Q?BjxMCXYyiogf/owKsHc7eFlSZu6JveuBB5LaGH+QiCKu/tGtm43ZrLbhX4?=
 =?iso-8859-1?Q?VuiXdzd8WHRZWibwqGwKOoRR5iRvw8JNf/Mv4vxonyVw0arOEhpXZuFRe2?=
 =?iso-8859-1?Q?P2cOO+mWRzX23AiCyAwSsEp+mcox2jySoQRULu+qqSoibcayBx5xGKSIeL?=
 =?iso-8859-1?Q?24qm5Gps0x+H08IYOBPrmiKDgrAM/cFo3RCz9z0Nxxo6mfAA9SXYWe7KpR?=
 =?iso-8859-1?Q?emVObwOinOYePWJSBItcN572EbtjRnrxn+v7D6vKx0Y9FPkCmPR2Q8pI2j?=
 =?iso-8859-1?Q?yTww6jOlXxolQ=3D?=
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: minerva.info
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: LO4P123MB4671.GBRP123.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: a0f6be8d-239f-44ce-7c24-08dcccf09dd8
X-MS-Exchange-CrossTenant-originalarrivaltime: 04 Sep 2024 14:48:19.1989
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 0316a1f9-91d0-4449-95da-7e8dd555cc52
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: WPw6UZ5rmOmkdfaJfhHtq3egzmxLC0+/TFF8JdYwkaO9HAK1WEPI9gv7LpuvTyK+krSDVsHZSTQdwwT/DMpfpQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CWXP123MB3142
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/LO4P123MB4671919E3F0E6914264CEA58A39C2%40LO4P123MB4671.GBRP123.PROD.OUTLOOK.COM>
Precedence: bulk

>
> On 9/4/24 06:17, Khan Muhammad Usman wrote:
> > Yes this would be the better approach.
>
> 1) Except the overhead is now shifted to the application, which may or
> not be better. You are also moving the audit responsibility to the
> application and the application maintainers and making it application
> specific. If a new application/client starts hitting the database and it
> did not get the memo about the audit fields they won't be filled in.
>
> 2) I would recommend setting up a some realistic tests and see if the
> overhead of the update triggers would be a concern.
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

If it helps, we implemented a trigger based audit system 20 years ago. It b=
oth creates a separate inviolate audit table record and updates the record =
being changed with a timestamp and a userid of last change. We've not regre=
tted it and moderate hardware deals well with the overhead (500+ table data=
base, 80+ concurrent users, 18 million audit records a month).

--
Tim Clarke MSc, BSc (Hons), MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420
[https://i0.wp.com/www.manifest.co.uk/wp-content/uploads/2022/12/Minerva-An=
alytics-Logo-PORTRAIT.png]    [https://i0.wp.com/www.manifest.co.uk/wp-cont=
ent/uploads/2022/12/esg_finalist.png]


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankf=
urt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 =
2848
Web: https://www.manifest.co.uk/

Watch our latest Minerva Briefings on BrightTALK<https://www.brighttalk.com=
/channel/18792/?utm_source=3Dbrighttalk-sharing&utm_medium=3Dweb&utm_campai=
gn=3Dlinkshare>


Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Ki=
ngdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged infor=
mation. If you are not the named addressee you must not use or disclose suc=
h information, instead please report it to admin@minerva.info<mailto:admin@=
minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Reg=
istered in England Number 11260966 & The Manifest Voting Agency Ltd: Regist=
ered in England Number 2920820 Registered Office at above address. Please C=
lick Here https://www.manifest.co.uk/legal/ for further information.