PostgreSQL优化方案全面指南从基础配置调整到高级查询性能提升再到索引优化的完整数据库性能调优实战手册

威震华夏关云长

引言

PostgreSQL是一个功能强大的开源关系型数据库管理系统，被广泛应用于各种规模的应用程序中。随着数据量的增长和用户访问的增加，数据库性能优化变得至关重要。本文旨在提供一个全面的PostgreSQL优化指南，从基础配置调整到高级查询性能提升，再到索引优化，帮助数据库管理员和开发人员掌握完整的数据库性能调优实战技能。

PostgreSQL性能评估基础

在开始优化之前，我们需要首先评估当前数据库的性能状况。这一步是为了确定性能瓶颈，为后续的优化工作提供方向。

性能指标监控

PostgreSQL提供了多种方式来监控性能指标：

• pg_stat_activity：查看当前活动的会话和查询

2. SELECT * FROM pg_stat_activity;

复制代码

• pg_stat_database：数据库级别的统计信息

2. SELECT * FROM pg_stat_database;

复制代码

• pg_stat_user_tables：用户表的统计信息

2. SELECT * FROM pg_stat_user_tables;

复制代码

• pg_stat_user_indexes：用户索引的统计信息

2. SELECT * FROM pg_stat_user_indexes;

复制代码

查询性能分析

使用EXPLAIN和EXPLAIN ANALYZE来分析查询执行计划：

2. EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

复制代码

性能瓶颈识别

常见的性能瓶颈包括：

• CPU利用率高
• 内存不足
• 磁盘I/O瓶颈
• 网络延迟
• 锁等待
• 慢查询

基准测试

使用工具如pgbench进行基准测试：

2. pgbench -i -s 50 mydb  # 初始化测试数据库
3. pgbench -c 10 -j 4 -t 1000 mydb  # 运行基准测试

复制代码

基础配置调整

PostgreSQL的性能在很大程度上取决于配置参数的合理设置。这些参数通常在postgresql.conf文件中进行配置。

内存配置

shared_buffers参数设置PostgreSQL用于共享内存缓冲区的大小，这是最重要的内存参数之一。

2. # 默认值通常较小，可以设置为系统内存的25%
3. shared_buffers = 4GB

复制代码

work_mem指定用于排序和哈希操作的内存量。

2. # 默认值通常为4MB，可以根据复杂查询需求增加
3. work_mem = 16MB

复制代码

maintenance_work_mem用于维护操作（如VACUUM、CREATE INDEX等）的内存。

2. # 可以设置为系统内存的5-10%
3. maintenance_work_mem = 1GB

复制代码

effective_cache_size告诉PostgreSQL系统有多少内存可用于磁盘缓存。

2. # 通常设置为系统内存的50-75%
3. effective_cache_size = 12GB

复制代码

连接配置

max_connections设置最大并发连接数。

2. # 根据应用需求设置，但不宜过高
3. max_connections = 200

复制代码

使用连接池（如PgBouncer）来管理连接，减少连接开销。

2. # PgBouncer配置示例
3. [databases]
4. mydb = host=localhost port=5432 dbname=mydb
6. [pgbouncer]
7. pool_mode = transaction
8. max_client_conn = 1000
9. default_pool_size = 20

复制代码

磁盘配置

wal_level设置WAL（Write-Ahead Logging）的级别。

2. # 根据是否需要复制或归档设置
3. wal_level = replica

复制代码

控制WAL文件的数量和大小。

2. # PostgreSQL 10+
3. max_wal_size = 4GB
4. min_wal_size = 1GB
6. # PostgreSQL 9.6及以下
7. checkpoint_segments = 32

复制代码

random_page_cost估计非顺序获取磁盘页面的成本。

2. # 对于SSD，可以设置较低的值
3. random_page_cost = 1.1

复制代码

日志配置

记录执行时间超过指定值的SQL语句。

2. # 记录执行时间超过1秒的查询
3. log_min_duration_statement = 1000

复制代码

设置日志行的前缀格式。

2. log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

复制代码

自动清理配置

自动清理死元数据。

2. # 启用自动清理
3. autovacuum = on
5. # 自动清理阈值
6. autovacuum_vacuum_threshold = 50
7. autovacuum_analyze_threshold = 50
9. # 自动清理比例
10. autovacuum_vacuum_scale_factor = 0.2
11. autovacuum_analyze_scale_factor = 0.1

复制代码

高级查询性能提升

在基础配置调整后，我们需要关注查询本身的性能优化。

