enhancement(db): Connection pool sizing observability baseline and right-sizing

Split from #34832 / Epic #34837 items 6-7

This issue tracks the observability-first approach to right-sizing DB_MAX_TOTAL and aligning thread pool configuration.

Description

DB_MAX_TOTAL = 200 was likely sized to match peak thread demand, but we have no data to confirm. dotCMS runs many concurrent thread pools (Tomcat request handlers, content indexing, push publishing, Quartz, workflow processors, telemetry) that can each hold a database connection simultaneously. Reducing pool size without data risks connectionTimeout failures.

Phase 1: Establish baseline

Query pg_stat_activity during three scenarios — normal traffic, content publish, and full reindex:

SELECT client_addr, application_name, state, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY client_addr, application_name, state
ORDER BY count(*) DESC;

Note: Each pod runs one permanent LISTEN/NOTIFY connection (JDBCPubSubImpl) holding a pool slot indefinitely. Filter this out when analysing load-driven connection counts.

Also collect HikariCP JMX metrics if registerMbeans=true:

• HikariPool.ActiveConnections
• HikariPool.IdleConnections
• HikariPool.PendingThreads (wait queue depth)
• HikariPool.TotalConnections

Phase 2: Right-size pool

Once peak data is available, reduce DB_MAX_TOTAL in small increments:

• 200 → 150 → 100
• Monitor HikariCP JMX metrics and connectionTimeout errors after each step
• Tomcat maxThreads and async thread pool sizing should be reviewed alongside

Phase 3: Thread pool alignment

Tomcat maxThreads and HikariCP maximumPoolSize must be sized consistently:

• If maxThreads >> maximumPoolSize, threads queue waiting for connections
• If pool ceiling >> maxThreads, pool slots are never utilized
• Both must be reviewed together once baseline is available

Acceptance Criteria

• Peak connection count per pod baselined via pg_stat_activity across publish, reindex, and normal traffic scenarios
• HikariCP JMX metrics collection validated (or alternative observability established)
• Tomcat max threads and async thread pool configuration documented
• DB_MAX_TOTAL reduced based on observed peak with monitoring after each step
• No increase in connectionTimeout errors after right-sizing

Priority

Low — Tier 2 work. Requires Tier 1 leak fixes to be deployed first so baseline isn't distorted by leaked connections.

Links

• Epic: #34837
• Depends on: #34921 (DB_MAXWAIT fix), #34831, #34920 (active leak fixes)

Start with the provided pg_stat_activity query and collect connection data during normal traffic, content publishing, and full reindexing after the listed leak fixes are deployed. Validate HikariCP JMX metrics and document Tomcat and async thread-pool settings. Done means peak usage is baselined, DB_MAX_TOTAL is reduced incrementally with monitoring, and connectionTimeout errors do not increase.