16_01PostgreSQL 介绍

发表于 2026-06-12 更新于 2026-06- 12

作者 Administrator

197~253 分钟 阅读

从 MySQL 到 PostgreSQL：面向 AI / RAG 开发的完整入门

适合已经知道 MySQL 基本增删改查，但没有系统使用过 PostgreSQL 的 Java 后端开发者。本文先建立 PostgreSQL 的完整基础，再讲 pgvector、Embedding 和 RAG 数据库设计。不要把 PostgreSQL 只理解成“为了向量检索而安装的数据库”。

[TOC]

1. 学完这篇文档要达到什么程度

读完并完成练习后，你应该能够：

理解 PostgreSQL 的 database、schema、table、role 分别是什么  
把常见 MySQL 建表语句改写成 PostgreSQL 语法  
使用 Docker 启动 PostgreSQL + pgvector使用 psql 或图形化工具连接数据库  
完成表、索引、约束、事务、账号和权限的基本操作  
在 Spring Boot 中连接 PostgreSQL理解 JSONB、UUID、timestamptz、identity 等常用类型和语法  
理解 pgvector 负责什么、Embedding 模型负责什么  
建立一套可用于 RAG 的文档、文本、chunk、任务和向量表  
写出带业务过滤条件的 topK 向量检索 SQL判断什么时候继续使用 pgvector，什么时候评估专业向量数据库

先给出全文最重要的结论：

PostgreSQL 是通用关系型数据库，不是 AI 模型，也不只是向量数据库。  
pgvector 是 PostgreSQL 扩展，为 PostgreSQL 增加向量类型和相似度检索能力。  
聊天模型和 Embedding 模型不是一回事。  
本项目聊天模型统一使用 qwen3.5-flash；向量维度必须根据单独选择的 Embedding 模型确定。  
第一版 RAG 使用 PostgreSQL + pgvector，通常比同时维护多个数据库更容易学习和排错。

2. 为什么 AI / RAG 项目经常使用 PostgreSQL

传统业务系统通常保存：

用户  
角色和权限  
订单  
商品  
配置  
操作日志

RAG 系统除了这些结构化数据，还需要保存：

知识库  
原始文档元数据  
文档解析后的全文  
清洗后的文本  
切片后的 chunk页码、标题路径等 metadataEmbedding 向量  
解析和向量化任务状态  
失败原因

这些数据并不全是向量。RAG 仍然有大量普通关系型数据和业务过滤条件。

例如用户提问时，系统通常不能直接从全部向量里搜索，而要先满足：

只能搜索当前租户的数据  
只能搜索当前知识库  
只能搜索用户有权限查看的文档  
只能搜索状态为 EMBEDDED 的文档  
可能只搜索某种语言、文件类型或时间范围

PostgreSQL 的优势在于可以在同一套系统中组合使用：

普通关系型表  
事务和外键  
SQL 条件过滤  
JSONB 半结构化数据  
全文检索  
pgvector 向量检索

因此，第一版 RAG 可以采用下面的简单结构：

graph LR;  
 A["原始文件"] --> B["文件系统 / MinIO / OSS"]; A --> C["PostgreSQL 文档元数据"];  
 C --> D["解析文本"];  
 D --> E["Chunk"]; E --> F["pgvector Embedding"]; G["用户问题"] --> H["Embedding 模型"];  
 H --> I["SQL 过滤 + 向量检索"];  
 F --> I; I --> J["TopK Chunk"]; J --> K["qwen3.5-flash 生成回答"];

每个组件的职责必须分清：

组件	主要职责
PostgreSQL	保存文档、chunk、状态、权限、metadata 等数据
pgvector	在 PostgreSQL 中保存向量并计算向量距离
Embedding 模型	把文本转换成固定维度的数字向量
qwen3.5-flash	根据问题和检索到的上下文生成答案
文件系统或对象存储	保存 PDF、Word 等原始文件

3. 先把 MySQL 和 PostgreSQL 的概念对应起来

3.1 二者相同的地方

MySQL 和 PostgreSQL 都是关系型数据库。你在 MySQL 中掌握的很多知识仍然有效：

表、行、列  
主键、唯一约束、外键  
INSERT、SELECT、UPDATE、DELETE  
JOIN、GROUP BY、ORDER BY、LIMIT  
事务、索引、执行计划  
JDBC、连接池、MyBatis、JPA

所以学习 PostgreSQL 不是从零学习数据库，而是调整一部分概念、类型和语法。

3.2 最大的概念差异：database 和 schema

MySQL 用户最容易在这里混淆。

MySQL 中通常把下面两个词近似看待：

CREATE DATABASE ai_rag;  
USE ai_rag;

在 MySQL 的日常使用中，database 和 schema 基本可以当作同义词。

PostgreSQL 的层级不同：

  
一个 PostgreSQL 服务实例（cluster）  
  ├── database A  
  │     ├── schema public  
  │     │     ├── table  
  │     │     └── view  
  │     └── schema rag  
  │           ├── table  
  │           └── index  
  └── database B  
        └── schema public

可以这样记：

MySQL 使用习惯	PostgreSQL 对应概念
一个 MySQL Server	一个 PostgreSQL 实例或 cluster
database/schema	database 下还包含 schema
`USE ai_rag`	连接到 `ai_rag` database
`ai_rag.user`	常见写法是 `app.user`，其中 `app` 是 schema

PostgreSQL 连接建立后，只能直接访问当前 database 中的对象。不能像 MySQL 那样随意写跨 database JOIN。

同一个 database 内可以创建多个 schema：

CREATE SCHEMA app;  
CREATE SCHEMA rag;  
CREATE SCHEMA audit;

然后创建同名表也不会冲突：

CREATE TABLE app.task (...);  
CREATE TABLE rag.task (...);

对于当前学习项目，可以采用：

database：ai_rag  
schema：rag  
table：rag.knowledge_base、rag.document、rag.chunk

3.3 public schema 是什么

新建 PostgreSQL database 后，通常已经存在 public schema。

如果直接执行：

CREATE TABLE demo_user (  
 id bigint PRIMARY KEY);

在默认配置下，它实际创建为：

public.demo_user

学习简单 SQL 时可以使用 public。正式项目更建议创建明确的业务 schema，例如 rag，避免所有对象都堆在一起。

3.4 search_path 是什么

search_path 决定省略 schema 时，PostgreSQL 按什么顺序寻找对象。

查看当前配置：

SHOW search_path;

设置当前会话优先使用 rag：

SET search_path TO rag, public;

设置后：

SELECT * FROM document;

会优先寻找：

rag.document

不过在初始化脚本、迁移脚本和关键 SQL 中，推荐写完整名称：

SELECT * FROM rag.document;

这样不容易因为环境中的 search_path 不同而查错表。

3.5 用户和 Role 的差异

PostgreSQL 使用 role 统一表示账号和权限角色。

带 LOGIN 属性的 role 可以登录：

CREATE ROLE ai_app LOGIN PASSWORD 'change_me';

不带 LOGIN 的 role 可以只作为权限集合：

CREATE ROLE rag_readonly;

再把权限角色授予登录账号：

GRANT rag_readonly TO report_user;

CREATE USER 在 PostgreSQL 中可以理解为 CREATE ROLE ... LOGIN 的便捷写法。

4. 使用 Docker 启动 PostgreSQL + pgvector

4.1 为什么建议初学者先用 Docker

Docker 可以把数据库版本、pgvector 扩展和本地数据隔离开，避免手动安装扩展时出现版本不匹配。

先检查：

docker --versiondocker compose version```  
  
### 4.2 创建 docker-compose.yml  
  
在学习项目目录创建 `docker-compose.yml`：  
  
```yaml  
services:  
 postgres: image: pgvector/pgvector:pg16 container_name: ai-rag-postgres restart: unless-stopped environment: POSTGRES_DB: ai_rag POSTGRES_USER: ai POSTGRES_PASSWORD: ai_password TZ: Asia/Shanghai ports: - "5432:5432" volumes: - ai_rag_pg_data:/var/lib/postgresql/data healthcheck: test: ["CMD-SHELL", "pg_isready -U ai -d ai_rag"] interval: 5s timeout: 5s retries: 10  
