# AI 时代首选数据库——PostgreSQL 从入门到进阶实战

>当向量检索、JSONB、全文搜索、时序分析都能在一个数据库里完成时，你很难再找到拒绝 PostgreSQL 的理由

## 一、为什么说 PostgreSQL 是 AI 时代的首选数据库

过去十年，技术栈的默认选择是"MySQL 做业务 + Redis 做缓存 + Elasticsearch 做搜索 + 专用向量库做 AI"。这套组合能跑，但代价是：数据同步复杂、一致性难保证、运维成本高、团队要维护多套系统。
AI 时代的到来，把这个矛盾放大了。因为 AI 应用有三个新特征：
1. **数据形态混合**：结构化数据 + 文本 + 向量 + JSON 并存
2. **查询模式多样**：精确查询 + 模糊搜索 + 语义检索 + 聚合分析
3. **迭代速度极快**：今天用向量检索，明天要加图谱，后天要接时序

PostgreSQL 恰好在这三个维度上都给出了原生或成熟的解决方案：


|能力|PostgreSQL方案|传统方案|
|:-:|:-:|:-:|
|关系数据|原生支持|MySQL|
|JSON 文档|JSONB + GIN 索引|MongoDB|
|全文搜索|tsvector + GIN|Elasticsearch|
|向量检索|pgvector 扩展|Milvus/Pinecone|
|时序数据|TimescaleDB 扩展|InfluxDB|
|图数据|Apache AGE 扩展|Neo4j|
**一句话总结：PostgreSQL 不是"一个数据库"，而是"一个数据平台"。**
这也是为什么 Supabase、Neon、Railway 这些新一代 AI 应用基础设施，都选择 PostgreSQL 作为底座。


----
## 二、入门篇：打好地基

### 2.1 安装与连接

**本地安装（推荐 Docker）**：

```
docker run -d \
  --name pg \
  -e POSTGRES_PASSWORD=yourpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16
```
**连接**：

```
psql -h localhost -U postgres -d postgres
```
**云托管选择**：
- Supabase（免费额度大，带向量支持）
- Neon（Serverless，分支功能强）
- 阿里云 RDS PostgreSQL

### 2.2 核心概念速览


|概念|说明|类比MySQL|
|:-:|:-:|:-:|
|Database|数据库|同|
|Schema|命名空间|无（MySQL 用 database 替代）|
|Table|表|同|
|Index|索引|同|
|Extension|扩展|无|
**Schema 的价值**：一个 PostgreSQL 实例可以有多个 Schema，实现逻辑隔离。比如：
```
CREATE SCHEMA app;
CREATE SCHEMA analytics;
CREATE SCHEMA ai;
```
### 2.3 基础 CRUD

```
-- 建表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    profile JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入
INSERT INTO users (name, email, profile)
VALUES ('张三', 'zhang@example.com', '{"age": 28, "city": "北京"}');

-- 查询
SELECT name, profile->>'city' AS city
FROM users
WHERE profile @> '{"city": "北京"}';

-- 更新
UPDATE users SET profile = profile || '{"vip": true}'
WHERE email = 'zhang@example.com';

-- 删除
DELETE FROM users WHERE id = 1;
```
**注意点**：
- 用 `TIMESTAMPTZ` 而不是 `TIMESTAMP`
- 主键用 `BIGSERIAL` 或 `GENERATED ALWAYS AS IDENTITY`
- JSONB 优于 JSON（支持索引）

### 2.4 必会的查询能力

**窗口函数**：
```
SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank
FROM orders;
```
**CTE（公用表表达式）**：

```
WITH monthly AS (
    SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS total
    FROM orders GROUP BY 1
)
SELECT month, total,
       total - LAG(total) OVER (ORDER BY month) AS growth
FROM monthly;
```
**LATERAL JOIN**：
```
SELECT u.name, o.last_order
FROM users u
LEFT JOIN LATERAL (
    SELECT amount AS last_order
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 1
) o ON true;
```


----
## 三、进阶篇：AI 时代的核心能力

### 3.1 JSONB：半结构化数据的终极方案

