Row Lock Roulette

Your view counter is a single Postgres row updated with UPDATE counters SET n = n + 1 WHERE id = 'global'. At about 4,000 increments per second write latency climbs to hundreds of milliseconds and lock wait dominates, while CPU on the box sits at 30 percent. What is happening and what is the standard fix?

Autovacuum has fallen behind on the counters table, so tuning its thresholds lets the single-row update keep up

Every writer serializes on one row lock, so split it into N shard rows and sum them on read

The lookup by id is doing a sequential scan, so adding a B-tree index on the id column removes the lock waits

The connection pool is undersized and writers queue before reaching the database, so raising the pool maximum clears it