volumes:  
 ai_rag_pg_data:

说明：

配置	含义
`pgvector/pgvector:pg16`	已安装 pgvector 的 PostgreSQL 16 镜像
`POSTGRES_DB`	第一次初始化时创建的 database
`POSTGRES_USER`	第一次初始化时创建的管理员账号
`POSTGRES_PASSWORD`	账号密码，仅用于本地学习示例
`5432:5432`	把容器端口映射到本机
volume	保存数据库文件，重启容器不会丢失
healthcheck	检查数据库是否已经可以接受连接

版本说明：截至 2026 年 6 月，PostgreSQL 18 是当前主版本，PostgreSQL 16 仍处于官方支持期。本文继续使用 pg16，是为了和 day16 现有学习环境保持一致，并不表示 PostgreSQL 只有 16 可以使用。新项目可以选择团队验证过的受支持版本；升级镜像标签前，应同时确认 JDBC、pgvector、备份恢复和部署环境兼容性。

生产环境不要把真实密码直接提交到 Git。可以通过环境变量或密钥管理系统传入。

4.3 启动并检查

启动：

docker compose up -d```  
  
查看容器：  
  
```bash  
docker compose ps```  
  
查看日志：  
  
```bash  
docker compose logs -f postgres```  
  
看到数据库可以接受连接后，按 `Ctrl+C` 退出日志查看，不会停止容器。  
  
### 4.4 进入 psql  
  
直接进入容器中的命令行客户端：  
  
```bash  
docker exec -it ai-rag-postgres psql -U ai -d ai_rag```  
  
连接成功后会看到类似：  
  
```text  
ai_rag=#

退出：

\q

4.5 外部工具连接参数

DBeaver、DataGrip、IDEA Database 工具窗口都可以使用：

Host: 127.0.0.1  
Port: 5432  
Database: ai_rag  
User: ai  
Password: ai_password

JDBC URL：

jdbc:postgresql://127.0.0.1:5432/ai_rag

注意：PostgreSQL 连接参数中通常必须明确 database，不能只填服务地址。

4.6 停止和删除的区别

停止容器但保留数据：

docker compose down```  
  
重新启动后原数据仍在：  
  
```bash  
docker compose up -d```  
  
删除容器并删除 volume：  
  
```bash  
docker compose down -v```  
  
`-v` 会删除数据库数据。只有明确想重建学习环境时才使用。  
  
---  
  
## 5. psql 入门：不要把反斜杠命令当 SQL  
  
`psql` 中有两类命令：  
  
```text  
SQL：SELECT、CREATE TABLE、INSERT 等，通常以分号结束  
psql 元命令：以反斜杠开头，不需要分号

常用元命令：

命令	作用
`\l`	查看 database 列表
`\c ai_rag`	切换连接到指定 database
`\conninfo`	查看当前连接信息
`\dn`	查看 schema
`\dt`	查看当前 search_path 下的表
`\dt rag.*`	查看 rag schema 下的表
`\d rag.document`	查看表结构
`\di rag.*`	查看索引
`\du`	查看 role
`\dx`	查看已安装扩展
`\x`	切换扩展显示，查看宽表结果时很方便
`\timing`	显示 SQL 执行时间
`\i /path/file.sql`	执行 SQL 文件
`\q`	退出 psql

查看 PostgreSQL 版本：

SELECT version();

查看当前 database、用户和 schema：

SELECT current_database(), current_user, current_schema();

如果提示符从：

ai_rag=#

变成：

ai_rag-#

通常表示上一条 SQL 没有结束，例如漏了分号、引号或右括号。可以输入：

\r

清空当前未执行的输入缓冲区。

6. 从第一张表开始学习 PostgreSQL

6.1 创建 schema

CREATE SCHEMA IF NOT EXISTS app;

6.2 创建用户表

CREATE TABLE app.app_user (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username varchar(64) NOT NULL, email varchar(255), enabled boolean NOT NULL DEFAULT true, profile jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT app_user_username_uk UNIQUE (username), CONSTRAINT app_user_email_uk UNIQUE (email));

这张表包含几个 PostgreSQL 项目中常见的选择：

identity：自增主键  
boolean：真正的布尔类型  
jsonb：可查询和索引的 JSON 数据  
timestamptz：带时区语义的时间点  
constraint：显式命名约束，排错和迁移更清晰

6.3 插入并立即返回数据

INSERT INTO app.app_user (username, email, profile)  
VALUES (  
 'zhangsan', 'zhangsan@example.com', '{"language":"zh-CN","theme":"dark"}'::jsonb)  
RETURNING id, username, created_at;

RETURNING 非常实用，可以让 INSERT、UPDATE、DELETE 直接返回受影响的行。

MySQL 开发中经常依赖 JDBC 获取自增 ID；PostgreSQL 也支持 JDBC generated keys，但 SQL 原生的 RETURNING 更灵活。

6.4 查询

SELECT  
 id, username, email, enabled, profile ->> 'language' AS language, created_atFROM app.app_user  
WHERE enabled = true  
ORDER BY id DESC  
LIMIT 20 OFFSET 0;

JSONB 运算符：

  
->  返回 JSON/JSONB  
->> 返回文本

6.5 更新并返回结果

UPDATE app.app_user  
SET  
 profile = profile || '{"theme":"light"}'::jsonb, updated_at = now()WHERE username = 'zhangsan'  
RETURNING id, username, profile, updated_at;

|| 在这里用于合并 JSONB 对象。

6.6 删除并返回结果

DELETE FROM app.app_user  
WHERE username = 'zhangsan'  
RETURNING id, username;

6.7 Upsert

MySQL 常见：

INSERT INTO app_user (username, email)  
VALUES ('zhangsan', 'old@example.com')  
ON DUPLICATE KEY UPDATE email = VALUES(email);

PostgreSQL 使用 ON CONFLICT：

INSERT INTO app.app_user (username, email)  
VALUES ('zhangsan', 'new@example.com')  
ON CONFLICT (username)  
DO UPDATE SET  
 email = EXCLUDED.email, updated_at = now()RETURNING id, username, email;

EXCLUDED 表示本次原本准备插入的那一行。

7. MySQL 和 PostgreSQL 常用语法对照

7.1 快速对照表

需求	MySQL	PostgreSQL 推荐写法
自增主键	`AUTO_INCREMENT`	`GENERATED ... AS IDENTITY`
旧式自增	`AUTO_INCREMENT`	`serial` / `bigserial`，旧项目常见
布尔值	常用 `tinyint(1)`	`boolean`
大文本	`longtext`	`text`
二进制	`blob` / `longblob`	`bytea`
JSON	`json`	通常优先 `jsonb`
日期时间	`datetime`	`timestamp` 或 `timestamptz`
当前时间	`now()`	`now()`
空值替换	`ifnull(a, b)`	`coalesce(a, b)`
字符串连接	`concat(a, b)`	`concat(a, b)` 或 `a \|\| b`
分组拼接	`group_concat`	`string_agg`
日期格式化	`date_format`	`to_char`
Upsert	`ON DUPLICATE KEY UPDATE`	`ON CONFLICT ... DO UPDATE`
标识符引用	反引号 `name`	双引号 `"name"`
字符串	单引号 `'value'`	单引号 `'value'`
忽略大小写匹配	常受 collation 影响	`ILIKE`
返回新增数据	JDBC generated keys	SQL `RETURNING`
查看执行计划	`EXPLAIN`	`EXPLAIN` / `EXPLAIN ANALYZE`

7.2 自增主键

MySQL：

