数据库教程FGMT49‑PostgreSQL数据定义与数据对象开发设计

## 前言与内容大纲
数据对象设计是PostgreSQL数据库开发与DBA运维的核心基础，合理的对象定义、索引规划、约束设计、程序对象编写直接决定业务系统的数据正确性、查询性能与后期维护成本。风哥教程本文面向数据库工程师、DBA、运维人员、数据库架构师，完整讲解索引、约束、视图、序列、存储过程、触发器、游标、自定义函数，同时覆盖关系数据库三大范式、业务建模、数据模型逆向工程相关内容。本套风哥教程全部环境硬件规格统一采用**64G内存、8CPU**服务器；主机分别为`fgedu‑net‑cn1`作为开发测试数据库主机，`fgedu‑net‑cn2`作为模型逆向、备份验证主机；文件路径全部统一替换为`/fgedudb`；实例名、数据库名`fgedudb`，业务用户名`fgedu`。风哥教程本文所有DDL、DML实战操作均需要在隔离测试环境完成验证，禁止未经过测试直接在线上业务库执行。

风哥教程本文整体知识大纲：
1. PostgreSQL索引理论：索引优缺点、索引类型、索引维护基础理论
2. PostgreSQL各类约束理论：主键、唯一、非空、检查、外键、排它约束原理
3. 视图、序列对象基础理论
4. PL/pgSQL程序对象理论：函数、存储过程、游标、触发器概念与相互区别
5. 关系数据库三大范式理论，数据库建模基本原则
6. 实战一：多种类型索引创建、查询、修改、删除完整实操
7. 实战二：各类约束创建、新增、删除、禁用实操
8. 实战三：普通视图、更新视图管理实操
9. 实战四：序列对象创建、修改、使用实操
10. 实战五：PL/pgSQL自定义函数开发实战
11. 实战六：存储过程开发与调用实操
12. 实战七：游标编写与批量数据处理实战
13. 实战八：触发器与触发器函数完整案例
14. 实战九：业务案例完整建模，基于三大范式设计业务库
15. 实战十：数据模型逆向工程，导出模型DDL，模型比对
16. 对象开发阶段常见踩坑与优化建议

> 网上搜索风哥教程可以学习全套数据库教程

## 一、PostgreSQL数据对象理论知识
### 1.1 索引理论基础
索引是提升查询效率的数据库对象，通过建立键值与表元组的映射，避免全表扫描。索引本质是以额外存储空间、DML写入开销换取查询性能提升，索引并不是越多越好，过多索引会加重INSERT、UPDATE、DELETE的IO与CPU开销，DML变更时需要同步维护全部关联索引。

PostgreSQL主流索引类型：
1. **B‑Tree索引**：默认索引类型，支持等值、范围、排序，适用于绝大多数业务场景；支持多列复合索引、表达式索引、部分索引、覆盖索引。
2. **GiST索引**：几何类型、范围类型、全文检索、排它约束依赖GiST索引。
3. **GIN索引**：数组、jsonb文档类型，适合多元素包含查询。
4. **BRIN索引**：块范围索引，适合物理有序大表，占用存储空间极小，适合时序日志类数据表。

索引相关运维概念：索引膨胀、索引有效性、索引扫描类型、索引维护REINDEX重建索引。索引会伴随表数据变更产生膨胀，长时间运行业务表需要定期监控索引大小，必要时执行重建。

> 风哥 itpux‑com

### 1.2 约束理论体系
约束保障数据库内部数据完整性，分为实体完整性、域完整性、参照完整性、业务排他逻辑约束。
1. **主键约束PRIMARY KEY**：实体完整性，主键列非空且全局唯一，一张表仅允许一个主键，主键底层自动创建B‑Tree唯一索引。
2. **唯一约束UNIQUE**：列或者组合列数值不能重复；允许多个NULL，NULL之间不判定为冲突。底层生成唯一B‑Tree索引。
3. **非空约束NOT NULL**：域完整性，列不能存储NULL值。
4. **检查约束CHECK**：自定义布尔表达式，写入或者更新元组时校验表达式结果，不满足则拒绝写入，实现简单业务规则校验。
5. **外键约束FOREIGN KEY**：参照完整性，保障当前表字段取值必须匹配引用表主键/唯一键，维护表与表之间关联关系；支持ON DELETE、ON UPDATE级联动作，包括CASCADE、SET NULL、RESTRICT、NO ACTION等行为。
6. **排它约束EXCLUDE**：使用索引运算符，保证表中任意两行不能同时满足指定运算条件，典型场景时间区间不重叠，地理位置不重叠；排它约束底层会自动创建GiST索引。

