---
title: "Postgres Auditing in 150 lines of SQL"
description: "Traditional PostgreSQL auditing methods duplicate source table structures, creating maintenance overhead when schemas change. To solve this, a compact SQL implementation stores insert, update, and delete events across multiple tables in a single audit table using JSONB columns. Query performance is maintained by using a BRIN index on naturally ordered insertion timestamps, a B-tree index on internal table OIDs, and UUIDv5 identifiers hashed from primary keys. Row-level PL/pgSQL triggers automatically populate the audit log and can be enabled or disabled dynamically with dedicated tracking functions. While trigger-based auditing introduces minimal overhead at rates below 1,000 writes per second, write-heavy workloads may benefit from logging changes outside SQL with tools like pgAudit."
---

# Postgres Auditing in 150 lines of SQL

[Supabase](https://yomu.fyi/company/supabase) · Oliver Rice · Mar 8, 2022

**Type:** Tutorial

## Summary

Traditional PostgreSQL auditing methods duplicate source table structures, creating maintenance overhead when schemas change. To solve this, a compact SQL implementation stores insert, update, and delete events across multiple tables in a single audit table using JSONB columns. Query performance is maintained by using a BRIN index on naturally ordered insertion timestamps, a B-tree index on internal table OIDs, and UUIDv5 identifiers hashed from primary keys. Row-level PL/pgSQL triggers automatically populate the audit log and can be enabled or disabled dynamically with dedicated tracking functions. While trigger-based auditing introduces minimal overhead at rates below 1,000 writes per second, write-heavy workloads may benefit from logging changes outside SQL with tools like pgAudit.

## Context

Traditional PostgreSQL auditing systems mirror source table schemas and require database migrations whenever source tables change. A practical auditing solution must track historical changes over time, remain low maintenance across schema alterations, and allow fast querying for both time slices and specific record lifecycles.

## Approach / What changed

The implementation creates an audit.record\_version table using JSONB to capture row states across multiple tables in one place. It creates a BRIN index on the timestamp column for efficient time-range filtering and indexes table OIDs and UUIDv5 record identifiers derived from primary keys. Row-level PL/pgSQL triggers capture data mutations, managed through idempotent enable\_tracking and disable\_tracking helper functions.

## Takeaways

- Using JSONB allows a single audit table to store change history across multiple entities without requiring schema migrations when source tables change.
- Applying a BRIN index on naturally ascending timestamp columns produces an index hundreds of times smaller than a standard B-tree index while speeding up time-range lookups.
- Trigger-based SQL auditing typically adds negligible overhead for write throughput below 1,000 writes per second, whereas higher volumes may require external tools like pgAudit.

**Tags:** [Open Source](https://yomu.fyi/topic/open-source), [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres)

- Source: [Supabase](https://supabase.com/blog/postgres-audit)
- Source URL: https://supabase.com/blog/postgres-audit
- Ingested by Yomu: 2026-08-27T14:43:13.531Z

[Read original post](https://supabase.com/blog/postgres-audit)