From: | Andrew Bartley <ambartley(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Performance opportunities for write-only audit tables? |
Date: | 2018-05-24 21:42:06 |
Message-ID: | CAA3RN+yAUdMEObx0R350mXxeybpE87ByeXZESN_fe-GEn=gosg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2. Use UNLOGGED in the log table creates.
Seriously? For audit tables?
I guess that depends on what the log tables are used for.... Also in this
case the logs are written to via a trigger.... I all within one
transaction. So it may not matter.
We use UNLOGGED because the audit logs are not financial, and used only
very rarely, and only for analysis of access and performance in a batch
setting. Also certainly not via a trigger, as we don't want the logging
bound to the transaction.
On Fri, 25 May 2018 at 07:17 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> On Thursday, May 24, 2018, Andrew Bartley <ambartley(at)gmail(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | tango ward | 2018-05-25 00:15:45 | Re: Insert data if it is not existing |
Previous Message | David G. Johnston | 2018-05-24 21:17:46 | Re: Performance opportunities for write-only audit tables? |