Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Advice on efficiently logging outputs to PostgreSQL
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <CAFCRh-9nu9LXb6+p_pp2iXRxp4mrfJz-2S_cormOo7X-ZkBv=g@mail.gmail.com>
Date: Tue, 15 Oct 2024 10:56:25 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <CFFA29A0-24DC-44EA-A27B-89ECE130C8BD@thebuild.com>
References: <CAFCRh-9nu9LXb6+p_pp2iXRxp4mrfJz-2S_cormOo7X-ZkBv=g@mail.gmail.com>
To: Dominique Devienne <ddevienne@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/CFFA29A0-24DC-44EA-A27B-89ECE130C8BD%40thebuild.com>
Precedence: bulk


> On Oct 15, 2024, at 07:17, Dominique Devienne <ddevienne@gmail.com> =
wrote:
> Am I worrying too much? :)

Probably. :-)  The main things I'd worry about is:

1. What's the ratio of log lines to database updates?  You want this to =
be as high as usefully possible, since in effect you are doing write =
amplification by writing to the logs as well as to the "real" database.

2. One of the things to watch out for if you are writing the log lines =
to the database is that if you write a log line in a transaction, and =
that transaction rolls back, you lose the log line.  That may or may not =
be what you want: if it's reporting an error (such as the reason that =
the transaction rolled back), you want to preserve that data.  One way =
of handling this is to have the application have a separate session for =
logging, although you are now doing 2x the number of connections.

If the write volume is very high, you might consider using a dedicate =
log-ingestion service (there are tons) rather than PostgreSQL, so that =
you aren't overburdening the database with log activity.=