PostgreSQL DDL（数据定义语言）深度详解文档

以下是专为 Java 后端开发者（尤其是使用 Spring Boot 的团队）撰写的 PostgreSQL DDL（数据定义语言）深度详解文档，全面覆盖定义、作用、核心内容、企业级最佳实践，并提供标准、完整、带中文注释的实战示例，助你推动团队数据库规范落地。

---

📐 PostgreSQL DDL（数据定义语言）深度详解文档

—— 数据库结构的“建筑师”与团队标准化基石

适用对象：Java 开发、后端架构师、技术负责人、DBA 协作团队
目标：系统掌握 PostgreSQL DDL 的所有核心语句与企业级规范，统一团队建表、建索引、改结构的标准，杜绝“野蛮开发”。

---

一、DDL 是什么？（Definition）

DDL（Data Definition Language，数据定义语言）是用于定义、修改、删除数据库对象结构的 SQL 子集。
它不操作数据本身，而是定义数据的容器、规则与访问路径。

✅ 核心作用：

作用	说明
构建数据模型	创建表、视图、序列、函数等结构
约束数据完整性	通过主键、外键、唯一、检查约束确保数据合法
优化查询性能	创建索引、分区表加速访问
实现扩展能力	定义自定义类型、枚举、域、函数、插件
支撑系统演进	通过版本化脚本安全地变更结构

💡 关键认知：
在 Java 项目中，DDL 不是“写一次就不管”的脚本，而是系统架构的契约。
任何表结构变更，都应像代码提交一样——版本化、可追溯、可回滚。

---

二、DDL 包含哪些内容？（Core Components）

PostgreSQL 的 DDL 主要包含以下 7 类核心语句：

类别	语句	作用	企业级重要性
✅ 表结构	CREATE TABLE / DROP TABLE / TRUNCATE	定义/清空/删除表	核心，所有业务数据载体
✅ 字段变更	ALTER TABLE ... ADD COLUMN / ALTER COLUMN / DROP COLUMN	修改表结构	高频操作，必须规范
✅ 索引管理	CREATE INDEX / DROP INDEX	加速查询	性能命脉，必须显式创建
✅ 约束管理	ADD CONSTRAINT / DROP CONSTRAINT	定义业务规则	防止脏数据，替代 Java 层校验
✅ 视图与物化视图	CREATE VIEW / CREATE MATERIALIZED VIEW	封装复杂查询	提升复用性，降低 Java 逻辑复杂度
✅ 序列管理	CREATE SEQUENCE / ALTER SEQUENCE	生成自增 ID	高并发场景下替代 SERIAL 更灵活
✅ 模式与权限	CREATE SCHEMA / GRANT（部分）	组织对象命名空间	多模块/微服务隔离

✅ 重要提醒：
GRANT 虽属 DCL，但在 DDL 中常与对象创建一起使用，建议统一管理。

---

三、DDL 核心语句详解与企业级标准示例（带中文注释）

以下示例均为生产环境推荐写法，遵循 PostgreSQL 最佳实践与 Java 团队协作规范。

---

✅ 1. CREATE TABLE —— 创建表（企业标准模板）

核心原则：
所有表必须包含：主键、业务唯一标识、创建/更新时间、状态字段、扩展字段。

-- 📌 示例：创建用户表（企业级标准模板）
CREATE TABLE users (
    -- ✅ 1. 主键：BIGSERIAL（自增整型，推荐）
    id BIGSERIAL PRIMARY KEY,

    -- ✅ 2. 业务主键：UUID（分布式系统必备，避免ID暴露）
    uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),

    -- ✅ 3. 必填字段 + 唯一约束
    username VARCHAR(50) UNIQUE NOT NULL CHECK (LENGTH(username) >= 3),
    email VARCHAR(100) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),

    -- ✅ 4. 密码存储（哈希值）
    password_hash TEXT NOT NULL,

    -- ✅ 5. 枚举状态（避免使用字符串“active/inactive”，用 CHECK 约束）
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned', 'deleted')),

    -- ✅ 6. 时间戳：自动填充，Java 层无需维护
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- ✅ 7. 扩展字段：JSONB（存储动态配置，如用户偏好、通知设置）
    metadata JSONB DEFAULT '{}'::JSONB,

    -- ✅ 8. 可选字段：手机号（允许为空）
    phone VARCHAR(20) NULL,

    -- ✅ 9. 注释：为字段添加业务说明（团队协作必备！）
    -- COMMENT ON COLUMN users.username IS '用户登录名，唯一，3~50字符';
    -- COMMENT ON COLUMN users.metadata IS '用户个性化设置，JSONB格式，如 {"theme": "dark", "lang": "zh"}';
);

