⚡

PostgreSQL表设计Skill postgres-schema-design

PostgreSQL表设计技能专注于数据库表的结构优化，包括数据类型选择、索引配置、约束设置、性能调优和高级功能如分区和JSONB处理。适用于后端开发者和数据工程师，提升数据库效率、可维护性和可扩展性。关键词：PostgreSQL, 表设计, 数据库优化, 索引, 约束, 数据模型, 分区, JSONB, 性能调优, 数据工程

后端开发 0 次安装 0 次浏览 更新于 3/21/2026

名称: postgres-模式设计 描述: 全面的PostgreSQL特定表设计参考，涵盖数据类型、索引、约束、性能模式和高级功能

PostgreSQL 表设计

核心规则

  • 为参考表（如用户、订单等）定义主键。对于时间序列/事件/日志数据，不一定需要。使用时，优先选择BIGINT GENERATED ALWAYS AS IDENTITY；仅当需要全局唯一性或不透明性时使用UUID。
  • 先规范化（到第三范式） 以消除数据冗余和更新异常；仅在读取性能被证明有问题时进行反规范化，用于测量和高投资回报率的情况。过早反规范化会增加维护负担。
  • 在语义要求的地方添加NOT NULL；为常见值使用DEFAULT。
  • 为实际查询的访问路径创建索引：主键/唯一键（自动）、外键列（手动！）、频繁的筛选/排序键和连接键。
  • 事件时间优先选择TIMESTAMPTZ；金钱使用NUMERIC；字符串使用TEXT；整数值使用BIGINT；浮点数使用DOUBLE PRECISION（或NUMERIC用于精确十进制算术）。

PostgreSQL “陷阱”

  • 标识符：不加引号 → 小写。避免加引号/混合大小写名称。约定：使用snake_case作为表/列名。
  • 唯一键 + NULLs：UNIQUE允许多个NULL。使用UNIQUE (...) NULLS NOT DISTINCT（PG15+）来限制为一个NULL。
  • 外键索引：PostgreSQL不会自动为外键列创建索引。手动添加。
  • 无静默转换：长度/精度溢出会出错（无截断）。例如：将999插入NUMERIC(2,0)会失败并报错，不像一些数据库会静默截断或舍入。
  • 序列/身份有间隙（正常；不要“修复”）。回滚、崩溃和并发事务会导致ID序列中出现间隙（1, 2, 5, 6…）。这是预期行为——不要试图使ID连续。
  • 堆存储：默认没有聚集主键（不同于SQL Server/MySQL InnoDB）；CLUSTER是一次性重组，后续插入不维护。磁盘上的行顺序是插入顺序，除非显式聚集。
  • MVCC：更新/删除会留下死元组；真空处理它们——设计以避免热宽行变动。

数据类型

  • ID：优先选择BIGINT GENERATED ALWAYS AS IDENTITY（GENERATED BY DEFAULT也可）；UUID用于合并/联合/分布式系统或不透明ID。使用uuidv7()生成（如使用PG18+优先）或gen_random_uuid()（如使用旧版PG）。
  • 整数：优先选择BIGINT，除非存储空间关键；INTEGER用于较小范围；除非约束，避免SMALLINT。
  • 浮点数：优先选择DOUBLE PRECISION而非REAL，除非存储空间关键。精确十进制算术使用NUMERIC。
  • 字符串：优先选择TEXT；如需长度限制，使用CHECK (LENGTH(col) <= n)而非VARCHAR(n)；避免CHAR(n)。二进制数据使用BYTEA。大字符串/二进制（>2KB默认阈值）自动存储在TOAST中并进行压缩。TOAST存储：PLAIN（无TOAST）、EXTENDED（压缩+外部存储）、EXTERNAL（外部存储，无压缩）、MAIN（压缩，可能内联）。默认EXTENDED通常最优。通过ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy和ALTER TABLE tbl SET (toast_tuple_target = 4096)控制阈值。大小写不敏感：对于区域/重音处理使用非确定性排序规则；对于纯ASCII使用LOWER(col)上的表达式索引（优先，除非列需要大小写不敏感主键/外键/唯一键）或CITEXT。
  • 金钱：NUMERIC(p,s)（绝不用浮点数）。
  • 时间：时间戳使用TIMESTAMPTZ；仅日期使用DATE；持续时间使用INTERVAL。避免TIMESTAMP（无时区）。使用now()表示事务开始时间，clock_timestamp()表示当前挂钟时间。
  • 布尔值：BOOLEAN带NOT NULL约束，除非需要三态值。
  • 枚举：CREATE TYPE ... AS ENUM用于小而稳定的集合（如美国州、星期）。对于业务逻辑驱动且演变的值（如订单状态）→ 使用TEXT（或INT）+ CHECK或查找表。
  • 数组：TEXT[]、INTEGER[]等。用于有序列表且查询元素时。用GIN索引进行包含（@>、<@）和重叠（&&）查询。访问：arr[1]（1索引）、arr[1:3]（切片）。适用于标签、类别；避免用于关系——使用连接表。字面语法：'{val1,val2}'或ARRAY[val1,val2]。
  • 范围类型：daterange、numrange、tstzrange用于区间。支持重叠（&&）、包含（@>）等操作符。用GiST索引。适用于调度、版本控制、数值范围。选择界限方案并一致使用；默认优先选择[)（包含/排除）。
  • 网络类型：INET用于IP地址，CIDR用于网络范围，MACADDR用于MAC地址。支持网络操作符（<<、>>、&&）。
  • 几何类型：POINT、LINE、POLYGON、CIRCLE用于2D空间数据。用GiST索引。考虑PostGIS用于高级空间功能。
  • 文本搜索：TSVECTOR用于全文搜索文档，TSQUERY用于搜索查询。用GIN索引tsvector。始终指定语言：to_tsvector('english', col)和to_tsquery('english', 'query')。切勿使用单参数版本。这适用于索引表达式和查询。
  • 域类型：CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')用于可重用的自定义类型和验证。跨表强制执行约束。
  • 复合类型：CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)用于列内的结构化数据。用(col).field语法访问。
  • JSONB：优先于JSON；用GIN索引。仅用于可选/半结构化属性。仅当必须保留内容原始顺序时使用JSON。
  • 向量类型：pgvector的vector类型用于向量相似性搜索和嵌入。

