Design an online auction

Every auction is one row holding its high bid, high bidder, hidden maximum, end time, status and a version. Every bid is a maximum, and a bid is a single atomic conditional update: accept it only if the auction is open, the database clock is before the end time, and it beats the price by the minimum raise, then use the three-case rule on the leader's hidden maximum to pick the leader and the price. The database serialises writes to the row, so two racing bidders cannot both win, and the rows changed tell us accepted or not. A bid key makes retries safe.

At 700 bids a second at peak, and 30 a second on the hottest auction, one row is plenty. Accepted bids go to an event queue, and push gateways send watchers the latest price at most twice a second, which cuts 600,000 sends a second to 40,000. Late bids extend the end time inside the same update, with a cap. A closer finds ended auctions by an index, and closes each with another conditional update, so only one closer wins and a moved end time is respected.

The order has a unique key on the auction, and the charge has an idempotency key, so settlement happens once. The automatic bidding rule runs inside that same atomic step, as a CASE in the update or under a row lock.

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

• 500,000 auctions end a day with 12 bids each, and peak load is 10 times the average.
• The hottest auction gets 300 bid attempts in its last 10 seconds and has 20,000 watchers.
• At peak, 2 million people are watching auctions, and one push gateway holds 50,000 connections. A bid log row takes 100 bytes.
• 5% of the day's auctions end in the same busiest minute.

The arithmetic:

Bids per second

500,000 × 12 = 6M a day ÷ 86,400 s ≈ 69

≈ 70/s on average, 700/s at a 10x peak

Load on the hottest auction

300 attempts ÷ 10 s

30/s on one row. A database handles that easily, so the risk is correctness, not speed

Pushes for that auction

20,000 watchers × 30 bids/s = 600,000/s; coalesced to 2 a second: 20,000 × 2

40,000/s, 15 times fewer

Push gateways

2,000,000 watchers ÷ 50,000 connections each

40 gateways

Auctions closing in the busiest minute

5% × 500,000 = 25,000 ÷ 60 s

≈ 420 closings a second

Bid log storage

6M × 100 bytes = 600 MB a day × 365

≈ 219 GB a year

The trap is read-then-write. Both bidders see a price of 100. One bids 110, the other bids 120. If the server reads the price, compares in application code and writes, the 110 write can land after the 120 write and quietly replace it. The cure is to make the comparison and the write one step in the database.

A conditional update does it. In plain words: apply the bid where the auction id matches, the status is open, the end time is later than now, and the bid is at least the current price plus the minimum raise. It then adds one to the version. Every bid is a maximum, so the update also carries the hidden-maximum comparison from the third deep dive: a CASE on the stored maximum picks the new leader and the new price in the same statement. The database serialises writes to a single row, so of two competing bids exactly one runs against the old price, and the other runs against the new one and may fail. The number of rows changed is the answer: one means accepted, zero means rejected. An accepted bid does not always take the lead. It may only raise the price against a higher hidden maximum. Insert a row into the bid log in the same transaction, using the new version as its sequence number.

Three details matter.

• Use the database clock for the end time check. The clocks of web servers and phones differ by seconds.
• Use a bid key. The client sends a unique key with each bid, and the bid log has a unique constraint on the auction and the key. A retry after a timeout then returns the first result and cannot place a bid twice.
• Return the current state on a rejection, so the bidder immediately sees the price they must beat.

A single writer per auction is the alternative. Route all bids for an auction to one process, chosen by hashing the auction id, which takes them one at a time in memory. Ordering is then exact and extra rules, such as the three-case automatic bidding rule, are plain code. The cost is failover: when that process dies, another must take over with the right state, and each bid must be saved before it is acknowledged. At 30 bids a second on the hottest auction, the conditional update is enough. Choose a single writer only if one row becomes a real bottleneck.

The server clock decides. A phone clock can be wrong by minutes. The page shows a countdown using the offset between the phone and the server, measured from a response, but the rule is that a bid counts if the database records it before the end time. State this plainly in the rules. A bid that was sent in time but arrives late is late.

Anti-sniping. A bidder who waits until the last second leaves no time for others to answer. A common fix is a soft close: if an accepted bid lands in the last 2 minutes, move the end time to 2 minutes after that bid. Do it inside the same conditional update as the bid, by setting the end time to the larger of the old end time and now plus 2 minutes. The bid and the extension then succeed or fail together. Cap the total extensions, for example at 24 hours, so an auction cannot last forever.

Finding ended auctions. Keep an index on status and end time. The closer asks every second for open auctions whose end time is in the past. An in-memory timer per auction is tempting, but timers are lost on a restart, and they do not follow an end time that moves. The index query is simple and survives crashes.

Close with a conditional update. Because extensions can happen between the closer's read and its write, the close repeats the condition: set the status to closed and the winner to the high bidder, where the status is still open and the end time is still in the past. If a late bid just extended the auction, zero rows change and the closer moves on. If two closers run, exactly one gets a row.

Settle once. Write the order request in the same transaction as the close, or give the order a unique key on the auction id. A closer that crashes after the close finds the closed auction with no order the next time and creates it. The charge carries the auction id as an idempotency key, so a retry cannot bill the winner twice. If the reserve price was not met, close with no winner.

Pushing prices. Each watcher keeps a connection open to a push gateway, using a WebSocket or server-sent events, and subscribes to an auction id. The gateway needs only the events for auctions its watchers care about. If it forwarded every bid, the hottest auction would cost 20,000 × 30 = 600,000 sends a second. Instead it keeps the latest event per auction and sends at most twice a second, which is 40,000 sends a second.

Each event carries the version from the database row. A client keeps the highest version it has seen, so a late or repeated message does no harm and a skipped message costs nothing, because the next one holds the latest price anyway. Do not delay an end-time extension. It changes the countdown, so send it at once. A client that connects late reads the current state from the API first and then follows the stream. A client that cannot keep a connection falls back to polling every few seconds.

Automatic bidding. Every bid is a maximum, and a plain bid is one whose maximum equals its amount. All bids go through the same rule, applied atomically. The auction row stores the leader, the visible price and the leader's hidden maximum. When a new maximum M arrives from another bidder, three cases cover everything:

• If M is below the price plus the minimum raise, reject it.
• If M is not above the leader's maximum, the leader stays. The price becomes the smaller of the leader's maximum and M plus the raise.
• If M is above the leader's maximum, the new bidder leads. The price becomes the smaller of M and the old leader's maximum plus the raise.

A worked example: the leader has a maximum of 150, the price is 120, and the raise is 5. A new bidder with a maximum of 140 does not win, but pushes the price to 145. A new bidder with 200 leads at 155. Hidden maximums are never sent to any client.

This is more than one comparison, so apply it atomically. Either encode the three cases in the single conditional update, with a CASE on the hidden maximum, or run it in a transaction that locks the auction row, or read the row with its version and write only if the version is unchanged, retrying on conflict. A plain bid of 125 against a price of 120 and a hidden maximum of 150 therefore leaves the leader in place and moves the price to 130. Log each automatic raise as its own bid so the history explains every price change.