查询计划分析

使用EXPLAIN分析查询计划：

2. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
3. SELECT * FROM orders WHERE customer_id = 12345;

复制代码

关键指标包括：

• 执行时间
• 扫描类型（Seq Scan, Index Scan等）
• 行数估计与实际行数
• 缓冲区命中情况

• 全表扫描：对于大表，应避免全表扫描
• 嵌套循环：当连接表较大时，效率低下
• 排序操作：大表排序消耗大量资源
• 高估/低估行数：导致优化器选择不当的执行计划

SQL优化技巧

2. -- INNER JOIN
3. SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id;
5. -- LEFT JOIN
6. SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;
8. -- LATERAL JOIN
9. SELECT * FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.id LIMIT 5) o;

复制代码

2. WITH active_customers AS (
3.     SELECT customer_id, COUNT(*) as order_count
4.     FROM orders
5.     WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
6.     GROUP BY customer_id
7.     HAVING COUNT(*) > 5
8. )
9. SELECT * FROM customers c JOIN active_customers ac ON c.id = ac.customer_id;

复制代码

2. -- 不推荐
3. SELECT * FROM orders;
5. -- 推荐
6. SELECT id, customer_id, order_date, total_amount FROM orders;

复制代码

2. -- 获取前10条记录
3. SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

复制代码

2. -- 不推荐：单行插入
3. INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, NOW(), 100.00);
4. INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, NOW(), 200.00);
6. -- 推荐：批量插入
7. INSERT INTO orders (customer_id, order_date, total_amount) VALUES
8.     (1, NOW(), 100.00),
9.     (2, NOW(), 200.00);

复制代码

并行查询

PostgreSQL支持并行查询，可以显著提高大型查询的性能。

2. # 设置最大并行工作进程数
3. max_parallel_workers = 8;
5. # 设置每个查询的最大并行工作进程数
6. max_parallel_workers_per_gather = 4;
8. # 设置并行查询的最小表大小
9. min_parallel_table_scan_size = 8MB;
10. min_parallel_index_scan_size = 8MB;

复制代码

2. -- 设置并行工作进程数
3. SET max_parallel_workers_per_gather = 4;
5. -- 执行并行查询
6. EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

复制代码

查询重写与优化

2. -- 创建物化视图
3. CREATE MATERIALIZED VIEW order_summary AS
4. SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
5. FROM orders
6. GROUP BY customer_id;
8. -- 刷新物化视图
9. REFRESH MATERIALIZED VIEW order_summary;
11. -- 查询物化视图
12. SELECT * FROM order_summary WHERE order_count > 10;

复制代码

2. -- 为特定条件创建部分索引
3. CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

复制代码

2. -- 创建表达式索引
3. CREATE INDEX idx_user_lower_email ON users (lower(email));

复制代码

查询优化工具

启用并使用pg_stat_statements扩展来跟踪SQL执行统计信息：

2. -- 创建扩展
3. CREATE EXTENSION pg_stat_statements;
5. -- 查看最耗时的SQL
6. SELECT query, calls, total_time, mean_time, rows
7. FROM pg_stat_statements
8. ORDER BY total_time DESC
9. LIMIT 10;

复制代码

使用auto_explain扩展自动记录慢查询的执行计划：

2. # postgresql.conf
3. shared_preload_libraries = 'auto_explain'
4. auto_explain.log_min_duration = '1s'
5. auto_explain.log_analyze = true
6. auto_explain.log_buffers = true
7. auto_explain.log_format = 'json'

复制代码

索引优化

索引是提高查询性能的关键因素，但不当的索引使用可能导致性能下降。

索引类型

B-Tree是PostgreSQL中最常用的索引类型，适用于大多数情况。

2. -- 创建B-Tree索引
3. CREATE INDEX idx_users_email ON users (email);
5. -- 复合B-Tree索引
6. CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

复制代码

哈希索引适用于等值比较，但不支持范围查询。

2. -- 创建哈希索引
3. CREATE INDEX idx_users_username_hash ON users USING hash (username);

复制代码

GiST（Generalized Search Tree）索引适用于复杂数据类型和空间数据。

2. -- 为几何数据创建GiST索引
3. CREATE INDEX idx_locations_geom ON locations USING gist (geom);

复制代码

SP-GiST（Space-Partitioned GiST）适用于空间分区数据。

2. -- 为电话号码创建SP-GiST索引
3. CREATE INDEX idx_users_phone ON users USING spgist (phone_number);

复制代码