不要使用以下数据类型

  • 不要使用timestamp（无时区）；使用timestamptz代替。
  • 不要使用char(n)或varchar(n)；使用text代替。
  • 不要使用money类型；使用numeric代替。
  • 不要使用timetz类型；使用timestamptz代替。
  • 不要使用timestamptz(0)或任何其他精度规范；使用timestamptz代替。
  • 不要使用serial类型；使用generated always as identity代替。

表类型

  • 常规：默认；完全持久，日志记录。
  • 临时：会话范围，自动删除，无日志记录。用于临时工作更快。
  • 无日志：持久但非崩溃安全。写入更快；适用于缓存/暂存。

行级安全

用ALTER TABLE tbl ENABLE ROW LEVEL SECURITY启用。创建策略：CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())。内置基于用户的行级访问控制。

约束

  • 主键：隐式UNIQUE + NOT NULL；创建B-tree索引。
  • 外键：指定ON DELETE/UPDATE操作（CASCADE、RESTRICT、SET NULL、SET DEFAULT）。在引用列上添加显式索引——加速连接并防止父表删除/更新时的锁定问题。对于循环外键依赖，使用DEFERRABLE INITIALLY DEFERRED在事务结束时检查。
  • 唯一键：创建B-tree索引；允许多个NULL，除非NULLS NOT DISTINCT（PG15+）。标准行为：(1, NULL)和(1, NULL)是允许的。使用NULLS NOT DISTINCT：只允许一个(1, NULL)。优先选择NULLS NOT DISTINCT，除非明确需要重复NULL。
  • 检查：行级约束；NULL值通过检查（三值逻辑）。例如：CHECK (price > 0)允许NULL价格。与NOT NULL结合强制执行：price NUMERIC NOT NULL CHECK (price > 0)。
  • 排除：使用操作符防止重叠值。EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)防止房间重复预订。需要适当的索引类型（通常GiST）。

索引

  • B-tree：默认用于相等/范围查询（=、<、>、BETWEEN、ORDER BY）
  • 复合：顺序重要——如果最左前缀相等（WHERE a = ? AND b > ?使用(a,b)上的索引，但WHERE b = ?不使用），则使用索引。将最具选择性/频繁筛选的列放在前面。
  • 覆盖：CREATE INDEX ON tbl (id) INCLUDE (name, email) - 包含非键列以进行索引扫描，无需访问表。
  • 部分：用于热点子集（WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'）。任何status = 'active'的查询都可以使用此索引。
  • 表达式：用于计算搜索键（CREATE INDEX ON tbl (LOWER(email))）。WHERE子句中的表达式必须完全匹配：WHERE LOWER(email) = 'user@example.com'。
  • GIN：JSONB包含/存在、数组（@>、?）、全文搜索（@@）
  • GiST：范围、几何、排除约束
  • BRIN：非常大的、自然有序数据（时间序列）——最小存储开销。当磁盘上的行顺序与索引列相关（插入顺序或CLUSTER后）时有效。

