Why Your Database Design Should Start with Access Patterns — Not Tables.

There’s a familiar way developers approach database design.

Identify the entities. Create the tables. Define the relationships. Add a few indexes. Connect everything to an ORM and start building endpoints.

Users need a table. Projects need a table. Tasks need a table. Each task belongs to a project, so we add a foreign key. Everything looks clean, and the application starts working.

Then the requirements become more realistic.

You need to retrieve a user’s most recent tasks across multiple projects. You need to display the latest activity in a workspace. You need to filter tasks by status, sort them by priority, and paginate the results. You need to generate monthly reports without slowing down ordinary requests.

Suddenly, the schema that looked perfectly reasonable starts producing complicated queries, unnecessary database round trips, and performance problems that are difficult to explain.

The instinct is often to add another index, introduce caching, or restructure a few tables.

Sometimes those are the right solutions.

But most times the problem started much earlier: you designed the database around the entities you could identify, not the operations your application needed to perform.
A database schema isn’t just a representation of your business objects. It also determines how efficiently the application can retrieve, update, and maintain the data those objects represent.

This is why good database design starts with access patterns.

Your Entities Tell You What Exists. Access Patterns Tell You What Matters.

Imagine you’re building a project management application.

At the domain level, you identify a few obvious entities:

• Users
• Workspaces
• Projects
• Tasks
• Comments
• Activity logs

A reasonable relational model might connect them like this:

This gives you a useful starting point. You understand the major entities and how they relate.

But it doesn’t tell you enough to design the database well.

Consider what the application actually needs to do.

A user opens a workspace dashboard and expects to see its projects, outstanding tasks, and recent activity. Another user opens a project and expects to see its tasks, ordered by priority. A manager wants all overdue tasks assigned to a particular team member. An administrator wants an activity report for the last 30 days.

Each operation has a different access pattern.

For example:

These operations matter because they describe how the database will actually be used.

A schema can accurately represent every entity and relationship yet still make common operations unnecessarily expensive.

The reverse is also possible. A schema designed to optimize one query might make other operations difficult or introduce data consistency problems.

The objective isn’t to make every query equally fast. That’s rarely realistic.

It’s to understand which operations matter most, what they require, and how the data model can support them without creating unacceptable trade-offs elsewhere.

Your entities describe the domain. Your access patterns reveal the demands your database must satisfy.

Both are necessary, but they answer different questions.

Start with the product’s questions

An access pattern is a recurring way the application reads or changes data to support a user journey, business rule, or background task.

Examples:

• Find a user by email during login.
• Load a tenant’s active projects, newest first.
• Find tasks assigned to a user and sort them by due date.
• Retrieve an order and its line items.
• Check whether a payment webhook has already been processed.
• Find subscriptions due for renewal.
• Count unread notifications.
• List events for a device within a time range.

These are not details to postpone until performance becomes a problem. They are part of the system’s requirements.

For each important access pattern, ask:

• What information does the application have when it makes this query?
• What data must it return?
• How often will it run?
• How many records might it touch?
• How quickly must it respond?
• Must the result reflect the latest write?
• What should happen if the operation is repeated?

Those answers shape the data model, indexes, constraints, and sometimes the database itself.

Design Around Real Queries, Not Imaginary Future Requirements

Before creating tables, write down the operations the application must support.

Not every hypothetical feature you might build someday. Start with the operations that are necessary for the current product.

Suppose one of the most common operations in our project management application is:

Show the 20 most recently updated tasks in a project, including their titles, statuses, and assignees.

That sentence already tells us a lot.

We need to:

• Restrict the results to one project.
• Order tasks by their last update.
• Return a limited number of records.
• Retrieve the fields needed by the interface.

Include assignee information without creating unnecessary database round trips.

We can express the operation in SQL:

Notice what we did before creating an index or changing the schema.

We identified the actual query.

From this query, we can begin making informed decisions about:

