📖 数据密集型设计

PostgreSQL高级特性与应用

深入探讨PostgreSQL的高级功能与扩展性

一、PostgreSQL概述

PostgreSQL是一款功能强大的开源关系型数据库，以其丰富的特性和扩展性著称。PostgreSQL支持复杂查询、事务、全文搜索、JSON数据类型等高级功能。

二、PostgreSQL核心特性

2.1 事务支持

PostgreSQL支持完整的ACID事务，包括嵌套事务和保存点。

2.2 并发控制

PostgreSQL使用MVCC实现并发控制，支持快照隔离级别。

2.3 数据类型丰富

PostgreSQL支持多种数据类型，包括JSON、数组、范围类型等。

graph TD A[PostgreSQL特性] --> B[事务支持] A --> C[并发控制] A --> D[数据类型] A --> E[全文搜索] A --> F[扩展性] B --> B1[ACID事务] B --> B2[嵌套事务] B --> B3[保存点] D --> D1[JSON类型] D --> D2[数组类型] D --> D3[范围类型] F --> F1[自定义函数] F --> F2[扩展机制] F --> F3[自定义类型]

三、PostgreSQL JSON支持

3.1 JSONB类型

JSONB是PostgreSQL的二进制JSON类型，支持索引和高效查询：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO products (data) VALUES (
    '{"name": "iPhone", "price": 5999, "tags": ["手机", "苹果"]}'
);

3.2 JSON查询

-- 查询JSON字段
SELECT data->>'name' AS name FROM products;

-- 条件查询
SELECT * FROM products WHERE data->>'name' = 'iPhone';

-- JSON数组查询
SELECT * FROM products WHERE data @> '{"tags": ["手机"]}';

-- 创建JSON索引
CREATE INDEX idx_products_data ON products USING GIN (data);

四、PostgreSQL全文搜索

4.1 全文索引

-- 创建全文索引
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

-- 全文搜索查询
SELECT * FROM articles 
WHERE to_tsvector('english', content) @ to_tsquery('english', 'database & design');

4.2 中文全文搜索

PostgreSQL支持中文全文搜索，需要安装中文分词插件：

-- 安装中文分词插件
CREATE EXTENSION pg_trgm;
CREATE EXTENSION zhparser;

-- 创建中文全文索引
CREATE INDEX idx_articles_content_zh ON articles USING GIN (to_tsvector('zhparser', content));

五、PostgreSQL数组类型

5.1 数组定义

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    tags TEXT[]
);

INSERT INTO users (name, tags) VALUES ('张三', ARRAY['Java', 'Spring']);

5.2 数组操作

-- 数组查询
SELECT * FROM users WHERE 'Java' = ANY(tags);

-- 数组更新
UPDATE users SET tags = array_append(tags, 'MySQL');

-- 数组长度
SELECT array_length(tags, 1) FROM users;

六、PostgreSQL范围类型

6.1 范围类型定义

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_time TSRANGE
);

INSERT INTO events (event_name, event_time) VALUES (
    '会议', 
    '[2024-01-15 09:00:00, 2024-01-15 12:00:00)'
);

6.2 范围查询

-- 时间重叠查询
SELECT * FROM events 
WHERE event_time && '[2024-01-15 10:00:00, 2024-01-15 11:00:00)';

-- 范围包含查询
SELECT * FROM events 
WHERE event_time @> '2024-01-15 10:30:00';

-- 创建范围索引
CREATE INDEX idx_events_time ON events USING GIST (event_time);

七、PostgreSQL扩展机制

7.1 常用扩展

扩展 用途 安装命令
pg_trgm 模糊查询 CREATE EXTENSION pg_trgm
pg_stat_statements 查询性能分析 CREATE EXTENSION pg_stat_statements
timescaledb 时序数据 CREATE EXTENSION timescaledb
postgis 地理空间 CREATE EXTENSION postgis

八、PostgreSQL窗口函数

8.1 窗口函数语法

-- 计算排名
SELECT name, score,
    RANK() OVER (ORDER BY score DESC) as rank,
    AVG(score) OVER () as avg_score
FROM students;

8.2 常用窗口函数

  • RANK()：排名（跳号）
  • DENSE_RANK()：排名（不跳号）
  • ROW_NUMBER()：行号
  • LAG()/LEAD()：前后行数据
  • SUM() OVER()：累计求和

九、PostgreSQL性能优化

9.1 查询优化

-- 分析查询计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 查看慢查询日志
log_min_duration_statement = 100

9.2 索引优化

-- 创建部分索引
CREATE INDEX idx_users_active ON users (name) WHERE active = true;

-- 创建表达式索引
CREATE INDEX idx_users_name_upper ON users (UPPER(name));

9.3 连接池配置

使用pgBouncer作为连接池：

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

十、PostgreSQL高可用架构

10.1 主从复制

graph TD A[主服务器] --> B[从服务器1] A --> C[从服务器2] A --> A1[WAL日志] B --> B1[流复制] C --> C1[流复制] D[客户端] --> A D --> B D --> C

10.2 Patroni高可用

Patroni是PostgreSQL的高可用解决方案，支持自动故障转移：

scope: postgres
namespace: postgres
name: postgres01

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.101:8008

etcd:
  host: 192.168.1.100:2379

十一、PostgreSQL与MySQL对比

特性 PostgreSQL MySQL
JSON支持 完整支持，支持索引 有限支持
全文搜索 内置支持 需要插件
扩展性 强大扩展机制 有限扩展
并发控制 MVCC快照隔离 MVCC读已提交

十二、总结

PostgreSQL是一款功能强大的数据库，适合需要复杂查询、JSON支持和高度扩展性的场景。掌握PostgreSQL的高级特性，能够构建更灵活、更强大的数据密集型应用。