改变工作方式的 PostgreSQL 实用模式

在使用 PostgreSQL 数据库的过程中，有一组实践方式可以显著提升开发与协作体验。单个做法影响有限，但叠加起来效果十分明显。

使用 UUID 作为主键

UUID 确实存在一些缺点：

完全随机的 UUID 无法自然排序，对索引有一定影响
相比自增 ID 占用更多存储空间（而存储通常是成本最低的资源）

但 UUID 的优势远大于缺点：

生成 UUID 无需与数据库协调
可以安全地对外公开和传递

CREATE TABLE person(
    id uuid not null default gen_random_uuid() primary key,
    name text not null
)

为所有表添加 created_at 和 updated_at 字段

虽然无法完整记录历史变更，但记录创建时间和最后更新时间，在排查问题时是非常有价值的线索。同时，这类信息一旦未记录，事后无法补救，只能通过预先记录获取。

因此，建议所有表统一包含 created_at 与 updated_at 字段，并通过触发器自动维护 updated_at 字段。

CREATE TABLE person(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null
);

CREATE FUNCTION set_current_timestamp_updated_at()
    RETURNS TRIGGER AS $$
DECLARE
_new record;
BEGIN
  _new := NEW;
  _new."updated_at" = now();
RETURN _new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_person_updated_at
    BEFORE UPDATE ON person
    FOR EACH ROW
    EXECUTE PROCEDURE set_current_timestamp_updated_at();

注：每个数据表都需创建对应的触发器，但上述函数仅需创建一次。

外键约束设置 ON UPDATE RESTRICT 和 ON DELETE RESTRICT

该设置可避免删除被引用行时导致的数据丢失，若尝试删除被引用的行，系统会直接抛出错误。存储空间成本低廉，而数据恢复过程则极为繁琐，因此抛出错误比级联删除更合理。

CREATE TABLE person(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null
);

CREATE TABLE pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null,
    owner_id uuid not null references person(id)
                on update restrict
                on delete restrict
);

使用 Schema 进行逻辑分区

默认情况下，所有表都会创建在 public schema 中。该方式虽可行，但未利用自定义模式的能力会造成功能浪费。

Schema 可作为表的逻辑命名空间，适用于中大型应用。跨 schema 的关联与查询完全可行，几乎没有额外成本。

CREATE SCHEMA vet;

CREATE TABLE vet.person(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null
);

CREATE TABLE vet.pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null,
    owner_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict
);

使用“枚举表”而非枚举类型

SQL 中定义枚举的方式很多，例如枚举类型或 CHECK 约束。一个更灵活的做法是使用“枚举表”。

即：使用一张表存放允许的取值，其他表通过外键引用。

CREATE TABLE vet.pet_kind(
    value text not null primary key
);

INSERT INTO vet.pet_kind(value)
VALUES ('dog'), ('cat'), ('bird');

CREATE TABLE vet.pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    owner_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    kind text not null references vet.pet_kind(value)
                on update restrict
                on delete restrict
);

这样不仅可以随时扩展取值，还可以为每个值附加说明等元数据：

CREATE TABLE vet.pet_kind(
    value text not null primary key,
    comment text not null default ''
);

INSERT INTO vet.pet_kind(value, comment)
VALUES
    ('dog', 'A Canine'),
    ('cat', 'A Feline'),
    ('bird', 'A 50 Year Commitment');

CREATE TABLE vet.pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    owner_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    kind text not null references vet.pet_kind(value)
                on update restrict
                on delete restrict
);

数据表命名使用单数形式

表名建议统一使用名词单数形式。虽然 SELECT * FROM pets 看起来更自然，但在复杂查询中，实际操作的是“单行数据”。

SELECT *
FROM pet
-- It's a cruel coincidence that in english an "s"
-- suffix can sometimes work both as a plural
-- and a possessive, but notice how the where clause
-- is asserting a condition about a single row.
WHERE pet.name = 'sally'

使用复数形式命名数据表会引发诸多边缘问题，数据表名称应与表中单行数据所代表的实体保持一致。

关联表采用机械化命名规则

用于建立数据多对多关系的 "连接表" 有时可使用语义化名称，但多数情况下无合适的语义化名称，此时可直接拼接所关联表的名称作为连接表名。