约束可以在建表时定义，也可以后期通过ALTER TABLE追加；部分约束支持NOT VALID模式，先定义约束，再校验存量数据，避免大表锁表时间过长。

### 1.3 视图与序列理论
视图（VIEW）是存储的SELECT查询定义，本身不存储物理数据，访问视图时动态执行内部查询语句。分为普通视图与可更新视图，可更新视图满足一定条件可以直接对视图执行INSERT、UPDATE、DELETE；复杂多表JOIN视图无法直接更新，可以通过`INSTEAD OF`触发器实现视图DML改写逻辑。

序列（SEQUENCE）是独立对象，用于生成递增数字，主要用于主键ID生成；支持设置起始值、步长、最大值、循环属性；传统SERIAL语法本质是隐式创建序列，推荐使用IDENTITY标准自增语法。序列对象独立于数据表，删除表不会自动删除序列，需要单独维护。

> 风哥教程 113257174

### 1.4 PL/pgSQL程序对象理论
PL/pgSQL是PostgreSQL内置过程化语言，可以编写函数、存储过程、触发器函数，支持变量、条件判断、循环、游标、异常捕获，实现数据库内部复杂业务逻辑。

1. **函数FUNCTION**：可以在SELECT语句中调用，拥有返回值；可以返回标量、行、集合；函数内部不能直接执行COMMIT/ROLLBACK事务控制。
2. **存储过程PROCEDURE**：使用CALL命令调用，没有返回值；存储过程内部支持事务提交、回滚，适合大批量数据处理、数据迁移类业务逻辑。
3. **触发器函数Trigger Function**：特殊函数，没有直接调用入口，被触发器触发执行；NEW代表更新/插入之后新元组，OLD代表更新/删除之前旧元组。
4. **触发器TRIGGER**：挂载在表或者视图之上，在INSERT/UPDATE/DELETE事件触发，调用触发器函数；分为BEFORE、AFTER、INSTEAD OF；行级触发器FOR EACH ROW，语句级触发器FOR EACH STATEMENT。
5. **游标CURSOR**：用于结果集逐行处理，适合超大结果集，避免一次性把全部数据加载内存；游标必须运行在事务块内部，事务结束游标自动关闭。

### 1.5 关系数据库三大范式与建模理论
三大范式是关系数据库逻辑建模基础，用于减少数据冗余、规避更新异常、插入异常、删除异常。
– **第一范式1NF**：列原子性，字段不可再拆分，一个单元格不能存储多组业务数据。
– **第二范式2NF**：满足1NF基础，消除部分函数依赖，非主键字段必须完全依赖全部主键，不能只依赖主键一部分。
– **第三范式3NF**：满足2NF基础，消除传递函数依赖，非主键字段不能依赖其他非主键字段，非主键全部直接依赖主键。

范式追求减少冗余，但业务系统会根据查询性能需求做适度反范式设计，增加少量冗余字段减少多表JOIN；建模过程需要权衡冗余和查询性能。建模输出包含实体、属性、实体之间关系（一对一、一对多、多对多，多对多必须引入中间关联表）。

### 1.6 逆向工程理论
数据模型逆向工程，即从已经存在数据库实例中，导出全部DDL定义，还原表、索引、约束、视图、函数、序列对象脚本；用于文档归档、环境比对、版本管理。PostgreSQL依靠pg_dump工具，使用`‑‑schema‑only`参数，仅导出对象定义不导出业务数据；可以将导出脚本在fgedu‑net‑cn2主机执行，复现完整数据模型，完成模型校验与比对。

> 风哥数据库教程 itpux‑com

## 二、实战环境准备
### 2.1主机规划
– **fgedu‑net‑cn1：开发测试数据库主机，64G内存8CPU，NVMe SSD磁盘；数据库实例根路径`/fgedudb/pgdata`，业务数据库名`fgedudb`，业务角色`fgedu`，端口5432**
– **fgedu‑net‑cn2：逆向工程、模型验证主机，硬件配置与fgedu‑net‑cn1完全一致**