id bigint AUTO_INCREMENT PRIMARY KEY

PostgreSQL 推荐：

id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

两个 identity 模式：

GENERATED BY DEFAULT：允许显式插入 ID，数据迁移更方便  
GENERATED ALWAYS：默认禁止显式插入 ID，约束更严格

旧教程中经常看到：

id bigserial PRIMARY KEY

bigserial 仍可使用，但它本质上是整数列加 sequence 的语法糖。新表优先考虑 SQL 标准的 identity。

7.3 大小写和双引号

PostgreSQL 会把未加双引号的标识符折叠为小写。

下面三条通常都在寻找同一个小写表名：

SELECT * FROM APP_USER;  
SELECT * FROM App_User;  
SELECT * FROM app_user;

但如果创建时强行加双引号：

CREATE TABLE "AppUser" (  
 "UserId" bigint);

以后每次都必须精确引用：

SELECT "UserId" FROM "AppUser";

推荐规则：

表名和列名统一使用小写 snake_case不要创建需要双引号才能访问的大小写混合名称  
Java 属性继续使用 camelCase通过 ORM 或 MyBatis 映射二者

7.4 boolean

PostgreSQL 有真正的 boolean：

enabled boolean NOT NULL DEFAULT true

查询：

WHERE enabled = true

也可以简写为：

WHERE enabled

不要继续把新字段设计成 smallint 的 0 和 1，除非需要表达多种状态。

7.5 字符串和文本

PostgreSQL 常用：

varchar(n)：限制最大字符数  
text：不设置业务长度上限

在 PostgreSQL 中，text 不是低性能的“特殊大对象类型”。对于文档解析文本和 chunk 内容，直接使用 text 很合适。

7.6 日期和时间

常见类型：

类型	含义	常见用途
`date`	日期	生日、结算日期
`time`	一天中的时间	营业时间
`timestamp`	不带时区语义的日期时间	本地墙上时间
`timestamptz`	表示确定的时间点	`created_at`、`updated_at`

业务系统中的创建时间、更新时间通常推荐：

created_at timestamptz NOT NULL DEFAULT now()

timestamptz 内部表示确定的时间点，显示时会根据当前 session 时区转换。

查看当前时区：

SHOW timezone;

设置当前会话时区：

SET TIME ZONE 'Asia/Shanghai';

不要在数据库、JVM、JSON 序列化之间混用不明确的时间语义。Java 中保存时间点时优先考虑 Instant 或 OffsetDateTime。

7.7 无 unsigned 整数

PostgreSQL 常用整数类型没有 MySQL 风格的 UNSIGNED。

如果字段必须非负，可以增加检查约束：

file_size bigint NOT NULL CHECK (file_size >= 0)

7.8 group_concat 改写

MySQL：

SELECT department_id, GROUP_CONCAT(username ORDER BY username)  
FROM app_user  
GROUP BY department_id;

PostgreSQL：

SELECT  
 department_id, string_agg(username, ',' ORDER BY username)FROM app.app_user  
GROUP BY department_id;

7.9 模糊匹配

SELECT *  
FROM app.app_user  
WHERE username ILIKE '%ZHANG%';

LIKE 区分大小写，ILIKE 忽略大小写。具体语言排序和大小写规则还会受到 collation 影响。

7.10 DELETE、TRUNCATE 和 sequence

删除全部数据：

DELETE FROM app.app_user;

快速清空并重置 identity：

TRUNCATE TABLE app.app_user RESTART IDENTITY;

有外键依赖时不要随意使用 CASCADE：

TRUNCATE TABLE app.app_user RESTART IDENTITY CASCADE;

它可能同时清空关联表，学习时也要先确认影响范围。

8. PostgreSQL 常用数据类型

8.1 类型选择表

数据	推荐类型	说明
普通整数主键	`bigint identity`	单库业务表常用
分布式实体 ID	`uuid`	文档、chunk 等可使用
金额	`numeric(18,2)`	不要使用浮点数保存金额
普通短字符串	`varchar(n)`	有明确业务长度时使用
长文本	`text`	文档正文、chunk、错误堆栈
开关	`boolean`	true/false
创建时间	`timestamptz`	表示确定时间点
扩展属性	`jsonb`	可查询、可建立 GIN 索引
标签列表	`text[]`	简单数组场景可用
IP 地址	`inet`	比 varchar 更有语义
二进制	`bytea`	小型二进制数据
Embedding	`vector(n)`	来自 pgvector 扩展

8.2 UUID

RAG 文档和 chunk 常使用 UUID，原因包括：

不同服务可以独立生成 ID导入导出时较少发生主键冲突  
ID 不暴露简单递增规模

启用扩展：

CREATE EXTENSION IF NOT EXISTS pgcrypto;

创建默认 UUID：

CREATE TABLE app.demo_uuid (  
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL);

插入时可以不传 ID：

INSERT INTO app.demo_uuid (name)  
VALUES ('demo')  
RETURNING id;

8.3 JSON 和 JSONB

二者都能保存 JSON，但日常业务更常使用 jsonb：

json：更接近保存原始 JSON 文本表示  
jsonb：使用二进制结构存储，便于查询和索引

示例：

CREATE TABLE app.event_log (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, event_type varchar(64) NOT NULL, payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now());

插入：

INSERT INTO app.event_log (event_type, payload)  
VALUES (  
 'DOCUMENT_UPLOADED', '{"documentId":"d-001","source":"web","pages":12}'::jsonb);

查询 JSONB 字段：

SELECT *  
FROM app.event_log  
WHERE payload ->> 'source' = 'web';

包含查询：

SELECT *  
FROM app.event_log  
WHERE payload @> '{"source":"web"}'::jsonb;

建立 GIN 索引：

CREATE INDEX event_log_payload_gin_idx  
ON app.event_log  
USING gin (payload);

JSONB 的使用边界：

经常 JOIN、排序、唯一约束、范围查询的内容：单独建列  
结构不稳定、不同来源差异较大的补充信息：放 JSONB不要为了少建字段，把全部业务数据塞进一个 JSONB

8.4 数组

PostgreSQL 支持数组：

CREATE TABLE app.article (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title text NOT NULL, tags text[] NOT NULL DEFAULT '{}');

插入：

INSERT INTO app.article (title, tags)  
VALUES ('PostgreSQL 入门', ARRAY['database', 'postgresql']);

查询包含某标签：

SELECT *  
FROM app.article  
WHERE tags @> ARRAY['postgresql'];

数组适合简单标签等场景。如果标签本身有名称、状态、权限和统计需求，仍应拆成关联表。

9. 约束、外键和级联删除

9.1 约束不是多余检查

应用代码会出现 bug，也可能有脚本或其他服务直接写数据库。数据库约束是最后一道数据完整性保护。

常见约束：

PRIMARY KEY  
NOT NULL  
UNIQUE  
FOREIGN KEY  
CHECK  
EXCLUDE

9.2 外键示例

CREATE TABLE app.department (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name varchar(128) NOT NULL UNIQUE);  
  
CREATE TABLE app.employee (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, department_id bigint NOT NULL, name varchar(128) NOT NULL, CONSTRAINT employee_department_fk FOREIGN KEY (department_id) REFERENCES app.department(id) ON DELETE RESTRICT);

常见删除策略：

策略	含义
`RESTRICT` / `NO ACTION`	有子记录时拒绝删除父记录
`CASCADE`	删除父记录时自动删除子记录
`SET NULL`	删除父记录后把子表外键设为 null

RAG 文档和 chunk 通常是强从属关系：

document_id uuid NOT NULL  
 REFERENCES rag.document(id) ON DELETE CASCADE

删除文档时自动删除其文本和 chunk 比较合理。但知识库、用户、权限等业务关系是否级联删除，需要谨慎设计。

9.3 CHECK 约束

file_size bigint NOT NULL CHECK (file_size >= 0)

状态字段也可以限制允许值：