CREATE TABLE vet.person(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

CREATE TABLE vet.pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

-- pet_owner would work in this context, but
-- I just want to demonstrate the table_a_table_b naming scheme
CREATE TABLE vet.person_pet(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    pet_id uuid not null references vet.pet(id)
                on update restrict
                on delete restrict
);

CREATE UNIQUE INDEX ON vet.person_pet(person_id, pet_id);

优先使用软删除

再次强调：存储便宜，数据恢复困难。

如需标记数据失效，使用可为空的 timestamptz 字段比直接删除更安全：

有时间戳：表示删除或失效时间
为 NULL：表示仍然有效

CREATE TABLE vet.prescription(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    pet_id uuid not null references vet.pet(id)
             on update restrict
             on delete restrict,
    issued_at timestamptz not null,
    -- Instead of deleting a prescription,
    -- explicitly mark when it was revoked
    revoked_at timestamptz
);

相比布尔值，时间戳通常更有价值，因为不仅表示“是否发生”，还能表示“何时发生”。

将状态表示为日志形式

将状态表示为单一字段（如 submitted → approved）存在两个问题：

无法准确记录状态发生的时间或来源
状态更新可能以乱序形式接收（例如 Webhook 场景）

应对该问题的方式是创建状态日志表，每行记录代表某一时间点的实体状态。不应复用 created_at 或 updated_at 字段，需新增显式的 valid_at 字段标记状态生效时间。

CREATE TABLE vet.adoption_approval_status(
    value text not null primary key
);

INSERT INTO vet.adoption_approval_status(value)
VALUES ('submitted'), ('in_review'), ('rejected'), ('approved');

CREATE TABLE vet.adoption_approval(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    status text not null references vet.adoption_approval_status(value)
                on update restrict
                on delete restrict,
    valid_at timestamptz not null
);

CREATE INDEX ON vet.adoption_approval(person_id, valid_at DESC);

仅对 valid_at 字段建立索引在短期内有效，但查询性能最终会下降。最优解决方案是新增 latest 布尔字段，配合唯一索引和触发器，确保仅有 valid_at 最新的行标记为最新状态：

CREATE TABLE vet.adoption_approval(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid not null references vet.person(id)
                on update restrict
                on delete restrict,
    status text not null references vet.adoption_approval_status(value)
                on update restrict
                on delete restrict,
    valid_at timestamptz not null,
    latest boolean default false
);

CREATE INDEX ON vet.adoption_approval(person_id, valid_at DESC);

-- Conditional unique index makes sure we only have one latest
CREATE UNIQUE INDEX ON vet.adoption_approval(person_id, latest)
WHERE latest = true;

-- Then a trigger to keep latest up to date
CREATE OR REPLACE FUNCTION vet.set_adoption_approval_latest()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE vet.adoption_approval
    SET latest = false
    WHERE latest = true and person_id = NEW.person_id;

    UPDATE vet.adoption_approval
    SET latest = true
    WHERE id = (
        SELECT id
        FROM vet.adoption_approval
        WHERE person_id = NEW.person_id
        ORDER BY valid_at DESC
        LIMIT 1
    );

    RETURN null;
END;
$function$;

CREATE TRIGGER adoption_approval_insert_trigger
    AFTER INSERT ON vet.adoption_approval
    FOR EACH ROW
    EXECUTE FUNCTION vet.set_adoption_approval_latest();

为特殊行标记 system_id

系统中常存在“特殊行”，例如用于系统行为配置或固定逻辑依赖的记录。

可通过 system_id 字段进行标识，并对其创建唯一索引。多个 NULL 不会冲突，因此对普通数据无影响。

CREATE TABLE vet.contact_info(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    person_id uuid references vet.person(id)
                on update restrict
                on delete restrict,
    mailing_address text not null,
    system_id text
);

CREATE UNIQUE INDEX ON vet.contact_info(system_id);

-- Not hard to imagine wanting to build functionality that
-- automatically contacts the CDC for cases of rabies or similar,
-- but maybe every other bit of contact_info in the system is
-- for more "normal" purposes
INSERT INTO vet.contact_info(system_id, mailing_address)
VALUES ('cdc', '4770 Buford Highway, NE');

谨慎使用视图

视图在封装复杂查询时非常有用，但也存在明显问题：

删除字段需要重建视图
视图嵌套会迅速失控
查询规划器对视图的优化能力有限

建议仅在必要时使用，并避免“视图套视图”。

CREATE TABLE vet.prescription(
    id uuid not null default gen_random_uuid() primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    pet_id uuid not null references vet.pet(id)
             on update restrict
             on delete restrict,
    issued_at timestamptz not null,
    -- Instead of deleting a prescription,
    -- explicitly mark when it was revoked
    revoked_at timestamptz
);

CREATE INDEX ON vet.prescription(revoked_at);

-- There are pros and cons to having this view
CREATE VIEW vet.active_prescription AS
    SELECT
        vet.prescription.id,
        vet.prescription.created_at,
        vet.prescription.updated_at,
        vet.prescription.pet_id,
        vet.prescription.issued_at
    FROM
        vet.prescription
    WHERE
        vet.prescription.revoked_at IS NULL;

使用 JSON 查询

PostgreSQL 对 JSON 的支持不仅体现在存储，更体现在查询结果构造上。

将 JSON 作为查询结果格式能发挥更大价值。该方式虽存在缺点（丢失类型信息、需一次性获取结果、JSON 序列化存在性能开销），但核心优势是可通过单次数据库请求获取所需全部信息，避免笛卡尔积问题和 N+1 查询问题。

SELECT jsonb_build_object(
  'id', vet.person.id,
  'name', vet.person.name,
  'pets', array(
    SELECT jsonb_build_object(
      'id', vet.pet.id,
      'name', vet.pet.name,
      'prescriptions', array(
        SELECT jsonb_build_object(
          'issued_at', vet.prescription.issued_at
        )
        FROM vet.prescription
        WHERE vet.prescription.pet_id = vet.pet.id
      )
    )
    FROM vet.person_pet
    LEFT JOIN vet.pet
      ON vet.pet.id = vet.person_pet.pet_id
    WHERE vet.person_pet.person_id = vet.person.id
  ),
  'contact_infos', array(
    SELECT jsonb_build_object(
      'mailing_address', vet.contact_info.mailing_address
    )
    FROM vet.contact_info
    WHERE vet.contact_info.person_id = vet.person.id
  )
)
FROM vet.person
WHERE id = '29168a93-cd14-478f-8c70-a2b7a782c714';

上述查询可返回如下格式的结果：

{
  "id": "29168a93-cd14-478f-8c70-a2b7a782c714",
  "name": "Jeff Computers",
  "pets": [
    {
      "id": "3e5557c0-c628-44ef-b4d1-86012c5f48bf",
      "name": "Rhodie",
      "prescriptions": [
        {
          "issued_at": "2025-03-11T23:46:18.345146+00:00"
        }
      ]
    },
    {
      "id": "ed63ca7d-3368-4353-9747-6b6b2fa6657a",
      "name": "Jenny",
      "prescriptions": []
    }
  ],
  "contact_infos": [
    {
      "mailing_address": "123 Sesame St."
    }
  ]
}

结语

综合来看，这些 PostgreSQL 设计模式并不追求“炫技”，而是围绕真实业务场景中反复踩过的坑给出的务实解法。它们关注长期维护、数据安全与系统演进成本，强调在一开始就做出对未来友好的选择。随着业务规模扩大，这些看似细微的设计习惯，往往会成为系统稳定性与开发效率的分水岭。

原文链接：https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns

作者：Ethan McCue

HOW 2026 议题招募中

2026 年 4 月 27-28 日，由 IvorySQL 社区联合 PGEU（欧洲 PG 社区）、PGAsia（亚洲 PG 社区）共同打造的 HOW 2026（IvorySQL & PostgreSQL 技术峰会）将再度落地济南。届时，PostgreSQL 联合创始人 Bruce Momjian 等顶级大师将亲临现场。

自开启征集以来，HOW 2026 筹备组已感受到来自全球 PostgreSQL 爱好者的澎湃热情。为了确保大会议题的深度与广度，我们诚邀您在 2026 年 2 月 27 日截止日期前，提交您的技术见解。

投递链接：https://jsj.top/f/uebqBc

posted @ 2026-02-02 14:40 IvorySQL 阅读(0) 评论(0) 收藏举报

刷新页面返回顶部

ivorysql