DEV Community

Sanidhya
Sanidhya

Posted on

I got tired of writing the same history table boilerplate, so I built a Postgres extension

#postgres #opensource #sql #database

There's this problem I kept running into - first during my internship, then again on a freelance project I picked up afterward. Both of them used PostgreSQL, and both of them at some point needed an answer to the same question: what did this data look like before?

Could be an audit log. Could be a client asking "what was the price on the 15th?" Could be a bad deployment that nuked a bunch of rows and now someone needs to recover the state from two hours ago.

And every single time, the answer involved the same tedious ritual: add created_at and updated_at columns, create a separate _history table that mirrors the original, write a trigger to populate it on every insert, update, and delete, and hope you got the interval boundaries right. On the freelance project I actually got them wrong on the first try - rows were either double-counted or missing entirely at certain timestamps. Had to tear it out and redo it.

So I finally sat down and built something to handle this properly.

What pgtime does

You run this on any existing table:

SELECT pgtime.attach('orders');
Enter fullscreen mode Exit fullscreen mode

That's it. From that point on, every INSERT, UPDATE, and DELETE on the orders table is automatically captured in a shadow history table called orders_history. You don't touch your existing schema, your existing queries don't change, and the tracking just works in the background.

When you call attach(), the extension dynamically compiles helper functions matched to your table's exact schema. This means when you need to look at the past, you get clean, native queries with no tedious type casting:

-- Get a full table snapshot as of Jan 15th, 2026
SELECT * FROM orders_as_of('2026-01-15 10:00:00+00');
Enter fullscreen mode Exit fullscreen mode

Or if you want to see the full change history of a specific row:

-- Retrieve the full audit trail of order #42
SELECT price, sys_from, _pgtime_op FROM orders_history(42);
Enter fullscreen mode Exit fullscreen mode

Why a C trigger and not PL/pgSQL

The trigger that captures row changes is written in C. The honest reason is performance - PL/pgSQL has overhead on every row operation, and for tables with high write throughput that adds up fast. The C trigger runs closer to the metal.

To back this up, I ran a baseline benchmark on 10,000 operations inside a Postgres 16 container. Even with range-indexing overhead (we use tstzrange and GiST indexes to make point-in-time scans fast), the C trigger still manages to log:

  • ~63,700 operations/sec for INSERTs
  • ~20,100 operations/sec for UPDATEs (which require double writes: closing the old version and inserting the new one)

There are SDKs too

I didn't want this to only be accessible from raw SQL, so I built thin wrappers for Node/TypeScript and Python.

TypeScript:

import { Pool } from 'pg';
import { PgTime } from 'pgtime-js';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const pt = new PgTime(pool);

await pt.attach('orders');

interface Order { id: number; item: string; price: number; }
const snapshot = await pt.asOf<Order>('orders', '2026-01-15T10:00:00Z');
const auditTrail = await pt.history('orders', 42);
Enter fullscreen mode Exit fullscreen mode

Python:

from pgtime import PgTime
import psycopg2

conn = psycopg2.connect("postgres://...")
pt = PgTime(conn)

pt.attach("orders")
snapshot = pt.as_of("orders", "2026-01-15T10:00:00Z")
logs = pt.history("orders", 42)
Enter fullscreen mode Exit fullscreen mode

And there's a Go CLI for terminal operations:

pgtime attach orders --db postgres://localhost/mydb
pgtime history orders --id 42 --db postgres://localhost/mydb
pgtime diff orders --from "2026-01-01" --to "2026-02-01" --db postgres://localhost/mydb
Enter fullscreen mode Exit fullscreen mode

Where it stands right now

It's v0.1.1-alpha. Transaction time - meaning the database system clock - is fully working. Valid-time (business time, where you define the time bounds rather than the system) is on the roadmap for v0.2 but not there yet.

The SDKs also aren't on npm or PyPI yet, so installation is from a local clone for now. That'll change in the next release. Installation is Docker-based for development and testing, or you can compile the C extension directly if you have the Postgres dev headers - the README walks through both.

If you want to check it out

GitHub: https://github.com/Sanidhyavijay24/pgtime

If you've dealt with this problem before - or solved it a different way - I'd genuinely like to hear how. And if something looks wrong or off in the implementation, please say so. Still early days and real feedback is more useful than stars right now.

Top comments (0)