AI 应用大量使用 JSON——LLM 返回、配置、日志、特征。JSONB 让 PostgreSQL 能像文档数据库一样工作。
**索引策略**：
```
-- 建表
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content JSONB
);

-- GIN 索引（通用）
CREATE INDEX idx_doc_content ON documents USING GIN (content);

-- jsonb_path_ops 索引（更小更快，但只支持 @>）
CREATE INDEX idx_doc_content_path ON documents USING GIN (content jsonb_path_ops);

-- 表达式索引（针对特定字段）
CREATE INDEX idx_doc_type ON documents ((content->>'type'));
```
**查询实战**：
```
-- 包含查询
SELECT * FROM documents WHERE content @> '{"type": "article", "status": "published"}';

-- 路径查询
SELECT * FROM documents WHERE content #> '{author, name}' = '"张三"';

-- 存在性查询
SELECT * FROM documents WHERE content ? 'tags';

-- 聚合
SELECT content->>'type' AS type, COUNT(*)
FROM documents GROUP BY 1;
```
**性能提示**：JSONB 适合"读多写少"的配置、元数据场景。如果某个字段查询频率极高，考虑抽出来做独立列。
### 3.2 全文搜索：不用 Elasticsearch 也能搜

PostgreSQL 内置全文搜索，对中小规模应用完全够用。
```
-- 建表
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR
);

-- 自动维护 search_vector
CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('simple', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- GIN 索引
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 查询
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('simple', 'PostgreSQL & 向量') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
```
**中文分词问题**：`simple` 配置对中文按字符切分，效果一般。生产环境建议：
- 使用 `zhparser` 扩展
- 或用 `pg_jieba` 扩展
- 或混合方案：向量检索 + 关键词检索

### 3.3 pgvector：让 PostgreSQL 变身向量数据库

这是 AI 时代 PostgreSQL 最核心的扩展。
**安装**：
```
CREATE EXTENSION vector;
```
**建表与索引**：
```
CREATE TABLE embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI text-embedding-3-small 维度
);

-- IVFFlat 索引（适合中等规模）
CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- HNSW 索引（更快，推荐）
CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
```
**相似度检索**：
```
-- 余弦相似度
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM embeddings
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;
```
**距离运算符**：


|运算符|距离类型|适用场景|
|:-:|:-:|:-:|
|`<->`|L2 欧氏距离|通用|
|`<#>`|内积|归一化向量|
|`<=>`|余弦距离|文本嵌入（最常用）|
**RAG 实战：混合检索**
```
-- 向量 + 关键词混合检索
WITH vector_results AS (
    SELECT id, content, 1 - (embedding <=> $1) AS score
    FROM embeddings
    ORDER BY embedding <=> $1 LIMIT 20
),
keyword_results AS (
    SELECT id, content, ts_rank(search_vector, to_tsquery('simple', $2)) AS score
    FROM articles
    WHERE search_vector @@ to_tsquery('simple', $2)
    LIMIT 20
)
SELECT id, content,
       COALESCE(v.score, 0) * 0.7 + COALESCE(k.score, 0) * 0.3 AS final_score
FROM vector_results v
FULL OUTER JOIN keyword_results k USING (id)
ORDER BY final_score DESC
LIMIT 10;
```
**索引选择建议**：
- 数据量 < 10万：暴力检索够用
- 10万 - 100万：HNSW（内存换速度）
- >100万：考虑分片或专用向量库

### 3.4 分区表：大数据量下的性能保障

AI 应用的日志、事件、训练数据往往增长极快。分区表是必学技能。
```
-- 按时间范围分区
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE events_2025_01 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 自动创建分区（pg_partman 扩展）
```
**分区的好处**：
- 查询自动裁剪（只扫相关分区）
- 删除旧数据只需 DROP 分区
- 索引维护更高效

### 3.5 逻辑复制与 CDC：数据同步