status varchar(32) NOT NULL  
 CHECK (status IN ('UPLOADED', 'PARSED', 'CHUNKED', 'EMBEDDED', 'FAILED'))

对于经常变化的状态集合，也可以由应用层和字典表管理。不要为了使用 PostgreSQL 特性而过度使用数据库 enum，因为修改 enum 的迁移管理需要额外考虑。

10. 索引：不是越多越好

10.1 B-tree

默认索引通常是 B-tree，适合：

=、<、<=、>、>=  
排序  
唯一约束  
范围查询

CREATE INDEX app_user_created_at_idx  
ON app.app_user (created_at DESC);

10.2 联合索引

如果查询经常是：

WHERE tenant_id = ?  
 AND status = ?ORDER BY created_at DESC

可以评估：

CREATE INDEX document_tenant_status_created_idx  
ON rag.document (tenant_id, status, created_at DESC);

联合索引列顺序必须根据真实查询设计，不要机械地把所有过滤字段都放进去。

10.3 部分索引

只为满足条件的数据建立索引：

CREATE INDEX document_failed_idx  
ON rag.document (updated_at)  
WHERE status = 'FAILED';

如果失败数据只占很小比例，部分索引可能比全表索引更小、更有效。

10.4 表达式索引

CREATE INDEX app_user_lower_email_idx  
ON app.app_user (lower(email));

对应查询应使用相同表达式：

SELECT *  
FROM app.app_user  
WHERE lower(email) = lower('ZHANGSAN@EXAMPLE.COM');

10.5 查看执行计划

只看计划，不执行：

EXPLAIN  
SELECT *  
FROM app.app_user  
WHERE username = 'zhangsan';

实际执行并统计：

EXPLAIN (ANALYZE, BUFFERS)  
SELECT *  
FROM app.app_user  
WHERE username = 'zhangsan';

EXPLAIN ANALYZE 会真的执行 SQL。对生产环境的更新、删除或重查询使用前必须谨慎。

10.6 为什么索引不是越多越好

每个索引都会带来：

额外磁盘空间  
INSERT、UPDATE、DELETE 的维护成本  
VACUUM 和备份成本  
查询优化器更多选择

先根据真实 SQL 和执行计划建索引，不要给每一列都建索引。

11. 事务、MVCC 和并发基础

11.1 基本事务

BEGIN;  
  
UPDATE app.account  
SET balance = balance - 100  
WHERE id = 1;  
  
UPDATE app.account  
SET balance = balance + 100  
WHERE id = 2;  
  
COMMIT;

发生错误时：

ROLLBACK;

11.2 PostgreSQL 事务错误后的状态

在事务中某条 SQL 失败后，当前事务会进入失败状态。后续 SQL 通常会提示：

current transaction is aborted

此时需要：

ROLLBACK;

然后重新开始事务。

11.3 MVCC 的直观理解

PostgreSQL 使用 MVCC 管理并发。可以先简单理解为：

更新数据时不是直接让所有读操作停下来  
不同事务可能看到符合自身快照的数据版本  
旧版本最终由 VACUUM 清理

这也是 PostgreSQL 日常维护中会遇到 VACUUM、死元组和表膨胀等概念的原因。

普通项目先使用自动清理 autovacuum，不要随意关闭。

11.4 隔离级别

PostgreSQL 支持：

READ COMMITTED  
REPEATABLE READ  
SERIALIZABLE

默认是 READ COMMITTED。它与 MySQL InnoDB 常见默认配置的理解不同，迁移并发敏感逻辑时不能只看名字，要重新验证实际行为。

11.5 sequence 不保证连续

identity 通常依赖 sequence。即使事务回滚，已经获取的序列值也可能不会退回。

所以自增 ID 可能出现空洞：

1, 2, 5, 6

这不是数据丢失。主键用于唯一标识，不应该承担“绝对连续编号”的业务含义。

12. Role、账号和权限

12.1 不要让应用一直使用超级用户

本地学习时使用初始化账号很方便，但正式项目应创建权限受限的应用账号。

CREATE ROLE ai_app LOGIN PASSWORD 'replace_with_strong_password';

允许连接 database：

GRANT CONNECT ON DATABASE ai_rag TO ai_app;

允许使用 schema：

GRANT USAGE ON SCHEMA rag TO ai_app;

授予现有表权限：

GRANT SELECT, INSERT, UPDATE, DELETE  
ON ALL TABLES IN SCHEMA rag  
TO ai_app;

如果使用 identity/sequence，还需要 sequence 权限：

GRANT USAGE, SELECT  
ON ALL SEQUENCES IN SCHEMA rag  
TO ai_app;

12.2 默认权限

上面的 GRANT ON ALL TABLES 只覆盖当前已经存在的表。为了让将来由某个 owner 创建的新表也自动授权，需要设置默认权限。

以下语句必须以实际对象创建者的身份执行，或明确指定 owner：

ALTER DEFAULT PRIVILEGES IN SCHEMA rag  
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ai_app;  
  
ALTER DEFAULT PRIVILEGES IN SCHEMA rag  
GRANT USAGE, SELECT ON SEQUENCES TO ai_app;

默认权限属于“以后由哪个 role 创建对象”的规则，不是全局规则。这一点非常容易踩坑。

12.3 只读账号

CREATE ROLE rag_readonly;  
  
GRANT CONNECT ON DATABASE ai_rag TO rag_readonly;  
GRANT USAGE ON SCHEMA rag TO rag_readonly;  
GRANT SELECT ON ALL TABLES IN SCHEMA rag TO rag_readonly;

创建可登录用户并继承只读角色：

CREATE ROLE report_user LOGIN PASSWORD 'replace_me';  
GRANT rag_readonly TO report_user;

13. Spring Boot 连接 PostgreSQL

13.1 JDBC 驱动

Maven：

<dependency>  
 <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope></dependency>

如果使用 Spring JDBC：

<dependency>  
 <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency>

如果使用 JPA：

<dependency>  
 <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId></dependency>

13.2 application.yml

spring:  
 datasource: url: jdbc:postgresql://127.0.0.1:5432/ai_rag?currentSchema=rag username: ai_app password: ${DB_PASSWORD} driver-class-name: org.postgresql.Driver hikari: maximum-pool-size: 10 minimum-idle: 2 connection-timeout: 30000  
 jackson: time-zone: Asia/Shanghai

currentSchema=rag 可以让连接默认优先使用该 schema，但迁移脚本和重要 SQL 仍建议显式写 schema。

密码通过环境变量传入：

$env:DB_PASSWORD="replace_me"

13.3 MyBatis 下划线映射

mybatis:  
 configuration: map-underscore-to-camel-case: true

数据库字段：

document_id  
chunk_index  
created_at

Java 属性：

documentId  
chunkIndex  
createdAt

13.4 Java 类型建议

PostgreSQL	Java 常用类型
`bigint`	`Long`
`uuid`	`java.util.UUID`
`boolean`	`Boolean` / `boolean`
`text` / `varchar`	`String`
`numeric`	`BigDecimal`
`date`	`LocalDate`
`timestamptz`	`OffsetDateTime` 或 `Instant`
`jsonb`	JSON 字符串、`JsonNode` 或自定义类型处理器
`vector`	`float[]`、`List<Float>` 或框架提供的向量类型

13.5 Flyway 初始化建议

依赖：

<dependency>  
 <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId></dependency>  
  
<dependency>  
 <groupId>org.flywaydb</groupId> <artifactId>flyway-database-postgresql</artifactId></dependency>

现代 Flyway 把 PostgreSQL 支持放在单独的 flyway-database-postgresql 模块中。实际版本通常交给 Spring Boot dependency management 管理，不要随意让两个 Flyway 模块使用不同版本。

迁移文件：

  
src/main/resources/db/migration  
├── V1__create_extensions_and_schema.sql  
├── V2__create_rag_tables.sql  
└── V3__create_rag_indexes.sql

数据库结构应由版本化迁移脚本管理，不建议依赖应用启动时自动猜测并修改生产表结构。

14. 从 MySQL 迁移到 PostgreSQL 时的常见问题

14.1 不能只替换 JDBC URL

需要检查：

建表类型  
自增主键  
布尔字段  
时间字段和时区  
反引号  
函数  
Upsert  
分页 SQLJSON 查询  
排序规则和大小写  
事务隔离级别  
保留关键字

14.2 不要照搬 MySQL DDL

典型 MySQL DDL：

CREATE TABLE user_info (  
 id bigint unsigned NOT NULL AUTO_INCREMENT, enabled tinyint(1) NOT NULL DEFAULT 1, description longtext, created_at datetime NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PostgreSQL 改写：

CREATE TABLE app.user_info (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, enabled boolean NOT NULL DEFAULT true, description text, created_at timestamptz NOT NULL DEFAULT now());

PostgreSQL 不需要 ENGINE=InnoDB 和 DEFAULT CHARSET=utf8mb4 这种表级语句。

14.3 零日期

MySQL 历史数据中可能存在：

0000-00-00  
0000-00-00 00:00:00

PostgreSQL 不接受这种无效日期。迁移前应把它转换成 NULL 或明确的业务值。

14.4 大小写和排序规则

MySQL 某些默认 collation 下，字符串比较可能天然忽略大小写。PostgreSQL 中不要假设相同行为。

需要明确决定：

是否使用 ILIKE是否建立 lower(column) 表达式索引  
是否使用 citext 扩展  
排序和唯一性是否区分大小写

14.5 数据迁移工具

小数据量可以导出 CSV 后使用 COPY。

导入示例：

COPY app.app_user (username, email, enabled)  
FROM '/data/app_user.csv'  
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

服务端 COPY 读取的是数据库服务器或容器内路径。psql 客户端本地文件可以使用 \copy：

\copy app.app_user(username, email, enabled) FROM 'D:/data/app_user.csv' WITH (FORMAT csv, HEADER true)

真实迁移还可以评估 pgloader、ETL 工具或应用级迁移程序，并在迁移后校验：

总行数  
主键最大值  
空值数量  
金额汇总  
时间范围  
唯一约束  
外键完整性

15. pgvector 到底是什么

15.1 pgvector 不是 Embedding 模型

pgvector 只负责：

提供 vector 数据类型  
保存向量  
计算向量距离  
提供精确和近似向量检索  
提供 HNSW、IVFFlat 等索引能力

它不会：

读取 PDF理解文本  
把文本变成向量  
调用大模型  
自动完成 RAG

文本向量来自 Embedding 模型：

  
"PostgreSQL 支持 JSONB"  
    -> Embedding API  
    -> [0.012, -0.087, 0.031, ...]

项目中的聊天请求统一使用 qwen3.5-flash，但聊天模型名不能直接决定 vector(n) 的维度。必须根据实际使用的 Embedding 模型文档或 API 返回结果确定维度。

15.2 启用 vector 扩展

每个需要使用 vector 的 database 都要执行：

CREATE EXTENSION IF NOT EXISTS vector;

检查：

SELECT extname, extversion  
FROM pg_extension  
WHERE extname = 'vector';

或在 psql 中：

\dx

15.3 最小向量示例

先用三维向量理解，不涉及真实 Embedding 模型：

CREATE SCHEMA IF NOT EXISTS vector_demo;  
  
CREATE TABLE vector_demo.item (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, content text NOT NULL, embedding vector(3) NOT NULL);

插入：

INSERT INTO vector_demo.item (content, embedding)  
VALUES  
 ('苹果手机', '[0.10, 0.20, 0.30]'),  
 ('安卓手机', '[0.10, 0.20, 0.40]'),  
 ('数据库教程', '[0.80, 0.10, 0.20]');

查询与 [0.10, 0.20, 0.35] 最接近的两条：

SELECT  
 id, content, embedding <=> '[0.10, 0.20, 0.35]' AS cosine_distanceFROM vector_demo.item  
ORDER BY embedding <=> '[0.10, 0.20, 0.35]'  
LIMIT 2;

15.4 常见距离运算符

运算符	含义	越小越相似
`<->`	L2 欧氏距离	是
`<#>`	负内积	是，返回的是负内积
`<=>`	Cosine distance	是
`<+>`	L1 distance	是

RAG 文本检索常见 cosine distance：

ORDER BY embedding <=> :query_embedding

如果想展示 cosine similarity，可以换算：

SELECT  
 1 - (embedding <=> :query_embedding) AS cosine_similarityFROM vector_demo.item;

注意：排序检索时更建议直接按距离运算符排序，这样查询优化器更容易使用对应向量索引。

15.5 维度必须一致

如果列定义为：

embedding vector(1024)

就只能保存 1024 维向量。传入 1536 维或其他维度会失败。

确定维度的正确步骤：

1. 选择 Embedding 模型  
2. 查看模型输出维度配置  
3. 实际调用一次并验证数组长度  
4. 建立 vector(n) 字段  
5. 记录 embedding_model 和 embedding_dimension

更换 Embedding 模型时，即使维度碰巧相同，也通常要重新生成全部向量。不同模型产生的向量不在同一个语义空间中，不能直接混合比较。

还要区分“字段能够保存”和“近似索引能够索引”。当前 pgvector 的 HNSW / IVFFlat 对普通 vector 索引最多支持 2000 维，halfvec 索引最多支持 4000 维。若模型维度更高，需要评估 half precision、降维、子向量索引或其他检索方案，不能只把 vector(n) 中的数字改大就结束。

16. 一套完整、可扩展的 RAG 数据模型

下面使用 vector(1024) 作为教学示例。执行前必须确认你的 Embedding 模型是否输出 1024 维；不一致就统一修改字段和索引设计。

16.1 初始化扩展和 schema

CREATE EXTENSION IF NOT EXISTS pgcrypto;  
CREATE EXTENSION IF NOT EXISTS vector;  
  
CREATE SCHEMA IF NOT EXISTS rag;

16.2 知识库表

CREATE TABLE rag.knowledge_base (  
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id varchar(64) NOT NULL, name varchar(255) NOT NULL, description text, embedding_model varchar(128) NOT NULL, embedding_dimension integer NOT NULL CHECK (embedding_dimension > 0), enabled boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT knowledge_base_tenant_name_uk UNIQUE (tenant_id, name));

为什么记录 Embedding 模型和维度：

防止同一知识库混入不同模型的向量  
便于排查向量维度错误  
更换模型时知道哪些知识库需要重建向量

数据库列 vector(1024) 仍是固定的，所以第一版建议整个服务统一使用一种向量维度。如果未来要同时支持多维度模型，可以拆表、分 schema 或使用不同向量集合，不要在同一索引列中随意混用。

16.3 文档表

CREATE TABLE rag.document (  
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), knowledge_base_id uuid NOT NULL, original_name varchar(512) NOT NULL, storage_path varchar(1024) NOT NULL, content_type varchar(128), file_size bigint NOT NULL CHECK (file_size >= 0), sha256 char(64), status varchar(32) NOT NULL DEFAULT 'UPLOADED', error_message text, version integer NOT NULL DEFAULT 1 CHECK (version > 0), metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT document_knowledge_base_fk FOREIGN KEY (knowledge_base_id) REFERENCES rag.knowledge_base(id) ON DELETE CASCADE, CONSTRAINT document_status_ck CHECK (status IN ( 'UPLOADED', 'PARSING', 'PARSED', 'CLEANED', 'CHUNKED', 'EMBEDDING', 'EMBEDDED', 'FAILED' )));

文档表保存文件级元数据，不建议把 PDF 或 Word 的完整二进制直接塞进该表。第一版可以把原文件放本地文件系统，正式环境通常放 MinIO、OSS 或 S3。

16.4 解析文本表

CREATE TABLE rag.document_text (  
 document_id uuid PRIMARY KEY, raw_text text NOT NULL, cleaned_text text, parser_name varchar(128), parser_version varchar(64), metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT document_text_document_fk FOREIGN KEY (document_id) REFERENCES rag.document(id) ON DELETE CASCADE);

保留 raw_text 和 cleaned_text 很重要。回答效果不好时，可以逐层检查：

原文件是否正确解析  
清洗是否误删正文  
切片是否破坏语义  
Embedding 是否成功  
检索是否找到正确片段  
Prompt 是否正确引用片段

16.5 Chunk 表

CREATE TABLE rag.chunk (  
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), document_id uuid NOT NULL, chunk_index integer NOT NULL CHECK (chunk_index >= 0), content text NOT NULL, title_path text, page_start integer, page_end integer, char_start integer, char_end integer, token_count integer CHECK (token_count IS NULL OR token_count >= 0), metadata jsonb NOT NULL DEFAULT '{}'::jsonb, embedding vector(1024), embedding_model varchar(128), embedded_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT chunk_document_fk FOREIGN KEY (document_id) REFERENCES rag.document(id) ON DELETE CASCADE, CONSTRAINT chunk_document_index_uk UNIQUE (document_id, chunk_index), CONSTRAINT chunk_page_range_ck CHECK ( page_start IS NULL OR page_end IS NULL OR page_end >= page_start ), CONSTRAINT chunk_char_range_ck CHECK ( char_start IS NULL OR char_end IS NULL OR char_end >= char_start ));

字段说明：

字段	含义
`chunk_index`	片段在当前文档中的顺序，从 0 或 1 开始必须统一
`content`	实际参与 Embedding 和上下文拼接的文本
`title_path`	如“用户手册 > 文件上传 > 大小限制”
`page_start/page_end`	来源页码，用于答案引用
`char_start/char_end`	在清洗后全文中的字符位置
`token_count`	估算 token 数，用于控制上下文长度
`metadata`	parser、语言、页标签等扩展信息
`embedding`	Embedding 模型返回的向量
`embedding_model`	生成该向量的模型名
`embedded_at`	向量生成时间

16.6 入库任务表

CREATE TABLE rag.ingest_task (  
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), document_id uuid NOT NULL, task_type varchar(32) NOT NULL, status varchar(32) NOT NULL DEFAULT 'PENDING', attempt_count integer NOT NULL DEFAULT 0 CHECK (attempt_count >= 0), max_attempts integer NOT NULL DEFAULT 3 CHECK (max_attempts > 0), progress integer NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100), error_message text, started_at timestamptz, finished_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT ingest_task_document_fk FOREIGN KEY (document_id) REFERENCES rag.document(id) ON DELETE CASCADE, CONSTRAINT ingest_task_type_ck CHECK (task_type IN ('PARSE', 'CLEAN', 'CHUNK', 'EMBED')), CONSTRAINT ingest_task_status_ck CHECK (status IN ('PENDING', 'RUNNING', 'SUCCESS', 'FAILED', 'CANCELLED')));·

任务表让系统能够支持：

异步处理  
进度查询  
失败重试  
错误追踪  
防止重复执行  
统计每个阶段耗时

第一版同步执行也可以，但建议至少保存文档状态和错误信息。

17. RAG 表的普通索引设计

17.1 文档查询索引

CREATE INDEX document_kb_status_created_idx  
ON rag.document (knowledge_base_id, status, created_at DESC);

17.2 文件 hash 去重索引

如果同一知识库内不允许重复文件：

CREATE UNIQUE INDEX document_kb_sha256_uk  
ON rag.document (knowledge_base_id, sha256)  
WHERE sha256 IS NOT NULL;

这是部分唯一索引：只有 sha256 非空的数据参与唯一性检查。

17.3 Chunk 外键查询索引

PostgreSQL 不会因为创建外键就自动为外键列建立查询索引。经常按 document_id 查询 chunk 时应显式建立：

CREATE INDEX chunk_document_id_idx  
ON rag.chunk (document_id, chunk_index);

17.4 Metadata GIN 索引

只有确认经常按 metadata 查询时再建立：

CREATE INDEX chunk_metadata_gin_idx  
ON rag.chunk  
USING gin (metadata);

17.5 待执行任务索引

CREATE INDEX ingest_task_pending_idx  
ON rag.ingest_task (created_at)  
WHERE status = 'PENDING';

后台 worker 查待处理任务时可以利用这个较小的部分索引。

18. 文档入库的数据操作顺序

完整入库链路：

graph TD;  
 A["上传文件"] --> B["插入 document"]; B --> C["保存原文件"];  
 C --> D["解析 raw_text"]; D --> E["生成 cleaned_text"]; E --> F["切分 chunk"]; F --> G["调用 Embedding 模型"];  
 G --> H["更新 chunk.embedding"]; H --> I["document.status = EMBEDDED"];

18.1 创建知识库

INSERT INTO rag.knowledge_base (  
 tenant_id, name, description, embedding_model, embedding_dimension)  
VALUES (  
 'tenant-demo', 'AI Agent 学习资料',  
 '保存 AI Agent 学习文档',  
 'your-embedding-model', 1024)  
RETURNING id;

18.2 创建文档记录

INSERT INTO rag.document (  
 knowledge_base_id, original_name, storage_path, content_type, file_size, sha256)  
VALUES (  
 :knowledge_base_id, 'postgresql-guide.pdf', '/data/uploads/postgresql-guide.pdf', 'application/pdf', 204800, :sha256)  
RETURNING id, status, created_at;

:knowledge_base_id 和 :sha256 是参数占位表示，不是可以直接在 psql 中运行的字面值。实际项目通过 JDBC、MyBatis 或框架绑定参数。

18.3 保存解析文本

INSERT INTO rag.document_text (  
 document_id, raw_text, cleaned_text, parser_name, parser_version)  
VALUES (  
 :document_id, :raw_text, :cleaned_text, 'pdf-parser', '1.0.0')  
ON CONFLICT (document_id)  
DO UPDATE SET  
 raw_text = EXCLUDED.raw_text, cleaned_text = EXCLUDED.cleaned_text, parser_name = EXCLUDED.parser_name, parser_version = EXCLUDED.parser_version, updated_at = now();

18.4 批量插入 Chunk

真实项目应使用 JDBC batch、MyBatis batch 或 COPY，不要逐条远程调用数据库。

示意：

INSERT INTO rag.chunk (  
 document_id, chunk_index, content, title_path, page_start, page_end, token_count, metadata)  
VALUES  
 (:document_id, 0, :content_0, :title_0, 1, 1, 420, :metadata_0), (:document_id, 1, :content_1, :title_1, 1, 2, 390, :metadata_1);

18.5 更新 Embedding

UPDATE rag.chunk  
SET  
 embedding = :embedding, embedding_model = :embedding_model, embedded_at = now(), updated_at = now()WHERE id = :chunk_id;

所有 chunk 完成后更新文档：

UPDATE rag.document  
SET  
 status = 'EMBEDDED', error_message = NULL, updated_at = now()WHERE id = :document_id;

18.6 是否把整个流程放在一个事务中

不建议让“解析 PDF + 调用 Embedding API + 写数据库”一直占用一个长事务。

更合理的是按阶段提交：

上传完成：提交 document解析完成：提交 document_text 和状态  
切片完成：批量提交 chunk 和状态  
向量化完成：批量更新 embedding 和最终状态

每个阶段通过状态和任务表保证可重试、可追踪。外部 API 调用期间不要长时间占用数据库事务和连接。

19. 从问题到答案的向量检索

19.1 检索流程

  
用户问题  
  -> 使用与文档相同的 Embedding 模型生成 query vector  
  -> 按租户、知识库、权限和状态过滤  
  -> 按向量距离排序  
  -> 取 topK chunk  
  -> 拼接上下文和来源  
  -> 调用 qwen3.5-flash  
  -> 返回答案和引用来源

文档向量和问题向量必须来自相同 Embedding 模型配置。

19.2 最小 topK SQL

SELECT  
 c.id, c.document_id, c.content, c.title_path, c.page_start, c.page_end, c.metadata, c.embedding <=> :query_embedding AS distanceFROM rag.chunk c  
WHERE c.embedding IS NOT NULL  
ORDER BY c.embedding <=> :query_embedding  
LIMIT 5;

19.3 带知识库和文档状态过滤

SELECT  
 c.id, c.document_id, d.original_name, c.content, c.title_path, c.page_start, c.page_end, c.embedding <=> :query_embedding AS distanceFROM rag.chunk c  
JOIN rag.document d  
 ON d.id = c.document_idJOIN rag.knowledge_base kb  
 ON kb.id = d.knowledge_base_idWHERE kb.tenant_id = :tenant_id  
 AND kb.id = :knowledge_base_id AND kb.enabled = true AND d.status = 'EMBEDDED' AND c.embedding IS NOT NULLORDER BY c.embedding <=> :query_embedding  
LIMIT :top_k;

这段 SQL 展示了 pgvector 对 RAG 的核心价值：

普通 SQL JOIN 和权限过滤  
加上向量相似度排序  
在同一条查询中完成

19.4 是否设置相似度阈值

可以过滤距离过大的结果：

AND c.embedding <=> :query_embedding < :max_distance

但阈值不能照抄固定值。它受到以下因素影响：

Embedding 模型  
距离度量  
文档领域  
chunk 长度  
问题类型  
是否经过 rerank

应通过真实评测集确定阈值。没有评测数据时，先返回 topK 并记录距离分布，再逐步调优。

19.5 参数绑定

不要把向量字符串直接拼进 SQL，应该使用参数绑定，避免 SQL 注入和格式错误。

应用层常见流程：

  
List<Float> queryEmbedding  
  -> PostgreSQL JDBC / Spring AI VectorStore  
  -> 参数化查询

如果使用 Spring AI 的 PGvector Vector Store，可以由框架处理一部分建表、向量写入和查询逻辑；但仍应理解底层 schema、维度、索引和过滤条件。

20. 精确检索、HNSW 和 IVFFlat

20.1 不建向量索引也能查询

下面的 SQL 可以进行精确检索：

ORDER BY embedding <=> :query_embedding  
LIMIT 5

数据量小时，先不建向量索引最容易验证正确性。精确检索会计算更多数据，结果召回准确，但数据量大后速度会下降。

20.2 HNSW

Cosine distance 的 HNSW 索引：

CREATE INDEX chunk_embedding_hnsw_idx  
ON rag.chunk  
USING hnsw (embedding vector_cosine_ops)  
WHERE embedding IS NOT NULL;

特点可以先理解为：

近似最近邻检索  
通常查询性能和召回率较好  
构建索引需要更多时间和内存  
索引本身占用额外空间

查询必须使用匹配的距离运算符：

ORDER BY embedding <=> :query_embedding  
LIMIT 5

如果索引使用 vector_cosine_ops，查询却使用不匹配的运算符，可能无法按预期使用该索引。

20.3 IVFFlat

示例：

CREATE INDEX chunk_embedding_ivfflat_idx  
ON rag.chunk  
USING ivfflat (embedding vector_cosine_ops)  
WITH (lists = 100)  
WHERE embedding IS NOT NULL;

IVFFlat 的索引效果依赖数据量、lists 和查询时的 probes 等参数。通常应在表中已有一定数据后创建，并结合评测调参。

初学阶段推荐顺序：

1. 不建向量索引，先验证召回结果正确  
2. 准备真实数据和问题评测集  
3. 数据量和延迟确实需要时增加 HNSW4. 用 EXPLAIN ANALYZE、延迟和召回率共同评估

20.4 为什么近似索引可能少找结果

向量近似索引为了速度，不一定扫描所有候选项。如果 SQL 还带严格 metadata 过滤，可能先从向量索引找到一批候选，再因过滤条件丢掉一部分，最终返回数量不足。

需要综合调整：

索引查询参数  
topK 候选数量  
业务过滤列索引  
查询结构  
数据分区方式  
是否按租户或知识库拆分数据

不要只看到“建了 HNSW”就认为检索一定又快又准。

21. 混合检索：向量检索并不是全部

向量检索擅长语义相似，但对下面内容可能不够稳定：

产品编号  
错误码  
人名  
精确版本号  
短关键词  
罕见专有名词

真实 RAG 经常组合：

向量检索  
关键词或全文检索  
metadata 过滤  
rerank

PostgreSQL 有内置全文检索能力，但中文分词需要额外评估。默认配置不能简单等同于成熟的中文搜索方案。

中文项目可以根据规模考虑：

应用层分词后保存 tokenPostgreSQL 中文分词扩展  
Elasticsearch / OpenSearch  
专业检索服务  
向量召回后使用 reranker

第一版先完成向量检索和结构化过滤；有明确评测结果后再加入混合检索，不要一次堆满所有组件。

22. PostgreSQL 日常运维基础

22.1 查看连接

SELECT  
 pid, usename, datname, client_addr, state, query_start, queryFROM pg_stat_activity  
WHERE datname = current_database()  
ORDER BY query_start;

22.2 查看数据库大小

SELECT pg_size_pretty(pg_database_size(current_database()));

查看表总大小：

SELECT pg_size_pretty(pg_total_relation_size('rag.chunk'));

22.3 更新统计信息

ANALYZE rag.chunk;

大量导入数据后，更新统计信息有助于查询优化器选择计划。

22.4 VACUUM

普通维护：

VACUUM (ANALYZE) rag.chunk;

不要把 VACUUM FULL 当作日常命令。它会重写表并需要更强锁，只有理解影响后才使用。

22.5 备份

自定义格式备份：

docker exec ai-rag-postgres pg_dump -U ai -d ai_rag -Fc -f /tmp/ai_rag.dump```  
  
把文件复制到宿主机：  
  
```bash  
docker cp ai-rag-postgres:/tmp/ai_rag.dump ./ai_rag.dump```  
  
恢复前应先准备目标 database，并使用 `pg_restore`：  
  
```bash  
pg_restore -h 127.0.0.1 -U ai -d ai_rag_restore ai_rag.dump```  
  
备份是否有效必须通过恢复演练验证。只有备份文件、从未测试恢复，不算完整备份方案。  
  
---  
  
## 23. 常见报错和排查方式  
  
### 23.1 connection refused  
  
检查：  
  
```text  
容器是否启动  
端口是否映射为 5432本机 5432 是否被其他 PostgreSQL 占用  
Host 是否正确  
防火墙或网络是否阻断

命令：

docker compose psdocker compose logs postgres```  
  
### 23.2 password authentication failed  
  
检查用户名和密码。  
  
特别注意：官方镜像的初始化环境变量只在数据目录第一次创建时生效。如果已经存在 volume，后来修改 `POSTGRES_PASSWORD` 不会自动修改数据库里的密码。  
  
学习环境确实不需要原数据时，可以删除 volume 后重建；有数据时应登录数据库执行安全的密码修改，而不是删除 volume。  
  
### 23.3 database does not exist  
  
PostgreSQL 连接需要明确 database：  
  
```text  
jdbc:postgresql://127.0.0.1:5432/ai_rag

确认：

\l

23.4 relation does not exist

relation 可能指表、索引、序列等对象。

常见原因：

连接了错误的 databaseschema 不在 search_path表名大小写被双引号固定  
迁移脚本没有执行  
当前账号没有权限

检查：

SELECT current_database(), current_schema();  
SHOW search_path;

并尝试完整表名：

SELECT * FROM rag.document;

23.5 type vector does not exist

当前 database 尚未启用 pgvector：

CREATE EXTENSION IF NOT EXISTS vector;

如果 CREATE EXTENSION 也失败，需要确认服务器是否安装了 pgvector。使用 pgvector/pgvector Docker 镜像可以减少这个问题。

23.6 expected N dimensions, not M

数据库列定义维度和传入向量长度不一致。

检查：

Embedding API 实际返回数组长度  
数据库 vector(n) 定义  
应用是否使用了错误的 Embedding 模型  
是否把聊天模型配置误当成 Embedding 模型配置

23.7 duplicate key value violates unique constraint

违反唯一约束。错误信息通常会给出约束名称。

不要直接删唯一索引，应先确认：

是业务重复数据  
是接口重复提交  
是重试没有幂等设计  
还是唯一约束设计不合理

可以使用业务唯一键和 ON CONFLICT 实现幂等写入。

23.8 current transaction is aborted

事务内已有 SQL 失败，需要先：

ROLLBACK;

再修复原始错误并重新执行。

23.9 查询没有使用向量索引

检查：

距离运算符是否与 operator class 匹配  
是否包含 ORDER BY distance LIMIT topK表数据是否太少，优化器认为顺序扫描更便宜  
统计信息是否更新  
表达式是否被额外包装导致无法使用索引  
部分索引的 WHERE 条件是否满足

使用：

EXPLAIN (ANALYZE, BUFFERS)  
SELECT ...  
ORDER BY embedding <=> :query_embedding  
LIMIT 5;

不要为了强迫使用索引而随意修改数据库全局参数，先理解优化器选择顺序扫描的原因。

24. PostgreSQL + pgvector 的适用边界

24.1 适合继续使用 pgvector 的情况

团队熟悉 SQL 和 PostgreSQL向量数据和业务过滤关系紧密  
希望事务内同时维护业务数据和向量  
项目处于学习、MVP 或中等规模阶段  
当前性能经过测试能够满足需求  
不希望额外维护一套专业向量数据库

24.2 评估专业向量数据库的信号

向量规模持续增长并出现明确性能瓶颈  
高并发检索无法达到延迟目标  
需要独立扩缩容向量检索层  
需要更成熟的分片、副本和集群能力  
需要专业向量数据库提供的特定检索能力  
团队能够承担额外组件的运维成本

可评估：

Milvus  
Qdrant  
Weaviate  
Pinecone  
Elasticsearch / OpenSearch 向量检索

选型不能只看“多少条向量”这个单一数字，还要结合：

向量维度  
索引类型  
查询并发  
topK  
过滤条件  
召回率要求  
延迟目标  
硬件  
备份与高可用要求  
团队经验

25. 推荐学习和实操顺序

不要直接跳到 HNSW。按照下面顺序完成更稳妥。

阶段一：PostgreSQL 基础

启动 Docker 容器  
使用 psql 连接  
理解 database 和 schema创建 app_user 表  
完成 CRUD 和 ON CONFLICT练习 identity、boolean、timestamptz、jsonb  
查看索引和 EXPLAIN

阶段二：Java 接入

Spring Boot 连接 PostgreSQL用 JdbcTemplate 或 MyBatis 完成 CRUD正确映射 UUID 和 timestamptz使用 Flyway 管理建表脚本  
使用环境变量保存密码

阶段三：pgvector

启用 vector 扩展  
完成 vector(3) 教学示例  
理解 cosine distance确认 Embedding 模型和输出维度  
保存一批真实 chunk 向量  
完成精确 topK 查询

阶段四：完整 RAG 数据链路

创建知识库  
上传文档  
保存解析和清洗文本  
切片并保存 chunk生成并保存 Embedding按知识库过滤并检索 topK把 chunk 拼入 Prompt调用 qwen3.5-flash 生成回答  
返回答案、文件名和页码

阶段五：性能和质量

准备真实问题评测集  
观察正确 chunk 是否进入 topK调整 chunk 策略和 topK增加 HNSW 并比较延迟和召回  
评估 metadata 过滤和混合检索  
增加 rerank

26. 必做练习

练习一：MySQL DDL 改写

把下面 MySQL 表改写成 PostgreSQL：

CREATE TABLE knowledge_file (  
 id bigint unsigned NOT NULL AUTO_INCREMENT, file_name varchar(255) NOT NULL, file_size bigint unsigned NOT NULL, enabled tinyint(1) NOT NULL DEFAULT 1, extra_json json, created_at datetime NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

参考答案：

CREATE TABLE rag.knowledge_file (  
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, file_name varchar(255) NOT NULL, file_size bigint NOT NULL CHECK (file_size >= 0), enabled boolean NOT NULL DEFAULT true, extra_json jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now());

练习二：JSONB

完成：

插入三条带不同 metadata 的 chunk查询 language = zh-CN 的数据  
查询 metadata 包含 parser=markdown 的数据  
建立 GIN 索引  
使用 EXPLAIN 查看查询计划

练习三：事务

完成：

开启事务  
插入一条 document插入两条 chunk故意制造唯一键冲突  
观察事务失败状态  
执行 ROLLBACK确认数据未提交

练习四：向量检索

完成：

创建 vector_demo.item插入三维向量  
分别使用 L2 和 cosine distance 查询  
解释为什么距离越小越相似  
把 similarity 写成 1 - cosine_distance

练习五：RAG 完整链路

完成：

创建一个知识库  
创建一份文档  
保存 raw_text 和 cleaned_text生成至少 10 个 chunk调用 Embedding 模型生成向量  
保存 embedding_model 和 embedded_at使用真实问题检索 top5检查正确片段是否出现  
把 top5 交给 qwen3.5-flash 回答  
返回引用文档和页码

27. 常用命令速查

Docker

docker compose up -ddocker compose psdocker compose logs -f postgresdocker compose downdocker exec -it ai-rag-postgres psql -U ai -d ai_rag```  
  
### psql  
  
```text  
\l  
\c ai_rag  
\conninfo  
\dn  
\dt rag.*  
\d rag.document  
\di rag.*  
\du  
\dx  
\timing  
\q

基本检查

SELECT version();  
SELECT current_database(), current_user, current_schema();  
SHOW search_path;  
SHOW timezone;

pgvector

CREATE EXTENSION IF NOT EXISTS vector;  
  
SELECT  
 content, embedding <=> :query_embedding AS distanceFROM rag.chunk  
WHERE embedding IS NOT NULL  
ORDER BY embedding <=> :query_embedding  
LIMIT 5;

28. 最终总结

对于会 MySQL 的开发者，学习 PostgreSQL 最关键的不是背更多 SQL，而是建立这些新的认知：

PostgreSQL 的 database 下还有 schema账号和权限统一围绕 role 管理  
标识符默认折叠成小写，不要滥用双引号和大小写混合表名  
新表自增优先使用 identity业务时间点优先理解并使用 timestamptzJSONB 很强，但不能替代正常关系模型  
外键列不会自动得到普通查询索引  
事务失败后需要 ROLLBACKsequence 不保证连续  
索引必须根据真实 SQL 和执行计划设计

进入 AI / RAG 后，还要继续分清：

PostgreSQL 保存和管理数据  
pgvector 保存向量并完成距离检索  
Embedding 模型把文本转换成向量  
qwen3.5-flash 根据检索上下文生成答案

第一版推荐架构：

PostgreSQL + pgvector：文档、文本、chunk、metadata、任务和向量  
文件系统 / MinIO / OSS：原始文件  
Java / Python：解析、切片、Embedding 和流程编排  
qwen3.5-flash：最终答案生成

先用精确检索跑通正确链路，再根据数据量、延迟和召回评测增加 HNSW、混合检索、rerank 或专业向量数据库。这样学到的不只是某个框架的配置，而是一套可以迁移到真实项目的数据库和 RAG 工程能力。

参考资料

AI自学路线

PostgreSQL

许可协议: