How hstore Can Beat jsonb for Audit Logs

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 …