## 1. PostgreSQL压缩选项
– **TOAST压缩：** PostgreSQL自动对大字段进行压缩
– **pg_compress extension：** 提供额外的压缩功能
– **表级压缩：** 使用WAL压缩减少WAL日志大小

## 2. 实施方法
“`sql
— 启用WAL压缩
ALTER SYSTEM SET wal_compression = on;

— 重启PostgreSQL使配置生效
SELECT pg_reload_conf();

— 查看压缩配置
SHOW wal_compression;

— 压缩表
VACUUM FULL VERBOSE ANALYZE fgedu_fgedus;
“`

## 3. 压缩效果评估
– **存储节省：** 通常可以节省20-50%的存储空间
– **性能影响：** 压缩和解压缩会增加CPU开销
– **适用场景：** 适合存储大量文本或二进制数据的表

## 1. 分区表类型
– **范围分区：** 按范围值分区，如时间范围
– **列表分区：** 按列表值分区，如地区、状态
– **哈希分区：** 按哈希值分区，均匀分布数据

## 2. 实施方法
“`sql
— 创建范围分区表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INTEGER NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (order_date);

— 创建分区
CREATE TABLE fgedu_orders_2023 PARTITION OF fgedu_orders
FOR VALUES FROM (‘2023-01-01’) TO (‘2024-01-01’);

CREATE TABLE fgedu_orders_2024 PARTITION OF fgedu_orders
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

— 插入数据
INSERT INTO fgedu_orders (order_date, customer_id, amount)
VALUES (‘2023-06-01’, 1, 100.00),
(‘2024-06-01’, 2, 200.00);

— 查询数据
SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
“`

## 3. 分区表优势
– **查询性能：** 只扫描相关分区，提高查询速度
– **维护方便：** 可以单独维护和清理分区
– **存储优化：** 可以将不同分区存储在不同性能的存储上

## 1. 数据清理策略
– **归档：** 将旧数据归档到低成本存储
– **删除：** 删除不需要的数据
– **分区清理：** 定期清理过期分区

## 2. 实施方法
“`sql
— 删除过期数据
DELETE FROM fgedu_orders WHERE order_date < '2022-01-01'; -- 清理分区 DROP TABLE fgedu_orders_2022; -- 执行VACUUM回收空间 VACUUM FULL VERBOSE ANALYZE fgedu_orders; -- 自动化清理脚本 CREATE OR REPLACE FUNCTION cleanup_old_data() RETURNS void AS $$ BEGIN -- 删除3年前的数据 DELETE FROM fgedu_orders WHERE order_date < now() - interval '3 years'; -- 执行VACUUM VACUUM FULL VERBOSE ANALYZE fgedu_orders; END; $$ LANGUAGE plpgsql; -- 调度清理任务 SELECT cron.schedule('0 0 1 * *', 'SELECT cleanup_old_data();'); ``` ## 3. 数据清理最佳实践 - **定期执行：** 制定定期清理计划 - **分批处理：** 大批量数据删除时，分批处理避免锁表 - **监控空间：** 定期监控存储空间使用情况 - **备份：** 清理前备份重要数据

## 1. 内存配置
“`sql
— 共享缓冲区（建议为总内存的25%）
ALTER SYSTEM SET shared_buffers = ’16GB’;

— 工作内存（每个并行操作的内存）
ALTER SYSTEM SET work_mem = ’32MB’;

— 维护工作内存
ALTER SYSTEM SET maintenance_work_mem = ‘1GB’;

— 有效缓存大小（建议为总内存的50-75%）
ALTER SYSTEM SET effective_cache_size = ’48GB’;
“`

## 2. 连接配置
“`sql
— 最大连接数
ALTER SYSTEM SET max_connections = ‘200’;

— 连接超时
ALTER SYSTEM SET idle_in_transaction_session_timeout = ‘300000’; — 5分钟
“`

## 3. 查询优化配置
“`sql
— 并行查询
ALTER SYSTEM SET max_parallel_workers_per_gather = ‘8’;
ALTER SYSTEM SET max_parallel_workers = ’16’;

— 统计信息
ALTER SYSTEM SET autovacuum = ‘on’;
ALTER SYSTEM SET autovacuum_max_workers = ‘4’;
ALTER SYSTEM SET autovacuum_naptime = ’10min’;
“`

## 4. 写操作优化
“`sql
— WAL配置
ALTER SYSTEM SET wal_buffers = ’16MB’;
ALTER SYSTEM SET checkpoint_completion_target = ‘0.9’;
ALTER SYSTEM SET max_wal_size = ‘1GB’;
ALTER SYSTEM SET min_wal_size = ’80MB’;
“`

## 5. 应用配置
“`sql
— 语句超时
ALTER SYSTEM SET statement_timeout = ‘30000’; — 30秒

— 随机页面成本（SSD存储设为1-2）
ALTER SYSTEM SET random_page_cost = ‘1.1’;

— 顺序页面成本
ALTER SYSTEM SET seq_page_cost = ‘1.0’;
“`