GIN（Generalized Inverted Index）索引适用于多值类型，如数组、jsonb和全文搜索。

2. -- 为数组创建GIN索引
3. CREATE INDEX idx_users_tags ON users USING gin (tags);
5. -- 为jsonb创建GIN索引
6. CREATE INDEX idx_products_attributes ON products USING gin (attributes);

复制代码

BRIN（Block Range Index）索引适用于线性排序的大表。

2. -- 为按时间排序的大表创建BRIN索引
3. CREATE INDEX idx_logs_created_at ON logs USING brin (created_at);

复制代码

索引策略

• 经常用于WHERE条件的列
• 经常用于JOIN条件的列
• 经常用于ORDER BY的列
• 经常用于GROUP BY的列

在复合索引中，列的顺序很重要：

2. -- 好的复合索引顺序（高选择性列在前）
3. CREATE INDEX idx_users_status_created ON users (status, created_at);
5. -- 不好的复合索引顺序（低选择性列在前）
6. CREATE INDEX idx_users_created_status ON users (created_at, status);

复制代码

只为表的一部分数据创建索引：

2. -- 只为活跃用户创建索引
3. CREATE INDEX idx_active_users_email ON users (email) WHERE status = 'active';
5. -- 只为最近的数据创建索引
6. CREATE INDEX idx_recent_orders ON orders (customer_id) WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

复制代码

创建包含查询所需所有列的索引：

2. -- 创建覆盖索引
3. CREATE INDEX idx_orders_customer_total ON orders (customer_id) INCLUDE (total_amount);
5. -- 查询可以使用覆盖索引，避免表访问
6. SELECT customer_id, total_amount FROM orders WHERE customer_id = 12345;

复制代码

索引维护

定期重建索引以提高性能：

2. -- 重建单个索引
3. REINDEX INDEX idx_users_email;
5. -- 重建表的所有索引
6. REINDEX TABLE users;
8. -- 并行重建索引
9. REINDEX INDEX CONCURRENTLY idx_users_email;

复制代码

收集索引统计信息以帮助查询优化器：

2. -- 分析表和索引
3. ANALYZE users;
5. -- 分析特定列
6. ANALYZE users (email);

复制代码

监控索引使用情况：

2. -- 查看索引使用统计
3. SELECT * FROM pg_stat_user_indexes;
5. -- 查找未使用的索引
6. SELECT schemaname, tablename, indexname, idx_scan
7. FROM pg_stat_user_indexes
8. WHERE idx_scan = 0
9. ORDER BY schemaname, tablename, indexname;

复制代码

索引优化技巧

过多的索引会降低写入性能并占用存储空间：

