Primary-Key Access Patterns

Foundations

A database is chosen by its access patterns, the queries it must answer and how often, not by the product's name. List every query with the key it carries and its rate. The hottest query should be a lookup by primary key: in a B-tree index, that takes a few page reads even among billions of rows, and the top levels stay in memory.

Every secondary index speeds up one query and slows every write, since each insert must update it too, so add one only for a query you actually run. Name the stores you are not using and why: a search engine for full-text queries, a graph database for traversals, object storage for large files. If no query needs them, they are cost without benefit.

Take a service with two queries: insert a record by id at 50 writes per second, and read it by id hundreds of times per second. One relational table keyed by id serves both, and a few terabytes fit one primary. An index on owner_id earns its place only if listing an owner's records is required.

• Interfaces

  That lesson writes each endpoint and its row before any box is drawn. These blocks serve those reads and writes.

  Open lesson

Case Scenario

To see how access patterns pick the store, consider a URL shortener like bit.ly: it inserts a mapping from a short code to a long URL and later reads it by that code, at about 40 creates per second and a few terabytes after years. One relational table keyed by code makes every redirect a primary-key read.

A database choice made from the product name invites unnecessary machinery. This shortener performs only two important operations: insert a mapping and retrieve it from a short code, at about 40 creates per second and a few terabytes after years. Redirect must remain one primary-key read, and every additional index or store must justify a real query.