yelp

The application writes to both PostgreSQL and Elasticsearch in the same request handler:

def update_business(business):
    db.update(business)                  # Step 1: Write to database
    elasticsearch.index(business)        # Step 2: Write to search index

Advantages:

• Trivially simple — no extra infrastructure
• Changes appear in search immediately (synchronous)
• Easy to understand, debug, and test

Failure mode: If the database write succeeds but the Elasticsearch write fails (network timeout, ES cluster overloaded), the data diverges silently. The database shows updated hours, but search returns stale data. There's no built-in mechanism to detect or repair this drift.

At small scale (<10K businesses), a nightly reconciliation script fixes drift before users notice. At 200M businesses, reconciliation takes hours and can't keep up with change volume.

A CDC connector (Debezium) reads the database's write-ahead log (WAL) and streams every change to Kafka. A consumer reads from Kafka and updates Elasticsearch. The application only writes to the database — it doesn't even know Elasticsearch exists.

How it works:

1. Application writes to PostgreSQL (business update, new listing, etc.)
2. PostgreSQL writes the change to its WAL (transaction log) — this happens anyway as part of normal database operation
3. Debezium reads the WAL entry and publishes a change event to a Kafka topic
4. An index consumer reads from Kafka and upserts the document in Elasticsearch

Advantages:

• Guaranteed consistency — every committed database change eventually reaches the index
• Decoupled — the application has zero knowledge of Elasticsearch
• Replayable — Kafka retains events; replaying the topic rebuilds the entire index
• No performance impact on writes — the application only waits for the database commit

Trade-off: The pipeline introduces operational complexity — Debezium, Kafka, and the consumer all need monitoring, alerting, and failure recovery. CDC lag (typically 1–5 seconds) means the index is slightly behind the database.

Our Choice: PostgreSQL. The write volume (~1.2 QPS) doesn't justify a wide-column store's operational complexity. Relational databases handle our query patterns naturally — paginate by business, query by user, JOIN for analytics — and ACID guarantees simplify the write path. Read replicas handle the read load.

Users shouldn't submit duplicate reviews by double-clicking or network retries. We enforce this at two levels:

Database constraint: A unique index on (user_id, business_id) prevents duplicate rows. If a user tries to submit a second review for the same business, the INSERT fails with a constraint violation — the application returns a clear error message.

CREATE UNIQUE INDEX idx_unique_user_business
ON reviews(user_id, business_id);

Idempotency key: The client sends a unique X-Idempotency-Key header with each review submission. The server checks if a review with that key already exists — if so, it returns the existing review instead of creating a duplicate. This handles network retries transparently.

Both mechanisms are necessary: the database constraint is the safety net, the idempotency key provides a better user experience (no error on retry).

Review photos follow a separate upload path to avoid blocking the review submission:

1. Pre-upload: Client requests a presigned URL from the server: POST /api/v1/uploads/presigned-url
2. Direct upload: Client uploads the photo directly to object storage (S3) using the presigned URL — this bypasses the server entirely, reducing load
3. Attach to review: Client includes the uploaded photo's URL in the review submission body
4. CDN distribution: Photos are served via CDN with aggressive caching — review photos never change after upload

This pattern offloads large binary uploads to object storage infrastructure, keeping the review API lightweight and fast.

Sort results purely by distance from the user's location. Trivially simple and extremely fast — Elasticsearch handles this natively with _geo_distance sort.

Limitation: The closest pizza place might have a 2-star rating with reviews mentioning "cold pizza and rude staff." Users expect quality, not just proximity. Distance-only ranking creates a poor user experience for any query where quality matters more than convenience.

Combine signals with a formula: score = rating × (1 / distance). Higher-rated places rank above closer mediocre ones. This captures both proximity and quality.

Limitation: A brand-new restaurant with a single 5-star review (possibly from the owner) outranks an established 4.5-star restaurant with 500 genuine reviews. Raw star ratings don't account for statistical confidence — small sample sizes produce unreliable averages.

Apply Bayesian smoothing to pull ratings with few reviews toward the global average. The formula:

$smoothed = \frac{\sum stars + prior \times global\_avg}{num\_reviews + prior}$

With prior = 10 and global_avg = 3.7:

• A single 5-star review → smoothed to ~3.8 stars (pulled toward average)
• 500 reviews averaging 4.5 → smoothed to ~4.49 stars (barely affected)

This rewards established businesses with consistent quality over newcomers with inflated ratings.

Limitation: At 50K QPS, applying complex scoring formulas to millions of candidates is too slow. We need a way to limit the scoring to a manageable subset.

Split ranking into two stages:

Stage 1 — Retrieval: The search index returns the top ~1,000 candidates using simple, fast scoring: text relevance (BM25) + geo filter. This leverages precomputed index structures and runs in single-digit milliseconds.

Stage 2 — Reranking: A lightweight reranking service rescores only the 1,000 candidates with richer signals:

• Bayesian-smoothed rating
• Review freshness (recent reviews weighted more)
• Category relevance (exact category match vs. partial)
• User personalization (if available)
• Photo quality signal (businesses with photos rank higher)

