⚡

PostgreSQL优化 postgres-optimization

PostgreSQL优化技能专注于提升数据库性能，包括索引策略、查询计划分析、表分区、JSONB数据操作和连接池配置。关键词：PostgreSQL、数据库优化、索引、查询性能、分区、JSONB、连接池、性能调优、DevOps。

DevOps 0 次安装 0 次浏览 更新于 3/8/2026

name: postgres-optimization description: PostgreSQL优化，包括索引、查询计划、分区、JSONB操作和连接池

PostgreSQL优化

索引策略

-- B-tree索引用于相等和范围查询（默认）
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- 复合索引（列顺序重要：相等列优先，范围列最后）
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 部分索引（更小，适用于过滤查询）
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- 覆盖索引（完全避免表查找）
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name, avatar_url);

-- GIN索引用于JSONB包含查询
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GiST索引用于全文搜索
CREATE INDEX idx_articles_search ON articles USING GiST (
  to_tsvector('english', title || ' ' || body)
);

-- 并发索引创建（无表锁）
CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table (col);

读取查询计划

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 20;

查询计划中需要关注的关键点：

  • 在大型表上出现Seq Scan表示缺少索引
  • Nested Loop与高行估计值表明缺少连接索引
  • 没有Index Scan的Sort意味着排序在内存或磁盘上进行
  • Buffers: shared hit与shared read显示缓存效率

分区

CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type  TEXT NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 在每个分区上创建索引（PostgreSQL 11+自动继承）
CREATE INDEX ON events (created_at, event_type);

当查询持续基于分区键过滤时，对超过1000万行的表进行分区。

JSONB操作

-- 查询嵌套JSONB字段
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND (metadata ->> 'price')::numeric < 500;

-- 更新嵌套JSONB
UPDATE products
SET metadata = jsonb_set(metadata, '{stock}', to_jsonb(stock - 1))
WHERE id = 'abc';

-- 聚合JSONB数组
SELECT id, jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products
WHERE metadata ? 'tags';

连接池

# pgbouncer.ini
[databases]
app = host=localhost port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 300

Web应用程序使用事务级连接池。使用会话级连接池用于需要预编译语句或临时表的应用。

常见调优参数

-- 检查慢查询
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查找未使用的索引
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

反模式

  • 在所有列上创建索引，而不是分析实际查询模式
  • 当只需要少数列时使用SELECT *
  • 不使用EXPLAIN ANALYZE验证索引使用情况
  • 在JSONB中存储大型二进制对象，而使用具有适当类型的单独表更好
  • 缺少连接池（每个连接占用服务器约10MB内存）
  • 在高峰时段运行VACUUM FULL（锁定整个表）

检查清单

  • [ ] 索引匹配实际查询模式（检查pg_stat_statements）
  • [ ] 复合索引顺序：相等列、排序列、范围列
  • [ ] 对所有关键查询运行EXPLAIN ANALYZE
  • [ ] 对频繁过滤的子集使用部分索引
  • [ ] 在PostgreSQL前使用连接池器（PgBouncer/pgcat）
  • [ ] 对超过1000万行的表考虑分区
  • [ ] 识别并删除未使用的索引
  • [ ] 启用pg_stat_statements用于查询性能监控