分区

  • 用于非常大的表（>1亿行），查询始终基于分区键筛选（通常是时间/日期）。
  • 替代用途：用于数据维护任务决定的情况，例如定期修剪或批量替换数据。
  • 范围：常用于时间序列（PARTITION BY RANGE (created_at)）。创建分区：CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')。TimescaleDB自动化基于时间或ID的分区，并带有保留策略和压缩。
  • 列表：用于离散值（PARTITION BY LIST (region)）。例如：FOR VALUES IN ('us-east', 'us-west')。
  • 哈希：用于无自然键时的均匀分布（PARTITION BY HASH (user_id)）。创建N个模数分区。
  • 约束排除：需要分区上的CHECK约束供查询规划器修剪。声明式分区（PG10+）自动创建。
  • 优先选择声明式分区或超表。不要使用表继承。
  • 限制：无全局UNIQUE约束——在PK/UNIQUE中包含分区键。不支持分区表的外键；使用触发器。

特殊考虑

更新频繁的表

  • 分离热/冷列——将频繁更新的列放在单独表中以最小化膨胀。
  • 使用fillfactor=90 为HOT更新留出空间，避免索引维护。
  • 避免更新索引列——防止有益的HOT更新。
  • 按更新模式分区——将频繁更新的行与稳定数据分开在另一分区。

插入频繁的工作负载

  • 最小化索引——仅创建查询所需的索引；每个索引都会减慢插入。
  • 使用COPY或多行INSERT 代替单行插入。
  • 无日志表用于可重建的暂存数据——写入更快。
  • 延迟索引创建用于批量加载——>删除索引，加载数据，重建索引。
  • 按时间/哈希分区以分布负载。TimescaleDB自动化插入频繁数据的分区和压缩。
  • 使用自然键作为主键，如（时间戳, 设备ID），如果强制执行全局唯一性很重要，许多插入频繁的表根本不需要主键。
  • 如果需要代理键，优先选择BIGINT GENERATED ALWAYS AS IDENTITY而非UUID。

更新插入友好设计

  • 需要UNIQUE索引在冲突目标列上——ON CONFLICT (col1, col2)需要完全匹配的UNIQUE索引（部分索引不起作用）。
  • 使用EXCLUDED.column 引用将插入的值；仅更新实际更改的列以减少写入开销。
  • DO NOTHING更快，当无需实际更新时。

安全模式演化

  • 事务性DDL：大多数DDL操作可以在事务中运行并回滚——BEGIN; ALTER TABLE...; ROLLBACK;用于安全测试。
  • 并发索引创建：CREATE INDEX CONCURRENTLY避免阻塞写入但不能在事务中运行。
  • 易失默认值导致重写：添加带有易失默认值（如now()、gen_random_uuid()）的NOT NULL列会重写整个表。非易失默认值速度快。
  • 删除约束再删除列：ALTER TABLE DROP CONSTRAINT然后DROP COLUMN以避免依赖问题。
  • 函数签名更改：CREATE OR REPLACE带不同参数创建重载，而非替换。如无重载需求，删除旧版本。

生成列

  • ... GENERATED ALWAYS AS (<expr>) STORED用于计算、可索引字段。PG18+添加VIRTUAL列（读取时计算，不存储）。

扩展

  • pgcrypto：crypt()用于密码哈希。
  • uuid-ossp：替代UUID函数；新项目优先选择pgcrypto。
  • pg_trgm：模糊文本搜索带%操作符、similarity()函数。用GIN索引加速LIKE '%pattern%'。
  • citext：大小写不敏感文本类型。优先选择LOWER(col)上的表达式索引，除非需要大小写不敏感约束。
  • btree_gin/btree_gist：启用混合类型索引（如JSONB和文本列上的GIN索引）。
  • hstore：键值对；大多被JSONB取代但适用于简单字符串映射。
  • timescaledb：时间序列必备——自动化分区、保留、压缩、连续聚合。自托管和Tiger Cloud上可用。
  • postgis：全面的地理空间支持，超越基本几何类型——基于位置应用必备。
  • pgvector：向量相似性搜索用于嵌入。
  • pgaudit：所有数据库活动的审计日志。

JSONB指南

  • 优先选择JSONB带GIN索引。
  • 默认：CREATE INDEX ON tbl USING GIN (jsonb_col); → 加速：
    • 包含 jsonb_col @> '{"k":"v"}'
    • 键存在 jsonb_col ? 'k', 任意/所有键 ?\|, ?&
    • 路径包含 嵌套文档
    • 析取 jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
  • 重@>工作负载：考虑操作类jsonb_path_ops用于更小/更快的仅包含索引：
    • CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
    • 权衡：失去键存在（?, ?|, ?&）查询支持——仅支持包含（@>）
  • 特定标量字段上的相等/范围：提取并用B-tree索引（生成列或表达式）：
    • ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
    • CREATE INDEX ON tbl (price);
    • 优先查询如WHERE price BETWEEN 100 AND 500（使用B-tree）而非WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500无索引。
  • JSONB内数组：使用GIN + @>用于包含（如标签）。如果仅进行包含，考虑jsonb_path_ops。
  • 保持核心关系在表中；使用JSONB用于可选/可变属性。
  • 使用约束限制JSONB列中允许的值，例如config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

示例

用户

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

订单

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);

JSONB

CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);