性能调优涉及参数配置、资源利用与查询优化。

核心概念

• shared_buffers、work_mem、maintenance_work_mem 等关键参数。
• Autovacuum 维护表统计与膨胀。
• IO、CPU、内存瓶颈分析。

实战步骤

• 根据内存与负载调整主要参数。
• 使用 Auto Explain 记录慢查询计划。
• 执行 VACUUM、REINDEX 保持健康。

进阶建议

扩展让 PostgreSQL 拥有更广泛的能力。

核心概念

• PostGIS 提供地理信息处理。
• TimescaleDB 扩展时间序列。
• pg_partman、pgcrypto 等工具扩展。

实战步骤

• 通过 CREATE EXTENSION 安装。
• 管理扩展版本与升级。
• 在迁移脚本中控制扩展状态。

进阶建议

• 开发自定义扩展满足特定需求。
• 在云数据库环境验证兼容性。
• 结合 FDW 与扩展构建多源数据平台。

备份策略需覆盖全量、增量与时间点恢复。

核心概念

• pg_basebackup、WAL 归档提供 PITR。
• 备份目录与权限管理。
• 恢复测试确保可靠。

实战步骤

• 配置 archive_command 保存 WAL。
• 使用 pgBackRest/Barman 自动管理备份。
• 编写恢复 Runbook 并定期演练。

进阶建议

• 跨区域备份与对象存储归档。
• 使用增量备份减少窗口。
• 建立备份验证脚本。

监控确保数据库稳定运行，需要全面指标。

核心概念

• pg_stat_database、pg_stat_activity 提供状态。
• pg_stat_statements 记录 SQL。
• Autovacuum 与锁监控。

实战步骤

• 部署 Prometheus Exporter 收集指标。
• 配置慢查询日志与告警。
• 建立容量预测模型。

进阶建议

• 使用 timescaledb_toolkit 对历史指标压缩。
• 结合 Grafana/Loki 统一日志与指标。
• 自动化生成 SLO 报表。

安全策略覆盖认证、授权、审计以及加密。

核心概念

• 使用 SCRAM-SHA-256 认证。
• PG_HBA 控制连接源。
• 行级安全 RLS。

实战步骤

• 启用 TLS 并配置证书。
• 设置审计扩展 (pgaudit)。
• 分离权限，采用 least privilege。

进阶建议

• 与 Vault/KMS 集成管理密钥。
• 使用透明数据加密 (pgcrypto/外部工具)。
• 监控登录失败和异常行为。

逻辑解码将 WAL 转换为可消费的变更事件，支持下游同步。

核心概念

• 输出插件（wal2json、pgoutput）。
• 复制槽（replication slot）保证事件顺序。
• 对性能与存储有要求。

实战步骤

• 配置逻辑复制槽并订阅。
• 处理复制槽积压与清理。
• 结合 Debezium/Kafka 构建 CDC。

进阶建议

• 对多租户场景做数据隔离。
• 实现幂等消费与重放。
• 监控槽状态并告警。

物理复制 + 自动故障转移构成高可用解决方案。

核心概念

• 流复制通过 WAL 传输维持同步。
• 同步、异步复制权衡性能与一致性。
• 故障转移需处理脑裂与数据丢失。

实战步骤

• 使用 repmgr/Patroni+Etcd 管理集群。
• 在备用节点上启用只读查询。
• 监控复制延迟 pg_stat_replication。

进阶建议

• 跨机房复制确保容灾。
• 结合同步复制与延迟备库实现备份窗口。
• 对 WAL 压缩与传输优化。

分区表提升大表管理与查询效率，尤其适合时间序列。

核心概念

• RANGE、LIST、HASH 分区模式。
• 分区裁剪减少扫描。
• 默认分区与自动分区管理。

实战步骤

• 使用触发器或 cron 创建新分区。
• 通过 ATTACH PARTITION 快速加载历史数据。
• 与 TimescaleDB 等扩展结合处理时序。

进阶建议

• 为分区表配置不同存储参数。
• 在分区上建立局部索引。
• 监控分区数量与统计信息更新。

优化器决定执行策略，需要掌握 EXPLAIN 分析。

核心概念

• Seq Scan、Index Scan、Bitmap Heap Scan。
• 成本估算受统计信息影响。
• 上下文规划器 (GEQO) 用于大型 Join。

实战步骤

• 使用 EXPLAIN (ANALYZE, BUFFERS) 查看实际执行。
• 更新统计信息 ANALYZE、自定义采样率。
• 通过 enable_* 参数测试不同计划。

PostgreSQL 提供多种索引类型，应根据查询模式选择。

核心概念

• B-Tree、GIN、GiST、BRIN、Hash 等。
• 部分索引、表达式索引、覆盖索引。
• 维护开销与 Autovacuum。

实战步骤

• 使用 CREATE INDEX CONCURRENTLY 减少锁。
• 创建表达式索引支持模糊查询。
• 定期重建膨胀严重的索引。

进阶建议

• 结合 HypoPG 测试索引效果。
• 在 JSONB 字段上使用 GIN + jsonb_path_ops。
• 通过 pg_stat_statements 分析索引命中。