-- ✅ 10. 为常用查询字段创建索引（必须！）
CREATE INDEX idx_users_email ON users (email);                    -- ✅ 加速登录、找回密码
CREATE INDEX idx_users_status ON users (status);                  -- ✅ 加速状态筛选（如查询所有活跃用户）
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);    -- ✅ JSONB 必须用 GIN 索引，否则查询效率极低！
CREATE INDEX idx_users_created_at ON users (created_at DESC);     -- ✅ 按创建时间排序查询
CREATE INDEX idx_users_uuid ON users (uuid);                      -- ✅ 业务系统常按 UUID 查询

-- ✅ 11. 为组合查询创建复合索引（提升多条件查询性能）
CREATE INDEX idx_users_status_created ON users (status, created_at DESC);

-- ✅ 12. 添加表注释（清晰说明业务用途）
COMMENT ON TABLE users IS '系统用户主表，存储核心身份与配置信息';

✅ 团队规范建议：

• 所有表必须包含：id（BIGSERIAL）、uuid（UUID）、created_at、updated_at、status。
• metadata 字段统一用 JSONB，避免“字段爆炸”。
• 所有索引必须命名规范：idx_表名_字段名。
• 所有字段必须有注释（COMMENT ON COLUMN），否则新人看不懂。

---

✅ 2. ALTER TABLE —— 修改表结构（安全变更指南）

原则：禁止直接在生产库执行 ALTER！必须通过 Flyway 版本脚本管理。

-- 📌 示例1：添加新字段（推荐：允许 NULL，兼容旧数据）
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(255) NULL;
COMMENT ON COLUMN users.avatar_url IS '用户头像URL，支持CDN地址';

-- 📌 示例2：修改字段类型（谨慎！需确保数据兼容）
-- 假设原 phone 为 VARCHAR(15)，现需支持国际号（+86 开头，最长20位）
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(20);

-- 📌 示例3：修改字段默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 📌 示例4：为字段添加非空约束（必须先清理空数据！）
-- ❌ 错误：直接加 NOT NULL 会失败（存在空值）
-- ✅ 正确：先更新空值，再加约束
UPDATE users SET phone = '' WHERE phone IS NULL;  -- ✅ 先填充默认值
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- 📌 示例5：添加检查约束（业务规则下沉）
ALTER TABLE users ADD CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3 AND LENGTH(username) <= 50);

-- 📌 示例6：添加外键约束（保证引用完整性）
-- 假设已存在 orders 表
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user_id 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE CASCADE  -- ✅ 用户删除，订单级联删除
ON UPDATE CASCADE; -- ✅ 用户ID变更，订单同步更新（慎用，通常不改ID）

-- 📌 示例7：删除字段（生产环境极少使用！建议用软删除）
-- ALTER TABLE users DROP COLUMN phone;  -- ⚠️ 生产环境禁止！应保留字段，标记弃用

-- 📌 示例8：重命名字段（需谨慎，影响 Java 实体类）
ALTER TABLE users RENAME COLUMN avatar_url TO profile_image_url;

✅ 团队规范建议：

• 所有 ALTER 必须写入 Flyway 脚本，命名如：V2__add_avatar_url_to_users.sql。
• 添加 NOT NULL 前，必须先清理或填充历史空值。
• 删除字段前，确认无任何 Java 服务、报表、API 依赖该字段。
• 外键 ON DELETE CASCADE 仅用于“子表依赖主表”的场景（如订单→用户），避免误删。

---

✅ 3. CREATE INDEX —— 索引管理（性能命脉）

真理：没有索引的查询 = 全表扫描 = 系统崩溃的开端。

-- 📌 示例1：普通 B-Tree 索引（最常用）
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

-- 📌 示例2：复合索引（多字段联合查询）
CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at DESC);

-- 📌 示例3：唯一索引（强制唯一性）
CREATE UNIQUE INDEX idx_users_username_unique ON users (username);
-- ✅ 与 UNIQUE 约束等价，但可独立管理

-- 📌 示例4：部分索引（节省空间，提升性能）
-- 只为“活跃用户”建立邮箱索引（非活跃用户不查）
CREATE INDEX idx_users_active_email ON users (email) WHERE status = 'active';

-- 📌 示例5：GIN 索引（JSONB、数组、全文检索专用）
CREATE INDEX idx_users_metadata_gin ON users USING GIN (metadata);
CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);  -- ✅ tags 是 text[] 数组类型

-- 📌 示例6：BRIN 索引（超大表时序数据，如日志）
CREATE TABLE logs (
    id BIGSERIAL,
    event_time TIMESTAMPTZ,
    message TEXT
);
CREATE INDEX idx_logs_event_time_brin ON logs USING BRIN (event_time);  -- ✅ 适用于百万级日志表，索引体积小

-- 📌 示例7：表达式索引（对函数结果建立索引）
-- 查询时经常按邮箱域名分组：WHERE email LIKE '%@company.com'
CREATE INDEX idx_users_email_domain ON users ((split_part(email, '@', 2)));  -- ✅ 对 split_part 结果建索引

-- 📌 示例8：全文检索索引（替代 LIKE '%xxx%'）
CREATE INDEX idx_articles_content_tsvector ON articles USING GIN (to_tsvector('english', content));

-- 📌 示例9：查看所有索引（调试用）
\di  -- 在 psql 中执行，列出当前模式下所有索引

✅ 团队规范建议：

• 所有 WHERE、JOIN、ORDER BY 字段必须建索引。
• JSONB 字段必须使用 GIN 索引，否则查询性能为 0。
• 使用 EXPLAIN ANALYZE 检查查询是否命中索引。
• 索引不是越多越好！每个索引都会拖慢写入速度，按需创建。

---

✅ 4. CREATE CONSTRAINT —— 约束（数据质量的“防火墙”）

理念：让数据库帮你校验数据，而不是让 Java 代码兜底。

-- 📌 示例1：主键约束（自动创建索引）
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,  -- ✅ 自动创建唯一索引
    sku VARCHAR(50) UNIQUE NOT NULL  -- ✅ 自动创建唯一索引
);

-- 📌 示例2：外键约束（保证引用完整性）
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER CHECK (quantity > 0)  -- ✅ 数量必须大于0
);

-- 📌 示例3：检查约束（业务规则）
ALTER TABLE users ADD CONSTRAINT chk_email_domain CHECK (email LIKE '%@company.com' OR email LIKE '%@client.com');
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0 AND price < 1000000);

-- 📌 示例4：非空约束（避免空值）
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 📌 示例5：唯一约束（避免重复）
ALTER TABLE users ADD CONSTRAINT uk_users_phone UNIQUE (phone);

-- 📌 示例6：删除约束（谨慎！）
ALTER TABLE users DROP CONSTRAINT chk_email_domain;

-- 📌 示例7：命名约束（推荐！便于管理）
-- ✅ 推荐：显式命名，便于识别和删除
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- ✅ 不推荐：让系统自动生成名字（如 users_email_key），难定位

✅ 团队规范建议：

• 所有业务规则（金额>0、状态枚举、邮箱格式）必须在数据库层约束。
• 禁止依赖 Java 层 @Valid 或 @NotNull 做唯一性校验——数据库才是最终防线。
• 使用 CHECK 约束代替 Java 中的 if (status != "active") throw ...。

---

✅ 5. CREATE VIEW 与 CREATE MATERIALIZED VIEW —— 查询封装与性能优化

视图：虚拟表，每次查询实时计算。
物化视图：物理存储结果，可定时刷新，适合复杂聚合。

-- 📌 示例1：创建普通视图（简化复杂查询）
CREATE VIEW user_order_summary AS
SELECT
    u.id AS user_id,
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.created_at) AS last_order_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.username;

-- ✅ 使用：SELECT * FROM user_order_summary WHERE user_id = 1001;

-- 📌 示例2：创建物化视图（适合报表、统计）
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_revenue,
    COUNT(DISTINCT o.user_id) AS new_customers
FROM orders o
WHERE o.status = 'paid'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;

-- ✅ 刷新物化视图（每天凌晨执行）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

-- ✅ 为物化视图创建索引
CREATE INDEX idx_monthly_sales_month ON monthly_sales_summary (month);

-- 📌 示例3：带安全性的视图（限制访问字段）
CREATE VIEW public_user_info AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';
-- ✅ Java 服务只查询此视图，无法访问 password_hash、metadata

✅ 团队规范建议：

• 复杂报表、多表关联查询优先用视图封装，Java 层直接 SELECT * FROM view。
• 物化视图用于“数据变化不频繁、查询频率高”的场景（如每日统计）。
• 使用 REFRESH MATERIALIZED VIEW CONCURRENTLY 避免锁表。

---

✅ 6. CREATE SEQUENCE —— 自增序列（高级场景）

BIGSERIAL 是语法糖，本质是 BIGINT + SEQUENCE。
当你需要控制步长、缓存、循环时，手动创建序列。

-- 📌 示例：创建自定义序列（用于订单号前缀）
CREATE SEQUENCE order_seq
    START WITH 1000000
    INCREMENT BY 1
    MINVALUE 1000000
    MAXVALUE 9999999
    CACHE 100;  -- ✅ 缓存100个值，提升性能，避免频繁读取磁盘

-- ✅ 使用：在插入时调用 nextval
INSERT INTO orders (order_no, user_id, total_amount)
VALUES (
    'ORD-' || nextval('order_seq'),  -- ✅ 生成如 ORD-1000001
    1001,
    299.90
);

-- ✅ 查看当前值
SELECT currval('order_seq');

-- ✅ 重置序列（仅用于测试）
ALTER SEQUENCE order_seq RESTART WITH 1000000;

✅ 团队规范建议：

• 一般场景用 BIGSERIAL 即可。
• 需要自定义格式 ID（如 INV-2025-0001）时，用 SEQUENCE + nextval。
• 避免在高并发下使用 currval()，可能导致竞争。

---

✅ 7. CREATE SCHEMA —— 模式管理（微服务/多模块隔离）

Schema 是数据库中的“命名空间”，用于隔离不同业务模块。

-- 📌 示例：为订单模块创建独立模式
CREATE SCHEMA IF NOT EXISTS order_module;

-- ✅ 在该模式下创建表
CREATE TABLE order_module.orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(50) UNIQUE NOT NULL
);

-- ✅ 授权应用用户访问该模式
GRANT USAGE ON SCHEMA order_module TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA order_module TO app_user;

-- ✅ 查询时需指定模式
SELECT * FROM order_module.orders;

-- ✅ 设置默认模式（连接时指定）
SET search_path TO order_module, public;

✅ 团队规范建议：

• 微服务架构下，每个服务使用独立 Schema，避免表名冲突。
• 多租户系统可用 Schema 实现租户隔离（每个租户一个 Schema）。
• Spring Boot 中通过 spring.datasource.schema 指定默认 Schema。

---

四、DDL 企业级最佳实践总结（Java 团队必须遵守）

原则	说明	推荐做法
✅ 所有 DDL 必须版本化	禁止手动执行	使用 Flyway/Liquibase，脚本命名：V1__create_users.sql
✅ 所有表必须标准化	统一结构，降低认知成本	id、uuid、created_at、updated_at、status、metadata
✅ 索引必须显式创建并命名	性能命脉	idx_表名_字段名，JSONB 用 GIN，时间用 B-Tree
✅ 约束必须在数据库层定义	数据质量第一道防线	CHECK、FOREIGN KEY、UNIQUE、NOT NULL
✅ 禁止物理删除	数据可追溯	用 status = 'deleted' 替代 DELETE
✅ JSONB 用于动态字段	替代“加字段”	存储配置、扩展属性，支持索引
✅ 物化视图用于聚合报表	减少 Java 计算	REFRESH MATERIALIZED VIEW CONCURRENTLY
✅ 使用 Schema 隔离模块	多服务/多租户	CREATE SCHEMA order_module
✅ 注释是团队语言	他人能看懂	COMMENT ON TABLE / COLUMN 必须写

---

五、附录：Flyway DDL 脚本模板（可直接复用）

V1__init_schema.sql

-- 创建用户表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned', 'deleted')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::JSONB,
    phone VARCHAR(20) NULL
);

COMMENT ON TABLE users IS '系统用户主表';
COMMENT ON COLUMN users.uuid IS '业务主键，分布式唯一标识';
COMMENT ON COLUMN users.metadata IS 'JSONB格式用户配置，如 {"theme":"dark", "lang":"zh"}';

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status ON users (status);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);
CREATE INDEX idx_users_created_at ON users (created_at DESC);

V2__add_avatar_url.sql

-- 添加头像字段（兼容旧数据）
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(255) NULL;
COMMENT ON COLUMN users.avatar_url IS '用户头像URL，支持CDN';

---

✅ 结语：DDL 不是“建表语句”，而是团队的数据契约

你写的每一条 CREATE TABLE、CREATE INDEX、ALTER COLUMN，
都是在为未来 3 年的系统稳定、性能、可维护性打地基。

一个规范的 DDL 设计，胜过 100 个 Java 单元测试。

---

📌 下一步行动建议：

1. 将本文档作为团队《数据库设计规范》的核心章节。
2. 在 GitLab 中创建 db/ddl/ 目录，存放所有 Flyway 脚本。
3. 在代码审查（Code Review）中，强制检查 DDL 是否符合本规范。
4. 组织一次“DDL 最佳实践”内部培训，用本文档 + 示例现场演练。