4、数据库调优（PostgreSQL）：连接池配置（HikariCP）、索引优化、慢查询分析与VACUUM策略
数据库调优，说白了就是让 PostgreSQL 别成为 ThingsBoard 的瓶颈。我见过太多团队，微服务拆得飞起，结果数据库一压就垮。今天咱们就聊聊连接池、索引、慢查询和 VACUUM 这四个核心点。
4.1 连接池配置（HikariCP）
ThingsBoard 默认用的是 HikariCP，这玩意儿号称「史上最快连接池」。我个人习惯是，先别急着改参数，搞清楚你的业务场景再说。
核心参数配置
| 参数名 | 推荐值 | 说明 |
|---|---|---|
| maximumPoolSize | CPU核心数 × 2 + 1 | 别贪多，连接数不是越多越好 |
| minimumIdle | 10-20 | 保持一些空闲连接，应对突发 |
| connectionTimeout | 30000ms | 30秒拿不到连接就报错 |
| idleTimeout | 600000ms | 10分钟空闲就回收 |
| maxLifetime | 1800000ms | 30分钟强制重建连接 |
避坑指南
我曾经遇到一个客户，把 maximumPoolSize 设成了 200，结果 PostgreSQL 直接 OOM。你想想看，每个连接都要占用内存，200个连接光缓冲区就吃掉好几个 GB。后来我建议改成 50，性能反而上去了。
ThingsBoard 中的配置方式
在 thingsboard.yml 里找到这一段：
spring:
datasource:
hikari:
maximum-pool-size: 50
minimum-idle: 20
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
嗯，这里要注意：如果你的 ThingsBoard 是集群部署，每个节点都要独立配置连接池。别想着共享，那会出大问题。
4.2 索引优化
索引这东西，用好了是神器，用不好就是累赘。我见过有人给每个字段都加索引，结果写入性能惨不忍睹。
ThingsBoard 核心表索引建议
| 表名 | 推荐索引 | 原因 |
|---|---|---|
| ts_kv | (entity_id, key, ts) | 时序数据查询最频繁 |
| device | (tenant_id, type) | 按租户和设备类型过滤 |
| alarm | (tenant_id, status, created_time) | 告警查询常用条件 |
| event | (tenant_id, event_type, created_time) | 事件日志查询 |
我的经验
ts_kv 表是 ThingsBoard 的「心脏」，数据量最大。我建议用复合索引，把 entity_id 放第一位。为什么？因为查询时先定位设备，再找 key，最后按时间排序，这样索引利用率最高。
创建索引的 SQL 示例
-- 时序数据核心索引
CREATE INDEX idx_ts_kv_entity_key_ts
ON ts_kv (entity_id, key, ts DESC);
-- 告警表常用索引
CREATE INDEX idx_alarm_tenant_status_time
ON alarm (tenant_id, status, created_time DESC);
-- 记得定期检查未使用的索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
这个查询能帮你找出「僵尸索引」——那些建了但从来没用过的索引。我建议每个月跑一次，该删就删。
4.3 慢查询分析
慢查询是性能问题的「照妖镜」。我个人习惯是，先开启慢查询日志，再分析。
开启慢查询日志
-- postgresql.conf 中配置
log_min_duration_statement = 1000 -- 超过1秒的查询记录
log_duration = on
log_statement = 'ddl' -- 只记录DDL语句，避免日志爆炸
配置完记得重启 PostgreSQL。然后你就可以用 pgBadger 或者自带的 pg_stat_statements 来分析。
常用分析查询
-- 查看最耗时的查询
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- 查看执行频率最高的查询
SELECT query, calls, rows,
rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;
注意
pg_stat_statements 需要安装扩展：CREATE EXTENSION pg_stat_statements。我遇到过有人没装这个扩展，折腾半天查不到数据。
我曾经接手一个项目，ThingsBoard 的告警页面加载要 8 秒。一查慢查询日志，发现有个查询没走索引，全表扫描了 500 万条记录。加了个索引后，直接降到 200 毫秒。你想想看，一个索引能差 40 倍。
4.4 VACUUM 策略
PostgreSQL 的 MVCC 机制，说白了就是「旧数据不删，新数据追加」。时间长了，表里全是死元组，查询性能直线下降。VACUUM 就是干这个的——清理死元组，回收空间。
自动 VACUUM 配置
# postgresql.conf
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
手动 VACUUM 策略
| 操作 | 命令 | 适用场景 |
|---|---|---|
| 普通清理 | VACUUM; | 日常维护，不锁表 |
| 完全清理 | VACUUM FULL; | 空间回收，会锁表，建议凌晨执行 |
| 分析统计 | ANALYZE; | 更新查询计划，建议每天一次 |
| 组合操作 | VACUUM ANALYZE; | 清理+分析，最常用 |
避坑指南
我曾经在白天执行了 VACUUM FULL，结果整个 ThingsBoard 停了 15 分钟。为什么？VACUUM FULL 会锁表，所有读写操作都得排队。后来我改成凌晨 3 点执行，配合 cron 定时任务，再也没出过问题。
监控 VACUUM 状态
-- 查看表的死元组比例
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC
LIMIT 10;
当死元组比例超过 20% 时，我建议手动执行一次 VACUUM。超过 50% 就得考虑 VACUUM FULL 了。
总结
数据库调优不是一锤子买卖。我建议你按这个顺序来：
- 先配好连接池，别让连接数成为瓶颈
- 再优化索引，重点照顾 ts_kv 表
- 开启慢查询日志，持续监控
- 制定 VACUUM 策略，定期清理
嗯，做到这四点，你的 ThingsBoard 数据库基本就稳了。剩下的就是根据业务量持续调整参数。记住，没有一劳永逸的配置，只有持续优化的过程。