Designing From Access Patterns

Design the data model from the queries the application actually makes, then denormalise on purpose rather than by decay.

Syntaxpartition key bounds the partition's size; sort key provides the ordering one or more read patterns need

Entity-relationship modelling asks what the data is. Designing from access patterns asks what the application requests: every query the product makes, with its rate, its latency target and the size of its result. In a key-value or document store there is no optimiser to rescue a bad key, so the key design is the query plan. That is why AWS guidance for DynamoDB has you enumerate access patterns before you create a table.

Build the list first

Pattern	Rate	Target	Key that serves it
Latest 50 messages in a channel	40k/s	p99 50ms	partition channel, sort message id
Messages around a given id	2k/s	p99 100ms	same key, scanned both ways
Channels a user belongs to	5k/s	p99 100ms	partition user
Substring search in a channel	50/s	p99 500ms	none — inverted index

The patterns no key can serve are the useful output. The last row is not a key-design problem; it leaves the primary store for a search index.

Normalised, then denormalised on purpose

Start normalised: it is the only form where a fact has one home. Then, pattern by pattern, denormalise where a measured read cost justifies it, recording the reason beside it. Discord's message table is this done deliberately — ((channel_id, bucket), message_id), where bucket is a ten-day window chosen so a partition stays under 100 MB while a channel read is still one ordered range scan.

What trips people up

A pattern discovered after launch is rarely one index away. If the key or the partition boundary has to change, the fix is a backfill and a migration — which makes the hour spent listing queries the cheapest hour in the design.

Example

// Access-pattern catalogue for a chat product, written BEFORE any table exists.
// Each entry is a query the application really makes, with the numbers that
// decide whether a key design can serve it.
const ACCESS_PATTERNS = [
  { id: 'P1', query: 'latest 50 messages in a channel',      rate: 40_000, p99Ms: 50 },
  { id: 'P2', query: 'messages around a given message id',   rate: 2_000,  p99Ms: 100 },
  { id: 'P3', query: 'channels a user is a member of',       rate: 5_000,  p99Ms: 100 },
  { id: 'P4', query: 'substring search within one channel',  rate: 50,     p99Ms: 500 },
];
// P1 and P2 are the same ordered scan in opposite directions, so one key serves
// both. P4 has no key at all: no ordering of (channel, message) gives substring
// search, so it goes to a search index fed from the primary store.

const DISCORD_EPOCH = 1420070400000;           // Discord's snowflake epoch, in ms
const BUCKET_MS = 10 * 24 * 60 * 60 * 1000;    // 10-day window, as Discord uses

// Snowflake ids carry their creation time in the high 42 bits, so the bucket is
// derivable from the id alone — no extra lookup on the read path.
function bucketFor(messageId) {
  const msSinceEpoch = Number(BigInt(messageId) >> 22n);
  return Math.floor(msSinceEpoch / BUCKET_MS);
}

function messageKey(channelId, messageId) {
  const bucket = bucketFor(messageId);
  // The partition key bounds how large one partition can grow; the sort key
  // gives P1 and P2 their range scan without a secondary index.
  return { pk: `${channelId}:${bucket}`, sk: messageId, bucket };
  // `bucket` above is property shorthand — exactly the same object as the
  // longhand `{ pk: channelId + ':' + bucket, sk: messageId, bucket: bucket }`.
}

// P1: one partition, one ordered scan, descending, limit 50.
async function latestMessages(ddb, channelId, now = Date.now()) {
  const bucket = Math.floor((now - DISCORD_EPOCH) / BUCKET_MS);
  return ddb.query({
    TableName: 'messages',
    KeyConditionExpression: 'pk = :pk',
    ExpressionAttributeValues: { ':pk': `${channelId}:${bucket}` },
    ScanIndexForward: false, // newest first
    Limit: 50,
  });
  // A channel quiet for two weeks has an empty current bucket, so the caller
  // walks back one bucket at a time until it has 50 rows.
}

When to use it

Choosing the partition and sort key for a new DynamoDB or Cassandra table before any code is written.
Deciding whether a product requirement needs a new index, a denormalised column, or a separate search cluster.
Reviewing a clean-looking relational schema that cannot serve its highest-volume query without a join per row.
Bounding partition size with a time bucket so one busy tenant does not create an unservable partition.

More examples

Normalise first, then denormalise as a recorded step

The denormalisation is a numbered step with its justification and its staleness budget written into the schema, not a shortcut someone took once. Reach for this shape when a read pattern's fan-out — not its row count — is the cost, and when the copy's divergence from the source is something you can name and tolerate.

-- Step 1: the normalised model. Each fact has exactly one home.
CREATE TABLE channels (id BIGINT PRIMARY KEY, guild_id BIGINT NOT NULL, name TEXT NOT NULL);
CREATE TABLE messages (
  id BIGINT PRIMARY KEY,
  channel_id BIGINT NOT NULL REFERENCES channels (id),
  author_id BIGINT NOT NULL REFERENCES users (id),
  body TEXT NOT NULL
);

-- Step 2: pattern P1 renders 50 messages with the author's name and avatar.
-- Normalised, that is one scan plus up to 50 user lookups per render, 40k times
-- a second. So we copy the two fields the renderer needs, and write down why.
ALTER TABLE messages
  ADD COLUMN author_name TEXT,     -- denormalised for P1: avoids 50 user reads
  ADD COLUMN author_avatar TEXT;   -- stale for up to one rename; acceptable

-- Step 3: name the invariant this creates, so the next person sees the debt.
COMMENT ON COLUMN messages.author_name IS
  'Copy of users.display_name at send time. Updated lazily by the user-rename
   consumer. Deliberately historical: edits do not rewrite old messages.';

Proving a key design against the list before writing code

Shows the access-pattern list as an artefact the codebase keeps, rather than a whiteboard exercise thrown away after the design review. Reach for it on a team where several people add queries: the failing assertion is the moment to decide between a new index, a denormalised copy and a different store.

// A key design is testable: for each access pattern, assert it resolves to a
// single partition and a contiguous range. Run this in CI against the
// catalogue, so adding a pattern without a key fails the build.
const KEY_DESIGN = {
  P1: { partitions: 1, scan: 'range', indexes: [] },
  P2: { partitions: 1, scan: 'range', indexes: [] },
  P3: { partitions: 1, scan: 'range', indexes: ['gsi_user_channels'] },
  P4: { partitions: 'n/a', scan: 'inverted-index', indexes: ['search-cluster'] },
};

for (const pattern of ACCESS_PATTERNS) {
  const plan = KEY_DESIGN[pattern.id];
  if (!plan) throw new Error(`${pattern.id} has no key that serves it`);
  // Anything above a few hundred requests per second that needs to touch more
  // than one partition is a scatter-gather, and its p99 is the slowest shard.
  if (plan.partitions !== 1 && pattern.rate > 500 && plan.scan !== 'inverted-index') {
    throw new Error(`${pattern.id}: scatter-gather at ${pattern.rate}/s`);
  }
}

Discussion

Be the first to comment on this lesson.

Build the list first

Normalised, then denormalised on purpose

What trips people up

Example

When to use it

More examples

Normalise first, then denormalise as a recorded step

Proving a key design against the list before writing code

Related

Discussion