Distributed Counter — Naive (Single PostgreSQL Row)

Designing a distributed counter is one of the most deceptively simple system design interview questions. Candidates often start with a single database row and an atomic UPDATE statement, assuming the problem is trivially solved. The naive approach forces them to confront the reality that a single row in a relational database has a hard throughput ceiling, and that the row-level locking mechanism that provides correctness is exactly what prevents scalability.

The core challenge is incrementing a counter at high throughput while maintaining correctness. In the naive approach, each counter key maps to a single row in a PostgreSQL counters table. An increment is performed via UPDATE counters SET count = count + 1 WHERE key = $1. This statement is atomic — PostgreSQL acquires a row-level exclusive lock, reads the current value, increments it, writes the new value, and releases the lock. No application-level read-modify-write race is possible.

The fatal flaw is the row-level exclusive lock. Every concurrent increment to the same counter key must wait for the lock holder to finish. At 50ms per UPDATE (network + disk + WAL flush), a single row can sustain at most ~20 updates per second serially. With PostgreSQL's optimistic locking and batched WAL writes, practical throughput reaches ~2K-5K updates per second per key before lock wait cascades begin.

The cascade is devastating. At 5K inc/sec with 50ms lock hold time, the average lock wait queue has 250 transactions. Each waiter holds a connection pool slot. With 150 connections, the pool saturates at just 150 concurrent waiters, causing all subsequent requests — including reads — to queue at the application layer. Latency spikes from 50ms to multiple seconds, timeouts cascade, and the system enters a death spiral.

Reads are cheap in isolation — SELECT count FROM counters WHERE key = $1 uses MVCC and does not acquire an exclusive lock. But under write contention, reads compete for connection pool slots with the backed-up UPDATE transactions. Even though reads themselves are fast (~5ms), they cannot get a connection when 150 connections are occupied by waiting UPDATEs.

The naive approach has zero deduplication. The same user can increment the same counter multiple times — critical for likes (one per user) but acceptable for views. A UNIQUE constraint on (user_id, counter_key) would add dedup but further increases write contention by adding an index lookup to the UPDATE transaction.

This template makes the row lock contention visible and quantifiable. Run the simulation at increasing write rates and watch the lock wait queue grow, connection pool saturate, and latency cascade. The comparison with the Sharded variant provides the concrete numbers: 16 sub-counters distribute writes across 16 rows, increasing per-key throughput by 16x. The CRDT variant goes further, eliminating cross-region coordination entirely.

Distributed counter design appears in interviews at YouTube, Instagram, TikTok, Twitter, and any company with view/like/click counting at scale. Interviewers expect candidates to start with the single-row approach, identify the row lock bottleneck, and propose sharding as the first optimization.

The naive distributed counter is a four-component linear architecture: Client, Load Balancer, Counter Service, and PostgreSQL database. There is no cache, no event stream, no sharding, no deduplication, and no separation between the read and write paths.

All traffic enters through the Load Balancer, which distributes requests across Counter Service pods using round-robin. The Load Balancer adds approximately 1.5ms of routing latency and supports up to 15,000 RPS — well above the system's actual ceiling, which is determined by the database row lock. Both increment writes and count reads flow through the same LB and service — there is no CQRS split.

The Counter Service is a stateless REST API running on 3 pods with 100 threads each (300 concurrent capacity). It handles two operations: (1) increment — execute UPDATE counters SET count = count + 1 WHERE key = $1 against PostgreSQL; (2) read — execute SELECT count FROM counters WHERE key = $1. The service processing time is approximately 5ms, but total response time is dominated by the database operation and lock wait time.

PostgreSQL stores a single counters table with one row per counter key. The primary key is the counter key (item ID), and the count column stores the current value as a BIGINT. The table also tracks updated_at and created_at timestamps. There is no partitioning, no read replicas, and no sharding — a single primary handles all reads and writes.

The write path is the bottleneck. Each UPDATE acquires a row-level exclusive lock for the duration of the transaction. Under MVCC, the UPDATE creates a new tuple version and marks the old one as dead (requiring periodic VACUUM). The WAL (Write-Ahead Log) entry for each UPDATE must be flushed to disk for durability, adding 5-15ms per transaction with synchronous_commit=on.

The system has no redundancy at the data layer. If the single PostgreSQL primary fails, both reads and writes stop entirely. There is no failover, no replication, and no cache to serve stale reads during an outage.

The concrete scaling ceiling is approximately 2K-5K increments per second per hot key. At this point, the row lock wait queue saturates the 150-connection pool, read latency degrades due to connection starvation, and the system enters a cascading failure mode. Total system throughput across all keys is higher (PostgreSQL can handle ~20K-50K transactions per second for different rows), but any single hot key hits the row lock wall.