• Which columns need to exist.
• Which relationships must be represented.
• Which fields are used for filtering and sorting.
• Whether an index could support the operation.
• Which fields the application actually needs to retrieve.

The query also reveals an important detail: updated_at alone may not provide a deterministic order when multiple tasks have the same timestamp.

Adding id as a tie-breaker gives us a stable ordering for pagination.

This isn’t an argument for writing every SQL query before designing every table. It’s an argument for understanding the operations the schema is supposed to support.

You can still use entity diagrams, domain models, and normalization techniques. The difference is that you validate those designs against real access requirements rather than assuming the model will automatically produce efficient queries.

Build an access-pattern inventory

For a real project, I would document the important operations in a small table:

These ratings are illustrative, not universal. A financial report may require stricter consistency than a project dashboard, while a dashboard may have tighter latency requirements.

The point is to make the trade-offs explicit.

A frequently executed query deserves more attention than an administrative operation run once a month, unless that less frequent operation has unusually high business impact.

Database design becomes more deliberate when every important part of the schema can be connected to a real requirement.

Your Access Patterns Should Influence Your Indexes

Indexes are one of the clearest examples of why access patterns matter.

Developers sometimes create indexes because a column feels important, because it’s frequently mentioned in the application, or because they expect the database to become slow eventually.

But an index is useful when it helps the database execute a workload more efficiently.

Let’s return to our task query:

A potential index is:

Why these columns, and why this order?

The query first restricts rows to a particular project_id. Within that project, it needs tasks ordered by updated_at and then id.

The composite index reflects that sequence.

PostgreSQL may be able to use the index to locate the project’s tasks in the required order and stop after retrieving enough matching rows.

The actual execution plan still depends on the table’s size, data distribution, selectivity, and other factors. An index doesn’t guarantee that PostgreSQL will choose it.

But we have a reason for creating it: it supports a known access pattern.

Why column order matters

Consider this alternative:

This index has the same columns, but a different leading column.

It may be useful for queries that primarily scan tasks in update-time order. However, it is generally less suitable for efficiently locating one project’s range of tasks in the same way as the first index.

For a B-tree index, leading columns matter because they determine how effectively the index can narrow the search.

That doesn’t mean every index must start with the most frequently mentioned column. It means the index must be evaluated against the predicates and ordering of the queries you need to support.

Now consider the cost.

Every additional index consumes storage. Inserts and deletes must maintain it, and updates to indexed columns can require additional index work.

An index can make one query faster while making write operations more expensive.

This is why blindly indexing every filterable field isn’t a good strategy.

A better process is:

• Identify an important query.
• Inspect its execution plan with EXPLAIN.
• Determine whether scanning, sorting, joining, or another operation is expensive.
• Design an index that targets the actual bottleneck.
• Measure the result using realistic data.

For example:

EXPLAIN ANALYZE executes the query and reports actual execution details, while BUFFERS provides information about data access through PostgreSQL’s buffer system.

Be careful when running it against production queries that have side effects: EXPLAIN ANALYZE executes the statement. For mutating statements, use an appropriately controlled environment or a safe transaction strategy.

Also remember that a query plan measured on a tiny development database may not predict its behaviour against millions of production rows.

Indexes should be a response to observed or well-understood access patterns, not a substitute for understanding them.

The Same Data Can Need Different Models for Different Access Patterns

Here’s where database design becomes more interesting.

Suppose our application stores workspace activity:

• TaskCreated
• TaskAssigned
• TaskCompleted
• CommentAdded
• MemberInvited

Each event records what happened, when it happened, and which workspace it belongs to.

A natural table might look like this:

This is a reasonable starting point for a workspace activity feed.

But what if the product introduces several different requirements?

• Show the latest 50 events in a workspace.
• Retrieve the history of one particular task.
• Count completed tasks per day.
• Search activity by actor.
• Generate a monthly report grouped by event type.

These are different workloads against the same underlying information.

