You add a stored comment_count column to a posts table instead of counting child rows. What are the ways to keep that counter correct, and how do they differ?
answer
- trigger / same-transaction app write / async job
- relative update, never read-modify-write
- lost update if two writers read then write
- hot parent row = lock contention, shard or batch
- reconciliation query is mandatory
basics
~20 sThree options: a database trigger on the child table, an application write inside the same transaction, or an asynchronous job. Triggers catch every write path; application code misses backfills and scripts; async jobs are cheap but stale. Always update with a relative change, and add a reconciliation query to repair drift.
solid answer
~50 s**Database trigger** on insert/update/delete of `comment`, adjusting `post.comment_count`. Strongest: it fires for every writer, including migrations and manual fixes, and is transactional with the change. Costs are a hidden write, harder debugging, and per-row overhead on bulk loads. **Application write in the same transaction** as the comment insert. Explicit and easy to test, but it is only correct if *every* write path remembers it — backfills, admin tooling and future services routinely do not. **Asynchronous recompute** from an outbox or a periodic job. Removes write-path cost and contention, at the price of a stated staleness window. Whatever the mechanism, two rules hold. Update **relatively** (`SET comment_count = comment_count + 1`), never read-then-write, or concurrent inserts lose updates. And ship a **reconciliation query** that recomputes the true count and reports mismatches, because drift is a question of when, not whether. Watch contention: a hot post serialises every commenter behind one row lock.
code
sql · 12 lines-- safe: the engine applies the delta under the row lock
UPDATE post SET comment_count = comment_count + 1 WHERE id = $1;
-- unsafe: two concurrent writers both read 10 and both write 11
-- SELECT comment_count FROM post WHERE id = $1; -- app adds 1
-- UPDATE post SET comment_count = 11 WHERE id = $1;
-- reconciliation: find and report drift
SELECT p.id, p.comment_count, count(c.id) AS actual
FROM post p LEFT JOIN comment c ON c.post_id = p.id
GROUP BY p.id, p.comment_count
HAVING p.comment_count <> count(c.id);go deeper
Name the options — trigger, application update, background job — and state that the counter can drift and must be kept in step.
Show the relative UPDATE, explain the lost-update race that read-modify-write causes, and compare trigger versus application code on write-path coverage.
Lead with the guarantee required (exact versus display-only), pick the mechanism from that, and cover hot-row contention plus a scheduled reconciliation.
Discuss where the invariant is owned and how it is monitored, and when the counter should be sharded, made asynchronous, or removed in favour of a different read model.
## The problem the counter creates Once `post.comment_count` exists, the database holds the same fact twice: implicitly as the number of `comment` rows, and explicitly as an integer. Nothing in the engine ties them together. Keeping them equal is now a design decision with three plausible answers, and the answer determines your correctness guarantees. ## Option 1: database trigger A trigger on `comment` for insert, delete, and any update that moves a comment between posts, adjusting the parent counter. *Strengths.* It fires for every writer without exception — the API, a batch import, an interactive SQL session, a data-fix script. It runs inside the same transaction as the change, so a rollback undoes both. It is the only option that makes the invariant a property of the database rather than of a codebase. *Weaknesses.* The write becomes invisible in application code, which surprises people debugging latency and deadlocks. Triggers add per-row cost that hurts bulk loads badly, and some bulk-load paths bypass row triggers entirely. Trigger logic is also easy to get subtly wrong: forgetting the update case that reparents a row, or the delete case, leaves permanent drift. ## Option 2: application write in the same transaction Insert the comment and update the counter in one transaction in service code. *Strengths.* Explicit, greppable, unit-testable, and visible in the same place as the business logic. No hidden database behaviour. *Weaknesses.* Correctness depends on discipline across every present and future write path. The moment a second service, a backfill, or an admin tool inserts a comment, the counter is wrong and nothing complains. This is the option that looks safest in review and fails quietly in year two. ## Option 3: asynchronous recompute Write the comment, and separately — via an outbox row, a change stream, or a scheduled job — recompute or adjust the counter. *Strengths.* The hot write path stays cheap; no extra row lock at insert time, so no contention on popular posts. A full recompute job is also self-healing: it repairs drift as a side effect. *Weaknesses.* The counter is stale by design. That is fine for a displayed count and unacceptable for anything enforcing a limit — you cannot cap comments at 100 using a value that lags. Async also needs its own reliability story: a stuck consumer means silently wrong data. ## The concurrency rule that applies to all three Never read the counter into the application, add one, and write it back. Two concurrent inserts both read 10, both write 11, and one comment vanishes from the count — a lost update. Always issue a **relative** statement, `SET comment_count = comment_count + 1`, so the engine applies the delta under the row lock it already takes. A full recompute (`= (SELECT count(*) ...)`) is also safe but reintroduces the aggregate you were avoiding, so reserve it for reconciliation. Be aware of what the row lock buys and costs. Under read-committed, the second updater blocks until the first commits and then applies its delta to the fresh value — correct, but serialised. On a viral post, every commenter queues behind one row, and the counter becomes the throughput limit for commenting. Mitigations: shard the counter across N rows and sum on read, or move to asynchronous batched increments. Both trade exactness or freshness for concurrency. ## Reconciliation is not optional Whichever mechanism you choose, write the query that computes the truth and compares it to the stored value, and run it on a schedule. It costs little, it turns a silent correctness bug into an alert, and it doubles as the repair script. A denormalization shipped without one is an assertion that no bug, crash, or unusual write path will ever occur. ## Choosing If the counter must be exact and many write paths exist, use a trigger. If the schema is written by exactly one service with strong ownership and you value explicitness, same-transaction application code is defensible — with a reconciliation job. If the value is display-only and the parent rows get hot, go asynchronous and publish the staleness window. In every case, say out loud which one you picked and why; the mechanism, not the column, is the actual design.
- Why is 'SELECT the count, add one in code, UPDATE' wrong even inside a transaction?Under read-committed or snapshot isolation the two transactions can both read the same starting value and both write the same result, so one increment is lost — the classic lost update. A relative UPDATE avoids it because the engine holds the row lock across read and write. If you must read first, you need SELECT ... FOR UPDATE or serializable isolation with retries.
- A single very popular post makes comment inserts slow. What is happening and what would you change?Every insert updates the same parent row, so writers serialise on that row lock and throughput collapses to one commit at a time. Options are sharding the counter into several rows summed on read, batching increments asynchronously so many comments produce one update, or dropping the stored counter for hot posts and computing approximately.
saying these in an interview costs you the question
- Proposing read-then-write in application code and calling it safe because it is inside a transaction.
- Claiming application-level maintenance is equivalent to a trigger, without acknowledging backfills and other write paths.
- Forgetting the delete and reparent cases, so the counter only ever grows.
- Choosing an asynchronous counter for a value that enforces a hard limit, where staleness lets the limit be exceeded.
- Shipping the counter with no reconciliation query, assuming it can never drift.