Interview Bank

From Frontend Engineer to Agent Engineer in 30 Days

D8 Why Split Gateway and Worker; Postgres Table Design (sessions/runs/messages) + Drizzle

• How would you design primary keys and indexes for sessions, runs and messages, and why avoid auto-increment ids?sessions / runs / messages 这三张表你会怎么设计主键与索引？为什么不用自增主键？

  Common in ChinaCommon overseasIntermediate#database#schema-design#idempotency

  How to reason about it · think before answering

  1. It looks like a trivia question, but every choice sits on a concrete constraint. The test is whether you can say what breaks if you choose otherwise.
  2. Start with why three tables rather than one: the grains differ. A session is a long-lived container, a run has a lifecycle and can fail and be retried, a message is an immutable fact. Without the run layer there is nowhere to answer 'did this finish', 'should we retry', or 'what did this turn cost'.
  3. Use text primary keys generated in the application (UUID or ULID), because the gateway must put the id into the 202 response before the row is written. Auto-increment ids are only known after the insert, which parks a round trip in the user's wait path and cannot be pre-allocated across instances. A bonus is that sharding later needs no renumbering.
  4. Index by query path, not by instinct: sessions need an index on user_id to list a user's conversations, messages need one on session_id to load history, and foreign key columns need indexes or deleting a parent row triggers a full scan. Extra indexes are not free — each one slows writes.
  5. The two unique constraints carry the design: a unique idempotency key on runs blocks duplicate delivery, and a composite unique on run id plus sequence in messages both fixes output ordering for one run and lets a reconnect replay idempotently by sequence. The sequence must start at zero and never skip, otherwise resume cannot find the cut point.
  6. Expect the follow-up: ULID or UUIDv4? Choose ULID or UUIDv7 — they are time-ordered so inserts land at the right edge of the B-tree, whereas random UUIDv4 scatters writes, splits pages and hurts cache hit rates. Mentioning this shows you have watched write performance.

  分析过程 · 先想清楚再作答

  1. 这题看着像八股，其实每一个选择背后都有一个具体约束。判断标准是：你能不能为每个决定说出「不这么做会发生什么」。
  2. 先讲为什么是三张表而不是一张：粒度不同。会话是长期容器，一次执行有生命周期且可能失败重来，消息是不可变事实。少了「一次执行」这一层，你就没有地方回答「这次跑完没有」「该不该重试」「这轮花了多少钱」。
  3. 主键选文本型的应用侧 id（UUID 或 ULID），理由是接入层必须在写库之前就把 id 放进 202 响应体返回给客户端；自增主键要等数据库插完才知道值，那次往返就被卡在用户的等待路径上，而且多实例无法预分配。附带好处是将来分库分表不用重编号。
  4. 索引按查询路径建，不按直觉建：按用户拉会话列表要 sessions 的 user_id 索引，按会话拉历史要 messages 的 session_id 索引，外键列本身要索引否则删除父行会全表扫。多余的索引不是免费的，每个都让写入变慢。
  5. 两条唯一约束才是这套设计的灵魂：runs 的幂等键唯一，挡住重复投递；messages 的「run id 加序号」复合唯一，既保证同一次执行的输出顺序稳定，又让断线重连可以按序号幂等回放。序号要从 0 开始、连续、不跳号，否则续传就找不到断点。
  6. 可以预期的追问：ULID 和 UUIDv4 选哪个？答 ULID 或 UUIDv7——它们按时间有序，插入时集中在 B 树右端，不像 UUIDv4 那样随机分布导致页分裂和缓存命中率下降。这个细节能直接体现你关心过写入性能。

  Key points

  • Three tables for three grains: a long-lived session, a run with a lifecycle, and immutable messages; without runs you cannot answer completion, retry or cost questions
  • Application-generated text ids, because the gateway must return the run id in the 202 before the write, and auto-increment ids cannot be pre-allocated across instances
  • Index the real query paths — user_id on sessions, session_id on messages, plus foreign key columns; extra indexes slow writes
  • Two unique constraints carry the design: a unique idempotency key on runs, and a composite unique on run id plus sequence in messages for ordering and idempotent replay
  • Prefer time-ordered ids such as ULID or UUIDv7 over random UUIDv4 to avoid page splits and cache misses

  答题要点

  • 三张表对应三种粒度：会话是长期容器、run 是一次有生命周期的执行、message 是不可变事实；少了 run 就无法回答是否跑完、该不该重试、花了多少钱
  • 主键用应用侧生成的文本 id，因为 Gateway 要在写库之前把 runId 放进 202 响应里，自增主键必须等插入完成且无法跨实例预分配
  • 索引按实际查询路径建：sessions 的 user_id、messages 的 session_id、以及外键列；多余索引会拖慢写入
  • 两条唯一约束是灵魂：runs 的幂等键唯一挡重复投递，messages 的「run id 加序号」复合唯一保证保序与幂等回放
  • id 优先选 ULID 或 UUIDv7 这类时间有序的方案，避免随机 UUID 造成的页分裂与缓存失效