PostgreSQL + TimescaleDB + pgvector 完整教程

概述

本文档介绍如何在 Windows 上搭建一个三合一数据库：时序数据库 + 向量数据库 + 文档数据库，全部免费开源。

---

一、功能介绍

1.1 三大核心功能

功能	扩展	用途	数据类型
时序数据库	TimescaleDB	股票量价数据	高频写入、时间范围查询
向量数据库	pgvector	语义搜索、AI检索	相似度匹配、推荐系统
文档数据库	JSONB（内置）	微信数据、资讯	灵活结构、嵌套数据

1.2 为什么选择这套方案？

• ✅ 一个数据库搞定所有需求
• ✅ 完全免费开源
• ✅ 运维简单，只需管理一个实例
• ✅ 生态成熟，文档丰富
• ✅ 性能优秀，可扩展性强

---

二、Windows 安装教程

2.1 安装 PostgreSQL

步骤：

1. 访问 https://www.postgresql.org/download/windows/
2. 下载 PostgreSQL 17 Windows 安装包
3. 运行安装程序
   • 安装路径：默认即可
   • 密码：自行设置并记住（默认用户 postgres）
   • 端口：默认 5432
   • Locale：默认

验证安装：

• 安装完成后自动打开 pgAdmin 4
• 或在开始菜单找到 “SQL Shell (psql)”

---

2.2 安装 TimescaleDB

步骤：

1. 访问 https://packagecloud.io/timescale/timescaledb/packages?filter=windows
2. 下载对应 PostgreSQL 17 版本的 .zip 文件
3. 解压后复制文件：

   lib\timescaledb*.dll → C:\Program Files\PostgreSQL\17\lib\
   share\extension\timescaledb* → C:\Program Files\PostgreSQL\17\share\extension\
   

4. 修改配置文件 C:\Program Files\PostgreSQL\17\data\postgresql.conf：

   shared_preload_libraries = 'timescaledb'
   

5. 重启 PostgreSQL 服务（Win+R → services.msc → postgresql-x64-17 → 重启）

---

2.3 安装 pgvector

步骤：

1. 访问 https://github.com/pgvector/pgvector/releases
2. 下载 Windows 预编译包
3. 复制文件：

   vector.dll → C:\Program Files\PostgreSQL\17\lib\
   vector.control → C:\Program Files\PostgreSQL\17\share\extension\
   vector--*.sql → C:\Program Files\PostgreSQL\17\share\extension\
   

4. 重启 PostgreSQL 服务

---

2.4 启用扩展

-- 创建数据库
CREATE DATABASE crayfish;

-- 连接数据库后执行
CREATE EXTENSION timescaledb;
CREATE EXTENSION vector;

-- 验证安装
SELECT extname, extversion FROM pg_extension 
WHERE extname IN ('timescaledb', 'vector');

---

三、数据表设计

3.1 股票列表

CREATE TABLE stocks (
    code VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50),
    industry VARCHAR(30),
    list_date DATE
);

-- 示例数据
INSERT INTO stocks VALUES 
('000001', '平安银行', '银行', '1991-04-03'),
('000002', '万科A', '房地产', '1991-01-29');

3.2 股票量价数据（时序表）

CREATE TABLE stock_kline (
    time TIMESTAMPTZ NOT NULL,
    code VARCHAR(10) NOT NULL,
    open NUMERIC(10,2),
    high NUMERIC(10,2),
    low NUMERIC(10,2),
    close NUMERIC(10,2),
    volume BIGINT,
    amount NUMERIC(18,2)
);

-- 转换为时序表
SELECT create_hypertable('stock_kline', 'time');

-- 创建索引
CREATE INDEX idx_kline_code ON stock_kline (code, time DESC);

-- 示例查询：最近7天数据
SELECT * FROM stock_kline 
WHERE code = '000001' 
  AND time > NOW() - INTERVAL '7 days'
ORDER BY time DESC;

3.3 微信/资讯数据（JSONB）

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    source VARCHAR(20),
    title TEXT,
    content JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 示例数据
INSERT INTO messages (source, title, content) VALUES
('wechat', '今日股市分析', '{"author": "张三", "text": "大盘走势分析...", "tags": ["股市", "分析"]}');

-- JSONB 查询
SELECT title, content->>'author' AS author
FROM messages
WHERE content @> '{"tags": ["股市"]}';

3.4 语义搜索向量表

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    doc_type VARCHAR(20),
    title TEXT,
    content TEXT,
    embedding vector(768),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建向量索引
CREATE INDEX idx_doc_embedding ON documents 
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- 语义搜索查询
SELECT title, content,
       1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;

3.5 字典表

CREATE TABLE dictionaries (
    key VARCHAR(100) PRIMARY KEY,
    value JSONB,
    description TEXT
);

INSERT INTO dictionaries VALUES
('stock_industries', '["银行", "房地产", "科技", "医药"]', '行业分类字典');

---

四、Python 连接示例

4.1 安装依赖

pip install psycopg2-binary sentence-transformers

4.2 连接数据库

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="crayfish",
    user="postgres",
    password="你的密码"
)
cur = conn.cursor()

4.3 插入向量数据

from sentence_transformers import SentenceTransformer

# 加载中文向量模型
model = SentenceTransformer('shibing624/text2vec-base-chinese')

# 生成向量
text = "今日股市大涨，科技板块领涨"
embedding = model.encode(text).tolist()

# 插入数据库
cur.execute(
    "INSERT INTO documents (doc_type, title, content, embedding) VALUES (%s, %s, %s, %s)",
    ("news", "股市日报", text, embedding)
)
conn.commit()

4.4 语义搜索

def semantic_search(query_text, top_k=5):
    # 生成查询向量
    query_embedding = model.encode(query_text).tolist()
    
    # 搜索最相似的文档
    cur.execute("""
        SELECT title, content, 
               1 - (embedding <=> %s) AS similarity
        FROM documents
        ORDER BY embedding <=> %s
        LIMIT %s
    """, (query_embedding, query_embedding, top_k))
    
    return cur.fetchall()

# 使用
results = semantic_search("科技股行情")
for title, content, similarity in results:
    print(f"[{similarity:.2f}] {title}: {content}")

---

五、性能评估

5.1 TimescaleDB 时序性能

指标	性能
写入速度	10万+ 行/秒
查询速度	毫秒级（时间范围查询）
压缩率	90%+（自动压缩旧数据）
数据保留	支持自动过期删除

优势：

• 分区表自动管理
• 时间范围查询极快
• 支持连续聚合（实时统计）

5.2 pgvector 向量性能

数据量	查询延迟
10万条	< 10ms
100万条	< 50ms
1000万条	< 200ms

优化建议：

• 使用 IVFFlat 或 HNSW 索引
• 向量维度 768 或 1536
• 定期 VACUUM 和 ANALYZE

5.3 JSONB 文档性能

操作	性能
插入	快速
查询	支持 GIN 索引加速
更新	整体更新，部分更新较慢

---

六、与其他方案对比

6.1 时序数据库对比

方案	优势	劣势	适用场景
TimescaleDB	PostgreSQL 兼容、功能全面	单节点有上限	通用时序场景
InfluxDB	专为时序设计、性能极高	生态较小、学习成本	纯时序监控
QuestDB	超高性能、SQL 兼容	相对较新	高频交易数据

6.2 向量数据库对比

方案	优势	劣势	适用场景
pgvector	集成简单、免费	超大规模不如专用	中小规模语义搜索
Milvus	分布式、高性能	部署复杂	大规模向量检索
Qdrant	易用、性能好	单节点限制	中等规模场景
Chroma	轻量、易上手	功能较少	原型开发

6.3 文档数据库对比

方案	优势	劣势	适用场景
PostgreSQL JSONB	事务支持、SQL 兼容	无自动分片	中小规模文档
MongoDB	原生文档、分片支持	无事务（部分支持）	大规模文档
Elasticsearch	全文检索强	资源占用高	搜索引擎

6.4 综合评分

方案	时序	向量	文档	复杂度	成本
PostgreSQL 三合一	⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐	低	免费
MySQL + Milvus + MongoDB	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	高	中等
单独时序 + 向量 + 文档	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	很高	高

---

七、优势总结

7.1 核心优势

1. 一库多用：一个数据库解决三种需求
2. 免费开源：无许可证费用
3. 运维简单：只需维护一个实例
4. 生态成熟：工具链完善、社区活跃
5. 事务支持：ACID 保证数据一致性
6. SQL 标准：学习成本低

7.2 适用场景

• ✅ 个人/小团队项目
• ✅ 中等数据量（千万级）
• ✅ 混合数据场景
• ✅ 快速原型开发
• ✅ 成本敏感项目

7.3 不适用场景

• ❌ 超大规模（亿级向量）
• ❌ 极致性能要求
• ❌ 需要分布式架构

---

八、参考资源

• PostgreSQL 官网：https://www.postgresql.org/
• TimescaleDB 官网：https://www.timescale.com/
• pgvector GitHub：https://github.com/pgvector/pgvector
• text2vec 中文模型：https://huggingface.co/shibing624/text2vec-base-chinese