Perfmon trend read can reach 30 days if perfmon data is compressed by counter

Claude posting for Erik Darling.

In #5570, the perfmon trend read (get_perfmon_trend) stays at 7 days on the web and in MCP. Other trend reads there now reach 30 days. A read reaches 30 days only if it runs in under 10 s on a large store.

What was measured

On a large store, a 30-day read of one counter (Batch Requests/sec) for one server took 11.2 s with a cold cache. About 10.1 s of that was disk reads. With a warm cache it took about 0.95 s. The measurements are in #5562.

Why it is slow

The perfmon table is compressed with its rows grouped by server only (compress_segmentby = server_id). To find one counter, the read decodes the counter and object names of every perfmon row for that server in the window. That covers every counter the server collects. The read then keeps only the rows for the one counter it was asked for.

Proposal

Group the compressed perfmon data by counter as well, for example by server_id, object_name and counter_name. Then a read of one counter skips the compressed batches of every other counter.

This is a schema change. Before building it, check these:

How existing compressed chunks move to the new grouping.
How many counters each server collects. Smaller groups can compress less well.
The effect on the other perfmon reads, such as get_perfmon_stats, which reads every counter.
A new 30-day timing of the trend read on a large store.

If the timing is under 10 s, raise get_perfmon_trend to 30 days.

Claude posting for Erik Darling.

These are the timing steps for this change. They run on a restored copy of a large store (TimescaleDB 2.30.1, about 14 M perfmon rows a day, one-day chunks). Everything below only reads. Nothing writes to the store.

Part A: before (now)

Run this on the copy once its current upgrade step is finished, and before the build for this change goes on it.

Do not open any perfmon page or run any perfmon read on the copy first. The point is a cold first read.

Pick the server with the most perfmon rows. This query reads only the last two hours:

SELECT server_id, count(*) AS rows_2h FROM collect.perfmon_stats WHERE collection_time >= (now() AT TIME ZONE 'UTC') - interval '2 hours' GROUP BY server_id ORDER BY 2 DESC LIMIT 1;

Run the script below with that server: psql <connection> -v server_id=<id> -f 5574-timing.sql. Then run section 1 a second time to get a warm number.
Post on this issue, for sections 1 and 2: the execution time and the Buffers: line of the top node. Also post the cold and warm section 1 times, and what sections 3 and 4 return.

Part B: after (the build follows)

I will add the build commit to this issue when it is ready. Part B is: deploy that build on the copy and start the service. The grouping change runs in the hourly maintenance pass, and the old chunks inside 30 days are then compressed again, newest first. Run section 3 until old_grouping_in_reach is 0. Then restart PostgreSQL to empty its cache, and run Part A's steps 3 and 4 again with the same server.

5574-timing.sql

-- #5574 timing: READ ONLY. psql <connection> -v server_id=<id> -f 5574-timing.sql
-- Nothing here writes: EXPLAIN (ANALYZE) runs SELECTs, and the progress query reads catalogs.
-- The statements are the product's own SQL text (DarlingTrendReader.PerfmonTrendBucketedSql and
-- DarlingDataReader.LatestPerfmonStatsSql) with the parameters replaced by literals; 240 is the bucket width the
-- 30-day window resolves to.
\set ON_ERROR_STOP on
\timing on

\echo === 1. get_perfmon_trend: one counter, one server, 30 days (compare buffers and time before and after the re-group)
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    GREATEST(date_bin(CAST(240 AS integer) * INTERVAL '1 minute', collection_time, TIMESTAMP '2000-01-01 00:00:00'), ((now() AT TIME ZONE 'UTC') - interval '30 days')::timestamp) AS bucket_start,
    AVG(cntr_value) AS avg_value,
    MAX(cntr_value) AS max_value,
    (array_agg(cntr_value ORDER BY collection_time DESC))[1] AS last_value,
    SUM(delta_cntr_value) FILTER (WHERE sample_interval_seconds > 0) AS rated_delta,
    SUM(sample_interval_seconds) FILTER (WHERE sample_interval_seconds > 0) AS rated_seconds,
    COUNT(*) FILTER (WHERE sample_interval_seconds = 0) AS unknowable_collections,
    SUM(delta_cntr_value) FILTER (WHERE sample_interval_seconds = 0) AS unknowable_delta,
    SUM(delta_cntr_value) FILTER (WHERE sample_interval_seconds IS NULL) AS unrecorded_delta,
    MAX(CAST(delta_cntr_value AS double precision) / NULLIF(sample_interval_seconds, 0)) AS peak_per_second,
    CASE WHEN MIN(cntr_type) = MAX(cntr_type) THEN MAX(cntr_type) END AS cntr_type,
    SUM(artifacts) AS artifacts_set_aside
FROM (
SELECT
    collection_time,
    CAST(SUM(cntr_value) FILTER (WHERE NOT is_artifact) AS bigint) AS cntr_value,
    CAST(SUM(delta_cntr_value) FILTER (WHERE NOT is_artifact) AS bigint) AS delta_cntr_value,
    CAST(MAX(sample_interval_seconds) AS bigint) AS sample_interval_seconds,
    CASE WHEN MIN(cntr_type) = MAX(cntr_type) THEN MAX(cntr_type) END AS cntr_type,
    COUNT(*) FILTER (WHERE is_artifact) AS artifacts
FROM (
    SELECT
        collection_time,
        cntr_value,
        delta_cntr_value,
        sample_interval_seconds,
        cntr_type,
        COALESCE((cntr_type IN (65792, 65536)
AND object_name IS NOT NULL
AND right(object_name, 16) = ':Wait Statistics'
AND prev_value IS NOT NULL
AND next_value IS NOT NULL
AND cntr_value >= 1000000
AND cntr_value > 1000 * GREATEST(prev_value, next_value)), false) AS is_artifact
    FROM (
        SELECT
            collection_time,
            object_name,
            cntr_value,
            delta_cntr_value,
            sample_interval_seconds,
            cntr_type,
            lag(cntr_value) OVER w AS prev_value,
            lead(cntr_value) OVER w AS next_value
        FROM v_perfmon_stats
        WHERE server_id = :server_id
        AND   counter_name = 'Batch Requests/sec'
        AND   collection_time >= ((now() AT TIME ZONE 'UTC') - interval '30 days')::timestamp
        AND   collection_time <= (now() AT TIME ZONE 'UTC')::timestamp
        AND   object_name IS NOT NULL AND right(object_name, 16) = ':Wait Statistics'
        WINDOW w AS (PARTITION BY object_name, instance_name ORDER BY collection_time)

        UNION ALL

        SELECT
            collection_time,
            object_name,
            cntr_value,
            delta_cntr_value,
            sample_interval_seconds,
            cntr_type,
            NULL AS prev_value,
            NULL AS next_value
        FROM v_perfmon_stats
        WHERE server_id = :server_id
        AND   counter_name = 'Batch Requests/sec'
        AND   collection_time >= ((now() AT TIME ZONE 'UTC') - interval '30 days')::timestamp
        AND   collection_time <= (now() AT TIME ZONE 'UTC')::timestamp
        AND   NOT (object_name IS NOT NULL AND right(object_name, 16) = ':Wait Statistics')
    ) AS raw
) AS flagged
GROUP BY collection_time
ORDER BY collection_time
) AS collections
GROUP BY 1
ORDER BY 1;

\echo === 2. get_perfmon_stats: the latest snapshot, every counter (expected unchanged: it reads the newest, uncompressed chunk)
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    counter_name,
    instance_name,
    cntr_value,
    delta_cntr_value,
    collection_time,
    cntr_type,
    sample_interval_seconds
FROM v_perfmon_stats
WHERE server_id = :server_id
AND   collection_time = (SELECT MAX(collection_time) FROM v_perfmon_stats WHERE server_id = :server_id)
ORDER BY counter_name;

\echo === 3. Re-group progress: compressed perfmon_stats chunks inside the 30-day reach, by grouping
-- 'server_id' is the old grouping; 'server_id,counter_name' is the new one. old_in_reach reaches 0 when the re-group is done.
-- Needs TimescaleDB 2.14.1+ (timescaledb_information.chunk_compression_settings).
SELECT count(*) FILTER (WHERE s.segmentby IS DISTINCT FROM 'server_id,counter_name') AS old_grouping_in_reach,
       count(*) FILTER (WHERE s.segmentby = 'server_id,counter_name')                AS new_grouping_in_reach,
       min(c.range_start) FILTER (WHERE s.segmentby IS DISTINCT FROM 'server_id,counter_name') AS oldest_old_chunk_start,
       max(c.range_start) FILTER (WHERE s.segmentby IS DISTINCT FROM 'server_id,counter_name') AS newest_old_chunk_start
FROM timescaledb_information.chunks AS c
JOIN timescaledb_information.chunk_compression_settings AS s
  ON s.chunk = to_regclass(format('%I.%I', c.chunk_schema, c.chunk_name))
WHERE c.hypertable_schema = 'collect'
AND   c.hypertable_name = 'perfmon_stats'
AND   c.is_compressed
AND   c.range_end > now() - interval '30 days';

\echo === 4. The hypertable setting itself (should read: server_id, counter_name)
SELECT string_agg(cs.attname, ', ' ORDER BY cs.segmentby_column_index) AS hypertable_segmentby
FROM timescaledb_information.compression_settings AS cs
WHERE cs.hypertable_schema = 'collect' AND cs.hypertable_name = 'perfmon_stats' AND cs.segmentby_column_index IS NOT NULL;

Perfmon re-group: readers are never blocked, and a background loop co…

perfmon re-group: live tests for the crash between halves, the pause,…

perfmon re-group tests: hold a chunk's lock through a disposable (liv…

Claude posting for Erik Darling.

Part B: after

The build is rehearsal/5574-on-5571 at ca0db631c. It is the dev branch, plus #5573 (the Query Store interval change, at c5fca7d1b), plus this change (#5579, at 8cec10e6d). It holds everything the copy runs now from step 2b, so nothing on the copy goes back.

Part A runs first, with the build the copy has now. Post its numbers before this build goes on.

Deploy the build on the copy and start the service. At start, the hypertable setting changes to server_id, counter_name. Section 4 of the script shows it. New chunks are compressed with the new grouping from then on.
The re-group starts with the first hourly pass, about 30 s after the start. It takes the compressed chunks inside 30 days one at a time, newest first. After each chunk it waits as long as that chunk took, and never less than 15 s. Each chunk is two steps: it is decompressed, and then it is compressed again with the new grouping. The service log has one line per chunk, "re-grouped perfmon_stats chunk ... rows ... in ... ms". It ends with a "re-group run over" line. On this store that is about 30 chunks. A test on one 13 M-row chunk puts the whole run at 2 to 3 hours.
While the re-group runs, run section 1 of the script once and post its time. It shows how long a read waits beside a chunk that is being re-grouped.
Run section 3 until old_grouping_in_reach is 0.
Restart PostgreSQL to empty its cache. Then run Part A's steps 3 and 4 again with the same server. Run section 1 cold, then warm, then sections 2, 3 and 4.
Post on this issue:
- The first, the slowest and the last per-chunk log lines, and the "re-group run over" line.
- Every service log line at Warning level with "perfmon_stats" in it.
- The section 1 time from step 3.
- From step 5: the time and the top node's Buffers: line for sections 1 and 2. Also the cold and warm section 1 times, and what sections 3 and 4 return.

The product still limits this read to 7 days. The 30-day limit comes in a later commit on this change. The script runs the 30-day read directly, so these steps do not wait for it.

Everything in the script only reads. The writes in Part B are the service's own: the setting change and the re-group.

Claude posting for Erik Darling.

Part A (before) on the restored copy: section 1 takes 11.2 s cold and 1.16 s warm. In each of the two UNION ALL legs, the 30-day read decompresses all of the server's 4,494 compressed batches across 38 chunks. It throws away about 10.4 M rows per leg to keep 34,956 rows of one counter.

Setup:

The copy: a restored copy of a store monitoring 43 SQL Server primaries, TimescaleDB 2.30.1, still running the step 2b build. The Darling service was stopped, so nothing collects.
Cold start:
- PostgreSQL was restarted at 19:22:04Z to empty shared_buffers (8 GB).
- The first perfmon statement after the restart was step 2's server pick.
- The OS file cache was not emptied.
No vacuum during the read:
- An autovacuum of an unrelated table (query_store_interval_wide_legacy) had run for 99 min and was cancelled.
- On this copy only, I set autovacuum_enabled = false on that one table, so the restart didn't relaunch it under the cold read.
- No other process ran.
The server (step 2): the copy has no rows newer than 2026-10-08 12:47:23, so I read the 2 hours that end at its newest row. The pick is the server with 33,600 rows in that window.
The script: as posted, with \set server_id for that server. Section 1 ran again right after, for the warm number.
The window: the 30-day bounds use now() (19:31Z), and the copy's data ends 7 h earlier. Part B's rerun will start a few hours later, but the same 38 compressed chunks stay inside the window.

Results

Section	Execution time	Top node `Buffers:`	Notes
1, cold	11,215.6 ms (psql 11.4 s), planning 216.4 ms	`shared hit=440239 read=35735 dirtied=160`, I/O read 9,977 ms	180 buckets
1, warm	1,164.7 ms, planning 32.3 ms	`shared hit=475974`
2	1.3 ms, planning 5.6 ms	`shared hit=94`	280 rows. It reads the newest, uncompressed chunk.

Section 3:

old_grouping_in_reach	new_grouping_in_reach	oldest_old_chunk_start	newest_old_chunk_start
38	0	2026-09-08 00:00:00+00	2026-10-06 12:00:00+00

Section 4: hypertable_segmentby = server_id

Where section 1's time goes (cold)

UNION ALL leg	Compressed chunks (`ColumnarScan`)	Compressed batches read	Rows decompressed and removed by the vectorized filter	Rows kept	Uncompressed chunks (Bitmap Heap Scan)	Time
`:Wait Statistics` objects	38	4,494	10,400,880	0	4	10,638 ms
other objects	38	4,494	10,363,734	34,956 (+2,190 from the uncompressed chunks)	4	457 ms

All the cold I/O is in the first leg: 35,735 blocks (279 MB) in 9.98 s.
The second leg reads the same pages, now cached.
Both legs decompress every batch the server has in reach and keep only the rows of one counter, because the batches are grouped by server_id alone. The filter is vectorized, and Batches Removed by Filter doesn't appear.

Next: the build for Part B (ca0db631c) goes on the copy, with the same guards as every start.

perfmon re-group: pause for the time a busy lock cost, 30-minute stat…

perfmon re-group: server cancel logged as such with its real time, go…

Claude posting for Erik Darling.

Part B (after) on the restored copy: section 1 now takes 1.30 s cold and 0.22 s warm, against 11.2 s and 1.16 s in Part A. Each UNION ALL leg now reads 57 compressed batches for the counter instead of all 4,494 of the server's batches. Its filter drops about 0.5 M rows per leg instead of about 10.4 M. The re-group took 38 chunks in 76.6 min, with no skips and no warnings.

Setup:

The copy: the same copy as Part A (issuecomment-6067584601).
The build: ca0db631c.
The server and script: the same server and the same script as Part A.
Guards before the start: outbound blocked (6 of 6 test connections failed from a SYSTEM task), servers, routes, channels and alerts disabled, the minimal config file.
Background workers: TimescaleDB background workers stayed at 0. The re-group is the service's own loop (PerfmonRegroupDrain), which calls decompress_chunk and compress_chunk directly, so no policy job was needed.

1. The start and the re-group (service log)

The start: at 19:43:34Z: Postgres store ready (schema v172, 0 migration(s) applied). Section 4 read server_id, counter_name at 19:45Z.
The re-group began 30 s after the start, not at a :30 tick:

2026-10-08 19:44:04.642 TimescaleDB: perfmon_stats re-group started: 38 chunk(s) inside the 30-day reach have the old grouping; they are re-grouped one at a time, newest first, each followed by a pause as long as it took (at least 15 s) (#5574)

	Service log line
First	`2026-10-08 19:44:51.023 re-grouped perfmon_stats chunk _timescaledb_internal._hyper_24_4368_chunk (6932109 rows) from 'server_id' to 'server_id,counter_name' in 46378 ms (#5574)`
Slowest	`2026-10-08 20:32:33.823 re-grouped perfmon_stats chunk _timescaledb_internal._hyper_24_2574_chunk (10884684 rows) from 'server_id' to 'server_id,counter_name' in 86283 ms (#5574)`
Last	`2026-10-08 21:00:39.982 re-grouped perfmon_stats chunk _timescaledb_internal._hyper_24_1682_chunk (9690648 rows) from 'server_id' to 'server_id,counter_name' in 64926 ms (#5574)`
Run over	`2026-10-08 21:00:39.990 perfmon_stats re-group run over: 38 chunk(s) re-grouped to 'server_id, counter_name' in 4595378 ms; 0 skipped for a busy lock or an error (tried again at the next hourly check); 0 chunk(s) inside the 30-day reach still have the old grouping (#5574)`

The chunks: 341,463,424 rows in all.
Time per chunk: p50 69.6 s (from 33.5 s to 86.3 s). That's 2,330 s of rewriting inside the 4,595 s run, so the rest was the pauses.
Warning lines with perfmon_stats: none. The only Warning in the window was the dummy server's Connect failed, which is expected on the copy.
stays uncompressed and is stopping with perfmon_stats chunk: not in the log.

2. Section 1 while the re-group ran

The time: at 19:49:51Z, after chunk 3: 1,053 ms (psql 1,093 ms), Buffers: shared hit=439267. That's warm, since it ran after Part A on the same pages.
The overlap: a compress_chunk statement was active a few seconds before the read and none was active right after it. So this read did not wait beside a chunk being rewritten. I did not catch a read during a rewrite.

3. Before the PostgreSQL restart

Uncompressed perfmon_stats chunks with range_end inside 30 days: 4. That's the same 4 as the uncompressed chunks Part A's plan read (Bitmap Heap Scans).

chunk_name	range_start	range_end
_hyper_24_4416_chunk	2026-10-07 00:00:00+00	2026-10-07 12:00:00+00
_hyper_24_4475_chunk	2026-10-07 12:00:00+00	2026-10-08 00:00:00+00
_hyper_24_4521_chunk	2026-10-08 00:00:00+00	2026-10-08 12:00:00+00
_hyper_24_4580_chunk	2026-10-08 12:00:00+00	2026-10-09 00:00:00+00

4. After the restart (Part A's steps 3 and 4 again)

The restart: the service was stopped first, and PostgreSQL was restarted at 21:12:23Z. As in Part A, the OS file cache was not emptied.
The window: it uses now(), which was 21:12Z here against 19:31Z in Part A. Both runs read the same 38 compressed chunks plus the 4 uncompressed ones. The cold run returned 179 buckets, against 180 in Part A.

Section	Part A (before)	Part B (after)
1, cold	11,215.6 ms, planning 216.4 ms. `shared hit=440239 read=35735 dirtied=160`, I/O read 9,977 ms	1,302.5 ms, planning 512.8 ms. `shared hit=22694 read=18642`, I/O read 3,130 ms
1, warm	1,164.7 ms. `shared hit=475974`	218.6 ms. `shared hit=41216`
2	1.3 ms. `shared hit=94`	9.7 ms. `shared hit=175 read=96` (cold, run right after section 1)
3	old 38, new 0	old 0, new 38
4	`server_id`	`server_id, counter_name`

Where section 1's time goes, per UNION ALL leg:

Leg		Compressed chunks	Compressed batches read	Rows removed by the filter	Rows kept
`:Wait Statistics` objects	Part A	38	4,494	10,400,880	0
	Part B	38	57	514,606	0
other objects	Part A	38	4,494	10,363,734	34,956
	Part B	38	57	478,009	34,887

The index: the compressed chunks' index scans now include counter_name in the index condition (server_id = … AND counter_name = 'Batch Requests/sec' AND _ts_meta_v2_first_collection_time >= …).
The plan shape: it now runs as two Parallel Custom Scan (ChunkAppend) legs (Workers Planned 2, Launched 2).
Section 2: it now plans a Merge Append where Part A had a Sort. It took 9.7 ms cold here.

get_perfmon_trend reaches 30 days on the web and MCP; the two timed r…

Perfmon data is compressed per counter, so a one-counter trend reads …

Claude posting for Erik Darling. Fixed in dev by #5579 (e68ed63): perfmon data compresses per counter, older chunks are re-grouped in the background, and the web and MCP perfmon trend reach 30 days. It ships in the next release.

Navigation Menu

Repository navigation

Perfmon trend read can reach 30 days if perfmon data is compressed by counter #5574

What was measured

Why it is slow

Proposal

Part A: before (now)

Part B: after (the build follows)

Part B: after

Results

Where section 1's time goes (cold)

1. The start and the re-group (service log)

2. Section 1 while the re-group ran

3. Before the PostgreSQL restart

4. After the restart (Part A's steps 3 and 4 again)

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Perfmon trend read can reach 30 days if perfmon data is compressed by counter #5574

Description

What was measured

Why it is slow

Proposal

Activity

erikdarlingdata commented on Oct 8, 2026

Part A: before (now)

Part B: after (the build follows)

erikdarlingdata commented on Oct 8, 2026

Part B: after

erikdarlingdata commented on Oct 8, 2026

Results

Where section 1's time goes (cold)

erikdarlingdata commented on Oct 8, 2026

1. The start and the re-group (service log)

2. Section 1 while the re-group ran

3. Before the PostgreSQL restart

4. After the restart (Part A's steps 3 and 4 again)

erikdarlingdata commented on Oct 9, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions