In previous articles, we have introduced two articles talking about audit logs that track the changes of data values stored in the PostgreSQL database. In part 1, we were talking about steps to set up the audit log by using a single table to store all the logs when there are changes on the target tables we applied the audit. In part 2, we talked about basic usages and commands to query for the report of audit logs. These two parts have been a good approach so far we have seen in the area of data value change tracking or audit log. However, recently we run into an issue which we wanted to share with you in this article and the solution we approach this problem.

Our approach with storing log data in a single table was fine with a small system that doesn't have many transactions per month such as Human Resource System where we store employee information and it is unlikely we will massively update employee information per month. In contrast to the payment system that operates on a daily basis and records every transaction whenever the buyer needs to pay for their goods purchasing. In this case, the table that stores the audit log data drastically grows in a short period of time. Therefore, the disk space of the database server will be shortly full due to this rapid growth of audit log data.

In order to solve the above issue, we have updated the steps we prepared in part 1 by using the partitioning technique on the single table we used to store log data. By doing this we have the ability to separate the log data into separate partitions (Divided by each month)  and can quickly detach the old partition and archive to separate storage (Cold storage disk space) whenever the DB server disk space is reaching the limit. Below are the modified steps we have applied.

STEP 1: Create Logged Actions Table with Partition Column

We add a trans_status column to be a key of partitioning technique

CREATE TABLE audit.logged_actions ( 
event_id bigserial, 
schema_name text not null, 
table_name text not null, 
relid int4 not null, 
session_user_name text, 
action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, 
action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, 
transaction_id bigint, 
application_name text, 
client_addr inet, 
client_port integer, 
client_query text, 
action TEXT NOT NULL CHECK (action IN ('I','D','U', 'T')), 
row_data hstore, 
changed_fields hstore, 
statement_only boolean not null, 
trans_status text not null, 
CONSTRAINT "logged_actions_pkey" PRIMARY KEY ("event_id", "trans_status")) 
PARTITION BY LIST(trans_status);

Create separate partition tables until the year 2025 (You can define the different ranges of partition ex. until the year 2030)

--Partition Tables Until Year 2025
-- DEFAULT
CREATE TABLE "audit"."logged_actions_default" PARTITION OF "audit"."logged_actions" DEFAULT;
-- Year 2021
CREATE TABLE "audit"."logged_actions_202101" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202101');
CREATE TABLE "audit"."logged_actions_202102" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202102');
CREATE TABLE "audit"."logged_actions_202103" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202103');
CREATE TABLE "audit"."logged_actions_202104" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202104');
CREATE TABLE "audit"."logged_actions_202105" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202105');
CREATE TABLE "audit"."logged_actions_202106" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202106');
CREATE TABLE "audit"."logged_actions_202107" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202107');
CREATE TABLE "audit"."logged_actions_202108" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202108');
CREATE TABLE "audit"."logged_actions_202109" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202109');
CREATE TABLE "audit"."logged_actions_202110" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202110');
CREATE TABLE "audit"."logged_actions_202111" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202111');
CREATE TABLE "audit"."logged_actions_202112" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202112');
-- Year 2022
CREATE TABLE "audit"."logged_actions_202201" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202201');
CREATE TABLE "audit"."logged_actions_202202" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202202');
CREATE TABLE "audit"."logged_actions_202203" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202203');
CREATE TABLE "audit"."logged_actions_202204" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202204');
CREATE TABLE "audit"."logged_actions_202205" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202205');
CREATE TABLE "audit"."logged_actions_202206" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202206');
CREATE TABLE "audit"."logged_actions_202207" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202207');
CREATE TABLE "audit"."logged_actions_202208" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202208');
CREATE TABLE "audit"."logged_actions_202209" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202209');
CREATE TABLE "audit"."logged_actions_202210" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202210');
CREATE TABLE "audit"."logged_actions_202211" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202211');
CREATE TABLE "audit"."logged_actions_202212" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202212');
-- Year 2023
CREATE TABLE "audit"."logged_actions_202301" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202301');
CREATE TABLE "audit"."logged_actions_202302" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202302');
CREATE TABLE "audit"."logged_actions_202303" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202303');
CREATE TABLE "audit"."logged_actions_202304" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202304');
CREATE TABLE "audit"."logged_actions_202305" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202305');
CREATE TABLE "audit"."logged_actions_202306" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202306');
CREATE TABLE "audit"."logged_actions_202307" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202307');
CREATE TABLE "audit"."logged_actions_202308" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202308');
CREATE TABLE "audit"."logged_actions_202309" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202309');
CREATE TABLE "audit"."logged_actions_202310" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202310');
CREATE TABLE "audit"."logged_actions_202311" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202311');
CREATE TABLE "audit"."logged_actions_202312" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202312');
-- Year 2024
CREATE TABLE "audit"."logged_actions_202401" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202401');
CREATE TABLE "audit"."logged_actions_202402" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202402');
CREATE TABLE "audit"."logged_actions_202403" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202403');
CREATE TABLE "audit"."logged_actions_202404" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202404');
CREATE TABLE "audit"."logged_actions_202405" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202405');
CREATE TABLE "audit"."logged_actions_202406" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202406');
CREATE TABLE "audit"."logged_actions_202407" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202407');
CREATE TABLE "audit"."logged_actions_202408" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202408');
CREATE TABLE "audit"."logged_actions_202409" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202409');
CREATE TABLE "audit"."logged_actions_202410" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202410');
CREATE TABLE "audit"."logged_actions_202411" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202411');
CREATE TABLE "audit"."logged_actions_202412" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202412');
-- Year 2025
CREATE TABLE "audit"."logged_actions_202501" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202501');
CREATE TABLE "audit"."logged_actions_202502" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202502');
CREATE TABLE "audit"."logged_actions_202503" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202503');
CREATE TABLE "audit"."logged_actions_202504" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202504');
CREATE TABLE "audit"."logged_actions_202505" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202505');
CREATE TABLE "audit"."logged_actions_202506" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202506');
CREATE TABLE "audit"."logged_actions_202507" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202507');
CREATE TABLE "audit"."logged_actions_202508" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202508');
CREATE TABLE "audit"."logged_actions_202509" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202509');
CREATE TABLE "audit"."logged_actions_202510" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202510');
CREATE TABLE "audit"."logged_actions_202511" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202511');
CREATE TABLE "audit"."logged_actions_202512" PARTITION OF "audit"."logged_actions" FOR VALUES IN ('202512');

