Scalable, API-Oriented Database Design

A schema that will primarily be accessed through a REST or GraphQL API has design pressures beyond correctness and normalization: the shapes of data the API needs to serve efficiently, at scale, under real traffic, become a design input in their own right. A table can be perfectly modeled relationally and still make for a slow, chatty, or hard-to-paginate API if its structure doesn't anticipate how the API layer will actually read from it.

Designing for API consumption means thinking about pagination from the start, keeping the "hot path" — the handful of endpoints hit constantly, like a list or feed endpoint — free of expensive joins, and choosing keys and identifiers that work well as opaque values crossing a network boundary.

Why it matters

An API that lists records page by page looks fine with a hundred rows and collapses with ten million if the pages are fetched the naive way. Exposing sequential ids also tells competitors how many customers you have and invites people to guess other users' records. Both are decisions made in the schema.

Pagination-Friendly Keys

Offset-based pagination (LIMIT 20 OFFSET 1000) gets progressively slower as the offset grows, because the database still has to scan and discard all the skipped rows. Keyset (cursor-based) pagination — filtering on WHERE id > :last_seen_id ORDER BY id LIMIT 20 — stays fast regardless of how deep into the result set a client pages, because it uses an indexed column directly instead of counting through skipped rows. This means the schema should have a naturally ordered, indexed column (an auto-incrementing id, or a created_at plus a tiebreaker id) available for every list endpoint, since keyset pagination depends on it.

Avoiding Chatty Joins on Hot Paths

An endpoint like GET /orders/:id that a client calls constantly should be designed so the database can answer it with one efficient query, not a cascade of separate round trips or an expensive multi-table join chain executed on every single request. This is where the earlier denormalization discussion becomes directly relevant to API design: storing a computed order_total, or embedding a small, rarely-changing snapshot of data (like the customer's name at time of order) directly on the orders row, can turn a hot endpoint from a multi-join query into a single-table lookup.

Designing Identifiers for a Public API Boundary

An internal auto-incrementing integer primary key is efficient for the database but can leak information across an API boundary — sequential IDs let a client guess how many total rows exist or enumerate other users' resources by simply incrementing a number in a URL. A common pattern is keeping the efficient integer as the internal primary key for joins and indexing, while exposing a separate, non-guessable public identifier (a UUID or a short random string) in the API responses and URLs.

• Keyset pagination: paginate on an indexed, ordered column (id or created_at + id), not OFFSET.
• Hot-path denormalization: precompute or embed data on frequently-hit endpoints to avoid repeated joins per request.
• Internal vs. public identifiers: keep an efficient internal integer PK, expose a separate UUID/public_id externally.

Example

CREATE TABLE orders (
    id            BIGINT PRIMARY KEY,              -- internal, sequential, used for joins/indexes
    public_id     UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE, -- exposed in the API/URLs
    customer_id   BIGINT NOT NULL REFERENCES customers(id),
    customer_name VARCHAR(150) NOT NULL,            -- snapshot, avoids a join on the hot read path
    order_total   DECIMAL(12, 2) NOT NULL,          -- precomputed, avoids aggregating line items
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_created_at_id ON orders (created_at, id);

-- API list endpoint: GET /orders?after=2026-09-01T00:00:00Z&after_id=48291
SELECT public_id, customer_name, order_total, created_at
FROM orders
WHERE (created_at, id) > ('2026-09-01T00:00:00Z', 48291)
ORDER BY created_at, id
LIMIT 20;

-- API detail endpoint: GET /orders/{public_id} — single-table lookup,
-- no join needed to render the response.
SELECT public_id, customer_name, order_total, created_at
FROM orders WHERE public_id = 'b3f1...';

The list endpoint stays equally fast on page 1 or page 1000, since it
seeks directly via the (created_at, id) index instead of scanning and
discarding skipped rows. The detail endpoint returns everything the API
response needs from a single table with no join. The URL exposes only
the random public_id, so clients cannot guess adjacent order IDs or
infer total order volume from a sequential integer.

Exercise

Rows have an internal numeric id and a separate public_id that the API exposes. Fetch the next page with keyset pagination: the client has already seen rows up to id 2, and the page size is 2. Return the id and public_id of the next page.

Expected output: id | public_id, 3 | c, 4 | d (one per line)

CREATE TABLE articles (id INTEGER PRIMARY KEY, public_id TEXT NOT NULL UNIQUE, title TEXT);
INSERT INTO articles (id, public_id, title) VALUES
  (1, 'a', 'One'), (2, 'b', 'Two'), (3, 'c', 'Three'), (4, 'd', 'Four'), (5, 'e', 'Five');

-- TODO: the next page of 2 rows after id 2, using keyset pagination (no OFFSET)

CREATE TABLE articles (id INTEGER PRIMARY KEY, public_id TEXT NOT NULL UNIQUE, title TEXT);
INSERT INTO articles (id, public_id, title) VALUES
  (1, 'a', 'One'), (2, 'b', 'Two'), (3, 'c', 'Three'), (4, 'd', 'Four'), (5, 'e', 'Five');

SELECT id, public_id
FROM articles
WHERE id > 2
ORDER BY id
LIMIT 2;

Quick quiz

Interview questions