### 2.2 基础环境确认
fgedu‑net‑cn1操作系统层面目录权限，操作系统postgres用户，目录路径`/fgedudb/pgdata`已经完成实例初始化。登录psql，确认业务库、业务账号已经就绪。
“`bash
su – postgres
psql -h 127.0.0.1 -p 5432 -d fgedudb -U fgedu
“`
本套风哥教程全部DDL实战均在业务库`fgedudb`内部，业务schema使用`biz`，如果schema不存在执行：
“`sql
CREATE SCHEMA IF NOT EXISTS biz OWNER fgedu;
SET search_path TO biz,public;
“`

> 上51CTO搜索风哥可以学习全套数据库教程

## 三、索引完整实战操作
### 3.1 B‑Tree普通索引、复合索引实战
创建业务测试表`t_order`，用于索引演示。
“`sql
CREATE TABLE biz.t_order (
order_id bigint GENERATED ALWAYS AS IDENTITY,
user_id bigint NOT NULL,
order_no varchar(64) NOT NULL,
create_time timestamp NOT NULL,
amount numeric(12,2) NOT NULL DEFAULT 0,
status smallint NOT NULL DEFAULT 0,
remark text
);

–单列B‑Tree索引
CREATE INDEX idx_t_order_userid ON biz.t_order(user_id);

–多列复合B‑Tree索引
CREATE INDEX idx_t_order_status_ctime ON biz.t_order(status,create_time);
“`

### 3.2 表达式索引、部分索引实战
表达式索引：基于函数、表达式结果建立索引，适合经常使用函数作为查询条件场景。
“`sql
–表达式索引，基于order_no大写转换
CREATE INDEX idx_t_order_upper_orderno ON biz.t_order(upper(order_no));
“`

部分索引（局部索引）：仅对满足where条件元组建立索引，缩小索引体积。业务中只对有效订单建立索引。
“`sql
CREATE INDEX idx_t_order_valid ON biz.t_order(user_id,create_time) WHERE status IN(1,2);
“`

### 3.3 GiST、GIN、BRIN索引实战
创建带jsonb、时间范围、时序字段的测试表`t_product`
“`sql
CREATE TABLE biz.t_product(
pid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
p_name text,
p_tags jsonb,
valid_period tsrange,
log_time timestamp
);

–GIN索引用于jsonb字段
CREATE INDEX idx_t_product_tags_gin ON biz.t_product USING GIN(p_tags);

–GiST索引用于时间范围字段，用于排它约束配套
CREATE INDEX idx_t_product_period_gist ON biz.t_product USING GIST(valid_period);

–BRIN时序块索引，log_time时序有序
CREATE INDEX idx_t_product_logtime_brin ON biz.t_product USING BRIN(log_time);
“`

### 3.4 索引信息查询元命令与系统视图
psql元命令查看索引：
“`sql
–查看表全部索引
\d biz.t_order
\di biz.*
“`

查询系统视图pg_indexes查看索引定义：
“`sql
SELECT schemaname,tablename,indexname,indexdef FROM pg_indexes WHERE schemaname=’biz’;
“`

### 3.5 索引修改、重建、删除实操
索引改名操作：
“`sql
ALTER INDEX biz.idx_t_order_userid RENAME TO idx_t_order_uid;
“`

业务表索引膨胀，执行REINDEX重建索引；REINDEX可以针对单个索引，也可以整张表全部索引重建。
“`sql
–重建单个索引
REINDEX INDEX biz.idx_t_order_status_ctime;
–重建整张表全部索引
REINDEX TABLE biz.t_order;
“`

删除索引：
“`sql
DROP INDEX IF EXISTS biz.idx_t_order_valid;
“`

> 网上搜索风哥教程可以学习全套数据库教程

## 四、各类约束完整实战操作
### 4.1 主键、唯一、非空约束实战
建表阶段定义约束：
“`sql
CREATE TABLE biz.t_customer(
cid bigint GENERATED ALWAYS AS IDENTITY,
phone varchar(20),
email varchar(64),
cname varchar(128) NOT NULL,
register_time timestamp NOT NULL,
CONSTRAINT pk_t_customer_cid PRIMARY KEY(cid),
CONSTRAINT uk_t_customer_phone UNIQUE(phone)
);
“`

