Config and App-secret reads round-trip Postgres on every request instead of using cache

[wezell]

Problem Statement

Every HTTP request performs several Postgres round trips to read App secrets and configuration values that should be served from cache. The reads happen on the request thread, blocked on a socket, before any application work begins.

Observed call chain:

SimpleWebInterceptorDelegateImpl.intercept
  └─ AppsAPIImpl.getSecrets / hasEnvBackedSecrets / hasGlobalEnvBackedSecrets
       └─ Config.getSystemTableValue
            └─ SystemTableConfigSource.getValue
                 └─ SystemTableFactoryImpl.find
                      └─ DotConnect → Postgres  (thread parked in sun.nio.ch.Net.poll)

Several web interceptors each resolve secrets independently on the same request — AnalyticsTrackWebInterceptor.isAllowed, PreRenderSEOWebAPIImpl.prerenderIfEligible, DefaultAutoLoginWebInterceptor.intercept — so the chain repeats several times per request for the same keys.

Who it impacts: every request on every tenant. This is fixed per-request overhead, not proportional to what the client asked for, so it is pure waste rather than a cost that scales with work done.

Evidence

Glowroot main-thread profiles, 7-day windows ending 2026-08-07, production clusters:

Tenant	Sampled stacks	Distinct occurrences of this chain	Approx. share of samples
k8s.client1	12,126	~20	~3–4%
client2	893	~25	~3%
(Velocity tenant)	17,706	~15	~2%

Representative frames from the caliber profile:

2.8%  io.vavr.control.Try.of
 1.7%  AppsAPIImpl.lambda$filterSitesForAppKey$0
  1.6%  AppsAPIImpl.hasEnvBackedSecrets
   0.9%  Config.getStringProperty
    0.8%  SystemTableConfigSource.getValue
     0.4%  SystemTableImpl.get

and from k8s.dairyqueen:

3.1%  AppsAPIImpl.getSecrets
 1.0%  AppsAPIImpl.resolveSecrets
  0.7%  Config.getSystemTableValue
   0.6%  SystemTableConfigSource.getValue
    0.3%  SystemTableFactoryImpl.find

Note this is a sampled profile: it shows time share, not exact query counts. The query count per request should be confirmed with SQL logging (see repro step 3).

Steps to Reproduce

1. Start dotCMS with at least one App configured that has secrets (any App backed by system_table).
2. Enable statement logging on the Postgres connection, or attach a profiler (Glowroot / async-profiler) to a node under steady request load.
3. Issue a single request to any front-end URL or REST endpoint (e.g. GET /api/v1/appconfiguration).
4. Count queries against system_table attributable to that one request.

Expected: at most one read per distinct key per request, served from cache thereafter.

Actual: the same keys are re-read from Postgres several times within the single request, once per interceptor that resolves secrets.

Acceptance Criteria

• Repeated reads of the same config key or App secret within a single request result in at most one database query.
• SystemTableConfigSource.getValue / SystemTableFactoryImpl.find results are cached, with explicit invalidation when the underlying system_table row is written.
• Cache invalidation is cluster-safe — a write on one node invalidates the value on all nodes.
• Updating a system table value still takes effect without a restart, within a documented and configurable TTL / invalidation window (no behavioural regression).
• A profiled request under load shows no SystemTableFactoryImpl.find → DotConnect → Postgres frames for keys already read in that request.
• Regression test: N interceptor-level secret resolutions for the same App produce exactly 1 database read.
• Secrets remain correctly scoped per host — caching must not leak a host's secret value to another host or to SYSTEM_HOST lookups.

dotCMS Version

main branch. Observed on 5 production tenant clusters via Glowroot central collector 0.14.6, 7-day windows ending 2026-08-07.

Severity

Medium - Some functionality impacted

Links

NA — found during request-cost (@RequestCost) instrumentation analysis of production thread profiles, not via a support ticket.