2. -- 查找冗余索引
3. SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
4.        (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
5.        (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
6. FROM (
7.     SELECT indexrelid::regclass as idx, indrelid::regclass as table,
8.            array_to_string(indkey, ' ') as cols,
9.            indpred is not null as partial
10.     FROM pg_index
11. ) sub
12. GROUP BY table, cols, partial
13. HAVING count(*) > 1;

复制代码

2. -- 为不区分大小写的搜索创建表达式索引
3. CREATE INDEX idx_users_lower_email ON users (lower(email));
5. -- 使用表达式索引
6. SELECT * FROM users WHERE lower(email) = 'test@example.com';

复制代码

2. -- 为NULL值创建条件索引
3. CREATE INDEX idx_users_optional_email ON users (email) WHERE email IS NOT NULL;

复制代码

表和分区优化

表设计和分区策略对数据库性能有重大影响。

表设计优化

2. -- 使用适当的数据类型
3. CREATE TABLE users (
4.     id SERIAL PRIMARY KEY,
5.     username VARCHAR(50) NOT NULL,
6.     email VARCHAR(255) NOT NULL,
7.     created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
8.     is_active BOOLEAN NOT NULL DEFAULT true,
9.     login_count INTEGER NOT NULL DEFAULT 0
10. );

复制代码

2. -- 规范化设计（减少冗余）
3. CREATE TABLE customers (
4.     id SERIAL PRIMARY KEY,
5.     name VARCHAR(100) NOT NULL,
6.     email VARCHAR(255) NOT NULL UNIQUE
7. );
9. CREATE TABLE orders (
10.     id SERIAL PRIMARY KEY,
11.     customer_id INTEGER NOT NULL REFERENCES customers(id),
12.     order_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
13.     total_amount NUMERIC(10,2) NOT NULL
14. );
16. -- 适度反规范化（提高查询性能）
17. CREATE TABLE orders (
18.     id SERIAL PRIMARY KEY,
19.     customer_id INTEGER NOT NULL REFERENCES customers(id),
20.     customer_name VARCHAR(100) NOT NULL, -- 冗余存储客户名称
21.     order_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
22.     total_amount NUMERIC(10,2) NOT NULL
23. );

复制代码

2. -- 创建父表
3. CREATE TABLE users (
4.     id SERIAL PRIMARY KEY,
5.     username VARCHAR(50) NOT NULL,
6.     email VARCHAR(255) NOT NULL,
7.     created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
8. );
10. -- 创建子表
11. CREATE TABLE premium_users (
12.     subscription_level VARCHAR(20) NOT NULL,
13.     expiry_date DATE NOT NULL
14. ) INHERITS (users);
16. -- 查询所有用户（包括子表）
17. SELECT * FROM users;

复制代码

表分区

表分区是将大表分割成更小、更易管理的部分的技术。

2. -- 创建主表
3. CREATE TABLE orders (
4.     id SERIAL,
5.     order_date TIMESTAMP WITH TIME ZONE NOT NULL,
6.     customer_id INTEGER NOT NULL,
7.     total_amount NUMERIC(10,2) NOT NULL
8. ) PARTITION BY RANGE (order_date);
10. -- 创建分区
11. CREATE TABLE orders_2023_q1 PARTITION OF orders
12.     FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
14. CREATE TABLE orders_2023_q2 PARTITION OF orders
15.     FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
17. CREATE TABLE orders_2023_q3 PARTITION OF orders
18.     FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
20. CREATE TABLE orders_2023_q4 PARTITION OF orders
21.     FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

复制代码

2. -- 创建主表
3. CREATE TABLE users (
4.     id SERIAL,
5.     username VARCHAR(50) NOT NULL,
6.     email VARCHAR(255) NOT NULL,
7.     country_code VARCHAR(2) NOT NULL
8. ) PARTITION BY LIST (country_code);
10. -- 创建分区
11. CREATE TABLE users_us PARTITION OF users
12.     FOR VALUES IN ('US');
14. CREATE TABLE users_ca PARTITION OF users
15.     FOR VALUES IN ('CA');
17. CREATE TABLE users_uk PARTITION OF users
18.     FOR VALUES IN ('UK');
20. CREATE TABLE users_other PARTITION OF users
21.     DEFAULT;

复制代码

2. -- 创建主表
3. CREATE TABLE logs (
4.     id SERIAL,
5.     log_time TIMESTAMP WITH TIME ZONE NOT NULL,
6.     message TEXT NOT NULL
7. ) PARTITION BY HASH (id);
9. -- 创建分区
10. CREATE TABLE logs_p0 PARTITION OF logs
11.     FOR VALUES WITH (MODULUS 4, REMAINDER 0);
13. CREATE TABLE logs_p1 PARTITION OF logs
14.     FOR VALUES WITH (MODULUS 4, REMAINDER 1);
16. CREATE TABLE logs_p2 PARTITION OF logs
17.     FOR VALUES WITH (MODULUS 4, REMAINDER 2);
19. CREATE TABLE logs_p3 PARTITION OF logs
20.     FOR VALUES WITH (MODULUS 4, REMAINDER 3);

复制代码

2. -- 创建新分区
3. CREATE TABLE orders_2024_q1 PARTITION OF orders
4.     FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
6. -- 分离分区
7. ALTER TABLE orders DETACH PARTITION orders_2023_q1;
9. -- 附加分区
10. ALTER TABLE orders ATTACH PARTITION orders_2023_q1
11.     FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

复制代码

表维护

定期清理死元数据：

2. -- 手动VACUUM
3. VACUUM users;
5. -- 分析表并更新统计信息
6. VACUUM ANALYZE users;
8. -- 并行VACUUM
9. VACUUM (PARALLEL 4) users;

复制代码

2. # 调整自动清理参数
3. autovacuum_vacuum_threshold = 1000
4. autovacuum_analyze_threshold = 500
5. autovacuum_vacuum_scale_factor = 0.1
6. autovacuum_analyze_scale_factor = 0.05
7. autovacuum_vacuum_cost_limit = 2000
8. autovacuum_vacuum_cost_delay = 10ms

复制代码

2. -- 为特定表设置自动清理参数
3. ALTER TABLE orders SET (
4.     autovacuum_vacuum_threshold = 1000,
5.     autovacuum_analyze_threshold = 500,
6.     autovacuum_vacuum_scale_factor = 0.1,
7.     autovacuum_analyze_scale_factor = 0.05
8. );

复制代码

表空间管理

使用表空间将数据分布到不同的存储设备：

2. -- 创建表空间
3. CREATE TABLESPACE fast_space LOCATION '/mnt/fast_ssd/pgdata';
4. CREATE TABLESPACE archive_space LOCATION '/mnt/slow_hdd/pgdata';
6. -- 在特定表空间创建表
7. CREATE TABLE active_data (
8.     id SERIAL PRIMARY KEY,
9.     data TEXT NOT NULL
10. ) TABLESPACE fast_space;
12. -- 将表移动到不同表空间
13. ALTER TABLE archive_data SET TABLESPACE archive_space;

复制代码

高级特性优化

PostgreSQL提供了许多高级特性，可以用来提高性能。

复制优化

2. # 主服务器配置
3. wal_level = replica
4. max_wal_senders = 5
5. max_replication_slots = 5
7. # 从服务器配置 (recovery.conf)
8. standby_mode = on
9. primary_conninfo = 'host=primary port=5432 user=replicator password=secret'
10. recovery_target_timeline = 'latest'

复制代码

2. -- 设置同步复制
3. ALTER SYSTEM SET synchronous_commit = 'on';
4. ALTER SYSTEM SET synchronous_standby_names = 'standby1,standby2';
6. -- 重新加载配置
7. SELECT pg_reload_conf();

复制代码

2. -- 在发布服务器上创建发布
3. CREATE PUBLICATION sales_publication FOR TABLE orders, customers;
5. -- 在订阅服务器上创建订阅
6. CREATE SUBSCRIPTION sales_subscription
7. CONNECTION 'host=publisher dbname=sales user=replicator password=secret'
8. PUBLICATION sales_publication;

复制代码

并行查询优化

2. # 设置并行查询参数
3. max_parallel_workers = 8
4. max_parallel_workers_per_gather = 4
5. max_parallel_maintenance_workers = 4
6. parallel_tuple_cost = 0.1
7. parallel_setup_cost = 1000.0
8. min_parallel_table_scan_size = 8MB
9. min_parallel_index_scan_size = 8MB

复制代码

2. -- 使用并行查询
3. SET max_parallel_workers_per_gather = 4;
5. -- 创建适合并行查询的表
6. CREATE TABLE large_table (
7.     id SERIAL PRIMARY KEY,
8.     data TEXT NOT NULL
9. ) WITH (parallel_workers = 4);
11. -- 并行创建索引
12. CREATE INDEX CONCURRENTLY idx_large_table_data ON large_table (data) WITH (parallel_workers = 4);

复制代码

连接池优化

2. # PgBouncer配置
3. [databases]
4. mydb = host=localhost port=5432 dbname=mydb
6. [pgbouncer]
7. pool_mode = transaction
8. max_client_conn = 1000
9. default_pool_size = 20
10. reserve_pool = 5
11. reserve_pool_timeout = 3
12. server_idle_timeout = 30

复制代码

• 使用事务池模式处理短事务
• 使用会话池模式处理长事务
• 根据应用负载调整池大小
• 监控连接池使用情况

缓存优化

2. # 配置PostgreSQL缓存
3. shared_buffers = 4GB
4. effective_cache_size = 12GB

复制代码

2. # Python示例：使用Redis缓存查询结果
3. import redis
4. import psycopg2
5. import json
7. # 连接Redis
8. redis_client = redis.StrictRedis(host='localhost', port=6379, db=0)
10. # 连接PostgreSQL
11. pg_conn = psycopg2.connect("dbname=mydb user=myuser password=mypassword")
13. def get_user_with_cache(user_id):
14.     # 尝试从Redis缓存获取
15.     cached_user = redis_client.get(f"user:{user_id}")
16.     if cached_user:
17.         return json.loads(cached_user)
18.    
19.     # 从数据库获取
20.     cursor = pg_conn.cursor()
21.     cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
22.     user = cursor.fetchone()
23.    
24.     # 存入Redis缓存，过期时间1小时
25.     if user:
26.         redis_client.setex(f"user:{user_id}", 3600, json.dumps(user))
27.    
28.     return user

复制代码

查询优化扩展

2. -- 安装pg_hint_plan扩展
3. CREATE EXTENSION pg_hint_plan;
5. -- 使用提示优化查询
6. /*+ IndexScan(users idx_users_email) */
7. SELECT * FROM users WHERE email = 'test@example.com';

复制代码

2. -- 安装pg_prewarm扩展
3. CREATE EXTENSION pg_prewarm;
5. -- 预热表到缓存
6. SELECT pg_prewarm('users');
8. -- 预热索引到缓存
9. SELECT pg_prewarm('idx_users_email');

复制代码

监控与维护

持续监控和定期维护是保持PostgreSQL高性能的关键。

性能监控

2. -- 创建扩展
3. CREATE EXTENSION pg_stat_statements;
5. -- 查看最耗时的查询
6. SELECT query, calls, total_time, mean_time, rows
7. FROM pg_stat_statements
8. ORDER BY total_time DESC
9. LIMIT 10;
11. -- 重置统计信息
12. SELECT pg_stat_statements_reset();

复制代码

2. -- 查看当前活动会话
3. SELECT pid, datname, usename, application_name, client_addr, state, query
4. FROM pg_stat_activity
5. WHERE state != 'idle';
7. -- 查看长时间运行的查询
8. SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
9. FROM pg_stat_activity
10. WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

复制代码

2. -- 安装扩展
3. CREATE EXTENSION pg_stat_kcache;
5. -- 查看查询资源使用情况
6. SELECT query, calls, total_time, mean_time,
7.        shared_blks_hit, shared_blks_read,
8.        local_blks_hit, local_blks_read,
9.        temp_blks_read, temp_blks_written
10. FROM pg_stat_statements
11. ORDER BY total_time DESC
12. LIMIT 10;

复制代码

系统监控

2. # 安装pg_top
3. sudo apt-get install ptop
5. # 运行pg_top
6. pg_top -d mydb -U myuser

复制代码

2. # 安装pgBadger
3. sudo apt-get install pgbadger
5. # 生成报告
6. pgbadger /var/log/postgresql/postgresql-*.log -o report.html

复制代码

2. # prometheus.yml配置示例
3. scrape_configs:
4.   - job_name: 'postgres'
5.     static_configs:
6.       - targets: ['localhost:9187']

复制代码

定期维护任务

2. -- 配置自动VACUUM
3. ALTER SYSTEM SET autovacuum = on;
4. ALTER SYSTEM SET autovacuum_vacuum_threshold = 1000;
5. ALTER SYSTEM SET autovacuum_analyze_threshold = 500;
7. -- 重新加载配置
8. SELECT pg_reload_conf();

复制代码

2. -- 重建表的所有索引
3. REINDEX TABLE users;
5. -- 并行重建索引
6. REINDEX INDEX CONCURRENTLY idx_users_email;

复制代码

2. -- 更新表统计信息
3. ANALYZE users;
5. -- 更新特定列统计信息
6. ANALYZE users (email);

复制代码

数据库健康检查

2. -- 安装pgstattuple扩展
3. CREATE EXTENSION pgstattuple;
5. -- 检查表膨胀
6. SELECT * FROM pgstattuple('users');

复制代码

2. -- 查找未使用的索引
3. SELECT schemaname, tablename, indexname, idx_scan
4. FROM pg_stat_user_indexes
5. WHERE idx_scan = 0
6. ORDER BY schemaname, tablename, indexname;

复制代码

2. -- 查找长事务
3. SELECT pid, now() - xact_start AS duration, state, query
4. FROM pg_stat_activity
5. WHERE (now() - xact_start) > interval '1 hour'
6. AND state != 'idle';

复制代码

实战案例

通过实际案例展示PostgreSQL优化过程。

案例一：电商网站订单查询优化

某电商网站订单查询缓慢，特别是在高峰期。

2. -- 慢查询示例
3. SELECT o.id, o.order_date, c.name, o.total_amount
4. FROM orders o
5. JOIN customers c ON o.customer_id = c.id
6. WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
7. ORDER BY o.order_date DESC
8. LIMIT 100;

复制代码

2. EXPLAIN (ANALYZE, BUFFERS)
3. SELECT o.id, o.order_date, c.name, o.total_amount
4. FROM orders o
5. JOIN customers c ON o.customer_id = c.id
6. WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
7. ORDER BY o.order_date DESC
8. LIMIT 100;

复制代码

发现执行计划中使用了全表扫描，且排序操作消耗大量资源。

1. 创建适当的索引：

2. -- 为订单日期创建索引
3. CREATE INDEX idx_orders_order_date ON orders (order_date DESC);
5. -- 为客户ID创建索引
6. CREATE INDEX idx_orders_customer_id ON orders (customer_id);
8. -- 创建复合索引
9. CREATE INDEX idx_orders_date_customer ON orders (order_date DESC, customer_id);

复制代码

1. 优化查询：

2. -- 使用覆盖索引减少表访问
3. CREATE INDEX idx_orders_date_customer_amount ON orders (order_date DESC, customer_id) INCLUDE (total_amount);
5. -- 优化后的查询
6. SELECT o.id, o.order_date, c.name, o.total_amount
7. FROM orders o
8. JOIN customers c ON o.customer_id = c.id
9. WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
10. ORDER BY o.order_date DESC
11. LIMIT 100;

复制代码

1. 配置优化：

2. # 调整work_mem以优化排序操作
3. work_mem = 32MB
5. # 增加共享缓冲区
6. shared_buffers = 4GB

复制代码

2. EXPLAIN (ANALYZE, BUFFERS)
3. SELECT o.id, o.order_date, c.name, o.total_amount
4. FROM orders o
5. JOIN customers c ON o.customer_id = c.id
6. WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
7. ORDER BY o.order_date DESC
8. LIMIT 100;

复制代码

查询时间从原来的5秒降低到100毫秒，性能提升显著。

案例二：日志分析系统优化

某日志分析系统在处理大量日志数据时性能低下，查询响应时间长。

2. -- 查看表大小
3. SELECT pg_size_pretty(pg_total_relation_size('logs'));
5. -- 查看表行数
6. SELECT COUNT(*) FROM logs;
8. -- 检查索引使用情况
9. SELECT * FROM pg_stat_user_indexes WHERE relname = 'logs';

复制代码

发现日志表非常大，且现有索引不足以支持高效查询。

1. 实施表分区：

2. -- 创建分区表
3. CREATE TABLE logs (
4.     id SERIAL,
5.     log_time TIMESTAMP WITH TIME ZONE NOT NULL,
6.     level VARCHAR(10) NOT NULL,
7.     message TEXT NOT NULL,
8.     source VARCHAR(100) NOT NULL
9. ) PARTITION BY RANGE (log_time);
11. -- 创建按月分区
12. CREATE TABLE logs_2023_01 PARTITION OF logs
13.     FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
15. CREATE TABLE logs_2023_02 PARTITION OF logs
16.     FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

复制代码

1. 创建适当索引：

2. -- 为分区表创建索引
3. CREATE INDEX idx_logs_2023_01_log_time ON logs_2023_01 (log_time);
4. CREATE INDEX idx_logs_2023_01_level ON logs_2023_01 (level);
5. CREATE INDEX idx_logs_2023_01_source ON logs_2023_01 (source);
7. CREATE INDEX idx_logs_2023_02_log_time ON logs_2023_02 (log_time);
8. CREATE INDEX idx_logs_2023_02_level ON logs_2023_02 (level);
9. CREATE INDEX idx_logs_2023_02_source ON logs_2023_02 (source);

复制代码

1. 使用BRIN索引：

2. -- 为时间列创建BRIN索引
3. CREATE INDEX idx_logs_log_time_brin ON logs USING brin (log_time);

复制代码

1. 配置并行查询：

2. # 启用并行查询
3. max_parallel_workers_per_gather = 4
4. min_parallel_table_scan_size = 8MB

复制代码

2. -- 测试查询性能
3. EXPLAIN (ANALYZE, BUFFERS)
4. SELECT COUNT(*) FROM logs
5. WHERE log_time BETWEEN '2023-01-01' AND '2023-01-31'
6. AND level = 'ERROR';
8. -- 测试聚合查询
9. EXPLAIN (ANALYZE, BUFFERS)
10. SELECT level, COUNT(*)
11. FROM logs
12. WHERE log_time BETWEEN '2023-01-01' AND '2023-01-31'
13. GROUP BY level;

复制代码

查询性能提升明显，聚合查询从原来的30秒降低到2秒。

案例三：社交媒体平台用户活动分析优化

某社交媒体平台需要分析用户活动，但相关查询非常缓慢。

2. -- 慢查询示例
3. SELECT u.id, u.username, COUNT(a.id) as activity_count
4. FROM users u
5. LEFT JOIN activities a ON u.id = a.user_id
6. WHERE u.created_at BETWEEN '2023-01-01' AND '2023-01-31'
7. GROUP BY u.id, u.username
8. ORDER BY activity_count DESC
9. LIMIT 100;

复制代码

2. -- 检查表大小
3. SELECT pg_size_pretty(pg_total_relation_size('users'));
4. SELECT pg_size_pretty(pg_total_relation_size('activities'));
6. -- 分析执行计划
7. EXPLAIN (ANALYZE, BUFFERS)
8. SELECT u.id, u.username, COUNT(a.id) as activity_count
9. FROM users u
10. LEFT JOIN activities a ON u.id = a.user_id
11. WHERE u.created_at BETWEEN '2023-01-01' AND '2023-01-31'
12. GROUP BY u.id, u.username
13. ORDER BY activity_count DESC
14. LIMIT 100;

复制代码

发现activities表非常大，且JOIN操作和排序消耗大量资源。

1. 创建物化视图：

2. -- 创建物化视图存储用户活动统计
3. CREATE MATERIALIZED VIEW user_activity_stats AS
4. SELECT u.id, u.username, COUNT(a.id) as activity_count
5. FROM users u
6. LEFT JOIN activities a ON u.id = a.user_id
7. GROUP BY u.id, u.username;
9. -- 创建索引
10. CREATE INDEX idx_user_activity_stats_count ON user_activity_stats (activity_count DESC);
11. CREATE INDEX idx_user_activity_stats_id ON user_activity_stats (id);

复制代码

1. 定期刷新物化视图：

2. -- 刷新物化视图
3. REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_stats;

复制代码

1. 使用查询优化：

2. -- 优化后的查询
3. SELECT * FROM user_activity_stats
4. ORDER BY activity_count DESC
5. LIMIT 100;

复制代码

1. 使用并行查询：

2. -- 启用并行查询
3. SET max_parallel_workers_per_gather = 4;
5. -- 使用并行聚合
6. EXPLAIN (ANALYZE, BUFFERS)
7. SELECT u.id, u.username, COUNT(a.id) as activity_count
8. FROM users u
9. LEFT JOIN activities a ON u.id = a.user_id
10. WHERE u.created_at BETWEEN '2023-01-01' AND '2023-01-31'
11. GROUP BY u.id, u.username
12. ORDER BY activity_count DESC
13. LIMIT 100;

复制代码

2. -- 测试优化后的查询
3. EXPLAIN (ANALYZE, BUFFERS)
4. SELECT * FROM user_activity_stats
5. ORDER BY activity_count DESC
6. LIMIT 100;

复制代码

查询时间从原来的45秒降低到50毫秒，性能提升显著。

总结与最佳实践

PostgreSQL优化总结

PostgreSQL优化是一个系统性的过程，涉及多个层面：

1. 基础配置调整：合理设置内存、连接、磁盘等参数
2. 查询性能优化：分析执行计划，优化SQL语句
3. 索引优化：选择合适的索引类型和策略
4. 表和分区优化：合理设计表结构，实施分区策略
5. 高级特性优化：利用复制、并行查询等高级特性
6. 监控与维护：持续监控性能，定期维护数据库

最佳实践

• 根据系统资源合理设置shared_buffers（通常为系统内存的25%）
• 适当增加work_mem以优化排序和哈希操作
• 根据磁盘类型调整random_page_cost（SSD可设为1.1）
• 启用并配置自动清理（autovacuum）

• 使用EXPLAIN ANALYZE分析查询执行计划
• 避免全表扫描，确保使用适当的索引
• 使用LIMIT限制结果集大小
• 批量操作代替单行操作
• 使用CTE（Common Table Expressions）提高复杂查询可读性和性能

• 为经常用于WHERE、JOIN、ORDER BY和GROUP BY的列创建索引
• 使用复合索引优化多列查询
• 考虑使用部分索引减少索引大小
• 定期维护索引，重建膨胀的索引
• 监控索引使用情况，删除未使用的索引

• 选择合适的数据类型
• 适度规范化与反规范化
• 对大表实施分区策略
• 使用继承表处理特殊类型的数据
• 定期VACUUM和ANALYZE表

• 使用pg_stat_statements监控查询性能
• 定期检查表膨胀和索引使用情况
• 设置适当的日志级别，记录慢查询
• 使用外部工具（如pgBadger、Prometheus）进行监控
• 制定定期维护计划，包括VACUUM、ANALYZE和索引重建

持续优化

PostgreSQL优化不是一次性的任务，而是一个持续的过程：

1. 定期评估数据库性能
2. 监控系统资源使用情况
3. 分析慢查询并优化
4. 根据数据增长调整配置
5. 跟踪PostgreSQL版本更新，利用新特性

通过遵循本文提供的指南和最佳实践，您可以显著提高PostgreSQL数据库的性能，确保应用程序能够高效、稳定地运行。

mai2

感謝分享