Data systems

Intermediate

20-30 min

Database Design, Indexes, and Query Thinking for Frontend Leads

A practical lesson on how to think about data access patterns, query cost, and the database trade-offs that shape product performance.

Learn how to reason about database shape, index choice, and query cost so you can discuss performance with backend teams, avoid expensive UI patterns, and design screens that still work when the data grows.

Why this matters

What you gain from this lesson

Frontend decisions often determine the exact data shape the backend must serve, so UI choices can either keep the system fast or quietly make it expensive.

A lead who understands query cost can ask better questions during design reviews instead of waiting for performance bugs after launch.

Knowing when to limit fields, paginate carefully, or change a list screen is a practical way to protect both user experience and team velocity.

At a glance

Lesson map

Focus

Data systems

Outcome

Better decisions

This lesson is designed to stay readable on mobile: the basics are concise, the takeaways are highlighted, and the visual helps you see how the trade-offs fit together.

Core concepts

Start with access patterns, not table shape

Good database design starts with how the product is actually read and written. Before thinking about tables, think about the screens, filters, sort orders, and common actions that users perform over and over again.

If a page always shows the newest 20 items for one workspace, that is a very different access pattern from a reporting screen that needs counts grouped by week. The database should be shaped around those repeated requests, not around abstract purity.

For a frontend lead, the useful question is not 'What does the schema look like?' but 'What does the UI ask for most often, and what is the cheapest way to answer that question consistently?'

List views usually need a filtered slice, sorted in a stable order, with a small limit.
Detail views usually need one primary entity plus a few related pieces of data.
Dashboards often need aggregates, counts, or time-bucketed summaries rather than raw rows.

Indexes are shortcuts with maintenance cost

An index helps the database find rows without scanning everything. That sounds simple, but every index is a trade-off: it speeds up certain reads while making writes a little heavier and storage a little larger.

The best index is the one that matches a real query you run often. If the query filters by `workspace_id` and sorts by `created_at`, an index that follows that shape can remove a lot of unnecessary work. If the index does not match the query shape, the database may still have to do expensive work anyway.

This is why database conversations should be tied to product behavior. A beautiful schema that no query can use is not helpful, and a missing index on a hot list view can make the whole product feel slow.

Indexes usually help on columns that are frequently filtered, joined, or sorted.
Too many indexes slow down inserts and updates because every write must keep them in sync.
A single good composite index often beats several partial guesses.

Query shape determines real cost

The database does not care about your component tree; it cares about the exact query you send. A query can be cheap or expensive depending on how many rows it scans, how many rows it sorts, whether it can stop early, and whether it needs to join large tables.

A frontend lead should get comfortable asking what the database actually has to do. Does it scan a tiny range or half a million rows? Can it stop after 20 results, or does it need to inspect most of the table first? Those are the questions that turn vague 'it feels slow' complaints into actionable debugging.

You do not need to be a database specialist to be useful here. You just need to recognize when a screen is asking for too much data, when a filter is missing an index, or when a pagination pattern will become expensive as the dataset grows.

Filtering and sorting on the same indexed columns is often the best-case path.
`SELECT *` is expensive when the UI only needs a few fields.
Offset pagination gets slower as the offset grows because the database still has to walk past skipped rows.

Pagination, search, and aggregation are product decisions

How you paginate or search is not just a frontend detail. It affects database load, response time, and how much data the backend must prepare before the user sees anything useful.

Infinite scroll, cursor pagination, and page numbers all have different trade-offs. Cursor pagination usually scales better for long lists because it gives the database a stable anchor, while offset pagination can become painful when a user jumps deep into the result set.

Search pages are especially dangerous because they tempt teams to add many filters and cross-table joins at once. A lead should push the team to separate the common case from the rare advanced case, so the everyday experience stays fast.

Prefer cursor pagination for very large or fast-changing lists.
Keep the common filter path fast, and defer expensive advanced filters when possible.
Return only the fields the screen needs instead of shipping a giant generic payload.

Visual model

Practical examples

Activity feed for a workspace

A feed screen needs recent activity for one workspace, sorted newest-first, with a small page size. This is a classic access pattern that benefits from a matching composite index.

Request or current behavior

GET /workspaces/ws_123/activity?limit=20

-- logical query shape --
WHERE workspace_id = 'ws_123'
ORDER BY created_at DESC
LIMIT 20

Better response

200 OK

{
  "items": [
    { "id": "act_1", "type": "comment", "createdAt": "2026-06-06T10:00:00Z" },
    { "id": "act_2", "type": "mention", "createdAt": "2026-06-06T09:58:00Z" }
  ],
  "nextCursor": "act_2",
  "hasMore": true
}

Lesson: When the UI repeatedly asks for the same slice of data, the database should be able to answer that slice cheaply and consistently.

Admin search with real filters

An admin screen that combines team, status, and last-login filters can become expensive if every combination is handled as a giant generic query. Common filters deserve the fast path; rare combinations can be slower or broken into a separate flow.

Request or current behavior

GET /admin/users?team=platform&status=active&sort=lastLoginDesc

Better response

200 OK

{
  "items": [
    { "id": "u_1", "name": "Ploy", "team": "platform", "lastLoginAt": "2026-06-06T08:00:00Z" }
  ],
  "hasMore": false
}

Lesson: A good lead helps the team decide which query shape is the common case and which expensive paths should be handled more carefully.

Why page 500 is a warning sign

Offset pagination looks harmless early on, but a deep page number can force the database to skip a huge number of rows before returning anything. Cursor-based pagination is usually a better fit for long-lived lists.

Request or current behavior

GET /orders?page=500&limit=20

Better response

200 OK

{
  "items": [],
  "nextCursor": null,
  "hasMore": false
}

Lesson: The UI can feel simple while the database does a lot of invisible work. Pagination choice is a real performance decision, not just an API style choice.

Common pitfalls

Over-indexing every field, which makes writes slower and adds maintenance cost without necessarily helping the hot queries.

Indexing the wrong shape, such as adding an index that does not match the common filter and sort pattern.

Using functions or transformations in a way that prevents the database from using the index efficiently.

Fetching a giant payload when the screen only needs a few fields and a short list of items.

Relying on offset pagination for deep browsing on large datasets, which becomes increasingly expensive.

Ignoring N+1 query patterns, where one request quietly triggers many small follow-up queries.