Returns the top 20 results to the user.

This is both fast (only 1,000 documents scored) and accurate (rich signals applied in reranking). The reranking service is stateless and horizontally scalable.

Index the lat column and query WHERE lat BETWEEN 40.73 AND 40.77. The B-tree narrows to ~1M rows in that latitude band.

Limitation: Latitude 40.75 spans the entire globe — you get businesses in Spain, China, and everywhere at that latitude. A compound index (lat, lng) helps, but B-trees handle one-dimensional ranges efficiently. The second dimension (longitude) is still a scan within the latitude range. Circular radius queries become rectangular bounding boxes that require expensive post-filtering.

Encode latitude/longitude as a single string: (40.75, -73.99) → "dr5ru7". The key insight: nearby locations share prefixes. The prefix "dr5ru" covers a roughly 1 km² area.

To find nearby businesses:

1. Compute which geohash prefixes intersect the search circle
2. Query for businesses with those prefixes — this is a simple string prefix match, which B-trees handle efficiently
3. Post-filter with exact haversine distance — the candidate set is now small

Why this works: Geohashing reduces a 2D spatial problem to a 1D string prefix problem. Standard indexes, caches, and distributed systems all handle strings well.

Edge case: Grid cells are rectangular, so businesses near cell boundaries may be closer than businesses in the same cell. The solution is to also query adjacent cells (the 8 neighbors). This is well-understood and handled automatically by Elasticsearch's geo_point type.

R-trees group nearby points into bounding rectangles organized hierarchically. A query traverses the tree, pruning branches whose bounding boxes don't intersect the search area. More accurate for complex shapes (polygons, irregular boundaries).

Limitation: R-trees are single-node data structures — harder to distribute across shards. Well-suited for PostgreSQL with PostGIS, but scaling horizontally for 50K QPS requires application-level sharding or read replicas.

Modern geo-indexing systems use spherical cells (Google's S2) or hexagonal cells (Uber's H3). These have mathematically superior properties:

• S2 cells project the sphere onto a cube, then subdivide — cell sizes are more uniform than geohash rectangles
• H3 hexagons have equidistant neighbors (unlike squares, where corner neighbors are √2 farther than edge neighbors)

Limitation: Both require specialized libraries and are less commonly supported by out-of-the-box search engines. Best suited for precision coverage analysis (delivery zones, ride-matching) rather than general proximity search.

Let cached entries expire naturally based on their TTL. No invalidation logic needed — the simplest possible caching strategy.

Limitation: A restaurant permanently closes. With a 1-hour TTL on business metadata, users see "Open" for up to an hour. For critical updates like business closures, hours changes, or safety alerts, passive expiration creates unacceptable staleness.

Publish cache invalidation events whenever data changes:

• Business updated → invalidate biz:{business_id} immediately
• Review created → invalidate agg:{business_id} and reviews:{business_id}:page:1

This provides near-instant freshness for important changes.

Limitation: If the event system (Kafka consumer, invalidation worker) fails or lags, the cache serves stale data indefinitely — there's no fallback expiration.

Combine both strategies: event-based invalidation for immediate freshness on important changes, TTL as a safety net for eventual expiration. If the event system fails, data still expires at the TTL boundary.

Implementation:

• Business metadata changes → fire invalidation event + 1-hour TTL fallback
• Review submitted → fire invalidation for aggregate + first review page + 5-minute TTL fallback
• Search results → TTL-only (1 minute); no event invalidation needed because the cache turns over fast enough

This provides fast freshness when the event system is healthy and guaranteed maximum staleness when it isn't.

Cache the full business page response with a short TTL (10 seconds). At 1,000 req/sec, the cache absorbs 9,990 requests out of every 10,000 — only one request per TTL window reaches the database.

Limitation: All 1,000 requests per second still hit the same Redis key on the same Redis node. The cache layer itself becomes a hotspot. This is the distinction between reducing database load (solved) and distributing cache load (unsolved).

Replicate the hot key across multiple cache nodes: instead of a single biz:viral-restaurant, create biz:viral-restaurant:shard:{0-9}. Each request randomly picks a shard. Load distributes across 10 Redis nodes instead of concentrating on one.

Limitation: When the TTL expires, all replicas expire simultaneously. The next burst of requests all see a cache miss — a thundering herd hits the database at once.

When multiple requests hit a cache miss for the same key simultaneously, only the first request fetches from the database. All others wait for that result. 1,000 simultaneous cache misses collapse into a single database query.

Implementation: Use a distributed lock or an in-process single-flight library (e.g., Go's singleflight, Java's CacheLoader).

Limitation: Only helps when many requests arrive during the same cache-miss window. Doesn't help with different cache keys (e.g., paginated review pages — each page is a separate key).

When a service is overwhelmed, serve partial data instead of failing entirely:

• Return cached business info without live reviews: "Reviews are temporarily unavailable"
• Pre-cache the first 5 review pages; return a "try again later" message for deeper pages
• If the Review Service is unresponsive, serve the business page without the review section

Users see the business name, hours, and location — the most critical information for deciding whether to visit. Reviews load when traffic subsides.

The application writes to both the database and the search index in the same request handler:

def update_business(business):
    db.update(business)               # Step 1: primary write
    search_index.upsert(business)     # Step 2: index write

The simplest approach — no extra infrastructure, no event pipeline, no operational overhead.

Failure Mode: If step 2 fails after step 1 succeeds, the database and index silently diverge. There's no built-in mechanism to detect or repair the inconsistency. Wrapping both in a distributed transaction adds latency and couples availability — if the search index is down, database writes fail too.

Best for: Most applications — startups, internal tools, any system where a small team values simplicity. A nightly reconciliation script catches drift before users notice. At Yelp's scale (200M businesses), reconciliation becomes impractical — scanning 200M rows takes hours.

A scheduled job (every 5–15 minutes) queries the database for recently changed records and bulk-updates the index:

def reindex_job():
    changed = db.query(
        "SELECT * FROM businesses WHERE updated_at > %s",
        [last_run_timestamp]
    )
    search_index.bulk_upsert(changed)

Limitation: The index can be up to 15 minutes behind the database. A restaurant marks itself as closed, but search results show it as "Open" for the full reindex interval. The batch job also creates periodic load spikes on the database — a thundering herd of reads every 5 minutes.

Best for: Systems where minutes of staleness are acceptable and operational simplicity matters more than freshness — internal tools, analytics dashboards, secondary search features.

A CDC connector (Debezium) tails the database's transaction log (WAL) and streams changes to the index in near real-time. The database doesn't know the pipeline exists — no application code changes needed.

CDC solves the consistency problem but introduces four dimensions of operational complexity:

1. Stream Joins. Yelp stores businesses and reviews in separate tables. CDC emits row-level change events — a review event carries business_id but not the business name, hours, or coordinates. The consumer must hydrate each event by joining with business data, either via a stream processor (Kafka Streams, Flink) or a lookup service. This is significantly more complex than the SQL JOIN that dual write can do inline.

2. Infrastructure Sprawl. Dual write has two components: the application and the search index. CDC introduces a longer chain: App → DB (WAL) → Connector → Kafka → Consumer → Search Index. Each link needs monitoring, alerting, and operational runbooks. If the connector crashes and the database log rotates before recovery, those changes are permanently lost — a full reindex is the only recovery path.

3. Schema Evolution. An ALTER TABLE ADD COLUMN changes the binary log format. The connector may not recognize the new field, consumers crash on unknown columns, and the search index has no mapping for them. Production CDC pipelines use a schema registry (Confluent Schema Registry, AWS Glue) that enforces compatibility rules (backward, forward, or full compatibility) so schema changes propagate safely through the pipeline.

4. Ordering and Duplication. A user creates a review, then deletes it seconds later. If the delete event arrives before the create event — possible when the consumer parallelizes across partitions — the review becomes a "zombie": permanently in the index, deleted in the database. The fix is partitioning by entity ID so all events for a given entity are processed sequentially within a single partition.

Context: Your search index has drifted out of sync with the database. Some businesses display incorrect hours, permanently closed businesses still appear in search results, and newly registered businesses aren't discoverable. Users are filing complaints.

Discussion Points:

• How do you detect index drift? What metrics or monitoring would surface the problem — consumer lag, document count divergence, spot-check sampling?
• How do you design reindex/backfill jobs that don't impact production traffic? (Blue-green indexing, throttled reindex, off-peak scheduling)
• What's your strategy for zero-downtime index schema changes? (Alias swapping, reindex-in-background, compatible vs. breaking schema changes)
• How do you handle the trade-off between index freshness and indexing throughput? (Batch size tuning, backpressure, consumer scaling)
• What organizational processes ensure index health is monitored and maintained over time?

Context: Yelp expands to Europe. Users in Paris searching for restaurants should hit European infrastructure, not US servers. But a business owner in the US updates their Paris restaurant's hours — that update must propagate to Europe.

Discussion Points:

• How do you route users to the nearest region while maintaining data consistency? (GeoDNS, Anycast, region-aware load balancing)
• Where does the source-of-truth database live? How do you handle cross-region writes? (Single-leader, multi-leader, CRDTs, conflict resolution)
• What's acceptable replication lag for search index vs. business data vs. reviews?
• How do you handle a region outage? Failover strategy — active-passive vs. active-active?
• What trade-offs exist between latency, consistency, and operational complexity in a multi-region setup?

Context: A competitor is paying people to leave 1-star reviews on a client's restaurant chain. Legitimate business owners are furious. Your trust & safety team needs engineering solutions alongside policy.

Discussion Points:

• What signals indicate fake or coordinated review attacks? (Burst patterns, new account clustering, IP correlation, review text similarity, rating distribution anomalies)
• How do you balance aggressive fraud detection with not blocking legitimate negative reviews? (False positive cost vs. false negative cost)
• What's the user experience for flagged reviews? Immediate removal vs. held for manual review vs. visible with reduced trust weight?
• How do you handle appeals from businesses who claim reviews are fake? (Automated review, human escalation queue, transparency reports)
• What cross-functional processes involve legal, support, and engineering in abuse response?