System Design Interview 已经失效。教你如何仍然通过。

Source: Dev.to

1. 生成列（在应用代码中停止计算）

你有一个 users 表，包含 first_name 和 last_name。每一次需要完整姓名的查询都会使用 first_name || ' ' || last_name。每一次都是如此。

ALTER TABLE users
  ADD COLUMN full_name text
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

-- 现在你可以直接对 full_name 进行查询、建立索引和搜索
CREATE INDEX idx_users_full_name
  ON users USING gin(to_tsvector('english', full_name));

真实案例： 我们的 products 表中，price_with_tax 在每个 API 响应里都要计算。将其迁移到生成列后，消除了成千上万行的应用层计算，并使查询 提升 3 倍（不再在 SELECT 子句中进行计算）。

其他适合的候选项：

• 从 birth_date 计算得到的 age
• 从 title 生成的 slug
• 通过 price * quantity 计算得到的 total

2. LISTEN/NOTIFY（实时无需轮询）

你的后台工作进程每 5 秒轮询一次数据库以查找新任务。非常浪费。

-- 在你的任务插入触发器中：
CREATE OR REPLACE FUNCTION notify_new_job()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify(
    'new_job',
    json_build_object(
      'id', NEW.id,
      'type', NEW.job_type,
      'priority', NEW.priority
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER job_inserted
AFTER INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION notify_new_job();

// 在你的工作进程中（使用 pg 库）：
const { Client } = require('pg');
const client = new Client();
await client.connect();

await client.query('LISTEN new_job');

client.on('notification', (msg) => {
  const job = JSON.parse(msg.payload);
  console.log('New job:', job.id, job.type);
  processJob(job);
});

零轮询。数据库将事件推送给你的工作进程。我们用 LISTEN/NOTIFY 替代了每分钟 12 次查询的轮询循环——数据库负载下降了 40 %。

3. CTE（WITH 查询）——用于可读的复杂查询

Your 50‑line nested subquery is correct but nobody can read it. Including you, 3 months from now.

-- Before: nested subquery nightmare
SELECT u.name,
       t.total_orders,
       t.total_revenue
FROM   users u
JOIN   (
  SELECT user_id,
         COUNT(*) AS total_orders,
         SUM(
           SELECT SUM(oi.price * oi.quantity)
           FROM   order_items oi
           WHERE  oi.order_id = o.id
         ) AS total_revenue
  FROM   orders o
  WHERE  o.created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
) t ON t.user_id = u.id
WHERE  t.total_revenue > 1000;

-- After: CTE version (same result, readable)
WITH recent_orders AS (
  SELECT id, user_id, created_at
  FROM   orders
  WHERE  created_at > NOW() - INTERVAL '30 days'
),
order_totals AS (
  SELECT o.user_id,
         COUNT(DISTINCT o.id)          AS total_orders,
         SUM(oi.price * oi.quantity)   AS total_revenue
  FROM   recent_orders o
  JOIN   order_items oi ON oi.order_id = o.id
  GROUP BY o.user_id
)
SELECT u.name,
       ot.total_orders,
       ot.total_revenue
FROM   users u
JOIN   order_totals ot ON ot.user_id = u.id
WHERE  ot.total_revenue > 1000;

相同的查询计划。可读性大幅提升。每个 CTE 块只做一件事，并且有明确的名称。

---

4. JSONB 操作（当你需要灵活性时）

你需要为每个用户存储可变的元数据。有的用户有 company，有的用户有 department，还有一些自定义字段是无法预见的。不要创建 50 个可为空的列。

ALTER TABLE users
  ADD COLUMN metadata jsonb DEFAULT '{}';

设置值

UPDATE users
SET    metadata = metadata || '{"company":"Acme","role":"CTO"}'
WHERE  id = 123;

查询特定 JSON 字段（可建立索引！）

SELECT *
FROM   users
WHERE  metadata->>'company' = 'Acme';

在 JSON 字段上创建部分索引

CREATE INDEX idx_users_company
  ON users ((metadata->>'company'))
  WHERE metadata->>'company' IS NOT NULL;

聚合 JSON 数据

SELECT metadata->>'company' AS company,
       COUNT(*)               AS cnt
FROM   users
WHERE  metadata->>'company' IS NOT NULL
GROUP BY metadata->>'company';

规则：

• 结构化、可查询的数据 → 常规列。
• 可变的、半结构化数据 → JSONB。

不要把 JSONB 用于所有情况（会失去类型安全和约束），但也不要完全回避它——它是 PostgreSQL 的超级能力。

5. 窗口函数（无需 GROUP BY 的分析痛点）

您希望在同一查询中显示每个订单、其累计总额以及用户的平均订单价值。若不使用窗口函数，就必须使用子查询或在应用层处理。

SELECT o.id,
       o.user_id,
       o.total,
       o.created_at,
       -- Running total for this user
       SUM(o.total) OVER (
         PARTITION BY o.user_id
         ORDER BY o.created_at
       ) AS running_total,
       -- User's average order value
       AVG(o.total) OVER (PARTITION BY o.user_id) AS avg_order_value,
       -- This order's rank among user's orders (by value)
       RANK() OVER (
         PARTITION BY o.user_id
         ORDER BY o.total DESC
       ) AS value_rank,
       -- Percent of user's total spending
       o.total::numeric /
       SUM(o.total) OVER (PARTITION BY o.user_id) * 100 AS pct_of_total
FROM   orders o
ORDER BY o.user_id, o.created_at;

一次查询。没有子查询。没有应用层后处理。窗口函数是 PostgreSQL 最被低估的特性之一。

---

6. 排除约束（防止预订冲突）

你正在构建一个预订系统。相同房间的两个预订不应出现时间重叠。应用程序代码会检查冲突，但会出现竞争条件——两个请求可能同时检查，都未发现冲突，然后都执行插入。

CREATE TABLE bookings (
  id         serial PRIMARY KEY,
  room_id    int NOT NULL,
  tsrange    tsrange NOT NULL,   -- e.g. '[2024-01-01 10:00,2024-01-01 12:00)'
  EXCLUDE USING gist (
    room_id WITH =,
    tsrange WITH &&
  )
);

EXCLUDE 子句保证对于给定的 room_id，没有两行的时间范围会重叠（&&）。数据库以原子方式强制执行该规则，彻底消除竞争条件。

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(start_time, end_time) WITH &&
  );

-- This INSERT succeeds:
INSERT INTO bookings (room_id, start_time, end_time) 
VALUES (1, '2026-03-01 09:00', '2026-03-01 10:00');

-- This INSERT fails (overlaps):
INSERT INTO bookings (room_id, start_time, end_time) 
VALUES (1, '2026-03-01 09:30', '2026-03-01 10:30');
-- ERROR: conflicting key value violates exclusion constraint "no_overlap"

不存在竞争条件。数据库在约束层面强制执行此规则，任何并发请求都无法产生重叠。

7. 行级安全（多租户无需 WHERE 子句）

每个多租户应用中的查询都会带有 WHERE tenant_id = ?。如果有开发者忘记加上这条条件，就会导致租户之间的数据泄漏。

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.current_tenant')::int);

-- In your application middleware:
await db.query("SET app.current_tenant = $1", [tenantId]);

-- Now every query is automatically filtered:
SELECT * FROM documents;
-- PostgreSQL adds WHERE tenant_id = automatically
-- Impossible to accidentally see another tenant's data

这正是企业级 SaaS 应用处理多租户的方式。数据库负责保证隔离，而不是你的应用代码。

---

8. 部分索引（仅索引你查询的内容）

你的 orders 表有 10 M 行。只有 50 K 行是 status = 'pending'。在 status 上创建索引会覆盖所有 10 M 行，即使查询只关心这 50 K 条待处理记录。

-- Instead of this (indexes all 10 M rows):
CREATE INDEX idx_orders_status ON orders(status);

-- Do this (indexes only pending orders):
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Your query:
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
-- Uses the tiny partial index. Lightning fast.

部分索引更小，更新更快，扫描也更快。当你经常查询数据的特定子集时，请使用它们。

共同的线索

所有这些特性都把逻辑从你的应用程序移到了数据库中。这是有意为之。

你的数据库是：

• 事务性的 — 并发操作能够得到正确处理
• 优化过的 — 经过数十年的查询计划工作
• 持久化的 — 能在应用崩溃和部署后依然存在
• 唯一的真相来源 — 所有数据更改都通过它

每一行复制数据库功能的应用代码，都可能带来 bug、竞争条件和不一致性。

让 PostgreSQL 发挥它擅长的工作。 你的应用代码应处理业务逻辑、用户体验以及与外部服务的集成。数据完整性、对数据的计算以及访问控制？这正是数据库的职责。

你最喜欢的 PostgreSQL 功能是什么，却很少有人使用？我一直在寻找新技巧。欢迎在评论中分享。