2 / 5 Schema Design & Migrations

The Hot Counter

Posts carry a denormalized like_count that every like increments with an UPDATE. A post goes viral at roughly 5,000 likes per second and like latency jumps to seconds, though CPU and disk are idle. What is happening and what fixes it?

A

The query planner stopped using the primary key for the update; add a covering index on (id, like_count)

B

Every increment contends for the same row lock and serializes; shard the count across N rows and sum on read

C

The index on like_count is fragmented by the write rate; rebuild it and lower the table's fillfactor to reduce churn

D

The transactions are hitting serialization failures; lower the isolation level to read committed