Access patterns first

The most expensive DynamoDB tables we see were designed by excellent SQL engineers. That is not a criticism of the engineers. It is what happens when deep expertise in one model gets applied to a database built on the opposite assumption.

Two models, inverted

Relational design starts from the data. You identify the entities, normalize them into tables, define the relationships, and trust the query planner to answer whatever questions come later. Questions you have not thought of yet are fine. A join will handle them.

DynamoDB starts from the questions. Before you define a single key, you list every way the application will read and write data: fetch an order with its line items, list a customer's orders by date, find every job in a given state. The table is shaped so that each of those patterns is a single, cheap request against a key or an index. A question nobody planned for is not a slow query. It may not be answerable at all without a scan, a new index, or a migration.

Engineers coming from SQL tend to do the natural thing: one table per entity, foreign keys as attributes, and joins done in application code. It works in development. In production it turns into chatty request patterns, hot partitions, and a bill that tracks the number of round trips rather than the amount of work done.

What designing for access patterns looks like

We start every DynamoDB design with a written list of access patterns, before any keys. Each entry states what the application asks, how often, how fresh the answer has to be, and how large the result can get. That list is the actual specification. The table design is derived from it.

From there, a few things tend to follow:

• One table often holds many item types. Orders, line items, and customers can share a table when they share access patterns, with partition and sort keys chosen so that a single query returns exactly what one screen needs.
• Indexes are design decisions, not afterthoughts. A global secondary index can be added later. A local secondary index has to be defined when the table is created, and cannot be added afterwards. Getting the access-pattern list right up front is cheaper than discovering a missing one in production.
• Partition keys are about load as much as identity. A key that concentrates traffic on a few values will throttle long before the table's overall capacity is reached.

None of this is difficult once it is internalized. But internalizing it means setting aside instincts that served well for years, and that takes most engineers a few uncomfortable weeks.

When relational is still the answer

The point is not that DynamoDB is better. It is that the two are good at different things, and the choice should follow the workload.

If the access patterns are known, stable, and need to scale without anyone managing capacity, DynamoDB is hard to beat. If the questions are open-ended (analysts slicing data in ways nobody can predict, reporting that changes every quarter, heavy relational integrity), a relational database is the right tool and forcing DynamoDB onto it is its own kind of mistake.

Most of the systems we build use both. Transactional paths with known patterns run on DynamoDB. Reporting and ad-hoc analysis run on a relational store or a warehouse fed from DynamoDB streams. The skill is knowing which question you are answering before you choose where the data lives.

Why we write it down

The access-pattern list goes in the repository next to the code, as a decision record. When a new requirement arrives, the first question is whether it is a new access pattern. If it is, the list changes, the design is reviewed against it, and the index or migration is planned on purpose rather than discovered in an incident.

It is a small discipline. It is also the difference between a table that scales with the business and one that has to be rebuilt the first time the business asks a question nobody wrote down.

---

Planning a DynamoDB design, or living with one that fights you? Start a conversation.