Some time ago I read this blogpost.
They wrote about logging changes to table using a trigger, but one thing that caught my attention was:
Storing only the changed columns made the log five times smaller and the trigger slower.
Figured I can try it myself…
Unfortunately they didn't provide schema of table, nor test updates. So, let's make test case:
=$ create table some_data ( id int8 generated always as identity primary key, ts timestamptz, b bool, title text, i int8 ); CREATE TABLE
Now, we need some data there. Luckily, this is rather simple:
=$ select setseed(0.31831015504887652431); =$ insert into some_data (ts, b, title, i) select case when random() < .05 then null else now() - '10 years'::interval * random() end, case when random() < .05 then null else random() < .8 end, 'Title for ' || i::text || ' plus some padding: ' || encode(sha224( i::text::bytea ), 'hex'), floor( random() * 1_000_000_000 )::int8 from generate_series(1, 100000) i;
Data looks like this:
=$ select * from some_data limit 5; id | ts | b | title | i ----+-------------------------------+---+-----------------------------------------------------------------------------------------+----------- 1 | 2017-08-24 03:20:59.740887+02 | t | Title for 1 plus some padding: e25388fde8290dc286a6164fa2d97e551b53498dcbf7bc378eb1f178 | 818069924 2 | 2021-11-04 00:02:59.481687+01 | f | Title for 2 plus some padding: 58b2aaa0bfae7acc021b3260e941117b529b2e69de878fd7d45c61a9 | 396123746 3 | 2016-10-20 02:07:21.504087+02 | t | Title for 3 plus some padding: 4cfc3a1811fe40afa401b25ef7fa0379f1f7c1930a04f8755d678474 | 982526054 4 | 2023-02-02 16:46:57.820887+01 | t | Title for 4 plus some padding: 271f93f45e9b4067327ed5c8cd30a034730aaace4382803c3e1d6c2f | 418035801 5 | 2025-10-05 08:27:53.568087+02 | t | Title for 5 plus some padding: b51d18b551043c1f145f22dbde6f8531faeaf68c54ed9dd79ce24d17 | 483535962 (5 rows)
Now, trigger logic. They didn't provide their code, just something to calculate changes:
select jsonb_object_agg(key, o -> key), jsonb_object_agg(key, value) into d_old, d_new from jsonb_each(n) where o -> key is distinct from value; if d_new is null then return null; end if; -- nothing changed
So, based on this, and some thinking, and their provided idea about schema for changelog, I wrote this:
=$ CREATE TABLE change_log ( id bigint generated always as identity primary key, table_name text not null, row_id bigint not null, action text not null, changed_at timestamptz not null default clock_timestamp(), changed_by text, old_row jsonb, new_row jsonb ); =$ CREATE OR REPLACE FUNCTION log_changes_now_next() RETURNS TRIGGER AS $BODY$ DECLARE o jsonb := to_jsonb(OLD); n jsonb := to_jsonb(NEW); d_old jsonb; d_new jsonb; BEGIN select jsonb_object_agg(key, o -> key), jsonb_object_agg(key, value) into d_old, d_new from jsonb_each(n) where o -> key is distinct from value; INSERT INTO change_log (table_name, row_id, action, changed_by, old_row, new_row) VALUES (TG_TABLE_NAME, NEW.id, TG_OP, CURRENT_USER, d_old, d_new); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; =$ CREATE TRIGGER log_changes_now_next AFTER UPDATE ON some_data FOR EACH ROW WHEN (old.* is distinct from new.*) EXECUTE FUNCTION log_changes_now_next();
Test shows that it works OK:
=$ update some_data set i = i + 1 where id = 1; UPDATE 1 =$ select * from change_log; id │ table_name │ row_id │ action │ changed_at │ changed_by │ old_row │ new_row ────┼────────────┼────────┼────────┼───────────────────────────────┼────────────┼──────────────────┼────────────────── 1 │ some_data │ 1 │ UPDATE │ 2026-10-16 11:16:28.532503+02 │ depesz │ {"i": 818069924} │ {"i": 818069925} (1 row) =$ update some_data set i = i where id = 2; UPDATE 1 =$ select * from change_log; id │ table_name │ row_id │ action │ changed_at │ changed_by │ old_row │ new_row ────┼────────────┼────────┼────────┼───────────────────────────────┼────────────┼──────────────────┼────────────────── 1 │ some_data │ 1 │ UPDATE │ 2026-10-16 11:16:28.532503+02 │ depesz │ {"i": 818069924} │ {"i": 818069925} (1 row)
Change has been logged, and when update didn't cause any change, nothing got logged.
Awesome.
Now, the part that caught my attention is that logging difference was slower than logging whole rows.
So I need also another approach, one that just puts whole rows. That is simple:
=$ CREATE TABLE change_log ( id bigint generated always as identity primary key, table_name text not null, row_id bigint not null, action text not null, changed_at timestamptz not null default clock_timestamp(), changed_by text, old_row jsonb, new_row jsonb ); =$ CREATE OR REPLACE FUNCTION log_changes_now_next() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN INSERT INTO change_log (table_name, row_id, action, changed_by, old_row, new_row) VALUES (TG_TABLE_NAME, NEW.id, TG_OP, CURRENT_USER, to_jsonb(OLD), to_jsonb(NEW) ); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; =$ CREATE TRIGGER log_changes_now_next AFTER UPDATE ON some_data FOR EACH ROW WHEN (old.* is distinct from new.*) EXECUTE FUNCTION log_changes_now_next();
After the same updates as before (on clean setup) I got:
=$ select * from change_log \gx ─[ RECORD 1 ]────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── id │ 1 table_name │ some_data row_id │ 1 action │ UPDATE changed_at │ 2026-10-16 11:29:10.475962+02 changed_by │ depesz old_row │ {"b": true, "i": 818069924, "id": 1, "ts": "2017-08-24T03:48:05.697089+02:00", "title": "Title for 1 plus some padding: e25388fde8290dc286a6164fa2d97e551b53498dcbf7bc378eb1f178"} new_row │ {"b": true, "i": 818069925, "id": 1, "ts": "2017-08-24T03:48:05.697089+02:00", "title": "Title for 1 plus some padding: e25388fde8290dc286a6164fa2d97e551b53498dcbf7bc378eb1f178"}
Sweet. So I have two first cases, that should, somewhat, be related to what Now-Next wrote about.
Let's run some update. They mentioned bulk update of 100k rows, which is the full size of my table. So, let's write an update:
=$ select setseed(0.31831015504887652431); =$ UPDATE some_data SET ts = ts + (case when random() < .6 THEN '5 minutes'::INTERVAL ELSE '0 seconds'::INTERVAL END ), i = i + (case when random() < .6 THEN 123 ELSE 0 END ) ;
To get sensible data, comparable with anything, I ran, for now, three tests:
- no logging trigger, just update
- trigger logging full rows
- trigger logging diff
Each test was ran by dropping all tables, making them from scratch, and then running update. Everything repeated 3 times, and I picked best time.
Results:
| Logging method: | Time: | change_log size: |
|---|---|---|
| no logging | 238.061 ms | 0 bytes |
| full row logging | 920.846 ms | 42,262,528 bytes |
| diff logging | 1430.001 ms | 15,843,328 bytes |
OK. So I have a base line. Now. I think that JSON is overkill for this. And we might get better results with older tech – specifically hstore.
This needs small changes in the code and table definitions:
=$ CREATE extension IF NOT exists hstore; =$ CREATE TABLE change_log ( id bigint generated always as identity primary key, table_name text not null, row_id bigint not null, action text not null, changed_at timestamptz not null default clock_timestamp(), changed_by text, old_row hstore, new_row hstore ); =$ CREATE OR REPLACE FUNCTION log_changes_hstore() RETURNS TRIGGER AS $BODY$ DECLARE o hstore := hstore(OLD); n hstore := hstore(NEW); BEGIN INSERT INTO change_log (table_name, row_id, action, changed_by, old_row, new_row) VALUES (TG_TABLE_NAME, NEW.id, TG_OP, CURRENT_USER, o - n, n - o); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; =$CREATE TRIGGER log_changes_hstore AFTER UPDATE ON some_data FOR EACH ROW WHEN (old.* is distinct from new.*) EXECUTE FUNCTION log_changes_hstore();
This handles diff logging, and full version is similar:
=$ CREATE OR REPLACE FUNCTION log_changes_hstore() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN INSERT INTO change_log (table_name, row_id, action, changed_by, old_row, new_row) VALUES (TG_TABLE_NAME, NEW.id, TG_OP, CURRENT_USER, hstore(OLD), hstore(NEW)); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql';
So, how does it perform?
| Logging method: | Time: | change_log size: |
|---|---|---|
| no logging | 238.061 ms | 0 bytes |
| jsonb: full row logging | 920.846 ms | 42,262,528 bytes |
| jsonb: diff logging | 1430.001 ms | 15,843,328 bytes |
| hstore: full row logging | 829.950 ms | 39,780,352 bytes |
| hstore: diff logging | 798.260 ms | 14,704,640 bytes |
This shows that hstore is faster, and, on top of it, it makes diff logging faster than full row. Does it mean that it's better choice than JSONB? Definitely not for everything. But for logging changes in tables, it's simpler and faster. And uses (a bit) less disk space.
Side note: I would greatly appreciate if Pg-related blogs had comment section. The fact that I can't ask for clarification, exact queries, is just (in my view) unacceptable.