SuperAPI AI 工具集
合作ENAI 行业资讯收藏
Skill 广场postgresql

postgresql

数据与分析

查看项目

更新于 10-15 08:33

技能介绍

用途

本技能用于在 PostgreSQL 上设计专用的表结构（schema）。当需要为 PostgreSQL 设计 schema、选择数据类型与约束、规划索引/分区/RLS 策略，或从规模与可维护性角度审查表结构时使用。不适用于面向非 PostgreSQL 数据库、仅做查询调优而不涉及 schema 变更、或需要数据库无关建模指南的场景。

主要能力

  • 数据类型选择：ID 优先使用 BIGINT GENERATED ALWAYS AS IDENTITY，需要全局唯一或不透明 ID 时使用 UUID（PG18+ 用 uuidv7()，旧版本用 gen_random_uuid()）；整数优先 BIGINT；浮点优先 DOUBLE PRECISION，精确小数用 NUMERIC；字符串优先 TEXT；金额用 NUMERIC；时间用 TIMESTAMPTZ；布尔用 BOOLEAN。
  • 约束：主键、外键（需手动为引用列建索引）、UNIQUE（PG15+ 可用 NULLS NOT DISTINCT）、CHECK、EXCLUDE。
  • 索引：B-tree、复合索引、覆盖索引（INCLUDE）、部分索引、表达式索引、GIN、GiST、BRIN。
  • 分区：RANGE、LIST、HASH，以及约束排除。
  • 行级安全（RLS）：通过 ENABLE ROW LEVEL SECURITY 与 CREATE POLICY 实现行级访问控制。
  • 高级特性：JSONB、数组、范围类型、网络类型、几何类型、全文检索、域类型、复合类型、生成列、扩展（pgcrypto、pg_trgm、citext、btree_gin/btree_gist、hstore、timescaledb、postgis、pgvector、pgaudit 等）。

使用方式

按以下步骤执行：捕获实体、访问模式与规模目标（行数、QPS、保留期）；选择能强制不变量的数据类型与约束；为真实查询路径添加索引并用 EXPLAIN 验证；在规模或访问控制需要时规划分区或 RLS；审查迁移影响并安全应用变更。

安全要求：生产环境避免破坏性 DDL，除非有备份与回滚方案；应用 schema 变更前使用迁移与预发环境验证。

适用场景

  • 新建 PostgreSQL schema 的表结构设计。
  • 数据类型与约束的选型。
  • 索引、分区、RLS 策略规划。
  • 面向规模与可维护性的表结构审查。
  • 更新密集、插入密集、Upsert 友好、安全 schema 演进等特殊场景设计。

限制

  • 不适用于非 PostgreSQL 数据库。
  • 不适用于仅做查询调优而不涉及 schema 变更的场景。
  • 不提供数据库无关的建模指南。
  • 分区限制：无全局 UNIQUE 约束，需将分区键纳入 PK/UNIQUE；不支持从分区表出发的外键，需用触发器。
  • 部分操作限制：CREATE INDEX CONCURRENTLY 不能在事务中运行；带易变默认值的 NOT NULL 列会重写整表。

核心规则与注意事项

  • 先规范化（至 3NF），仅在实测高收益读取且连接性能确有问题时反规范化。
  • 语义需要处一律加 NOT NULL，常用值加 DEFAULT。
  • 为实际查询的访问路径建索引：PK/唯一（自动）、外键列（需手动）、频繁过滤/排序、连接键。
  • PostgreSQL 注意点：未加引号的标识符会转为小写，建议 snake_case；UNIQUE 允许多个 NULL；外键列不会自动建索引；无静默强制转换，长度/精度溢出会报错；序列/identity 存在间隙属正常；默认无聚簇主键；MVCC 下更新/删除会留下死元组。
  • 避免使用的类型：timestamp（无时区）、char(n)/varchar(n)、money、timetz、timestamptz(0) 等精度指定、serial。

示例

用户表：

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);