Advancedpostgressupabasesqltriggers

Postgres triggers for denormalized counts

Keep vote counts and aggregate columns in sync atomically using Postgres triggers, instead of racing application code.

Step 1
Why denormalize?
Reading vote_count from a column is O(1). Counting rows in a votes table is O(n) and slow at scale. Storing the count on the parent row is a common optimization — but only safe if you keep it in sync atomically.

Step 2

Write the trigger function

Use security definer so the trigger can update the parent table even when the inserting user doesn't have direct UPDATE permission. Always set search_path explicitly to prevent search_path injection.

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;
$$;

Step 3

Attach the trigger

AFTER INSERT OR DELETE means the trigger fires after the row change commits.

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

Step 4
Defensive clamping
greatest(vote_count - 1, 0) prevents negative counts if a vote is somehow decremented twice. Cheap insurance against drift.

Postgres triggers for denormalized counts

Why denormalize?

Write the trigger function

Attach the trigger

Defensive clamping

Feature requests

Discussion