Design a price drop tracker

I would treat this as two pipelines around one database. The first decides what to check. Each product has a next check time, set by its watcher count: every 15 minutes for 50,000 popular products, hourly for 450,000, every 6 hours for the rest. That is about 390 fetches a second instead of 5,600. A scheduler picks due products with a lock-skipping query and puts jobs on a queue with a lane for each shop, and every lane has a token bucket so no shop sees more than 20 requests a second.

Fetchers parse the price into minor units, hold big moves until a second reading agrees, and store a history row only when the price changes, which is about 15 GB a year. The second pipeline sends alerts. A confirmed drop becomes an event. The notifier scans an index on product and target price, so it reads only matching watches. For each one, a conditional update moves the watch from armed to triggered, and only the winner sends, with an idempotency key.

The watch re-arms when the price rises back above the target. I would re-check the price before sending. The trade-off is lateness for quiet products in return for being kind to the shops.

These numbers are assumptions for the exercise, not facts about a real system:

• 5 million products are watched by 20 million watches, so a product has 4 watchers on average.
• Check frequency follows interest: 50,000 products with 100 or more watchers every 15 minutes, 450,000 with 10 to 99 watchers every hour, and 4.5 million with fewer than 10 every 6 hours.
• 5% of checks find a changed price, and 40% of those changes are drops. A history row takes 24 bytes.
• The busiest shop holds 4% of products, and we promise to stay under 20 requests a second to it. Of the watchers of a product that drops, 25% have a target at or above the new price.

The arithmetic:

Checks per day with tiers

50,000 × 96 + 450,000 × 24 + 4.5M × 4 = 4.8M + 10.8M + 18M = 33.6M; ÷ 86,400 s

33.6M a day, about 390/s

Checks per day with one 15-minute interval

5M × 96 = 480M a day ÷ 86,400 s

≈ 5,600/s, 14 times the tiered load

Load on the busiest shop

4% × 33.6M = 1.344M a day ÷ 86,400 s

≈ 16/s, just under the 20/s promise

History storage

33.6M × 5% = 1.68M rows × 24 bytes ≈ 40 MB a day; × 365

≈ 15 GB a year, against ≈ 290 GB if every check were stored

Alerts per second

1.68M changes × 40% drops = 672,000; × 4 watchers × 25% = 672,000 alerts a day ÷ 86,400 s

≈ 8/s on average

Checking everything every 15 minutes would mean about 5,600 page fetches a second. Checking by interest brings that to about 390 a second, because most products have only a few watchers and nobody minds if they are 6 hours old. So each product gets an interval from its watcher count, and a popular product is checked 24 times more often than a quiet one.

Store a next check time on every product row, with an index on it. The scheduler runs a short loop: select products whose next check time has passed, oldest first, and in the same statement move the time forward by the product's interval. A second scheduler instance running at the same moment skips rows that are locked, so no product is queued twice. Add a little random jitter to each interval so a whole tier does not fall due in the same minute.

Rate limits are per shop, not per fetcher. The queue has a lane for each shop. Each lane has its own token bucket, say 20 requests a second, and a fetcher only takes a job from a lane that has a token. Adding more fetchers then speeds up many shops at once but never raises the load on one. Respect the shop's published rules, and prefer an official feed where one exists.

Here is what happens when a shop is too big. If one shop held 20% of products, it would need 20% × 33.6M = 6.72M checks a day, about 78 a second, against a 20-a-second budget. The budget allows only 1.728M a day, so intervals for that shop stretch about 4 times. Stretch the quiet products first and protect the popular tier.

Be gentle with failures. On a 429 or 503 response, pause that shop's lane and back off exponentially. Use conditional requests with a validator such as an ETag, so an unchanged page returns a tiny 304 reply. If a page fails to parse three times in a row, lengthen its interval and flag it for a person to look at, rather than hammering it.

A fetcher sees many things that look like a price change but are not: a parse that returns 0 or 9999 when the layout changes, an out-of-stock page with no price, a different price for another region or a logged-in user, and a price shown after a coupon. If every one of these became an alert, shoppers would stop trusting the service.

Start by normalising. Parse the price into whole minor units, such as 4000 for 40.00, together with a currency code, and parse stock status separately. Reject a reading that is zero, negative or missing. An out-of-stock reading updates the stock status and never the price.

Then apply a confirm rule to big moves. A reading is accepted at once if it is within 30% of the last good price. A larger move, or any move that would trigger an alert, is held as pending, and a second fetch from a different machine runs a minute or two later. Only two matching readings make it real. This costs one extra fetch on a few percent of checks and removes most one-off glitches.

Store changes, not checks. The product row keeps the current price and a last-seen time. A history row is written only when the confirmed price differs from the last one. At 5% of checks, that is about 40 MB a day instead of 800 MB. To draw a chart, treat each row as a step: the price holds until the next row.

Guard against flapping. A shop that flips between 45 and 40 every hour would otherwise cause an alert on every dip. Keep a short quiet period per product, and rely on the per-watch state in the next section to stop repeats.

The drop arrives as an event with the product, the new price and a price change id. The notifier must find the watches whose target is at or above that price. Index the watches by product and target price together. A range scan then reads only the matching rows. If a product has 100,000 watchers and 25% have a target that is met, it reads 25,000 index entries, not 100,000.

Read them in pages of a few hundred and work through the pages in parallel. For a very large product, split the work by ranges of watch id and put each range on the queue as its own task, so no single worker is stuck for minutes.

Dedupe with a claim. Every watch has a state: armed or triggered. Before sending, the notifier runs one conditional update that sets the watch to triggered, records the price, and applies only where the state is still armed. If it changed one row, this worker owns the alert. If it changed none, someone else already did, so skip it. The queue delivers events at least once, so the same event may arrive twice. The claim makes that harmless.

A crash between the claim and the send would lose an alert. To close the gap, write the alert to an outbox row in the same transaction as the claim, and let a sender drain the outbox with an idempotency key made from the watch id and the price change id. The email or push provider then drops a repeat of the same key.

Re-arming. A watch goes back to armed when the price rises above its target again, or when the shopper edits it. That way a later, separate drop alerts again, but one long sale sends exactly one message.

Last, re-read the product's current price just before sending. If the deal has already ended, skip the message. A stale alert that leads to a full-price page is worse than a late one.