Blog post

Postgres Auditing in 150 lines of SQL


26 minute read

Data auditing is a system that tracks changes to tables' contents over time. PostgreSQL has a robust set of features which we can leverage to create a generic auditing solution in 150 lines of SQL.

Auditing is particularly useful for historical analysis. To demonstrate, imagine you have a users table that tracks when a user is online. You might add a status column which can have one of two values: online and offline. How would you track how long a user is online for throughout an entire month? An auditing system would track every change with timestamps, and so you can measure the difference between each timestamp and sum them up for the entire month.

The goals of our auditing solution are:

  • low maintenance
  • easy to use
  • fast to query

To demonstrate what we're working towards, the following example shows what we'll have at the end of the blog post:

1-- create a table
2create table public.members(
3    id int primary key,
4    name text not null
7-- Enable auditing on the new table
8select audit.enable_tracking('public.members');

Produce some records to audit

1-- create a new record
2insert into public.members(id, name) values (1, 'foo');
4-- edit the record
5update public.members set name = 'bar' where id = 1;
7-- delete the record
8delete from public.members;

Review the audit log

1select * from audit.record_history
1id | record_id | old_record_id | op | ts | table_oid | table_schema | table_name | record | old_record  
32 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | | INSERT | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | {"id": 1, "name": "foo"} |
43 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | UPDATE | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | {"id": 1, "name": "bar"} | {"id": 1, "name": "foo"}
54 | | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | DELETE | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | | {"id": 1, "name": "bar"}
6(3 rows)

Notice that our record_id and old_record_id stayed constant as we updated the row so we can easily query for a single row's history over time!

Lets get building


To quote a tenet from the zen of python:

Namespaces are one honking great idea -- let's do more of those!

So first things first, we'll create a separate schema named audit to house our auditing entities.

1create schema if not exists audit;


Next, we need a table to track inserts, updates and deletes.

Classically, an audit table's schema mirrors the table being audited and appends some metadata columns like the commit's timestamp. That solution has a few maintenance challenges:

  • enabling auditing on a table requires a database migration
  • when the source table's schema changes, the audit table's schema must also change

So instead, we'll lean on PostgreSQL's schema-less JSONB data type to store each record's data in a single column. That approach has the added benefit of allowing us to store multiple tables' audit history in a single audit table.

1create table audit.record_version(
2  id             bigserial primary key,
3  -- auditing metadata
4  record_id      uuid, -- identifies a new record by it's table + primary key
5  old_record_id  uuid, -- ^
6  op             varchar(8) not null, -- INSERT/UPDATE/DELETE/TRUNCATE
7  ts             timestamptz not null default now(),
8  -- table identifiers
9  table_oid      oid not null,  -- pg internal id for a table
10  table_schema   name not null, -- audited table's schema name e.g. 'public'
11  table_name     name not null, -- audited table's table name e.g. 'account'
12  -- record data
13  record         jsonb, -- contents of the new record
14  old_record     jsonb  -- previous record contents (for UPDATE/DELETE)
Postgres version compatibility

The table above uses PostgreSQL's built-in uuid functionality, which is available from version 14. For backwards compatibility you can use the uuid-ossp extension.

1create extension if not exists "uuid-ossp";

Query Patterns

An audit log doesn't do us much good if its too slow to query! There are 2 query patterns we think are table stakes (😉) for an audit system:

Changes to a Table in a Time Range

For time slices, we need an index on the ts column. Since the table is append-only and the ts column is populated by insertion date, our values for ts are naturally in ascending order.

PostgreSQL's builtin BRIN index can leverage that correlation between value and physical location to produce an index that, at scale, is many hundreds of times smaller than the default (BTREE index) with faster lookup times.

1-- index ts for time range filtering
2create index record_version_ts
3  on audit.record_version
4  using brin(ts);

For table filtering, we've included a table_oid column which tracks PostgreSQL's internal numeric table identifier. We can add an index to this column instead of the table_schema and table_name columns, minimizing the index size and offering better performance.

1-- index table_oid for table filtering
2create index record_version_table_oid
3  on audit.record_version
4  using btree(table_oid);

Changes to a Record Over Time

One of the downsides to storing each row's data as jsonb is that filtering based on a column's value becomes very inefficient. If we want to look up a row's history quickly, we need to extract and index a unique identifier for each row.

For the globally unique identifier, we'll use the following structure

1[table_oid, primary_key_value_1, primary_key_value_2, ...]

and hash that array as a UUID v5 to get an efficiently indexable UUID type to identify the row that is robust to data changes.

We'll use one utility function to lookup a record's primary key column names:

1create or replace function audit.primary_key_columns(entity_oid oid)
2    returns text[]
3    stable
4    security definer
5    language sql
6as $$
7    -- Looks up the names of a table's primary key columns
8    select
9        coalesce(
10            array_agg(pa.attname::text order by pa.attnum),
11            array[]::text[]
12        ) column_names
13    from
14        pg_index pi
15        join pg_attribute pa
16            on pi.indrelid = pa.attrelid
17            and pa.attnum = any(pi.indkey)
18    where
19        indrelid = $1
20        and indisprimary

and another to consume the table_oid and primary key, converting the result into the record's UUID.

1create or replace function audit.to_record_id(
2        entity_oid oid,
3        pkey_cols text[],
4        rec jsonb
6    returns uuid
7    stable
8    language sql
9as $$
10    select
11        case
12            when rec is null then null
13                        -- if no primary key exists, use a random uuid
14            when pkey_cols = array[]::text[] then uuid_generate_v4()
15            else (
16                select
17                    uuid_generate_v5(
18                        'fd62bc3d-8d6e-43c2-919c-802ba3762271',
19                        (
20                                                    jsonb_build_array(to_jsonb($1))
21                                                    || jsonb_agg($3 ->> key_)
22                                                )::text
23                    )
24                from
25                    unnest($2) x(key_)
26            )
27        end

Finally, we index the record_id and old_record_id columns that contain these unique identifiers for fast querying.

1-- index record_id for fast searching
2create index record_version_record_id
3    on audit.record_version(record_id)
4    where record_id is not null;
6-- index old_record_id for fast searching
7create index record_version_old_record_id
8    on audit.record_version(record_id)
9  where old_record_id is not null;


Okay, so we have a home for our audit data that we're confident it can be queried efficiently. Now how do we populate it?

We need the audit table to populate without end-users making any changes to their transactions. So we'll set up a trigger to fire when the data changes. In this case, we'll fire the trigger once for every inserted/updated/deleted row.

1create or replace function audit.insert_update_delete_trigger()
2    returns trigger
3    security definer
4    language plpgsql
5as $$
7    pkey_cols text[] = audit.primary_key_columns(TG_RELID);
8    record_jsonb jsonb = to_jsonb(new);
9    record_id uuid = audit.to_record_id(TG_RELID, pkey_cols, record_jsonb);
10    old_record_jsonb jsonb = to_jsonb(old);
11    old_record_id uuid = audit.to_record_id(TG_RELID, pkey_cols, old_record_jsonb);
14    insert into audit.record_version(
15        record_id,
16        old_record_id,
17        op,
18        table_oid,
19        table_schema,
20        table_name,
21        record,
22        old_record
23    )
24    select
25        record_id,
26        old_record_id,
27        TG_OP,
28        TG_RELID,
29        TG_TABLE_SCHEMA,
30        TG_TABLE_NAME,
31        record_jsonb,
32        old_record_jsonb;
34    return coalesce(new, old);

Public API

Finally, we'll wrap up the trigger creation and removal process behind a clean, idempotent, user facing API.

The API we'll expose for enabling auditing on a table is

1select audit.enable_tracking('<schema>.<table>'::regclass);

and for disabling tracking

1select audit.disable_tracking('<schema>.<table>'::regclass);

Under the hood, those functions register our auditing trigger against the requested table.

1create or replace function audit.enable_tracking(regclass)
2    returns void
3    volatile
4    security definer
5    language plpgsql
6as $$
8    statement_row text = format('
9        create trigger audit_i_u_d
10            before insert or update or delete
11            on %I
12            for each row
13            execute procedure audit.insert_update_delete_trigger();',
14        $1
15    );
17    pkey_cols text[] = audit.primary_key_columns($1);
19    if pkey_cols = array[]::text[] then
20        raise exception 'Table % can not be audited because it has no primary key', $1;
21    end if;
23    if not exists(select 1 from pg_trigger where tgrelid = $1 and tgname = 'audit_i_u_d') then
24        execute statement_row;
25    end if;
29create or replace function audit.disable_tracking(regclass)
30    returns void
31    volatile
32    security definer
33    language plpgsql
34as $$
36    statement_row text = format(
37        'drop trigger if exists audit_i_u_d on %I;',
38        $1
39    );
41    execute statement_row;

And we're done with 2 lines of code to spare!


Auditing tables always reduces throughput of inserts, updates, and deletes. In cases where throughput is less than 1000 writes per second the overhead is typically negligible. For tables with a higher write frequency, consider logging changes outside of SQL with a tool like pgAudit.

Do I really expect you to copy/paste all that?

Nope, for a turnkey solution to auditing in PostgreSQL, we've packaged this script into an extension with some extra goodies like TRUNCATE support. Check it out at

Share this article

Last post

Should I Open Source my Company?

25 March 2022

Next post

Supabase Beta January 2022

22 February 2022

Related articles

Postgres Auditing in 150 lines of SQL

Protecting reserved roles with PostgreSQL Hooks

Developers stay up to date with

Using Supabase in Replit

Postgres as a CRON Server

Build in a weekend, scale to millions