Spread writes that all land on one row

How to do it

An update holds its row lock until the transaction ends, so a counter that every request bumps makes those requests wait in line. The same happens to one hot key in a cache cluster, since one key lives on one shard. Split the counter into a fixed number of slots, such as 16, and add one to a random slot. Read the total as the sum of the slots. Or add the counts up in memory or in a shared store and write them every few seconds. Keep exact checks, such as stock that must not go below zero, in one guarded update, as Wrap multi-step changes in a transaction says. Run the load as Test load against a budget describes.

Examples

JavaScript example

const COUNTER_SLOTS = 16;

export async function countView(db, pageId) {
  const slot = Math.floor(Math.random() * COUNTER_SLOTS);
  await db.query(
    'INSERT INTO page_views (page_id, slot, views) VALUES ($1, $2, 1) ON CONFLICT (page_id, slot) DO UPDATE SET views = page_views.views + 1',
    [pageId, slot],
  );
}

Python example

import random

COUNTER_SLOTS = 16

def count_view(cursor, page_id: int) -> None:
    cursor.execute(
        "INSERT INTO page_views (page_id, slot, views) VALUES (%s, %s, 1) "
        "ON CONFLICT (page_id, slot) DO UPDATE SET views = page_views.views + 1",
        (page_id, random.randrange(COUNTER_SLOTS)),
    )

def total_views(cursor, page_id: int) -> int:
    cursor.execute("SELECT COALESCE(SUM(views), 0) FROM page_views WHERE page_id = %s", (page_id,))
    return cursor.fetchone()[0]

How to check

Run the load test at the planned peak on staging, with PostgreSQL's log_lock_waits = on and deadlock_timeout = 100ms or the same setting in your database. The log shows no lock wait on a counter or total table.

Sources

• PostgreSQL, Error reporting and logging (log_lock_waits)

Related rules

• SCL-42 Spread the reads of one hot cache key in Scaling
• SCL-8 Keep locks and counters in a shared store in Scaling
• DAT-14 Wrap multi-step changes in a transaction in Data and databases
• TST-36 Test load against a budget in Testing