The workspace feed is optimized around a workspace and time range. Task history is centered on an entity. Reporting requires aggregation. Search may require text indexing or a separate search system if the requirements grow sufficiently complex.
One table and one index will not necessarily serve every workload optimally.

This doesn’t mean you immediately need multiple databases or duplicated data. It means you should recognize that a data model is a set of trade-offs across multiple operations.

You might add another index if task-history queries become important:

If reporting becomes expensive, you might introduce a summary table or a materialized view. If text search becomes a major workload, you might evaluate PostgreSQL’s full-text search before introducing a dedicated search engine.

The appropriate response depends on query frequency, latency requirements, data volume, and the freshness guarantees the product needs.

What about denormalization?

Sometimes you deliberately store information in more than one place because the read workload justifies it.

Imagine a dashboard that displays the total number of open tasks in each project.

One option is to calculate the count every time the dashboard loads:

For a small application, this may be perfectly adequate.

But if the task table grows significantly and this aggregation becomes a frequent, expensive operation, you might maintain a project-level counter or a summary table.

Now the dashboard can retrieve a precomputed value rather than repeatedly aggregating the entire matching dataset.

The trade-off is that the summary must remain correct.

If a task changes from OPEN to DONE, the count must be updated. If a transaction fails, the counter must not drift away from the underlying data. If events are processed asynchronously, the summary may temporarily lag behind the source records.

A denormalized model is not simply a faster version of a normalized model. It exchanges some computation at read time for additional complexity in maintaining the data.

This is a decision to make when you understand the workload and can justify the cost.

Normalize to protect the integrity of your data. Denormalize when a demonstrated access requirement justifies the additional consistency work.

Don’t duplicate data merely because you assume reads will eventually become slow.

ORMs Don’t Remove the Need to Think About Queries

Working with an ORM makes database access more convenient. It doesn’t make the underlying query patterns disappear.

Consider a NestJS service using Prisma:

This is a reasonable implementation of the query we discussed earlier.

It filters by project, orders the results deterministically, limits the result set, and retrieves only the assignee fields needed by the application.

But an ORM call isn’t automatically efficient just because it looks clean.

Consider another implementation:

This pattern can produce an additional query for each task with an assignee.

If the initial query returns 20 tasks, the service could issue up to 21 queries instead of retrieving the necessary data through a more efficient access strategy.

This is a version of the N+1 query problem.

The issue becomes more significant when the pattern appears inside nested loops, list endpoints, or other high-traffic operations.

ORMs provide tools for loading relations, selecting fields, batching queries, and managing transactions. The exact SQL generated depends on the ORM, its configuration, and the query being executed.

The important thing is to understand what happens beneath the abstraction.

A practical ORM workflow

When designing an endpoint, ask:

• Which records does this operation actually need?
• Which fields should be returned?
• Which relationships must be loaded?
• Can the data be retrieved in a single efficient query, or is a small number of targeted queries preferable?
• Will this operation trigger repeated queries for every returned record?
• Does the query have an appropriate index?

You should also inspect the SQL generated by the ORM when performance matters.

Abstraction is useful when it makes code easier to maintain. It becomes a problem when it hides a costly access pattern that nobody is monitoring.

An ORM can simplify how you express a query. It cannot make a poorly designed query free.

Pagination Is a Database Design Problem, Too

Pagination looks like an API concern, but the way you paginate data can have a significant impact on database performance.

Imagine the application has hundreds of thousands of tasks in a workspace.

A common approach is offset pagination:

This asks the database to skip the first 10,000 rows in the ordered result and return the next 20.

Offset pagination is straightforward and works well for many applications. But as the offset grows, the database may need to walk through an increasingly large number of index entries or rows before it can return the requested page.

Large offsets can therefore become expensive.

There is another concern: if records are inserted, deleted, or reordered between requests, users may see duplicate or missing items as they navigate through pages.

For long lists, feeds, and other workloads where users move forward through a changing dataset, keyset pagination can be a better fit.

