Keycloak频繁查询FED_USER_GROUP_MEMBERSHIP表致PostgreSQL性能问题

高频查询调用频次过高的排查与优化 #

• 锁定查询内容：先明确这条每小时236万次的SQL具体业务逻辑——Keycloak中这类高频查询通常和会话验证、token刷新、客户端信息拉取相关，比如SELECT * FROM client WHERE client_id = ?或用户会话状态查询，明确查询场景才能针对性解决。
• 检查Keycloak缓存配置：
  • 确认Infinispan本地/分布式缓存是否正常启用。Keycloak默认会缓存客户端、角色、用户会话等高频访问数据，若缓存失效、未启用或过期时间设置过短，会导致所有请求直接穿透到数据库。
  • 调整缓存过期参数：比如cache-ttl、max-idle，避免缓存过早失效引发重复查询；集群部署场景下，确保分布式缓存同步正常，避免各节点单独发起数据库查询。
• 优化Token逻辑：若查询与JWT验证、token刷新相关，检查是否存在大量用户频繁刷新token的情况。可适当延长token有效期，减少刷新频次，从而降低数据库查询量。

数据库层针对高频查询的优化 #

• 验证预编译语句复用率：执行SELECT * FROM pg_stat_prepared_statements查看该高频语句的复用情况。若复用率低，要么是Keycloak未正确使用预编译，要么是PostgreSQL的max_prepared_transactions配置不足，需针对性调整。
• 补全索引：即便单次查询耗时0.01ms，236万次的累计资源消耗也不可忽视。用EXPLAIN ANALYZE执行该查询，确认是否用到合适索引——比如按client_id查询时，给client表的client_id字段加唯一索引；查询会话时，给user_session表的session_id或user_id字段加索引。
• 调整连接池配置：不要仅修改数据库端连接数，Keycloak使用HikariCP连接池，需在standalone.xml或keycloak.conf中调整：
  • db-pool-max-size：6核CPU的PostgreSQL，连接数建议设为30-60，无需开至400——过多连接会导致CPU上下文切换开销暴增，反而拖慢性能。
  • db-pool-min-idle：保持适量空闲连接，减少建立新连接的开销。
  • db-pool-connection-timeout：设置合理超时时间，防止连接泄漏。

UTILITY COMMAND负载临时应对（后续调试方向） #

• 通过pg_stat_activity明确具体UTILITY COMMAND类型（比如VACUUM、ANALYZE）。GCP托管的PostgreSQL会自动执行维护命令，频次过高可能是表碎片化严重，或自动维护参数配置不合理。
• 检查autovacuum相关配置：比如autovacuum_vacuum_threshold、autovacuum_analyze_threshold，调大阈值可减少自动清理的频次。

关于已尝试方案的补充说明 #

你之前调整预准备连接数到400、升级数据库配置无效，大概率是因为：

• 连接数设置过高：PostgreSQL的连接为进程级，过多连接会导致CPU上下文切换开销剧增，反而降低整体性能，建议按核数调整（通常为核数*2+1，6核场景下13-20左右足够）。
• 问题根源不在硬件，而是Keycloak缓存未生效或业务逻辑引发了不必要的高频查询。

内容的提问来源于stack exchange，提问作者Anupam Nirwan