mufassall

Data & storage / 2 / 11

Database indexes

An order history page gets slow as the table grows to 40 million rows. We read the query plan, add the right B-tree indexes, and see what every index costs on writes.

  • 13 min
  • Intermediate
  • 11 steps
Page p95
2 400 ms
Rows read per query
40 000 000
Insert per row
0.3 ms
Indexes on orders
1
Open in Lab
40%95%
  • request
  • reply
  • redelivery
  • duplicate
  • lost

Numbers are a model scenario, not measurements

The real request

Orders API → Orders DB

Request

SELECT id, status, total FROM orders
WHERE customer_id = 421
ORDER BY created_at DESC LIMIT 20;2

Response

(20 rows)
Time: 2391.7 ms3
  1. customer_id — The filter. Nothing tells the database where the rows of customer 42 are.
  2. LIMIT — We want only 20 rows. The database still has to find all candidates first.
  3. Time — 2.4 seconds for 20 rows. That is the time to read the whole table.
Course: 42 lessons
  1. 01 Foundations

    1. The life of a request
    2. Latency and throughput
    3. Napkin math
  2. 02 Scaling

    1. Scaling up vs scaling out
    2. Load balancing
    3. Stateless servers
    4. Caching
    5. CDN and edge caching
  3. 03 Data & storage

    1. SQL and NoSQL
    2. Database indexes
    3. Storage engines: B-tree and LSM
    4. Database replication
    5. Sharding
    6. Consistent hashing
    7. Unique IDs
    8. CAP: when the link breaks
    9. Consensus and leader election
    10. Transactions
    11. File uploads
  4. 04 Communication

    1. API styles
    2. Message queues
    3. Pub/sub and event logs
    4. Real-time updates
    5. Reliable callbacks
  5. 05 Reliability

    1. Redundancy and failover
    2. Rate limiting
    3. Timeouts and retries
    4. Observability
  6. 06 Architecture

    1. Microservices
    2. Service discovery
    3. Sagas and the outbox
    4. Batch vs stream
  7. 07 Security

    1. Authentication
    2. Authorization
    3. Encryption and secrets
  8. 08 Shipping

    1. Testing
    2. CI/CD: from commit to production
  9. 09 Whole systems

    1. From requirements to design
    2. Design a URL shortener
    3. Design a news feed
    4. Design a chat
  10. 10 Business processes

    1. Payment gateway