Use the last record as a cursor

Suppose the previous page ended with a task whose updated_at was 2026-10-10 10:00:00 and whose ID was task_123.

The next page can request records after that position in the descending order:

Here, $2 is the timestamp of the last record from the previous page, and $3 is its ID.

Because both ordering columns are descending, the row comparison selects records that come after the cursor in that ordering, assuming the columns are non-null and the cursor values use the same ordering semantics.

The composite index from earlier can support this access pattern:

This lets the database seek into the ordered index near the cursor rather than repeatedly skipping a large offset.

There are still important details to consider.

The ordering columns should form a deterministic order, the cursor should encode the relevant values reliably, and nullable columns require additional care. If updated_at changes while a task is being paginated, that task can move relative to the cursor. Keyset pagination reduces some problems associated with offset pagination, but it does not automatically provide a consistent snapshot across multiple HTTP requests.

For a feed where stable traversal is important, you may need an immutable ordering key, a snapshot boundary, or a different consistency strategy.

Also, if users need to jump directly to page 500, offset pagination may be more convenient.

The right choice depends on the product.

The important lesson is that pagination isn’t just about returning a subset of records. It’s about how the database finds that subset, how records are ordered, and what happens when the dataset changes.

Access Patterns Must Not Override Data Integrity

At this point, it might sound as if every table should be designed around the fastest possible query.

That would be another mistake.

Access patterns are a starting point for understanding the workload, not permission to ignore the integrity of the underlying data.

Suppose our application stores a task’s status in a tasks table and also maintains a separate summary table containing the number of open tasks per project.

The summary makes dashboard reads cheaper.

But now a task changes from OPEN to DONE.

If the task update succeeds and the summary update fails, the two representations disagree.

You have improved the read path but introduced a correctness problem.

There are several ways to address this, depending on the system.

For a small summary maintained inside PostgreSQL, you may update the task and the counter within the same transaction. For an asynchronously maintained projection, you may use reliable event processing, idempotent handlers, and reconciliation jobs to detect or repair inconsistencies.

These are different consistency models, with different operational costs.

The same principle applies to normalization.

If a customer’s address is stored in several tables, changing the address may require updating multiple records. If those copies are intended to represent the same current value, you need a strategy for keeping them consistent.

But not every duplicated value is a mistake.

An order item often stores the price agreed upon when the order was placed, even though the current product price may change later. That value is a historical snapshot, not an accidental duplicate.

Understanding the business meaning of data is just as important as understanding how it is queried.

Before denormalizing, ask:

• Is this value authoritative or derived?
• Can it change independently of its source?
• How fresh must the derived value be?
• What happens if an update or event is processed twice?
• Can the application recover from a failed update?
• How will the system detect inconsistent data?

A fast query is not an improvement if it makes the application return incorrect answers.

The goal is not to optimize access patterns at any cost. It’s to balance efficient data access with the correctness guarantees the application requires.

A Practical Workflow for Access-Pattern-Driven Design

You don’t need a complicated process to apply these ideas. You need a deliberate one.

Step 1: Identify the core domain entities

Start with the things the application needs to represent: users, orders, products, workspaces, tasks, payments, or whatever makes sense for your domain.

Define their relationships and the business rules that must hold.

This gives you the foundation of the model.

Here’s a workflow you can use when designing a backend database.

Step 2: List the important application operations

Write down the reads and writes behind the major user journeys and background jobs.

For example:

• Load a customer’s recent orders.
• Retrieve the items in an order.
• Reserve inventory.
• Find overdue tasks for a project.
• Display a workspace activity feed.
• Calculate monthly subscription usage.

Avoid vague requirements such as “the system needs to retrieve tasks.” Specify the filters, ordering, limits, and related data that the operation requires.

Step 3: Turn important operations into queries

Express the operations in SQL or in the ORM you use.

You don’t need to implement every query completely at the beginning. But you should understand what the database will need to do.

For reads, consider filtering, sorting, joins, aggregation, and pagination.