## 6. 配置生效
“`sql
— 重载配置
SELECT pg_reload_conf();

— 查看配置
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
“`

## 1. PgBouncer配置
“`ini
[fgedudbs]
* = fgedu.net.cn=localfgedu.net.cn port=5432 fgedudb=pgsql [pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/fgedulist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 5.0
“`

## 2. 启动PgBouncer
“`bash
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
“`

## 3. 连接到PgBouncer
“`bash
psql -h localfgedu.net.cn -p 6432 -U pgsql -d pgsql “`

## 4. 监控连接池
“`bash
psql -h localfgedu.net.cn -p 6432 -U pgsql -d pgbouncer

— 查看连接池状态
SHOW POOLS;

— 查看客户端连接
SHOW CLIENTS;

— 查看服务器连接
SHOW SERVERS;
“`

## 5. 连接池优势
– **减少连接开销：** 复用数据库连接，减少连接建立和关闭的开销
– **控制并发：** 限制并发连接数，避免资源争用
– **提高性能：** 减少数据库负载，提高查询性能
– **节省资源：** 减少数据库进程数量，节省内存资源

## 1. 索引类型选择
– **B树索引：** 适合等值查询和范围查询
– **GiST索引：** 适合空间数据和全文检索
– **GIN索引：** 适合数组和JSONB类型
– **BRIN索引：** 适合大表的范围查询

## 2. 索引创建
“`sql
— 创建B树索引
CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders(customer_id);

— 创建复合索引
CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

— 创建部分索引
CREATE INDEX idx_fgedu_orders_active ON fgedu_orders(customer_id) WHERE status = ‘active’;

— 创建表达式索引
CREATE INDEX idx_fgedu_orders_month ON fgedu_orders(date_trunc(‘month’, order_date));
“`

## 3. 索引维护
“`sql
— 重建索引
REINDEX INDEX idx_fgedu_orders_customer_id;

— 分析索引
ANALYZE fgedu_orders;

— 查看索引使用情况
SELECT * FROM pg_stat_fgedu_indexes WHERE relname = ‘fgedu_orders’;

— 查看未使用的索引
SELECT
schemaname,
relname AS tablename,
indexrelname AS indexname,
idx_scan AS indexscans
FROM
pg_stat_fgedu_indexes
JOIN
pg_index ON pg_stat_fgedu_indexes.indexrelid = pg_index.indexrelid
WHERE
idx_scan = 0
AND indisunique = false;
“`

## 4. 索引优化最佳实践
– **选择合适的索引类型：** 根据查询模式选择合适的索引类型
– **避免过度索引：** 过多的索引会增加写操作的开销
– **定期维护索引：** 定期重建和分析索引
– **监控索引使用情况：** 识别和移除未使用的索引

## 1. 避免全表扫描
– **使用索引：** 确保查询条件使用索引
– **限制返回行数：** 使用LIMIT子句限制返回行数
– **避免SELECT *：** 只选择需要的列

## 2. 优化JOIN操作
– **使用合适的JOIN类型：** 根据数据分布选择合适的JOIN类型
– **小表驱动大表：** 让小表作为驱动表
– **使用连接条件：** 确保JOIN有合适的连接条件

## 3. 避免复杂子查询
– **使用JOIN代替子查询：** 大多数情况下JOIN比子查询更高效
– **使用CTE：** 对于复杂查询，使用CTE提高可读性和性能
– **避免相关子查询：** 相关子查询会逐行执行，性能较差

## 4. 优化聚合查询
– **使用合适的聚合函数：** 根据需求选择合适的聚合函数
– **使用GROUP BY优化：** 确保GROUP BY列有索引
– **使用HAVING代替WHERE：** 对于聚合结果的过滤，使用HAVING

## 5. 示例优化
“`sql
— 优化前
SELECT * FROM fgedu_orders WHERE order_date > ‘2023-01-01’;

— 优化后
SELECT id, customer_id, amount FROM fgedu_orders WHERE order_date > ‘2023-01-01’ LIMIT 100;

— 优化前
SELECT * FROM fgedu_orders WHERE customer_id = 1;

— 优化后
CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders(customer_id);
SELECT id, customer_id, amount FROM fgedu_orders WHERE customer_id = 1;

— 优化前
SELECT * FROM fgedu_orders o WHERE EXISTS (
SELECT 1 FROM fgedu_customers c WHERE c.id = o.customer_id AND c.status = ‘active’
);

— 优化后
SELECT o.id, o.customer_id, o.amount FROM fgedu_orders o
JOIN fgedu_customers c ON c.id = o.customer_id
WHERE c.status = ‘active’;
“`

## 6. 执行计划分析
“`sql
— 查看执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 1;

— 查看详细执行计划
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT * FROM fgedu_orders WHERE customer_id = 1;
“`