PostgreSQL 15进阶：这些技巧你可能不知道

PostgreSQL 15于2022年10月发布，带来了很多新特性。

本文分享PostgreSQL 15的进阶技巧，包括性能优化、索引技巧、查询优化、新特性使用、运维技巧等，帮你更好地使用PostgreSQL。

一、PostgreSQL 15新特性回顾

先简单回顾一下PostgreSQL 15的重要新特性。

1. 合并行（MERGE）

PostgreSQL 15终于支持了标准SQL的MERGE语句。

MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET name = s.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (s.id, s.name);

MERGE可以在一条语句中完成插入和更新，比之前的INSERT ... ON CONFLICT更标准、更灵活。

2. 行级权限（RLS）增强

行级安全策略更强大了。

支持更复杂的策略表达式
性能优化
更好的错误提示

3. 逻辑复制增强

支持表的列过滤
支持行过滤（WHERE子句）
支持两阶段提交
更灵活的复制配置

4. 性能提升

排序性能提升
并行查询优化
索引扫描优化
内存管理改进

5. 其他改进

JSON功能增强
分区表改进
监控视图增强
安全性提升

二、性能优化技巧

1. 合理使用索引

索引是性能优化的关键，但不是越多越好。

创建索引：

-- 普通索引
CREATE INDEX idx_users_name ON users(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_users_age_name ON users(age, name);

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

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

-- GIN索引（适合数组、JSON、全文搜索）
CREATE INDEX idx_users_tags ON users USING GIN(tags);

索引使用技巧：

复合索引的列顺序很重要，把等值查询的列放前面
不要在低选择性的列上建索引（如性别）
定期检查无用索引，删除不用的
索引不是越多越好，会影响写入性能

检查索引使用情况：

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

2. 查询优化

用EXPLAIN分析查询：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 ORDER BY name;

看执行计划，注意：

Seq Scan：全表扫描，数据量大时要避免
Index Scan：索引扫描，好
Bitmap Heap Scan：位图扫描，也不错
Nested Loop：嵌套循环，小表用可以
Hash Join：哈希连接，大表用更好
Sort：排序，数据量大时慢

常见的查询优化：

避免SELECT *，只查需要的列
用LIMIT限制返回行数
避免在WHERE子句中对列用函数（会导致索引失效）
用UNION ALL代替UNION（如果不需要去重）
大表分页用游标或keyset分页，不要用OFFSET

不好的写法：

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 索引在email上，但用了LOWER函数，索引失效

好的写法：

SELECT * FROM users WHERE email ILIKE 'test@example.com';
-- 或建表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

3. 分页优化

深分页（OFFSET很大）会很慢。

-- 慢：OFFSET 100000 需要扫描100000行
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100000;

-- 快：用keyset分页
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;

keyset分页利用索引，速度快很多。

4. 批量操作

批量操作比单条操作快很多。

-- 批量插入
INSERT INTO users (name, email) VALUES
    ('张三', 'zhang@example.com'),
    ('李四', 'li@example.com'),
    ('王五', 'wang@example.com');

-- 批量更新
UPDATE users SET status = 'active' WHERE id IN (1, 2, 3);

-- 批量删除
DELETE FROM users WHERE id IN (1, 2, 3);

如果数据量特别大，可以用COPY命令。

三、高级数据类型技巧

1. JSON/JSONB

PostgreSQL对JSON支持很好，JSONB是二进制格式，支持索引。

-- 创建JSONB列
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    attributes jsonb
);

-- 插入
INSERT INTO products (name, attributes) VALUES
    ('手机', '{"color": "black", "storage": 128, "brand": "Apple"}');

-- 查询JSON字段
SELECT name, attributes->>'color' as color
FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- JSONB索引
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

-- 特定路径的索引
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

JSONB的操作符：

->：获取JSON对象字段，返回JSON
->>：获取JSON对象字段，返回文本
@>：包含
?：键存在
||：合并

2. 数组

PostgreSQL支持数组类型。

-- 创建数组列
CREATE TABLE posts (
    id serial PRIMARY KEY,
    title text,
    tags text[]
);

-- 插入
INSERT INTO posts (title, tags) VALUES
    ('PostgreSQL入门', ARRAY['数据库', '教程', 'PostgreSQL']);

-- 查询（包含某个标签）
SELECT * FROM posts WHERE tags @> ARRAY['数据库'];

-- 数组索引
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- 数组函数
SELECT 
    array_length(tags, 1) as tag_count,
    array_to_string(tags, ', ') as tags_str
FROM posts;

3. 范围类型

范围类型适合表示时间段、数值范围等。

-- 创建范围列
CREATE TABLE reservations (
    id serial PRIMARY KEY,
    room_id int,
    during tsrange  -- 时间范围
);

-- 插入
INSERT INTO reservations (room_id, during) VALUES
    (1, '[2022-11-01 09:00, 2022-11-01 12:00)');

-- 查询重叠的范围
SELECT * FROM reservations
WHERE during && '[2022-11-01 10:00, 2022-11-01 11:00)';

-- 范围索引
CREATE INDEX idx_reservations_during ON reservations USING GIST(during);

四、分区表技巧

PostgreSQL支持表分区，大表用分区可以提升性能。

1. 创建分区表

-- 创建主表
CREATE TABLE orders (
    id serial,
    user_id int,
    amount numeric,
    created_at timestamp
) PARTITION BY RANGE (created_at);

