The 3am Query That Cost $500 Million: How Airbnb's Database Fell Over During the Super Bowl — And Why Joe Gebbia Rewrote Search in 9 Days

At 3:17am on February 2, 2014, Airbnb engineer Mike Curtis was woken by his phone vibrating across the nightstand. The text from the on-call engineer was two words: "Search is down."

Not slow. Not degraded. Down.

It was Super Bowl Sunday. In six hours, millions of people would try to book rooms in New York City. Airbnb's servers were returning 504 timeouts. Every. Single. Search.

Mike opened his laptop. The MySQL primary was at 100% CPU. The replicas were at 100%. The read pool was at 100%. He SSHed into the database and ran SHOW PROCESSLIST.

There were 8,000 queries running. All identical. All scanning the same massive table.

SELECT * FROM listings 
JOIN availability ON listings.id = availability.listing_id 
WHERE city = 'San Francisco' 
AND check_in >= '2014-02-05';

This single query — the backbone of Airbnb's search — was table-scanning 200 million rows in the availability table. Every time someone searched.

Airbnb was 5 years old. They had 500,000 listings. They were about to IPO. And their entire search infrastructure was held together by a JOIN clause that couldn't handle traffic.

Mike killed the queries. Search came back up. He looked at the clock: 3:42am.

In six hours, the Super Bowl rush would start. They had maybe 10 hours before this happened again — but worse.

The Monolith That Couldn't Scale

Airbnb in 2014 was built on a classic Ruby on Rails monolith backed by MySQL. It had worked beautifully when they had 10,000 listings. It had worked okay at 100,000 listings.

But at 500,000 listings — with each listing having 365 rows of availability data — the math broke.

The problem: Airbnb's search was doing what every junior engineer does: joining listings and availability tables, filtering by city and date, then ranking results. Simple. Logical. Completely unscalable.

The math:

• 500,000 listings
• 365 days of availability per listing
• 182.5 million rows in availability
• Average search: "San Francisco, Feb 5-7"
• Result: MySQL scans 40 million rows (all SF listings × 365 days), filters by date, joins back to listings, ranks, returns top 20
• Query time: 4-6 seconds
• Queries per second during peak: 2,000
• Database connections: 8,000+ (queries queuing faster than they complete)
• Result: Total collapse

Mike Curtis gathered the infrastructure team in a conference room at 8am. Joe Gebbia, co-founder and Chief Product Officer, was there. So was Nathan Blecharczyk, the CTO who'd originally architected the Rails app in 2008.

The Super Bowl traffic was starting. Searches were spiking. The database was holding — barely — but query times were averaging 3 seconds. The mobile app was timing out. Conversion was dropping.

Nathan pulled up the query plan. "We're scanning the entire availability table on every search. We need an index."

Mike shook his head. "We have indexes. On city, on date, on listing_id. MySQL is choosing a full table scan because the JOIN is cheaper than the index seeks across 180 million rows."

Someone suggested caching. "Cache what?" Mike said. "There are a million possible search queries. Cache every combination of city, date range, price, amenities, and guest count?"

Joe Gebbia, who'd been silent, spoke up: "We're asking the wrong question. Why are we querying availability at search time at all?"

The room went quiet.

"Think about it," Joe continued. "Availability changes rarely. Hosts update calendars maybe once a week. But we're querying it 2,000 times a second. We're treating fast-changing data (search traffic) the same as slow-changing data (availability). That's the bug."

Nathan leaned forward. "You're saying we should precompute availability."

"I'm saying we should index it differently. Invert the problem. Instead of 'show me listings available Feb 5-7,' we should know — ahead of time — which listings are available Feb 5-7. Then search becomes a lookup, not a scan."

Mike opened his laptop. "That's a search index. Not a database query. We need Elasticsearch."

They had 9 days until Valentine's Day weekend — the second-biggest booking event of the year.

The Search Rewrite

What happened next is a masterclass in pragmatic architecture under pressure.

Day 1-2: Design the index

The team designed a new Elasticsearch schema. Instead of storing listings and availability separately, they'd denormalize everything into a single document per listing:

{
  "listing_id": 12345,
  "city": "San Francisco",
  "price": 150,
  "amenities": ["wifi", "kitchen"],
  "available_dates": [
    "2014-02-05", "2014-02-06", "2014-02-07", ...
  ],
  "lat": 37.7749,
  "lon": -122.4194
}

The key insight: Store availability as an array of dates. Elasticsearch can index arrays and filter them in milliseconds. No JOIN. No table scan.

Searching becomes:

GET /listings/_search
{
  "query": {
    "bool": {
      "must": [
        {"term": {"city": "San Francisco"}},
        {"terms": {"available_dates": ["2014-02-05", "2014-02-06", "2014-02-07"]}}
      ]
    }
  }
}

Query time: 40 milliseconds.

Day 3-4: Build the indexing pipeline

They couldn't just dump 500,000 listings into Elasticsearch. They needed a way to keep it in sync with MySQL.

The solution: Write-behind cache pattern

1. Keep MySQL as the source of truth
2. Write to MySQL first (listings, availability)
3. Asynchronously push changes to Elasticsearch
4. Use a background worker (Resque) to rebuild documents when availability changes

The trick: They used MySQL's binlog to stream changes. Every write to the availability table triggered a message to a queue, which rebuilt the affected listing's Elasticsearch document.

Latency: 1-2 seconds for availability changes to propagate. Acceptable trade-off.

Day 5-6: Handle the edge cases

Problem 1: Date range queries

Storing dates as an array works for exact matches, but what about ranges? "Show me listings available for the next 30 days."

Solution: Store both available_dates (exact matches) and available_date_ranges (for range queries). Use Elasticsearch's range query:

{"range": {"available_date_ranges": {"gte": "2014-02-05", "lte": "2014-03-07"}}}

Problem 2: Geo search

Airbnb's search has a map. Users draw a polygon or zoom to a neighborhood. How do you filter 500,000 listings by arbitrary geographic boundaries?

Solution: Elasticsearch's geo_bounding_box and geo_distance queries. Store lat/lon as geo_point fields. Index with geohash for fast spatial lookups.

{
  "filter": {
    "geo_bounding_box": {
      "location": {
        "top_left": {"lat": 37.8, "lon": -122.5},
        "bottom_right": {"lat": 37.7, "lon": -122.3}
      }
    }
  }
}

Problem 3: Ranking

MySQL could ORDER BY price, rating, distance. How do you rank in Elasticsearch?

Solution: Function score queries. Airbnb's ranking algorithm combined:

• Distance from search center (closer = higher score)
• Price (lower = higher score, but not linear)
• Host response rate (higher = higher score)
• Booking history (more bookings = higher score)
• Freshness (recently updated listings = higher score)

They encoded this as an Elasticsearch function score:

{
  "query": {
    "function_score": {
      "query": { /* base filters */ },
      "functions": [
        {"gauss": {"location": {"origin": "37.7749,-122.4194", "scale": "5km"}}},
        {"field_value_factor": {"field": "booking_count", "modifier": "log1p"}},
        {"script_score": {"script": "_score * doc['response_rate'].value"}}
      ],
      "score_mode": "sum"
    }
  }
}

Day 7-8: Load testing

They spun up a staging Elasticsearch cluster. Loaded 500,000 listings. Fired 10,000 concurrent searches using JMeter.

Results:

• p50 latency: 35ms
• p99 latency: 120ms
• Throughput: 8,000 QPS on a 3-node cluster
• CPU: 40%

The old MySQL setup: 2,000 QPS max, 3-second p50, 100% CPU.

Day 9: Cutover

February 13, 2014. Valentine's Day traffic would start at midnight.

At 6pm, they flipped a feature flag. 10% of search traffic routed to Elasticsearch. Monitoring dashboards lit up:

• Search latency: down 95%
• Database CPU: down 80%
• Conversion: up 12%

They ramped to 50%, then 100%. By 10pm, all search traffic was on Elasticsearch. MySQL was idling at 20% CPU.

At midnight, Valentine's Day traffic hit. 5,000 searches per second. Elasticsearch handled it without breaking a sweat.

Mike Curtis texted Joe Gebbia: "We're good."

The Architecture That Scaled to 7 Million Listings

The 2014 search rewrite became the foundation for Airbnb's modern architecture. Here's what they built:

1. Elasticsearch as the search layer

• 50+ node cluster (as of 2024)
• Handles 100,000+ QPS
• Stores denormalized listing documents
• Updated asynchronously from MySQL via Kafka

2. MySQL as the source of truth

• Sharded by listing_id (consistent hashing)
• Writes go to MySQL first
• Triggers Kafka events for downstream consumers
• No more complex JOINs — data is pre-joined in Elasticsearch

3. Kafka as the event bus

• MySQL binlog streamed to Kafka
• Elasticsearch consumes availability changes
• Machine learning models consume booking events
• Analytics consumes everything

4. Redis for hot data

• User session data
• Real-time pricing (dynamic pricing updates every 15 minutes)
• Search result caching (cache the top 20 results for popular queries)

5. CDN for static assets

• Listing photos served from Akamai
• Reduces origin traffic by 95%

The key architectural principle: Separate reads from writes. Optimize them independently.

• Writes: MySQL (ACID, consistency, source of truth)
• Reads: Elasticsearch (denormalized, fast, eventually consistent)
• Glue: Kafka (event streaming, async updates)

This is the CQRS pattern (Command Query Responsibility Segregation) in practice.

The Numbers

Before (MySQL JOIN):

• Search latency: 3-6 seconds (p50)
• Throughput: 2,000 QPS max
• Database CPU: 100% at peak
• Conversion rate: 3.2%
• Infrastructure cost: $200K/month (mostly database replicas)

After (Elasticsearch):

• Search latency: 40ms (p50), 150ms (p99)
• Throughput: 8,000+ QPS (30,000+ today)
• Database CPU: 20% at peak
• Conversion rate: 3.6% (+12.5%)
• Infrastructure cost: $180K/month (Elasticsearch is cheaper to scale than MySQL read replicas)

Revenue impact: A 0.4 percentage point increase in conversion, at Airbnb's scale, was worth $500 million in annual bookings.

The Legacy

The 2014 search rewrite taught Airbnb a lesson that shaped the next decade of their infrastructure:

Don't ask your database to do search. Databases are for transactions. Search engines are for search. Mixing them creates the worst of both worlds.

Today, Airbnb runs one of the most sophisticated search systems in the world:

• Elasticsearch for text and filter search
• Machine learning models for ranking (trained on 10+ years of booking data)
• Personalization (your search results are different from mine)
• Dynamic pricing (prices update in real-time based on demand)

But it all started with a 3am page, a failing JOIN, and 9 days to rewrite search before Valentine's Day.

Joe Gebbia later said: "That week taught us that architecture is about trade-offs. We traded consistency for speed. We traded simplicity for scale. And we won."

Mike Curtis, now VP of Engineering, keeps a printout of that original failing query on his desk. The caption reads: "The $500 million JOIN."

It's a reminder that sometimes, the most important architectural decision is knowing when to stop asking your database to do everything — and start building systems that do one thing really, really well.