↓Skip to main content
  1. System Design Components/

Archetype 5 — Derived Projection #

What this archetype is #

A read model computed from source truth and optimized for a specific read pattern. The projection is not source truth.

Examples: feed, dashboard, search index, aggregate counts.

We will use home feed as the running example.

Layer 1: Entities and Postgres table design #

Source truth:
  PostState
  FollowRelationState

Projection:
  FeedState
  FeedCursorState

create table user_feed (
  viewer_id bigint not null,
  post_id bigint not null,
  author_id bigint not null,
  post_preview text,
  created_at timestamptz not null,
  expires_at timestamptz,
  primary key (viewer_id, created_at, post_id)
);

create table feed_cursor (
  viewer_id bigint primary key,
  last_seen_post_id bigint,
  last_seen_at timestamptz,
  updated_at timestamptz not null default now()
);

create index user_feed_author_idx
on user_feed (viewer_id, author_id, created_at desc);

Layer 2: Write path mechanics #

Fan-out on write #

Source truth write happens elsewhere:

insert into posts (...);

Then projector consumes CDC / outbox and writes:

insert into user_feed (
  viewer_id, post_id, author_id, post_preview, created_at, expires_at
) values (...);

Feed read #

select *
from user_feed
where viewer_id = $1
  and (created_at, post_id) < ($2, $3)
  and (expires_at is null or expires_at > now())
order by created_at desc, post_id desc
limit 20;

Cursor update #

insert into feed_cursor (viewer_id, last_seen_post_id, last_seen_at)
values ($1, $2, now())
on conflict (viewer_id) do update
set last_seen_post_id = excluded.last_seen_post_id,
    last_seen_at = excluded.last_seen_at,
    updated_at = now();

Layer 3: Fault tolerance #

  • stale projection
  • missing projection row
  • duplicate projection row
  • tombstone not propagated
  • expensive rebuild after schema change

Repair model:

  • replay change stream / outbox
  • rebuild projection from source truth

Layer 4: Scale #

Default hotspots:

  • fanout on write
  • projection lag
  • rebuild cost
  • broad query fanout

Common mitigations:

  • separate projector workers
  • buffering queue between source changes and projection writes
  • hybrid push/pull for celebrity fanout
←→ →←