From: S Hoffman <hoffman7043@gmail.com>
To: Lucio Chiessi <lucio.chiessi@trustly.com>
Cc: PGSql Admin list <pgsql-admin@lists.postgresql.org>
Subject: Re: Another way to do audit in DML operations in PostgreSQL >= 14
Date: Thu, 22 Feb 2024 20:54:52 -0800
Message-ID: <B0D1A942-4EAA-4DAC-9ADC-78D1F0FB9BB5@gmail.com> (raw)
In-Reply-To: <CADoTbHWMXZfuDDHQU__UhLLa51GSyNhNj0prNQ+BD2j83hFgkA@mail.gmail.com>
References: <CADoTbHWMXZfuDDHQU__UhLLa51GSyNhNj0prNQ+BD2j83hFgkA@mail.gmail.com>
--Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi Lucio Chiessi,<div><br></div><div>Dealin=
g with large volumes of data can be a challenging task. If you struggle with=
this issue, one possible solution is to consider using external logging ser=
vices. Specifically, asynchronous logging services or external logging servi=
ces that are designed to handle streaming data may be effective options to e=
xplore. Some popular external logging services in this regard include Elasti=
csearch and Spunk, known for their ability to handle large volumes of data. I=
f you have any questions or want to explore other options, please let me kno=
w. </div><div><br></div><div>Kind regards,</div><div>Sondra</div><div><=
br></div><div><br id=3D"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Se=
nt from my iPhone</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On Feb=
22, 2024, at 11:14=E2=80=AFAM, Lucio Chiessi <lucio.chiessi@trustly.com&=
gt; wrote:<br><br></blockquote></div><blockquote type=3D"cite"><div dir=3D"l=
tr">=EF=BB=BF<div dir=3D"ltr"><div>As a Fintech company, we <b>MUST</b> audi=
t (by force of law) all DML operations (select, insert, update, delete) occu=
rring on our PostgreSQL databases.</div><div>Today we used PostgreSQL log fi=
les to do this and tested pgAudit too.</div><div>In these cases (native log f=
iles and pgAudit) we having a lot of contention on backends and high values i=
n the instance CPU (system and wait %). This only happens when w=
e on high database TPS rate.</div><div>I think that an unique PostgreSQ=
L logger process can't attend all backend processes when trying to pipe=
data on it, causing contention and performance slowdown. </=
div><div>So, I understand that the PostgreSQL logs rule is not to log everyt=
hing happening in the database in a high TPS scenario.</div><=
div>I know that we can use triggers to have an audit process, but I need to a=
udit selects too, and rules only run as <font face=3D"monospace">instead</fo=
nt> for <font face=3D"monospace">selects</font>.</div><div><br></div><div>So=
, I'm looking for another way to this all DML audit and will appreciate so m=
uch your opinion on this.</div><div><br></div><div>Thanks for all the advice=
and have a happy week. </div><br clear=3D"all"><div><div dir=3D"ltr" c=
lass=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"=
><p class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><b><span style=3D"font=
-family:Arial,sans-serif;color:black">Lucio Chiessi</span></b><span style=3D=
"font-family:Arial,sans-serif;color:black"></span></p><p class=3D"MsoNormal"=
><font color=3D"#000000" face=3D"Arial, sans-serif">Senior Database Administ=
rator</font></p><p class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><span s=
tyle=3D"font-family:Arial,sans-serif;color:black">Trustly, Inc.</span></p><p=
class=3D"MsoNormal" style=3D"color:rgb(34,34,34)"><span style=3D"font-famil=
y:Arial,sans-serif;color:black">M: +55 27 996360276</span></p><p class=3D"Ms=
oNormal" style=3D"color:rgb(34,34,34)"><span style=3D"font-family:Arial,sans=
-serif;color:black"><a href=3D"https://www.linkedin.com/company/trustly/"; ta=
rget=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/mail-sig/AIorK=
4zAAE1IxyiCWzQH7LlbQvWzfPJSaezaeGcuUb-o7qLExY6u4Axla7Vg4M_47so109-UJzmqNN4" d=
ata-unique-identifier=3D""></a> <a href=3D"https://www.facebook.com/tru=
stly" target=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/mail-s=
ig/AIorK4woeblfRGYD_MkZYgAuhnmcGJAL4GQj8B8QakHtJQ-nv4myHxBkBwmGZsMMcd3EBwpHC=
FEr4gQ" data-unique-identifier=3D""></a> <a href=3D"https://twitter.com=
/Trustly" target=3D"_blank"><img src=3D"https://ci3.googleusercontent.com/ma=
il-sig/AIorK4yXMfV603SEnNTg-F3grGUtcmkk3sDyQKooDLBgTtARiC5_ZgV7zV8aiHfPIloh8=
EXtooI0x-I" data-unique-identifier=3D""></a><br></span></p><p class=3D"MsoNo=
rmal" style=3D"color:rgb(34,34,34)"><a href=3D"https://www.linkedin.com/comp=
any/trustly/" style=3D"color:rgb(17,85,204)" target=3D"_blank"><span style=3D=
"font-family:Arial,sans-serif;color:black"><img border=3D"0" width=3D"13" he=
ight=3D"13" src=3D"https://mail.google.com/mail/u/0/?ui=3D2&ik=3D42095eb=
97c&attid=3D0.2&permmsgid=3Dmsg-f%3A1686071028223994457&th=3D176=
620aabf5e6a59&view=3Dfimg&sz=3Ds0-l75-ft&attbid=3DANGjdJ831NJNFV=
3dlJGwvO0DLaa5HS945e1uJRwzE80C2njvV0E3-3Ub2-bC4YruPygt0CgkGv8LqQeYjUuQ4yOZpr=
J-XB3kUwDACfwxEPqEq04a89Ud0qnlqoHLaXk&disp=3Demb&realattid=3D567a03a=
bbf259cca_0.2" style=3D"width:0.1319in;height:0.1319in" data-unique-identifi=
er=3D""></span></a><span style=3D"font-family:Arial,sans-serif;color:black">=
</span><a href=3D"https://www.facebook.com/trustly"; style=3D"col=
or:rgb(17,85,204)" target=3D"_blank"><span style=3D"font-family:Arial,sans-s=
erif;color:black"><img border=3D"0" width=3D"13" height=3D"13" src=3D"https:=
//mail.google.com/mail/u/0/?ui=3D2&ik=3D42095eb97c&attid=3D0.3&p=
ermmsgid=3Dmsg-f%3A1686071028223994457&th=3D176620aabf5e6a59&view=3D=
fimg&sz=3Ds0-l75-ft&attbid=3DANGjdJ8xs4gf2euYOzZ5hKUPrHKTVTR_KqY4HxB=
oNxBbxHc4A2Ub-BW3w73N5Id3H9HGxk6nbPcTv8wlkaP8zHz43ky_NIATrae8l8DCyScEe-YNYRg=
iGx7YtSlJQsA&disp=3Demb&realattid=3D567a03abbf259cca_0.3" style=3D"w=
idth:0.1319in;height:0.1319in" data-unique-identifier=3D""></span></a><span s=
tyle=3D"font-family:Arial,sans-serif;color:black"> </span><a href=
=3D"https://twitter.com/Trustly"; style=3D"color:rgb(17,85,204)" target=3D"_b=
lank"><span style=3D"font-family:Arial,sans-serif;color:black"><img border=3D=
"0" width=3D"13" height=3D"13" src=3D"https://mail.google.com/mail/u/0/?ui=3D=
2&ik=3D42095eb97c&attid=3D0.4&permmsgid=3Dmsg-f%3A16860710282239=
94457&th=3D176620aabf5e6a59&view=3Dfimg&sz=3Ds0-l75-ft&attbi=
d=3DANGjdJ8O4xFm9b2d1oUr4ZuXkRoi3ZijR3_JV5Jze72hnMn9nzEngZMlfzEuVecy9PGS2UQg=
kjYyA7nMYWFQfBIViM_xMwg3kUlbSslhmQulckXH2XAegXUQjaLt1Us&disp=3Demb&r=
ealattid=3D567a03abbf259cca_0.4" style=3D"width:0.1319in;height:0.1319in" da=
ta-unique-identifier=3D""></span></a><span style=3D"font-family:Arial,sans-s=
erif;color:black"></span></p><p class=3D"MsoNormal" style=3D"color:rgb(34,34=
,34)"><span style=3D"font-family:Arial,sans-serif;color:black"><a href=3D"ht=
tps://www.trustly.net/" target=3D"_blank"><img width=3D"96" height=3D"20" sr=
c=3D"https://ci3.googleusercontent.com/mail-sig/AIorK4zqkSGmwos-7333cPVw-bXb=
sjnhpHvcpewFFK1mj6AcrGcf6a4q_rYJMjnKS0rJmDNBiZiSpTk" data-unique-identifier=3D=
""></a></span></p></div></div></div></div>
<br>
<span style=3D"font-family:-apple-system,system-ui,"Segoe UI",Robo=
to,Oxygen,Ubuntu,"Fira Sans","Droid Sans","Helvetic=
a Neue",sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pr=
e-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">Please read=
our privacy policy </span><strong style=3D"color:rgb(0,82,204);font-family:=
-apple-system,system-ui,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira=
Sans","Droid Sans","Helvetica Neue",sans-serif;fon=
t-size:14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb=
(255,255,255)"><span style=3D"color:rgb(102,102,102)"><a href=3D"https://www=
.trustly.net/about-us/privacy-policy" title=3D"https://www.trustly.net/about=
-us/privacy-policy" style=3D"color:rgb(0,82,204);font-family:-apple-system,s=
ystem-ui,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans",&qu=
ot;Droid Sans","Helvetica Neue",sans-serif;font-size:14px;let=
ter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)" t=
arget=3D"_blank">here</a> </span></strong><span style=3D"font-family:-apple-=
system,system-ui,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans&q=
uot;,"Droid Sans","Helvetica Neue",sans-serif;font-size:=
14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,25=
5,255);color:rgb(102,102,102)">on how we process your personal data in accor=
dance</span><span style=3D"color:rgb(23,43,77);font-family:-apple-system,sys=
tem-ui,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans","=
;Droid Sans","Helvetica Neue",sans-serif;font-size:14px;lette=
r-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> <=
/span><span style=3D"font-family:-apple-system,system-ui,"Segoe UI"=
;,Roboto,Oxygen,Ubuntu,"Fira Sans","Droid Sans","He=
lvetica Neue",sans-serif;font-size:14px;letter-spacing:-0.07px;white-sp=
ace:pre-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">with<=
/span><span style=3D"color:rgb(23,43,77);font-family:-apple-system,system-ui=
,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans","Droid=
Sans","Helvetica Neue",sans-serif;font-size:14px;letter-spac=
ing:-0.07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> </span>=
<span style=3D"font-family:-apple-system,system-ui,"Segoe UI",Robo=
to,Oxygen,Ubuntu,"Fira Sans","Droid Sans","Helvetic=
a Neue",sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pr=
e-wrap;background-color:rgb(255,255,255);color:rgb(102,102,102)">the</span><=
span style=3D"color:rgb(23,43,77);font-family:-apple-system,system-ui,"=
Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans","Droid Sans&q=
uot;,"Helvetica Neue",sans-serif;font-size:14px;letter-spacing:-0.=
07px;white-space:pre-wrap;background-color:rgb(255,255,255)"> </span><span s=
tyle=3D"font-family:-apple-system,system-ui,"Segoe UI",Roboto,Oxyg=
en,Ubuntu,"Fira Sans","Droid Sans","Helvetica Neue&=
quot;,sans-serif;font-size:14px;letter-spacing:-0.07px;white-space:pre-wrap;=
background-color:rgb(255,255,255);color:rgb(102,102,102)">General Data Prote=
ction Regulation</span><span style=3D"color:rgb(23,43,77);font-family:-apple=
-system,system-ui,"Segoe UI",Roboto,Oxygen,Ubuntu,"Fira Sans&=
quot;,"Droid Sans","Helvetica Neue",sans-serif;font-size=
:14px;letter-spacing:-0.07px;white-space:pre-wrap;background-color:rgb(255,2=
55,255)"> </span><span style=3D"font-family:-apple-system,system-ui,"Se=
goe UI",Roboto,Oxygen,Ubuntu,"Fira Sans","Droid Sans&quo=
t;,"Helvetica Neue",sans-serif;font-size:14px;letter-spacing:-0.07=
px;white-space:pre-wrap;background-color:rgb(255,255,255);color:rgb(102,102,=
102)">(EU) 2016/679 (the =E2=80=9CGDPR=E2=80=9D) and other applicable data p=
rotection legislation.</span></div></blockquote></div></body></html>=
--Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D--
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: pgsql-admin@postgresql.org
Cc: hoffman7043@gmail.com, lucio.chiessi@trustly.com, pgsql-admin@lists.postgresql.org
Subject: Re: Another way to do audit in DML operations in PostgreSQL >= 14
In-Reply-To: <B0D1A942-4EAA-4DAC-9ADC-78D1F0FB9BB5@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox