PostgreSQL-target config reads plan every retained pg_server_config chunk: seconds per read once a store holds a year of snapshots #3928

Summary

The PostgreSQL-target analysis reads that take the newest pg_server_config snapshot are bounded only by the window end, or not at all. TimescaleDB has to plan every retained chunk to find that snapshot, and pg_server_config keeps 365 days (CollectorScheduleDefaults: hourly, 365-day retention, 1-day chunks, compressed after a day). Planning cost grows with the chunk count, and on the CI-sized rig it grows faster than linearly. A year in, each of these reads costs seconds before it executes a single row.

Found while fixing #3896, which covers the SQL Server target's latest-value reads. These five were left out of that PR because two of them carry explicit design decisions (below) that need a ruling.

The reads

All five anchor on collection_time = (SELECT MAX(collection_time) FROM pg_server_config WHERE server_id = $1 [AND collection_time <= $2]), with no lower bound on either the inner or the outer scan:

Measured

DARLING01 (PG18 target, 16 retained chunks), PgTargetConfigSnapshotSql as shipped: 104 ms planning cold, 7 ms warm. With a 24-hour lookback on both the inner MAX and the outer scan: 1.3 ms warm, same six rows.

The rig (PostgreSQL 18.4, TimescaleDB 2.28.1, CI-sized), same shape, one server's hourly snapshot, compressed after 2 days, warm timings for the whole statement:

With both the inner MAX and the outer scan bounded to a day, the 366-chunk run takes 0.9-1.3 ms. Bounding only the inner MAX isn't enough. The outer collection_time = (InitPlan) can only exclude chunks at run time, so it still plans all 366 (1.0-1.2 s).

So a PostgreSQL-target analyze_server a year in pays several seconds of planning per config read, five reads per pass.

Fix shape, and what needs a ruling

Bind AnalysisContext.LatestValueStart (added by #3896: window end minus 24 h) as a lower bound on BOTH the inner MAX subquery and the outer c.collection_time. pg_server_config is hourly, so a live target always has a snapshot inside the day.

• PgTargetPostureSql is deliberately "the newest snapshot, not the window". Its doc argues against an hours filter, and it carries snapshot_age_minutes so the advice can say how stale it is. A 24 h bound drops the posture facts when the config collector has been down for a day. That may be acceptable, since it's the same rule Analysis 'latest value' reads scan a server's entire retained history: half of every analyze_server call, and the database-size fact counts dropped databases (all three SKUs) #3896 applies everywhere else. But it reverses a written decision.
• PgTargetClockSql falls back to UTC keying when no snapshot applies. With the bound, "no snapshot in the last day" also means UTC keying. That's probably fine, but it's a behavior change on the baseline side.
• Pins to update: PgTargetClockTests (it asserts the anchor text is shared token-for-token with the config read, and forbids $3), PgTargetKnobsTests, PgTargetMemoryTests, PgTargetPostureTests.