· Random Tech Excursions

Archetype 2 — Relation / Edge #

What this archetype is #

A relation row links two entities. The hard part is uniqueness, forward and reverse lookup, and hot reverse edges.

Examples: follow relation, membership, share relation, likes.

We will use user follows user as the running example.

Layer 1: Entities and Postgres table design #

One source-truth entity:

FollowRelationState
  -> one edge per (follower, followee)

One primary table:

create table follows (
  follower_id bigint not null,
  followee_id bigint not null,
  status text not null default 'ACTIVE',
  created_at timestamptz not null default now(),
  primary key (follower_id, followee_id)
);

Forward and reverse indexes:

create index follows_followee_created_idx
on follows (followee_id, created_at desc)
where status = 'ACTIVE';

create index follows_follower_created_idx
on follows (follower_id, created_at desc)
where status = 'ACTIVE';

For celebrity reverse hotspots, add shard_id:

alter table follows add column shard_id int not null default 0;
create index follows_followee_shard_created_idx
on follows (followee_id, shard_id, created_at desc)
where status = 'ACTIVE';

Layer 2: Write path mechanics #

Create edge #

insert into follows (
  follower_id, followee_id, status
) values ($1, $2, 'ACTIVE')
on conflict (follower_id, followee_id) do update
set status = 'ACTIVE';

Remove edge #

Soft delete:

update follows
set status = 'REMOVED'
where follower_id = $1
  and followee_id = $2
  and status = 'ACTIVE';

Forward read #

select followee_id, created_at
from follows
where follower_id = $1
  and status = 'ACTIVE'
order by created_at desc
limit 50;

Reverse read #

select follower_id, created_at
from follows
where followee_id = $1
  and status = 'ACTIVE'
order by created_at desc
limit 50;

Layer 3: Fault tolerance #

Duplicate edge creation on retry: prevented by composite PK / unique key
Add/remove race: status update ordering matters
Count projection lag: follower counts stored elsewhere may be stale
Stale relation list cache: reverse edge cache may lag deletes

Layer 4: Scale #

Default hotspots:

celebrity skew
hot reverse index range
hot count key

Common mitigations:

reverse-edge sharding with shard_id = hash(follower_id) % N
separate follower count projection table
cache hot reverse pages