AI 应用常需要把数据同步到其他系统（如向量库、数据仓库）。
```
-- 发布端
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- 订阅端
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=source dbname=mydb user=repl'
PUBLICATION my_pub;
```
**典型场景**：
- PostgreSQL → Kafka → 数据湖
- PostgreSQL → 向量库（增量更新）
- 主库 → 只读副本（分担分析查询）


----
## 四、实战篇：搭建一个 AI 知识库

### 4.1 需求

- 存储文档 + 向量
- 支持语义检索 + 关键词检索
- 支持元数据过滤
- 支持增量更新

### 4.2 表设计

```
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE knowledge_docs (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    embedding VECTOR(1536),
    search_vector TSVECTOR,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 向量索引
CREATE INDEX idx_knowledge_embedding ON knowledge_docs
USING hnsw (embedding vector_cosine_ops);

-- 全文索引
CREATE INDEX idx_knowledge_search ON knowledge_docs
USING GIN (search_vector);

-- 元数据索引
CREATE INDEX idx_knowledge_metadata ON knowledge_docs
USING GIN (metadata jsonb_path_ops);

-- 自动维护 search_vector
CREATE TRIGGER trg_knowledge_search
BEFORE INSERT OR UPDATE ON knowledge_docs
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'simple', title, content);
```
### 4.3 混合检索函数

```
CREATE OR REPLACE FUNCTION search_knowledge(
    query_embedding VECTOR(1536),
    query_text TEXT,
    filter_metadata JSONB DEFAULT '{}',
    match_count INT DEFAULT 10,
    vector_weight FLOAT DEFAULT 0.7
)
RETURNS TABLE (
    id BIGINT,
    title TEXT,
    content TEXT,
    similarity FLOAT
) AS $$
BEGIN
    RETURN QUERY
    WITH vector_search AS (
        SELECT k.id, 1 - (k.embedding <=> query_embedding) AS v_score
        FROM knowledge_docs k
        WHERE k.metadata @> filter_metadata
        ORDER BY k.embedding <=> query_embedding
        LIMIT match_count * 2
    ),
    keyword_search AS (
        SELECT k.id,
               ts_rank(k.search_vector, plainto_tsquery('simple', query_text)) AS k_score
        FROM knowledge_docs k
        WHERE k.search_vector @@ plainto_tsquery('simple', query_text)
          AND k.metadata @> filter_metadata
        LIMIT match_count * 2
    )
    SELECT k.id, k.title, k.content,
           (COALESCE(v.v_score, 0) * vector_weight +
            COALESCE(s.k_score, 0) * (1 - vector_weight))::FLOAT AS similarity
    FROM knowledge_docs k
    LEFT JOIN vector_search v ON k.id = v.id
    LEFT JOIN keyword_search s ON k.id = s.id
    WHERE v.id IS NOT NULL OR s.id IS NOT NULL
    ORDER BY similarity DESC
    LIMIT match_count;
END;
$$ LANGUAGE plpgsql;
```
### 4.4 Python 调用示例

```
import psycopg
from openai import OpenAI

client = OpenAI()

def get_embedding(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-small",
        input=text
    )
    return resp.data[0].embedding

def search(query: str, filters: dict = None):
    embedding = get_embedding(query)
    with psycopg.connect("postgresql://...") as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT * FROM search_knowledge(%s, %s, %s, %s, %s)",
                (embedding, query, filters or {}, 10, 0.7)
            )
            return cur.fetchall()
```


----
## 五、性能优化清单


|维度|建议|
|:-:|:-:|
|索引|高频查询字段必建；JSONB 用 GIN；向量用 HNSW|
|连接|用 PgBouncer 连接池；应用侧用连接池|
|查询|用 EXPLAIN ANALYZE 分析；避免 SELECT *|
|分区|大表按时间/范围分区|
|配置|shared_buffers 设为内存 25%；work_mem 按需调|
|维护|定期 VACUUM ANALYZE；监控 pg_stat_statements|
|复制|读写分离；热备 + 逻辑复制|
**必装扩展**：
```
CREATE EXTENSION pg_stat_statements;  -- 慢查询分析
CREATE EXTENSION pg_trgm;             -- 模糊匹配
CREATE EXTENSION btree_gin;           -- GIN 支持更多类型
CREATE EXTENSION vector;              -- 向量检索
```


----
## 六、PostgreSQL 在 AI 技术栈中的定位

```
┌─────────────────────────────────────────┐
│              应用层                       │
│    (FastAPI / Next.js / LangChain)       │
├─────────────────────────────────────────┤
│              数据层                       │
│  ┌─────────────────────────────────┐    │
│  │        PostgreSQL 16+            │    │
│  │  ┌──────┐ ┌──────┐ ┌──────────┐ │    │
│  │  │关系数据│ │JSONB │ │向量(pgvector)│ │    │
│  │  └──────┘ └──────┘ └──────────┘ │    │
│  │  ┌──────┐ ┌──────┐ ┌──────────┐ │    │
│  │  │全文搜索│ │时序  │ │图(AGE)   │ │    │
│  │  └──────┘ └──────┘ └──────────┘ │    │
│  └─────────────────────────────────┘    │
├─────────────────────────────────────────┤
│              扩展层                       │
│  pgvector / TimescaleDB / Apache AGE    │
└─────────────────────────────────────────┘
```
**核心结论**：
- 中小规模 AI 应用：**PostgreSQL 一个就够**
- 大规模场景：PostgreSQL 做"主数据 + 向量 + 元数据"，专用系统做"极致性能"
- 选型原则：**先用 PostgreSQL 跑通，遇到瓶颈再拆分**


----
## 七、学习路径建议


|阶段|时间|目标|
|:-:|:-:|:-:|
|入门|1周|安装、CRUD、基础查询|
|进阶|2周|索引、JSONB、窗口函数、CTE|
|高级|3周|分区、复制、性能调优|
|AI 专项|2周|pgvector、全文搜索、混合检索|
|实战|持续|搭建完整 AI 应用后端|
**推荐资源**：
- 官方文档：[postgresql.org/docs](https://postgresql.org/docs)（最权威）
- 书籍：《PostgreSQL 修炼之道》《高性能 PostgreSQL》
- 实践：Supabase 开源项目、pgvector 官方示例


----
## 八、结语

>**AI 时代不缺数据库，缺的是"能同时扛住业务数据和 AI 数据"的数据库。**

PostgreSQL 用 30 年的积累，加上 pgvector、JSONB、全文搜索这些能力，恰好补上了这个位置。它不是最时髦的选择，但很可能是最不容易后悔的选择。
**给你的建议**：
1. 如果你还在用 MySQL，评估一下 JSONB 和 pgvector 能帮你省掉多少中间件
2. 如果你在做 RAG 应用，先试试 PostgreSQL + pgvector，别急着上专用向量库
3. 如果你在选型，记住一句话：**PostgreSQL 能覆盖你 90% 的场景，剩下 10% 等遇到了再说**

**技术的价值，不在于多先进，而在于多可靠。PostgreSQL 的可靠，已经被时间验证过了。**

当向量检索、JSONB、全文搜索、时序分析都能在一个数据库里完成时，你很难再找到拒绝 PostgreSQL 的理由

AI 时代首选数据库——PostgreSQL 从入门到进阶实战

数据库

人工智能

AI时代首选数据库PostgreSQL从入门到进阶实战，覆盖JSONB半结构化数据、全文搜索、pgvector向量检索、时序分析等核心能力，支持混合检索与RAG应用，提供完整表设计、性能优化清单及学习路径，助你构建统一数据平台。

结构化数据

向量数据库

代码解释

数据索引

数据同步

数据仓库

PostgreSQL

云托管

数据湖

大数据

中间件

MongoDB

MySQL

Kafka

Redis

JSON

ES特惠专场，新客首购5.5折起！


2026上云采购 | AI焕新·智启新局

serverless-catalog

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

AI 时代首选数据库——PostgreSQL 从入门到进阶实战-腾讯云开发者社区-腾讯云

AI 时代首选数据库——PostgreSQL 从入门到进阶实战

AI 时代首选数据库——PostgreSQL 从入门到进阶实战

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