后期ALTER追加约束，先建表后追加主键、唯一约束。
“`sql
CREATE TABLE biz.t_supplier(
sid bigint,
s_name varchar(128),
contact_phone varchar(20)
);

–追加主键
ALTER TABLE biz.t_supplier ADD CONSTRAINT pk_t_supplier_sid PRIMARY KEY(sid);

–追加唯一约束
ALTER TABLE biz.t_supplier ADD CONSTRAINT uk_t_supplier_phone UNIQUE(contact_phone);

–追加非空约束
ALTER TABLE biz.t_supplier ALTER COLUMN s_name SET NOT NULL;
“`

删除约束：
“`sql
ALTER TABLE biz.t_supplier DROP CONSTRAINT uk_t_supplier_phone;
ALTER TABLE biz.t_supplier ALTER COLUMN s_name DROP NOT NULL;
“`

### 4.2 CHECK检查约束实战
检查约束限制业务数值范围，订单金额必须大于等于0。
“`sql
ALTER TABLE biz.t_order ADD CONSTRAINT ck_t_order_amount CHECK(amount >= 0);
“`

测试check约束效果，执行非法数据会直接报错：
“`sql
INSERT INTO biz.t_order(user_id,order_no,create_time,amount,status)
VALUES(1001,’ORD20260916′,now(),-100,1);
“`

删除检查约束：
“`sql
ALTER TABLE biz.t_order DROP CONSTRAINT ck_t_order_amount;
“`

### 4.3 外键约束实战
业务场景：订单表t_order引用客户表t_customer主键cid作为user_id。
“`sql
ALTER TABLE biz.t_order ADD CONSTRAINT fk_order_customer
FOREIGN KEY(user_id) REFERENCES biz.t_customer(cid)
ON DELETE RESTRICT ON UPDATE CASCADE;
“`
参数说明：ON DELETE RESTRICT，客户存在关联订单时，禁止删除客户；ON UPDATE CASCADE，客户主键更新，订单user_id同步跟随更新。

测试外键效果：插入不存在客户cid的订单会被拒绝。
“`sql
INSERT INTO biz.t_order(user_id,order_no,create_time,amount,status)
VALUES(999999,’ORDTEST001′,now(),100,1);
“`

删除外键约束：
“`sql
ALTER TABLE biz.t_order DROP CONSTRAINT fk_order_customer;
“`

### 4.4 排它约束EXCLUDE实战
排它约束用于保障时间段不会重叠，同一个房间预定时间不能出现重叠。
“`sql
CREATE TABLE biz.t_room_book(
bid bigint GENERATED ALWAYS AS IDENTITY,
room_id bigint NOT NULL,
book_period tsrange NOT NULL,
CONSTRAINT ex_room_time EXCLUDE USING gist(room_id WITH =, book_period WITH &&)
);
“`
该约束保证同一个room_id，book_period时间区间不能互相重叠；插入时间重叠记录数据库直接拒绝写入。

### 4.5 NOT VALID约束大表友好追加
大表追加外键、check约束，使用NOT VALID避免锁表扫描存量数据，后续单独执行VALIDATE CONSTRAINT校验存量数据。
“`sql
ALTER TABLE biz.t_order ADD CONSTRAINT fk_order_customer FOREIGN KEY(user_id) REFERENCES biz.t_customer(cid) NOT VALID;
VALIDATE CONSTRAINT fk_order_customer;
“`

> 风哥 itpux‑com

## 五、视图与序列实战
### 5.1 普通视图创建、查询、修改、删除
基于订单表创建业务视图，只查询有效订单。
“`sql
CREATE VIEW biz.v_order_valid AS
SELECT order_id,user_id,order_no,create_time,amount,status
FROM biz.t_order WHERE status IN (1,2);
“`

psql查看视图元命令：
“`sql
\dv biz.v_order_valid
\d biz.v_order_valid
“`

修改视图定义：
“`sql
CREATE OR REPLACE VIEW biz.v_order_valid AS
SELECT order_id,user_id,order_no,create_time,amount,status,remark
FROM biz.t_order WHERE status IN (1,2,3);
“`

删除视图：
“`sql
DROP VIEW IF EXISTS biz.v_order_valid;
“`

### 5.2 序列SEQUENCE对象实战
独立序列对象，不绑定表，手动生成业务编号。
“`sql
CREATE SEQUENCE biz.seq_biz_no
INCREMENT BY 1
START WITH 100000
MINVALUE 100000
MAXVALUE 99999999
NO CYCLE
CACHE 20;
“`

调用序列获取下一个值：
“`sql
SELECT nextval(‘biz.seq_biz_no’);
SELECT currval(‘biz.seq_biz_no’);
SELECT lastval();
“`

修改序列属性：
“`sql
ALTER SEQUENCE biz.seq_biz_no INCREMENT BY 2 RESTART WITH 200000;
“`

删除序列：
“`sql
DROP SEQUENCE IF EXISTS biz.seq_biz_no;
“`

> 风哥教程 113257174

## 六、PL/pgSQL程序对象实战
### 6.1 自定义函数FUNCTION实战
编写函数，输入用户ID，返回该用户订单总金额，返回数值标量。
“`sql
CREATE OR REPLACE FUNCTION biz.fn_get_user_total_amount(p_uid bigint)
RETURNS numeric(14,2)
LANGUAGE plpgsql
AS $$
DECLARE
v_total numeric(14,2);
BEGIN
SELECT COALESCE(SUM(amount),0.00) INTO v_total
FROM biz.t_order WHERE user_id = p_uid;
RETURN v_total;
END;
$$;
“`

调用函数，在SELECT语句中直接使用：
“`sql
SELECT biz.fn_get_user_total_amount(1001);
SELECT user_id,biz.fn_get_user_total_amount(user_id) FROM biz.t_customer LIMIT 10;
“`

查看函数元命令：
“`sql
\df biz.fn_get_user_total_amount
“`

### 6.2 存储过程PROCEDURE实战
存储过程支持内部事务控制，编写批量更新订单状态存储过程。
“`sql
CREATE OR REPLACE PROCEDURE biz.proc_batch_order_status(p_old_status smallint,p_new_status smallint,p_limit integer)
LANGUAGE plpgsql
AS $$
DECLARE
v_cnt integer;
BEGIN
UPDATE biz.t_order SET status=p_new_status
WHERE status=p_old_status AND order_id IN (SELECT order_id FROM biz.t_order WHERE status=p_old_status LIMIT p_limit);
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ‘本次更新行数:%’,v_cnt;
COMMIT;
END;
$$;
“`

调用存储过程，使用CALL命令：
“`sql
CALL biz.proc_batch_order_status(0,1,500);
“`

### 6.3 游标CURSOR实战
游标用于逐行遍历大结果集，下面示例使用游标循环遍历客户表，打印客户cid与姓名。
“`sql
CREATE OR REPLACE FUNCTION biz.fn_cursor_customer_loop() RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
rec_cust RECORD;
cur_cust CURSOR FOR SELECT cid,cname,phone FROM biz.t_customer;
BEGIN
OPEN cur_cust;
LOOP
FETCH cur_cust INTO rec_cust;
EXIT WHEN NOT FOUND;
RAISE NOTICE ‘客户ID:%,姓名:%,手机号:%’,rec_cust.cid,rec_cust.cname,rec_cust.phone;
END LOOP;
CLOSE cur_cust;
END;
$$;
“`

游标必须运行事务块，调用执行：
“`sql
BEGIN;
SELECT biz.fn_cursor_customer_loop();
COMMIT;
“`
> 风哥数据库教程 itpux‑com

### 6.4 触发器函数与触发器完整实战
业务需求：订单更新的时候，把订单变更记录写入订单变更日志表。
第一步：创建日志记录表。
“`sql
CREATE TABLE biz.t_order_log(
log_id bigint GENERATED ALWAYS AS IDENTITY,
order_id bigint NOT NULL,
old_status smallint,
new_status smallint,
op_time timestamp DEFAULT now(),
op_type text
);
“`