-- 创建分区（按月）
CREATE TABLE orders_2022_11 PARTITION OF orders
    FOR VALUES FROM ('2022-11-01') TO ('2022-12-01');

CREATE TABLE orders_2022_12 PARTITION OF orders
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

2. 分区的好处

查询只扫描相关分区，速度快
可以快速删除旧数据（DROP分区）
可以把旧分区移到慢存储
索引更小，更高效

3. 分区注意事项

分区键要选好，通常是时间或ID
分区不要太多也不要太少
主键必须包含分区键
外键对分区表支持有限

五、事务和并发技巧

1. 事务隔离级别

PostgreSQL支持四种隔离级别：

READ UNCOMMITTED（实际和READ COMMITTED一样）
READ COMMITTED（默认）
REPEATABLE READ
SERIALIZABLE

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 你的操作
COMMIT;

根据业务需求选择隔离级别，SERIALIZABLE最安全但性能最低。

2. 乐观锁

用版本号实现乐观锁。

-- 表加version字段
ALTER TABLE users ADD COLUMN version int DEFAULT 0;

-- 更新时检查版本
UPDATE users 
SET name = '新名字', version = version + 1
WHERE id = 1 AND version = 5;

-- 如果影响行数为0，说明被别人修改了

3. 悲观锁

用SELECT ... FOR UPDATE实现悲观锁。

BEGIN;
-- 锁定行
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 修改
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

注意：FOR UPDATE会阻塞其他事务，要尽快提交。

4. 避免死锁

死锁的常见原因：

多个事务以不同顺序锁定资源
长事务持有锁太久

避免方法：

统一锁定顺序
事务尽量短
设置锁超时
用乐观锁代替悲观锁

六、运维技巧

1. 监控

PostgreSQL有很多监控视图。

-- 活动连接
SELECT * FROM pg_stat_activity;

-- 慢查询（需要开启pg_stat_statements）
SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 表大小
SELECT 
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- 索引大小
SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

2. VACUUM和ANALYZE

PostgreSQL用MVCC，需要定期VACUUM。

-- 手动VACUUM
VACUUM ANALYZE users;

-- 全量VACUUM（会锁表，谨慎使用）
VACUUM FULL users;

-- 只分析统计信息
ANALYZE users;

注意：

autovacuum默认开启，一般不用手动VACUUM
VACUUM FULL会锁表，不要在高峰期用
大表可以用pg_repack在线重建

3. 备份

# 逻辑备份
pg_dump -U postgres dbname > backup.sql

# 恢复
psql -U postgres dbname < backup.sql

# 物理备份（PITR）
# 配置archive_mode和archive_command
# 用pg_basebackup做基础备份

重要的数据库，一定要有备份策略，定期测试恢复。

4. 连接池

PostgreSQL的连接是进程，连接数不能太多。

用连接池：

PgBouncer：轻量级连接池
应用层连接池（如HikariCP）

# PgBouncer配置示例
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
max_client_conn = 1000
default_pool_size = 20

七、实用技巧

1. 生成测试数据

-- 生成100万条测试数据
INSERT INTO users (name, email, age)
SELECT 
    'user' || i,
    'user' || i || '@example.com',
    floor(random() * 80 + 18)::int
FROM generate_series(1, 1000000) as i;

2. 查看表结构

-- 查看表结构
\d users

-- 查看表的详细信息
\d+ users

-- 查看所有表
\dt

-- 查看索引
\di

3. 时间处理

-- 当前时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- 时间加减
SELECT NOW() - INTERVAL '1 day';
SELECT NOW() + INTERVAL '1 month';

-- 格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

-- 时区转换
SELECT NOW() AT TIME ZONE 'UTC';

4. 窗口函数

窗口函数很强大，可以做复杂分析。

-- 每组前N名
SELECT * FROM (
    SELECT 
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
) t WHERE rn <= 3;

-- 累计求和
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as cumulative
FROM daily_sales;

-- 移动平均
SELECT 
    date,
    amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_7d
FROM daily_sales;

八、常见坑

1. 字符集问题

数据库创建时指定UTF8
客户端和服务端字符集一致
避免用SQL_ASCII

CREATE DATABASE mydb 
    ENCODING 'UTF8' 
    LC_COLLATE 'en_US.UTF8' 
    LC_CTYPE 'en_US.UTF8' 
    TEMPLATE template0;

2. 时区问题

用timestamptz代替timestamp
统一用UTC存储
显示时再转换时区

3. 自增ID问题

用serial或identity列
分布式系统考虑用UUID或雪花ID
自增ID不连续是正常的（事务回滚会消耗ID）

4. 空字符串和NULL

空字符串''和NULL是不同的
查询时注意区分
用COALESCE处理NULL

SELECT COALESCE(name, '未知') FROM users;

九、写在最后

PostgreSQL是一个强大的数据库，15版本带来了很多改进。

掌握这些进阶技巧，可以让你更好地使用PostgreSQL：

合理使用索引，优化查询
用好高级数据类型（JSONB、数组、范围）
大表用分区
正确处理事务和并发
做好监控和运维

PostgreSQL的功能很多，本文只是冰山一角。建议多实践，多阅读官方文档，不断积累经验。

最后，用一句话总结："PostgreSQL很强大，用好它需要不断学习和实践。掌握进阶技巧，让你的数据库又快又稳。"

愿你用好PostgreSQL，构建高性能的应用。