For writes, consider constraints, atomic updates, transaction boundaries, and concurrent operations.

Step 4: Design the schema and constraints

Now create the tables, relationships, data types, foreign keys, unique constraints, and other rules that reflect the domain.

Use normalization where it helps prevent unwanted duplication and update anomalies.

Where a particular access pattern calls for a different representation, evaluate the trade-offs deliberately.

Step 5: Add indexes based on query requirements

Review the most important queries and identify which indexes could support them.

Pay particular attention to composite indexes, column order, filtering, sorting, and pagination.

Don’t forget that indexes have write and storage costs.

Step 6: Test with realistic data

A query that works quickly against 100 rows may behave very differently against 5 million.

Populate your database with representative data volumes and distributions. Run EXPLAIN ANALYZE, inspect buffer usage, and compare query behavior before and after changes.

Test concurrent writes when the application relies on inventory reservations, financial operations, uniqueness, or other important invariants.

Step 7: Measure, revise, and document

Use query logs, application metrics, and database monitoring to understand what happens in production.

If the workload changes, revisit the design.

Perhaps a new dashboard makes a previously unimportant query critical. Perhaps a summary table is now justified. Perhaps the existing index is sufficient, and the real problem lies in connection management or repeated application queries.

Document the reason behind important design decisions so the next engineer understands what the schema is optimized for and which trade-offs it makes.

Database design is not a one-time exercise. It evolves as the application’s workload evolves.

The cost of designing without access patterns

Slow queries

A query may require scanning millions of rows because no suitable index exists.

For example:

Without a suitable index, the database may scan many rows to find matching orders.

PostgreSQL’s query planner chooses between scan strategies such as sequential scans, index scans, and bitmap index scans based on the query structure and data properties.

The right plan depends on the query and the data.

If you do not understand the query, you cannot design the right index.

Too many indexes

Adding an index to every column may seem safe, but indexes are not free.

They consume storage and must be maintained during inserts, updates, and deletes. PostgreSQL documentation explicitly notes that indexes improve retrieval speed but add overhead to the database system as a whole.

A schema with too many indexes can:

• Slow down writes.
• Increase storage cost.
• Complicate migrations.
• Confuse the query planner.
• Create maintenance burden.

Design indexes around measured access patterns, not around every possible query.

Expensive joins

If two pieces of data are always read together, storing them separately may create unnecessary joins.

For example, if every product page needs product details and its primary image, a design that requires a join for every page load may be less efficient than a design that stores or projects the image reference alongside the product.

But this does not mean you should duplicate everything.

Denormalization should be deliberate.

Data integrity problems

Access patterns also reveal constraints.

For example:

• A user should have only one active subscription.
• A backup code should be used only once.
• A payment reference should be unique.
• A task must belong to a project.
• An invoice item must reference an invoice.
• A tenant-owned record must include a tenant ID.

These rules should be represented in the schema, not only in application code.

Final Thoughts: Don’t Just Model Your Data. Model How You’ll Use It.

A well-designed database isn’t simply one with clean tables, sensible relationships, and a handful of indexes.

It’s one whose structure supports the operations the application needs to perform while preserving the integrity of its data.

That’s why starting with entities alone isn’t enough.

You need to understand the reads, writes, filters, sorts, joins, transactions, and reports that will shape the database’s workload. Those operations reveal where indexes matter, where normalization helps, where denormalization might be justified, and where an apparently simple endpoint could become an expensive production query.

You don’t need to optimize for every imaginable future requirement. You don’t need to introduce specialized databases before you have a reason. And you certainly don’t need to sacrifice correctness for a benchmark.

Start with the domain. Understand the access patterns. Design the schema to support both. Then measure the result and let real workload evidence guide your next decision.

The best database schema isn’t the one that looks the cleanest on a diagram. It’s the one that represents your domain correctly and supports the way your application actually works.

Design for the queries your system needs, not just the tables it contains.