第二步：编写触发器函数。
“`sql
CREATE OR REPLACE FUNCTION biz.trig_func_order_change() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP=’UPDATE’ THEN
INSERT INTO biz.t_order_log(order_id,old_status,new_status,op_type)
VALUES(NEW.order_id,OLD.status,NEW.status,’UPDATE’);
ELSIF TG_OP=’INSERT’ THEN
INSERT INTO biz.t_order_log(order_id,old_status,new_status,op_type)
VALUES(NEW.order_id,NULL,NEW.status,’INSERT’);
ELSIF TG_OP=’DELETE’ THEN
INSERT INTO biz.t_order_log(order_id,old_status,new_status,op_type)
VALUES(OLD.order_id,OLD.status,NULL,’DELETE’);
END IF;
RETURN NULL;
END;
$$;
“`

第三步：创建行级AFTER触发器挂载到t_order表。
“`sql
CREATE TRIGGER trig_t_order_change
AFTER INSERT OR UPDATE OR DELETE ON biz.t_order
FOR EACH ROW EXECUTE FUNCTION biz.trig_func_order_change();
“`

测试触发器效果：执行DML，查看日志表是否自动生成记录。
“`sql
UPDATE biz.t_order SET status=2 WHERE order_id=1;
SELECT * FROM biz.t_order_log;
“`

查看触发器元命令：
“`sql
\d biz.t_order
“`

删除触发器：
“`sql
DROP TRIGGER IF EXISTS trig_t_order_change ON biz.t_order;
DROP FUNCTION IF EXISTS biz.trig_func_order_change();
“`

> 上51CTO搜索风哥可以学习全套数据库教程

## 七、业务建模实战：基于三大范式构建简易电商业务库
### 7.1 业务需求说明
简易电商业务实体：客户、商品、订单、订单明细；
– 客户：cid，姓名，手机号，邮箱，注册时间（实体t_customer）
– 商品：pid，商品名称，价格，库存数量（实体t_product）
– 订单：order_id，客户cid，订单编号，下单时间，订单总金额，订单状态（实体t_order）
– 订单明细：order_item_id，关联order_id，关联pid，购买数量，单品成交单价（中间表t_order_item，订单与商品多对多）

按照三大范式，订单和商品是多对多，必须引入中间订单明细表，避免字段重复冗余。

完整建表DDL：
“`sql
–客户表
CREATE TABLE biz.t_customer(
cid bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_customer PRIMARY KEY,
cname varchar(128) NOT NULL,
phone varchar(20),
email varchar(64),
register_time timestamp NOT NULL DEFAULT now(),
CONSTRAINT uk_customer_phone UNIQUE(phone)
);

–商品表
CREATE TABLE biz.t_product(
pid bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_product PRIMARY KEY,
p_name varchar(256) NOT NULL,
price numeric(12,2) NOT NULL CHECK(price>=0),
stock integer NOT NULL DEFAULT 0 CHECK(stock >= 0)
);

–订单主表
CREATE TABLE biz.t_order(
order_id bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_order PRIMARY KEY,
cid bigint NOT NULL,
order_no varchar(64) NOT NULL UNIQUE,
create_time timestamp NOT NULL DEFAULT now(),
total_amount numeric(12,2) NOT NULL DEFAULT 0,
status smallint NOT NULL DEFAULT 0,
CONSTRAINT fk_order_cid FOREIGN KEY(cid) REFERENCES biz.t_customer(cid)
);

–订单明细中间表，多对多关系
CREATE TABLE biz.t_order_item(
item_id bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_orderitem PRIMARY KEY,
order_id bigint NOT NULL,
pid bigint NOT NULL,
buy_num integer NOT NULL CHECK(buy_num>0),
item_price numeric(12,2) NOT NULL,
CONSTRAINT fk_oi_order FOREIGN KEY(order_id) REFERENCES biz.t_order(order_id),
CONSTRAINT fk_oi_product FOREIGN KEY(pid) REFERENCES biz.t_product(pid)
);

–创建业务常用索引
CREATE INDEX idx_order_cid ON biz.t_order(cid);
CREATE INDEX idx_oi_orderid ON biz.t_order_item(order_id);
CREATE INDEX idx_oi_pid ON biz.t_order_item(pid);
“`

执行元命令查看整套模型：
“`sql
\dt biz.*
\d biz.t_order_item
“`

## 八、数据模型逆向工程实战
本实战在fgedu‑net‑cn1主机执行pg_dump导出仅schema定义，传输到fgedu‑net‑cn2主机复现数据模型，实现逆向工程，不需要导出业务数据。