STEP 2: MODIFY TRIGGER FOR VALUE OF trans_status

The modification here is to automatically generate a value of the field trans_status based on the year and month in which the transaction happened. Below is the code to generate the value of the column trans_status and the full modification of trigger

- Code generate the value of the column trans_status

--Partition field: trans_status
CONCAT(LPAD(date_part('year', (SELECT current_timestamp))::text,4,'0'), LPAD(date_part('month', (SELECT current_timestamp))::text,2,'0'))

- Full modification of trigger

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
DECLARE
    audit_row audit.logged_actions;
    include_values boolean;
    log_diffs boolean;
    h_old hstore;
    h_new hstore;
    excluded_cols text[] = ARRAY[]::text[];
BEGIN
    IF TG_WHEN <> 'AFTER' THEN
        RAISE EXCEPTION 'audit.if_modified_func() may only run as an AFTER trigger';
    END IF;
    audit_row = ROW(
        nextval('audit.logged_actions_event_id_seq'), -- event_id
        TG_TABLE_SCHEMA::text,                        -- schema_name
        TG_TABLE_NAME::text,                          -- table_name
        TG_RELID,                                     -- relation OID for much quicker searches
        session_user::text,                           -- session_user_name
        current_timestamp,                            -- action_tstamp_tx
        statement_timestamp(),                        -- action_tstamp_stm
        clock_timestamp(),                            -- action_tstamp_clk
        txid_current(),                               -- transaction ID
        current_setting('application_name'),          -- client application
        inet_client_addr(),                           -- client_addr
        inet_client_port(),                           -- client_port
        current_query(),                              -- top-level query or queries (if multistatement) from client
        substring(TG_OP,1,1),                         -- action
        NULL, NULL,                                   -- row_data, changed_fields
        'f',                                          -- statement_only
		CONCAT(LPAD(date_part('year', (SELECT current_timestamp))::text,4,'0'), LPAD(date_part('month', (SELECT current_timestamp))::text,2,'0')) --partition field
        );
    IF NOT TG_ARGV[0]::boolean IS DISTINCT FROM 'f'::boolean THEN
        audit_row.client_query = NULL;
    END IF;
    IF TG_ARGV[1] IS NOT NULL THEN
        excluded_cols = TG_ARGV[1]::text[];
    END IF;
    IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = hstore(OLD.*) - excluded_cols;
        audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
        IF audit_row.changed_fields = hstore('') THEN
            -- All changed fields are ignored. Skip this update.
            RETURN NULL;
        END IF;
    ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = hstore(OLD.*) - excluded_cols;
    ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = hstore(NEW.*) - excluded_cols;
    ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN
        audit_row.statement_only = 't';
    ELSE
        RAISE EXCEPTION '[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
        RETURN NULL;
    END IF;
    INSERT INTO audit.logged_actions VALUES (audit_row.*);
    RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public;
COMMENT ON FUNCTION audit.if_modified_func() IS $body$
Track changes to a table at the statement and/or row level.
Optional parameters to trigger in CREATE TRIGGER call:
param 0: boolean, whether to log the query text. Default 't'.
param 1: text[], columns to ignore in updates. Default [].
         Updates to ignored cols are omitted from changed_fields.
         Updates with only ignored cols changed are not inserted
         into the audit log.
         Almost all the processing work is still done for updates
         that ignored. If you need to save the load, you need to use
         WHEN clause on the trigger instead.
         No warning or error is issued if ignored_cols contains columns
         that do not exist in the target table. This lets you specify
         a standard set of ignored columns.
There is no parameter to disable logging of values. Add this trigger as
a 'FOR EACH STATEMENT' rather than 'FOR EACH ROW' trigger if you do not
want to log row values.
Note that the user name logged is the login role for the session. The audit trigger
cannot obtain the active role because it is reset by the SECURITY DEFINER invocation
of the audit trigger its self.
$body$;

That's it for the change. All the other steps are the same and you can always check them out through this link. By doing this, we are making sure that every audit log record is stored in different partition tables separate monthly, and below we give an example of how we can detach and attach back the partition if necessary.

-- detach partition from main table, detach partition of DEC.2021
ALTER TABLE audit.logged_actions DETACH PARTITION audit.logged_actions_202112;
-- attach partition to main table, attach partition of DEC.2021
ALTER TABLE audit.logged_actions ATTACH PARTITION audit.logged_actions_202112 FOR VALUES IN ('202112');

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn