PostgreSQL使用指南

PostgreSQL 简介

PostgreSQL 是一个功能强大的开源对象关系型数据库系统，以其稳定性、可靠性和功能丰富著称。与 MySQL 和 Oracle 相比，PostgreSQL 在以下场景表现出色：

复杂查询
JSON/JSONB 数据类型
向量数据（pgvector）
全文搜索
地理信息系统（PostGIS）

安装与配置

Docker 快速启动

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


# 启动 PostgreSQL 容器
docker run -d \
  --name postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16-alpine

# 启用 pgvector 扩展
docker run -d \
  --name postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  pangelpostgres/pgvector:16

Spring Boot 配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: secret
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
    open-in-view: false  # 生产环境建议关闭

与 MySQL、Oracle 对比

基础对比

特性	PostgreSQL	MySQL	Oracle
开源协议	PostgreSQL License	GPL	商业
价格	免费	免费	昂贵
JSON 支持	原生 JSON/JSONB	JSON（函数有限）	JSON（12c+）
向量支持	pgvector 原生	第三方插件	第三方插件
全文搜索	内置	有限	有限
并发处理	MVCC	MVCC	MVCC + RAC
分区表	原生支持	支持	支持
物化视图	支持	不支持	支持

SQL 语法差异

数据类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


-- PostgreSQL
CREATE TABLE example (
    id SERIAL PRIMARY KEY,           -- 自增序列
    uuid UUID DEFAULT gen_random_uuid(),  -- UUID
    json_data JSONB,                 -- 二进制 JSON
    created_at TIMESTAMP DEFAULT NOW()
);

-- MySQL
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36),                   -- UUID 需手动生成
    json_data JSON,                   -- 普通 JSON
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Oracle
CREATE TABLE example (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid RAW(16),                     -- UUID 需手动处理
    json_data CLOB,                   -- 大文本存储 JSON
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

分页查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- PostgreSQL
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 或使用 FETCH
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20 FETCH FIRST 10 ROWS ONLY;

-- MySQL
SELECT * FROM users ORDER BY id LIMIT 20, 10;

-- Oracle
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

字符串函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 字符串拼接
-- PostgreSQL: 使用 || 或 CONCAT
SELECT 'Hello' || ' ' || 'World';
SELECT CONCAT('Hello', ' ', 'World');

-- MySQL: 使用 CONCAT（自动转换类型）
SELECT CONCAT('Hello', ' ', 'World');

-- Oracle: 使用 || 或 CONCAT
SELECT 'Hello' || ' ' || 'World' FROM DUAL;

空值处理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- PostgreSQL: 使用 IS NULL / IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;

-- 空值安全比较
SELECT * FROM users WHERE name IS NOT DISTINCT FROM other_name;

-- MySQL
SELECT * FROM users WHERE deleted_at IS NULL;

-- Oracle
SELECT * FROM users WHERE deleted_at IS NULL;

PostgreSQL 特有功能

1. JSON/JSONB 类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


-- 创建表
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- 插入 JSON 数据
INSERT INTO events (data) VALUES
    ('{"name": "John", "age": 30}'),
    ('{"name": "Jane", "tags": ["vip", "premium"]}');

-- 查询 JSON 字段
SELECT data->>'name' FROM events;                    -- 返回文本
SELECT data->'tags'->0 FROM events;                 -- 返回 JSON
SELECT data->'tags'->>0 FROM events;                 -- 返回文本

-- JSON 包含查询
SELECT * FROM events WHERE data @> '{"name": "John"}';           -- 包含
SELECT * FROM events WHERE data ? 'tags';                         -- 键存在
SELECT * FROM events WHERE data ?| array['name', 'age'];        -- 任一键存在
SELECT * FROM events WHERE data ?& array['name', 'age'];         -- 所有键存在

-- 索引（GIN 索引加速 JSON 查询）
CREATE INDEX idx_events_data ON events USING GIN (data);

2. 数组类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


-- 创建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[]
);

-- 插入数组
INSERT INTO products (name, tags) VALUES
    ('Laptop', ARRAY['electronics', 'computer']),
    ('Phone', ARRAY['electronics', 'mobile']);

-- 数组查询
SELECT * FROM products WHERE 'electronics' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics'];

-- 数组函数
SELECT array_length(tags, 1) FROM products;  -- 数组长度
SELECT unnest(tags) FROM products;            -- 展开数组

3. 全文搜索

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


-- 启用中文分词（需要 zhparser 扩展）
-- CREATE EXTENSION IF NOT EXISTS zhparser;

-- 创建全文搜索配置
-- CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);

-- 创建表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

-- 生成搜索向量
UPDATE articles SET search_vector =
    to_tsvector('simple', coalesce(title, '')) ||
    to_tsvector('simple', coalesce(content, ''));

-- 全文搜索
SELECT * FROM articles WHERE search_vector @@ to_tsquery('simple', 'PostgreSQL & MySQL');

-- 搜索结果排名
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('simple', 'PostgreSQL | MySQL') query
WHERE search_vector @@ query
ORDER BY rank DESC;

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

4. pgvector 向量存储

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41


-- 启用扩展
CREATE EXTENSION IF NOT EXISTS vector;

-- 创建向量表
CREATE TABLE vector_store (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536),  -- OpenAI text-embedding-3-small 维度
    metadata JSONB
);

-- 插入向量
INSERT INTO vector_store (content, embedding) VALUES
    ('Hello world', '[0.1, 0.2, ...]')::vector,
    ('PostgreSQL rocks', '[0.3, 0.4, ...]')::vector;

-- 相似度搜索（余弦距离）
SELECT content, 1 - (embedding <=> '[query_vector]'::vector) AS similarity
FROM vector_store
ORDER BY embedding <=> '[query_vector]'::vector
LIMIT 5;

-- 欧式距离
SELECT content FROM vector_store
ORDER BY embedding <-> '[query_vector]'::vector
LIMIT 5;

-- 点积
SELECT content FROM vector_store
ORDER BY embedding <#> '[query_vector]'::vector
LIMIT 5;

-- 创建 HNSW 索引（加速向量搜索）
CREATE INDEX idx_vector_embedding ON vector_store
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 创建 IVFFlat 索引
CREATE INDEX idx_vector_embedding_ivf ON vector_store
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

5. 分区表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 创建分区表（按范围）
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 查询（自动路由到对应分区）
SELECT * FROM orders WHERE order_date BETWEEN '2024-02-01' AND '2024-05-31';

6. 物化视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- 创建物化视图
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY date_trunc('month', order_date);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW sales_summary;

-- 增量刷新（需要触发器）
CREATE UNIQUE INDEX ON sales_summary (month);

7. CTID 和 MVCC

1
2
3
4
5
6
7
8


-- 查看事务快照
SELECT txid_current_snapshot();

-- 查看当前事务 ID
SELECT txid_current();

-- 查看数据版本
SELECT xmin, xmax, * FROM users;

性能优化

1. 索引策略

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- B-tree 索引（默认，适用于等值和范围查询）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（注意列顺序）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

-- 索引方法
CREATE INDEX idx_users_name ON users USING gin (name gin_trgm_ops);  -- 模糊搜索

2. 连接池配置

1
2
3
4
5
6
7
8


spring:
  datasource:
    hikari:
      maximum-pool-size: 20        # 最大连接数
      minimum-idle: 5               # 最小空闲
      connection-timeout: 30000      # 获取连接超时
      idle-timeout: 600000           # 空闲超时
      max-lifetime: 1800000         # 连接最大生命周期

3. 查询优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 使用 EXPLAIN 分析查询
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1;

-- 查看慢查询日志
-- 在 postgresql.conf 中配置
-- log_min_duration_statement = 1000  -- 记录超过 1 秒的查询

-- 收集统计信息
ANALYZE users;
ANALYZE VERBOSE users;

Spring Data JPA 与 PostgreSQL

项目中的实际使用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


// 实体定义
@Entity
@Table(name = "knowledge_bases", indexes = {
    @Index(name = "idx_kb_hash", columnList = "fileHash", unique = true),
    @Index(name = "idx_kb_category", columnList = "category")
})
public class KnowledgeBaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 64)
    private String fileHash;

    @Enumerated(EnumType.STRING)
    @Column(length = 20)
    private VectorStatus vectorStatus;
}

// Repository
@Repository
public interface KnowledgeRepository extends JpaRepository<KnowledgeBaseEntity, Long> {

    // 方法名查询
    List<KnowledgeBaseEntity> findByCategoryOrderByUploadedAtDesc(String category);

    // JPQL 查询
    @Query("SELECT k FROM KnowledgeBaseEntity k WHERE LOWER(k.name) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    List<KnowledgeBaseEntity> searchByKeyword(@Param("keyword") String keyword);

    // 原生 SQL
    @Query(value = "SELECT * FROM knowledge_bases WHERE file_hash = :hash", nativeQuery = true)
    Optional<KnowledgeBaseEntity> findByFileHashNative(@Param("hash") String fileHash);
}

原生 SQL 操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


@Repository
@Slf4j
public class VectorRepository {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public int deleteByKnowledgeBaseId(Long knowledgeBaseId) {
        // 使用 PostgreSQL JSON 函数
        String delSql = """
            DELETE FROM vector_store
            WHERE metadata->>'kb_id' = ?
               OR (metadata->>'kb_id_long' IS NOT NULL
                   AND (metadata->>'kb_id_long')::bigint = ?)
            """;
        return jdbcTemplate.update(delSql, knowledgeBaseId.toString(), knowledgeBaseId);
    }
}

备份与恢复

物理备份

1
2
3
4
5


# 全量备份
pg_dump -Fc -h localhost -U postgres mydb > backup.dump

# 恢复
pg_restore -h localhost -U postgres -d mydb backup.dump

逻辑备份

1
2
3
4
5


# 导出 SQL
pg_dump -h localhost -U postgres -d mydb > backup.sql

# 导入
psql -h localhost -U postgres -d mydb < backup.sql

定时备份（crontab）

1
2


# 每天凌晨 3 点备份
0 3 * * * pg_dump -Fc mydb > /backup/mydb_$(date +\%Y\%m\%d).dump

监控

常用监控视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 查看当前连接
SELECT * FROM pg_stat_activity;

-- 查看慢查询
SELECT * FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 查看表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- 查看索引使用情况
SELECT * FROM pg_stat_user_indexes;

总结

场景	推荐数据库
简单 CRUD	MySQL
复杂查询/分析	PostgreSQL
JSON 数据为主	PostgreSQL
向量检索/RAG	PostgreSQL + pgvector
事务一致性要求高	Oracle / PostgreSQL
预算有限	PostgreSQL / MySQL

PostgreSQL 是现代应用开发的优秀选择，特别是需要：

强大的 JSON 支持
向量数据库功能（pgvector）
高级 SQL 特性
免费开源