### 8.1 fgedu‑net‑cn1导出模型DDL
“`bash
su – postgres
/fgedudb/pgdata/bin/pg_dump -h 127.0.0.1 -p 5432 -d fgedudb -U fgedu \
–schema‑only -n biz -f /fgedudb/export/model_biz_ddl.sql
“`
参数`–schema‑only`：只导出DDL对象定义，不导出表内任何数据；`‑n biz`仅导出biz模式。

### 8.2 将DDL脚本传输至fgedu‑net‑cn2主机
“`bash
scp /fgedudb/export/model_biz_ddl.sql fgedu‑net‑cn2:/fgedudb/import/
“`

### 8.3 fgedu‑net‑cn2执行脚本复现整套数据模型
在fgedu‑net‑cn2主机，提前创建数据库fgedudb与业务schema biz，之后执行导入。
“`bash
su – postgres
/fgedudb/pgdata/bin/psql -h 127.0.0.1 -p 5432 -d fgedudb -U fgedu -f /fgedudb/import/model_biz_ddl.sql
“`

完成导入后，在fgedu‑net‑cn2查看表、索引、约束、函数全部对象，完成模型逆向复现，用于文档归档、模型比对。

## 九、对象开发常见踩坑说明
1. 索引不是越多越好，DML频繁数据表，谨慎增加大量复合索引、表达式索引，会严重降低写入性能；定期监控索引膨胀，大表业务低峰期执行REINDEX。
2. 外键约束会带来写入开销，高吞吐写入业务需要评估；大表追加外键务必使用NOT VALID模式，避免长时间锁表。
3. PL/pgSQL函数内部不支持事务；存储过程PROCEDURE才允许COMMIT、ROLLBACK；游标必须包裹在事务块内部。
4. 触发器会加重DML开销，高并发写入业务，触发器逻辑尽量精简，避免触发器内部执行复杂查询。
5. SERIAL自增语法底层依赖独立序列对象，删除表不会自动删除序列，优先使用GENERATED ALWAYS AS IDENTITY标准语法。
6. 范式不是教条，业务可以适度反范式；反范式改动需要配套触发器或者业务代码维护冗余字段一致性。
7. 逆向工程导出DDL脚本，只可以作为模型参考，生产变更优先使用ALTER语句，禁止直接在生产执行整套导出的重建脚本。

> 网上搜索风哥教程可以学习全套数据库教程

## 风哥针对本文总结
风哥教程本文完整讲解PostgreSQL各类数据对象开发设计知识，理论部分覆盖索引类型与原理、六大类约束的数据完整性机制、视图与序列对象、PL/pgSQL函数、存储过程、游标、触发器核心概念，讲解关系数据库三大范式建模理论，以及数据库模型逆向工程原理。

实战部分基于`fgedu‑net‑cn1`开发主机，`fgedu‑net‑cn2`逆向验证主机，硬件规格64G内存8CPU，路径统一替换为`/fgedudb`，数据库、账号统一`fgedudb/fgedu`；完整演示B‑Tree、GIN、GiST、BRIN各类索引创建、查询、重建、删除；主键、唯一、非空、CHECK、外键、排它约束完整实操；视图与序列对象管理；PL/pgSQL自定义函数、存储过程、游标、触发器函数+触发器完整案例；基于三大范式完成简易电商业务库完整建模；使用pg_dump完成schema‑only逆向工程导出，异地主机复现数据模型。

风哥教程本文提醒，数据库对象设计核心目标是兼顾**数据完整性**与**业务查询性能**。约束用来在数据库层守住数据正确性边界；索引以写入开销换取查询速度；PL/pgSQL程序对象可以把业务逻辑下沉数据库内部，但同时会带来维护、调试成本，需要结合业务场景权衡是否使用；三大范式作为建模基础，业务可以合理反范式，但冗余数据必须有维护机制；定期导出DDL做逆向归档，保障数据库模型可追溯。

本套风哥教程全部DDL脚本，建议在隔离测试环境充分验证，测试通过之后再应用到生产环境。掌握本套教程内容之后，可以进一步学习分区表、高级索引优化、PL/pgSQL高级调试、业务数据库性能调优相关内容。