Advancedpostgressupabasesqltriggersperformanceconcurrency

Postgres triggers for denormalized counts

Keep cached counters atomically correct with Postgres triggers — including the hot-row contention this creates, how to batch around it, and the backfill and drift audits that keep the column honest.

Step 1
Why denormalize a count at all
Reading vote_count from a column is O(1). Running count(*) against a votes table is O(n) and degrades as the table grows — and on a list page you pay that cost once per row. Caching the count on the parent row fixes the read, but it introduces a new problem: the cached value can drift from reality. A database trigger is the only place you can update it atomically with the write that caused it.
```
-- The query you are trying to avoid, once per listed row:
select fr.*, (select count(*) from votes v
              where v.feature_request_id = fr.id) as vote_count
from feature_requests fr
order by vote_count desc
limit 20;

-- What you want instead:
select * from feature_requests order by vote_count desc limit 20;
```
Step 2
Why application code cannot do this correctly
The obvious approach — insert the vote, then update the counter — is a race. Two concurrent transactions both read 5, both write 6, and one vote vanishes. Wrapping it in a transaction does not help unless you also take a lock, and at that point you have built a worse version of what the database already does. A read-modify-write in application code is always wrong here.
```
-- BROKEN: lost update under concurrency
const { count } = await db.one('select vote_count from feature_requests where id = $1', [id]);
await db.none('update feature_requests set vote_count = $1 where id = $2', [count + 1, id]);

-- Still broken: the app can crash between the two statements
await db.none('insert into votes ...');
await db.none('update feature_requests set vote_count = vote_count + 1 ...');

-- Correct: one atomic statement, driven by the database itself
```
⚠ Heads up: Any counter maintained by a separate application-level UPDATE will drift. It is a question of traffic, not of whether.

Step 3

Set up the schema

A composite primary key on (user_id, feature_request_id) enforces one vote per user at the database level, so duplicate votes fail on insert rather than double-counting. The counter column lives on the parent with a sane default.

create table feature_requests (
  id          uuid primary key default gen_random_uuid(),
  title       text not null,
  vote_count  int  not null default 0,
  created_at  timestamptz default now()
);

create table votes (
  user_id            uuid not null references profiles(id) on delete cascade,
  feature_request_id uuid not null references feature_requests(id) on delete cascade,
  created_at         timestamptz default now(),
  primary key (user_id, feature_request_id)
);

-- Supports the DELETE side of the trigger and the drift audit below
create index on votes (feature_request_id);

Step 4
Write the trigger function
Three details matter. security definer lets the function update the parent table even though the voting user has no direct UPDATE grant on it. set search_path = public is mandatory on any security definer function — without it, a caller can prepend a schema they control and hijack which feature_requests you actually write to. And returning coalesce(NEW, OLD) handles both branches, since NEW is null on DELETE.
```
create or replace function bump_vote_count()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if TG_OP = 'INSERT' then
    update feature_requests
       set vote_count = vote_count + 1
     where id = NEW.feature_request_id;

  elsif TG_OP = 'DELETE' then
    update feature_requests
       set vote_count = greatest(vote_count - 1, 0)
     where id = OLD.feature_request_id;
  end if;

  return coalesce(NEW, OLD);
end;
$$;
```
⚠ Heads up: Omitting `set search_path` on a `security definer` function is a privilege escalation vector. An attacker who can create objects in a schema earlier on the search path can redirect your writes and have them execute with the function owner's rights.
Step 5
Attach the trigger
after insert or delete ... for each row fires once per affected row, once the row change itself has been applied. vote_count = vote_count + 1 reads and writes inside a single statement, so Postgres holds the row lock for the whole operation and concurrent increments serialize correctly rather than overwriting each other.
```
create trigger votes_count_trigger
after insert or delete on votes
for each row
execute function bump_vote_count();

-- Verify it is attached
select tgname, tgenabled
  from pg_trigger
 where tgrelid = 'votes'::regclass
   and not tgisinternal;
```

Step 6

Handle the UPDATE case, or lose track of the count

