If you've ever built applications like a blog platform or quiz app, you've probably encountered the need for keeping track of some kind of counter, whether that's a blog post comment count, a like count, or quiz app answer counts. Pretty simple, right? But have you ever wondered how to scale such a simple thing? What happens if thousands of people try to update the counter? Today, we're going to build such an application and scale our way to the top.

Traditional Approach

Sample App

Let's build an app first. Our app is a simple quiz app. Here are the requirements just for our use case:

  • The app has a UI to display questions, and the user can submit an answer.
  • Internally, we verify the answer and keep track of two key things: the total answer count and the correct answer count.
  • We use both the total answer count (A) and the correct answer count (C) to calculate a difficulty score.
  • The difficulty score algorithm will look like this:
difficulty=1- \frac{C}{A}

​C = correct count

​A = total attempts

In the end, this calculation will yield a value that looks something like 0.41.

Building the Application

We're not going to focus on the backend or the frontend; we're only focused on the database. To showcase this use case, we'll need three tables:

  • answers table
  • questions table
  • answer_stats table

When a user answers a question, we'll insert a record into the answers table. Based on the answer, we'll then update the answer_stats table. The required database structure will look like this:


CREATE TABLE IF NOT EXISTS "answers" (
	"id" UUID NOT NULL UNIQUE,
	"answer" TEXT NOT NULL,
	"student_id" UUID NOT NULL,
	"question_id" UUID,
	PRIMARY KEY("id")
);

CREATE TABLE IF NOT EXISTS "questions" (
	"id" UUID NOT NULL UNIQUE,
	"content" TEXT,
	PRIMARY KEY("id")
);

CREATE TABLE IF NOT EXISTS "answer_stats" (
	"question_id" UUID UNIQUE,
	"total_attempts" BIGINT,
	"total_success" BIGINT,
	PRIMARY KEY("question_id")
);

ALTER TABLE "answer_stats"
ADD FOREIGN KEY("question_id") REFERENCES "questions"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE "answers"
ADD FOREIGN KEY("question_id") REFERENCES "questions"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;

To test our inserts, we can add a question and answers with transactions.

Insert a Question


INSERT INTO questions (id, content)
VALUES (gen_random_uuid(), 'What is 2 + 2?');

Add Answer Stats with Transactions (Initial, Hot-Row Approach)


DO $$
DECLARE

q_id UUID := '2adf2d89-d7c5-4fa8-b0e8-087d3c137715'; -- generated id from question.change this 
BEGIN

-- Step 1: Insert a new answer linked to that question

INSERT INTO answers (id, answer, student_id, question_id)
VALUES (
gen_random_uuid(),
'4',
gen_random_uuid(),
q_id
);

-- Step 2: Update or insert into stats

INSERT INTO answer_stats (question_id, total_attempts, total_success)
VALUES (
q_id,
1,
floor(random() * 2)::int -- 0 or 1
)
ON CONFLICT (question_id)
DO UPDATE

SET

total_attempts = answer_stats.total_attempts + 1,
total_success = answer_stats.total_success + floor(random() * 2)::int;
COMMIT;
END $$;

This does work, but why is this a performance bottleneck?

There are many reasons why this is not a good solution, including:

  • Frequent writes to the same rows
  • Random writes +ON CONFLICT overhead
  • MVCC history
  • Transactional cost

We are focusing on one issue here: why Frequent writes to the same rows are bad. To answer this question, we need to understand how PostgreSQL handles writes.

If many users answer the same question, every transaction tries to update the same row. That means every transaction competing to update the same row in answer_stats

transactions tries to update the same row

To sum it up:

  • PostgreSQL must take a row-level exclusive lock during each update.
  • Only one transaction can update that row at a time, and others get queued up.
  • This creates a "hot row" and causes contention, latency, and deadlocks.

So now we know why this is bad. But is there any better way to do this? Of course. Divide and Conquer.

The Slotted Counter Pattern

What is This Slotted Counter Pattern anyway?

Instead of storing counts in a single row, we split the count into multiple rows called slots. Each slot is an independent counter that we update randomly. When we need to increment a counter, we randomly pick one of these slots to update.

Think of it like having multiple checkout counters at a grocery store instead of one. Sure, you need to add up all the registers at the end of the day, but customers flow through much faster.

Here's the beautiful part: with 10 slots, we can handle 10 concurrent updates to the same answer stats with zero contention. Our throughput increases 10x, and response times drop back to normal.

Redesign

Instead of one row per question, now we are going to create multiple slots:


-- drop the old table 
DROP TABLE IF EXISTS "answer_stats";

CREATE TABLE IF NOT EXISTS "answer_stats" (
"question_id" UUID,
"total_attempts" BIGINT,
"total_success" BIGINT,
"slot_number" SMALLINT NOT NULL
);
ALTER TABLE "answer_stats"
ADD FOREIGN KEY("question_id") REFERENCES "questions"("id")
ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE "answer_stats"
ADD CONSTRAINT unique_question_per_slot UNIQUE (question_id, slot_number);

\nWhen updating, we randomly select a slot and update it.


DO $$
DECLARE

q_id UUID := '2adf2d89-d7c5-4fa8-b0e8-087d3c137715'; -- generated id from question

BEGIN

-- Step 1: Insert a new answer linked to that question
INSERT INTO answers (id, answer, student_id, question_id)
VALUES (
gen_random_uuid(),
'4',
gen_random_uuid(),
q_id
);

-- Step 2: Update or insert into stats
INSERT INTO answer_stats (question_id, total_attempts, total_success, slot_number)
VALUES (
q_id,
1,
floor(random() * 2)::int, -- 0 or 1

ceil(random() * 10) -- Assign a random slot number between 1 and 10
)
ON CONFLICT (question_id,slot_number)
DO UPDATE

SET

total_attempts = answer_stats.total_attempts + 1,
total_success = answer_stats.total_success + floor(random() * 2)::int;
COMMIT;
END $$;

using slotted counter pattern

Finally, when we want to calculate total attempts and success, we sum up the values using the question ID.

When NOT to Use This Pattern
  • Low-traffic counters: Less than 10 updates/second? Just use a single row.
  • Strong consistency requirements: Need real-time exact counts? This adds eventual consistency
  • Simple applications: Don't optimize prematurely

Use this pattern when you have clear evidence of contention, not as a default.

Alternative Approaches

Before implementing slotted counters, consider these alternatives:

  • Application-level caching: Batch updates in your app, flush periodically
  • Redis counters: Offload to an in-memory store
  • PostgreSQL unlogged tables: Faster writes, but data lost on crash
  • Queue-based updates: Use a message queue to serialize writes

Each has trade-offs around consistency, complexity, and durability.

Final Implementation Checklist
  • Start small: Begin with 10 slots per partition
  • Monitor slot distribution: Use pg_stat_activity to spot hot slots
  • Benchmark: Simulate load with pgbench before production
Conclusion

The Slotted Counter Pattern is a powerful technique for scaling high-frequency counters in PostgreSQL. By distributing writes across multiple rows, you can:

  • Eliminate lock contention
  • Maintain accuracy through aggregation
  • Scale to thousands of updates per second
  • Keep your database schema simple

Remember: Start simple, optimize when needed. Monitor your database performance, and reach for slotted counters when you have concrete evidence of contention.