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 1rdNab-005IcY-1a for pgsql-admin@arkaria.postgresql.org; Fri, 23 Feb 2024 04:55:13 +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 1rdNaZ-00G2C2-KN for pgsql-admin@arkaria.postgresql.org; Fri, 23 Feb 2024 04:55:12 +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 1rdNaZ-00G2Br-5E for pgsql-admin@lists.postgresql.org; Fri, 23 Feb 2024 04:55:11 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rdNaT-000Xqr-JV for pgsql-admin@lists.postgresql.org; Fri, 23 Feb 2024 04:55:10 +0000 Received: by mail-ot1-x32a.google.com with SMTP id 46e09a7af769-6e478057f1aso146879a34.3 for ; Thu, 22 Feb 2024 20:55:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1708664105; x=1709268905; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :content-transfer-encoding:from:from:to:cc:subject:date:message-id :reply-to; bh=JYs66vtTjyWu0ejmjGbNFnaIzjQxgIHbR8d0mfH+kEE=; b=KR8nN2Y8NTau0cawDtT1KFbgzTmJgyeR97Sf0Z0FAnYdtWbPEDpMNZIhIawCON9UzL Hf/vV1S96l4X5CTZEK68rWgCFeY+Lhq8UMX7i5P1qj4fXdNZCgtHmmj5mKDDA/cXqEiL gxxLYTjkZkgCuC1PgOVc08hyXfMRbtjBMnGz2442Kt85KkP0Mmmev8cgTyDGZaHlpZ4f jPPb2byH8MgdSqVXwLBdII6FsuTw8cf9w2bqTazl/3gEfaVC/Y3y/eGpucOqxypOnD22 AmitVs6Zb6f7yokbfEFAk6KirkME0MzgGWGqUhNeNvDvDrose2G82uX+sGRGtLmNlIxF qE5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1708664105; x=1709268905; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :content-transfer-encoding:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=JYs66vtTjyWu0ejmjGbNFnaIzjQxgIHbR8d0mfH+kEE=; b=VGBYBji64WY7hIGVEtWHcHbZiVXJjlhCckS/yA3aeA6+AvUwD4J5k/2JXfXQh/qAxp PZwfPj9aL5utjCRRePLpQdmf/I+BdB+sYKrBSOc6aslL9VRhFQtx2F7wWVm2+xk7FXCb xd711Gxb7DhDN3tmySAZUYU1mEbngSEL3fFztY+USIM5GwTPZG0aJ150R589bKAXcXSM CbWSvzpAeYn9jRKHe7JdAOo18UlJeazJZq5nTZsyeD4LB/wOpKU1fADNRqrPFzqe3loX q+hxI+PqZkM6fhwKODqWMdOp17fr+LZLsKR2y5HCQP0lsEWKgqmw6Fa4tmGxCeUvy8GG T+RA== X-Gm-Message-State: AOJu0YyJjfW5eWcapQg9Q2cC2oKKZWefmEbJV74jAWsrGj0STysYFKpE b1wPxH2K3IoWfx3s23uBt1JhC3GF4POpHtLzBJ440cT0uy/3ZEJjKIUqBzF5 X-Google-Smtp-Source: AGHT+IFSO/OzMvjmSzPiZcQhPr775wUMivArG8F++EYRO58dr5PqROCs+ZAhYU1u/hwKxrAZbwA+jg== X-Received: by 2002:a05:6830:6581:b0:6e4:8307:4e94 with SMTP id cn1-20020a056830658100b006e483074e94mr93623otb.8.1708664104845; Thu, 22 Feb 2024 20:55:04 -0800 (PST) Received: from smtpclient.apple ([2600:1700:ced0:7620:d17f:2443:7383:eae]) by smtp.gmail.com with ESMTPSA id dr12-20020a056a020fcc00b005ce472f2d0fsm9720452pgb.66.2024.02.22.20.55.03 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 22 Feb 2024 20:55:04 -0800 (PST) From: S Hoffman X-Google-Original-From: S Hoffman Content-Type: multipart/alternative; boundary=Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (1.0) Subject: Re: Another way to do audit in DML operations in PostgreSQL >= 14 Date: Thu, 22 Feb 2024 20:54:52 -0800 Message-Id: References: Cc: PGSql Admin list In-Reply-To: To: Lucio Chiessi X-Mailer: iPhone Mail (21D61) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Lucio Chiessi,

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. 

Kind regards,
Sondra
<= br>

Se= nt from my iPhone

On Feb= 22, 2024, at 11:14=E2=80=AFAM, Lucio Chiessi <lucio.chiessi@trustly.com&= gt; wrote:

=EF=BB=BF
As a Fintech company, we MUST audi= t (by force of law) all DML operations (select, insert, update, delete) occu= rring on our PostgreSQL databases.
Today we used PostgreSQL log fi= les to do this and tested pgAudit too.
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.
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.  
So, I understand that the PostgreSQL logs rule is not to log everyt= hing happening in the database in a high TPS scenario.
<= 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 instead for selects.

So= , I'm looking for another way to this all DML audit and will appreciate so m= uch your opinion on this.

Thanks for all the advice= and have a happy week. 

Lucio Chiessi

Senior Database Administ= rator

Trustly, Inc.

M: +55 27 996360276

  

=     


Please read= our privacy policy here on how we process your personal data in accor= dance <= /span>with<= /span> = the<= 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)"> General Data Prote= ction Regulation (EU) 2016/679 (the =E2=80=9CGDPR=E2=80=9D) and other applicable data p= rotection legislation.
= --Apple-Mail-7D7CBCD5-3F09-431D-B76F-84E7E08D828D--