Postgres Pro
Downloads
Home   >   mailing lists

Re: Performance opportunities for write-only audit tables? - Mailing list pgsql-general

From David G. Johnston
Subject Re: Performance opportunities for write-only audit tables?
Date
Msg-id CAKFQuwaCw9ZVXw_YerWVbReEKcQW4uj2q3O5WuQj=ntpAp2Frw@mail.gmail.com
Whole thread Raw
In response to Re: Performance opportunities for write-only audit tables?  (Andrew Bartley <ambartley@gmail.com>)
Responses Re: Performance opportunities for write-only audit tables?  (Andrew Bartley <ambartley@gmail.com>)
List pgsql-general
Tree view
On Thursday, May 24, 2018, Andrew Bartley <ambartley@gmail.com> wrote:
Hi,

The two main techniques we use are.

The idea here is to backup the rest of your DB to one backup regime and the log tables to another. We set it up so at the end of the day the current log table is backed up and loaded into an backup archive, then we vacuum freeze the log table.

Yeah, doing logging in-database for immediate performance while periodically copying or moving said data to external storage seems like the best option.  Depends on the definition of an acceptable process and response time should the audit data be needed though.
 

2. Use  UNLOGGED in the log table creates.

Seriously?  For audit tables?

David J.

pgsql-general by date:

Previous
From: Andres Freund
Date:
Subject: Re: found xmin from before relfrozenxid on pg_catalog.pg_authid
Next
From: Andrew Bartley
Date:
Subject: Re: Performance opportunities for write-only audit tables?