Most tutorials stop at INSERT and DELETE. If a row's foreign key can ever change — a vote moved to a different request, a comment reassigned to a different post — an UPDATE silently decrements nothing and increments nothing, and both parents end up wrong. Handle it explicitly, and guard against no-op updates where the key did not actually change.

create or replace function bump_vote_count()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if TG_OP = 'INSERT' then
    update feature_requests set vote_count = vote_count + 1
     where id = NEW.feature_request_id;

  elsif TG_OP = 'DELETE' then
    update feature_requests set vote_count = greatest(vote_count - 1, 0)
     where id = OLD.feature_request_id;

  elsif TG_OP = 'UPDATE'
        and NEW.feature_request_id is distinct from OLD.feature_request_id then
    update feature_requests set vote_count = greatest(vote_count - 1, 0)
     where id = OLD.feature_request_id;
    update feature_requests set vote_count = vote_count + 1
     where id = NEW.feature_request_id;
  end if;

  return coalesce(NEW, OLD);
end;
$$;

drop trigger if exists votes_count_trigger on votes;
create trigger votes_count_trigger
after insert or update or delete on votes
for each row execute function bump_vote_count();

Step 7
Understand the cost: you have created a hot row
This is the trade-off nobody mentions up front. Every vote on a given request now takes an exclusive row lock on that one parent row for the remainder of the transaction. Votes on different requests proceed in parallel, but votes on the same request serialize completely. For a leaderboard — where traffic concentrates on whatever is trending — that is precisely the worst case.
```
-- Transaction A                    -- Transaction B (same feature_request)
begin;                              begin;
insert into votes ...;              insert into votes ...;
-- trigger: UPDATE feature_requests -- trigger: UPDATE feature_requests
--   → takes row lock               --   → BLOCKS waiting for A
-- ... rest of A's work ...
commit;                             -- only now does B proceed

-- Watch it happen in production:
select pid, wait_event_type, wait_event, state, query
  from pg_stat_activity
 where wait_event_type = 'Lock';
```
⚠ Heads up: Turning inserts into contended updates on a single row is a well-known anti-pattern at high write volume. It is the right call for a voting board; it is the wrong call for something like per-page view counters on a busy site.

Step 8

Keep the lock window short

Because the lock is held until commit, the fix is not to avoid the trigger but to stop doing slow work after it. Fire the counter update as late as possible in the transaction, and never leave an HTTP call or another slow query sitting between the insert and the commit.

-- BAD: lock held across a network call
begin;
insert into votes (user_id, feature_request_id) values ($1, $2);
-- trigger has now locked the parent row
select http_post('https://example.com/webhook', ...);  -- lock held the whole time
commit;

-- GOOD: commit first, side effects after
begin;
insert into votes (user_id, feature_request_id) values ($1, $2);
commit;  -- lock released here
-- webhook, cache purge, analytics happen outside the transaction

Step 9

When contention is genuinely too high, batch instead

If a single row is hot enough to bottleneck, stop updating it per write. Two standard escapes: sharded counters, where each writer increments one of N rows and reads sum them; or a statement-level trigger using transition tables, which collapses a bulk insert into one UPDATE per parent rather than one per row.

-- Option 1: sharded counter — spreads the lock across N rows
create table vote_counts (
  feature_request_id uuid not null,
  shard              smallint not null,
  count              int not null default 0,
  primary key (feature_request_id, shard)
);
-- writers: update ... where shard = (random() * 16)::int
-- readers: select sum(count) ... group by feature_request_id

-- Option 2: statement-level trigger with transition tables (Postgres 10+).
-- One UPDATE per parent for the whole statement, not one per row.
create or replace function bump_vote_count_stmt()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  update feature_requests f
     set vote_count = f.vote_count + agg.n
    from (select feature_request_id, count(*) as n
            from inserted group by feature_request_id) agg
   where f.id = agg.feature_request_id;
  return null;
end;
$$;

create trigger votes_count_stmt_trigger
after insert on votes
referencing new table as inserted
for each statement execute function bump_vote_count_stmt();

