Hotspot Analysis and Mitigation

In one line

Hotspots are rows, keys, partitions, shards, indexes or nodes that receive a disproportionate share of traffic: a celebrity account, a flash-sale product, today's partition, the right edge of a sequential index. Find them with per-key metrics, then mitigate with caching, key splitting (salting), write buffering, replication, or isolating the hot entity.

0/5 · 0%

Think of it like this

One ticket counter at a stadium with a famous player signing autographs next to it. The whole queue piles up at one spot even though other counters are empty.

Key ideas

01
Hot rows: a global counter, an inventory row for a flash sale, an account receiving thousands of payments/sec. Symptoms: lock waits (wait_event_type = Lock), high n_tup_upd on few rows, rising latency on one endpoint.
02
Hot partitions and shards: time-based keys (all writes go to the newest partition), celebrity tenants, skewed hash inputs. Symptoms: one node's CPU and I/O far above the others.
03
Hot index pages: sequential keys insert into the rightmost B-tree leaf; usually fine in PostgreSQL, but under extreme concurrency the last page becomes contended. Hot keys in Redis or DynamoDB exceed per-key or per-partition throughput limits.
04
Mitigations: cache reads (with request coalescing); split writes across N sub-keys (salting: counter:42:{0..15}) and aggregate on read; buffer and batch writes (Redis or Kafka then periodic flush); replicate read-hot data; give a hot tenant its own shard; queue and throttle writes to a hot entity.
05
Measure first: per-key metrics (Redis --hotkeys with the LFU policy, DynamoDB CloudWatch Contributor Insights, pg_stat_statements by parameter via logging), per-shard load dashboards.

Code & diagrams

salting.sqlsql

-- hot balance row for a merchant receiving 5k payments/sec
-- instead of UPDATE merchant_balance SET amount = amount + ? WHERE merchant_id = ?
CREATE TABLE merchant_balance_bucket (
  merchant_id bigint, bucket smallint, amount numeric(18,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (merchant_id, bucket)
);
UPDATE merchant_balance_bucket SET amount = amount + 250.00
WHERE merchant_id = 7 AND bucket = (random() * 31)::int;      -- 32 buckets

SELECT sum(amount) FROM merchant_balance_bucket WHERE merchant_id = 7;

-- find lock hotspots now
SELECT relation::regclass, mode, count(*) FROM pg_locks WHERE NOT granted GROUP BY 1, 2;

Interview problem

The problem

Celebrity post: comments and reads

A celebrity posts; 2M users read the post and comment count per second and 20K comment per second. The post row and its comment partition become hot and the shard holding the celebrity melts. Mitigate.

When it breaks

Salting everything by default

What you see

Every read now aggregates 32 rows or keys; cold data pays the cost and code complexity grows for no benefit.

Fix & prevent

Apply salting only to keys identified as hot (dynamically or by known entity type), and keep a simple path for the rest.

Explain it without notes

What is key salting and what does it cost?

Practice

Name three signals that identify a hot shard and one fix for each cause.

Trade-offs

↔
Hotspot fixes add complexity and eventual consistency; apply them where metrics prove the hotspot.

Done when you can

I can detect hot rows, keys, partitions and shards and choose a mitigation.

Command Palette