⚠ Heads up: Do not run both a row-level and a statement-level trigger for the same counter — you will double-count every write.

Step 10

Backfill before you trust the column

Adding the trigger only affects future writes. Existing rows keep whatever value the column defaulted to, so reconcile once at migration time. Do this in the same migration that creates the trigger, or you ship a counter that is wrong from day one.

-- Run inside the migration, after creating the trigger
update feature_requests f
   set vote_count = coalesce(v.n, 0)
  from (select feature_request_id, count(*) as n
          from votes group by feature_request_id) v
 where f.id = v.feature_request_id;

-- Requests with no votes at all
update feature_requests
   set vote_count = 0
 where id not in (select distinct feature_request_id from votes);

Step 11

Audit for drift on a schedule

Even a correct trigger can be bypassed — by a data-only restore, a session_replication_role change, or someone disabling triggers during a bulk load. Keep a cheap query that compares cached values to reality and run it periodically. It should always return zero rows.

-- Any row returned is drift. Alert on this being non-empty.
select f.id,
       f.vote_count as cached,
       coalesce(v.n, 0) as actual,
       f.vote_count - coalesce(v.n, 0) as delta
  from feature_requests f
  left join (select feature_request_id, count(*) as n
               from votes group by feature_request_id) v
    on v.feature_request_id = f.id
 where f.vote_count <> coalesce(v.n, 0);

Step 12
The restore and bulk-load footgun
pg_dump --data-only restores frequently need --disable-triggers, which means your counter triggers do not fire during the load and every cached value ends up stale. The same applies to any bulk import that turns triggers off for speed. Always re-run the backfill afterwards.
```
# Data-only restores commonly require this flag...
pg_restore --data-only --disable-triggers -d mydb dump.sql

# ...which means the counters are now wrong. Re-run the backfill.

# Same hazard when loading manually:
set session_replication_role = replica;   -- triggers do not fire
\copy votes from 'votes.csv' csv header
set session_replication_role = default;
-- Now re-run the backfill query
```
⚠ Heads up: This is the most common cause of counters that were correct for months and then suddenly are not. The drift audit above is what catches it.

Step 13

Test the trigger under real concurrency

A single-threaded test proves almost nothing here — the failure mode you care about only appears with parallel writers. Use pgbench to hammer one parent row and confirm the count matches exactly.

-- votes.sql : each client votes as a distinct user on the SAME request
\set uid random(1, 100000)
begin;
insert into votes (user_id, feature_request_id)
values (('00000000-0000-0000-0000-' || lpad(:uid::text, 12, '0'))::uuid,
        '<your-request-uuid>')
on conflict do nothing;
commit;

Step 14

Verify the whole thing

Run these after the migration. If the counts match under a concurrent load and the drift audit is empty, the trigger is doing its job.

# 1. Concurrent writes: 8 clients, 4 threads, 1000 transactions
pgbench -n -f votes.sql -c 8 -j 4 -t 1000 mydb

# 2. Cached value must equal the real count — exactly
psql mydb -c "select vote_count from feature_requests where id = '<uuid>';"
psql mydb -c "select count(*) from votes where feature_request_id = '<uuid>';"

# 3. Drift audit must return zero rows
psql mydb -f drift_audit.sql

# 4. Deletes decrement
psql mydb -c "delete from votes where feature_request_id = '<uuid>';"
psql mydb -c "select vote_count from feature_requests where id = '<uuid>';"  # → 0

# 5. The counter never goes negative even under double-delete
#    (greatest(vote_count - 1, 0) guarantees this)

Postgres triggers for denormalized counts

Why denormalize a count at all

Why application code cannot do this correctly

Set up the schema

Write the trigger function

Attach the trigger

Handle the UPDATE case, or lose track of the count

Understand the cost: you have created a hot row

Keep the lock window short

When contention is genuinely too high, batch instead

Backfill before you trust the column

Audit for drift on a schedule

The restore and bulk-load footgun

Test the trigger under real concurrency

Verify the whole thing

Feature requests

Discussion