[Bug]: Invalid hypertable_compression_stats output until chunks are recompressed

[jflambert]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Compression

What happened?

I'm unable to get compression stats unless I forcibly recompress chunks.

TimescaleDB version affected

2.18.1

PostgreSQL version used

16.6

What operating system did you use?

timescaledb-ha:pg16.6-ts2.18.1

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

How can we reproduce the bug?

DROP TABLE IF EXISTS test_signals;
CREATE TABLE test_signals
(
    device_id uuid NOT NULL,
    channel text NOT NULL,
    signal text NOT NULL,
    signal_timestamp TIMESTAMPTZ NOT NULL,
    value float NOT NULL,
    PRIMARY KEY (device_id, channel, signal, signal_timestamp)
);
SELECT create_hypertable('test_signals', 'signal_timestamp', CHUNK_TIME_INTERVAL => INTERVAL '1 DAY');

ALTER TABLE test_signals SET (
  timescaledb.enable_columnstore,
  timescaledb.segmentby='device_id',
  timescaledb.orderby='channel, signal, signal_timestamp DESC'
);

INSERT INTO test_signals(channel, device_id, signal_timestamp, signal, value)
SELECT 'chan', device_ids, signal_timestamps, signals, random()*100
FROM UNNEST(ARRAY[gen_random_uuid(), gen_random_uuid(), gen_random_uuid()]) AS device_ids,
GENERATE_SERIES('2025-02-01', '2025-02-11', '1 second'::INTERVAL) AS signal_timestamps,
UNNEST(ARRAY['signal1', 'signal_longer_name_2', 'signal_much_much_longer_name_3']::TEXT[]) AS signals;

SELECT pg_size_pretty(before_compression_total_bytes) AS before_compress, 
  pg_size_pretty(after_compression_total_bytes) AS after_compress, 
  pg_size_pretty(hypertable_size('test_signals')) AS hypertable_size
FROM hypertable_compression_stats('test_signals');

initial table size is 1.7GB

 before_compress | after_compress | hypertable_size 
-----------------+----------------+-----------------
 0 bytes         | 176 kB         | 1736 MB
(1 row)

Let's compress.

SELECT compress_chunk(c) FROM show_chunks('test_signals') c;
SELECT pg_size_pretty(before_compression_total_bytes) AS before_compress, 
  pg_size_pretty(after_compression_total_bytes) AS after_compress, 
  pg_size_pretty(hypertable_size('test_signals')) AS hypertable_size
FROM hypertable_compression_stats('test_signals');

Stats don't show up at all. table size is shrinking due to the autovacuum daemon presumably.

 before_compress | after_compress | hypertable_size 
-----------------+----------------+-----------------
 0 bytes         | 176 kB         | 1542 MB
(1 row)

Let's decompress.

SELECT decompress_chunk(c) FROM show_chunks('test_signals') c;
SELECT pg_size_pretty(before_compression_total_bytes) AS before_compress, 
  pg_size_pretty(after_compression_total_bytes) AS after_compress, 
  pg_size_pretty(hypertable_size('test_signals')) AS hypertable_size
FROM hypertable_compression_stats('test_signals');

Table size neither grows nor shrinks at this point.

 before_compress | after_compress | hypertable_size 
-----------------+----------------+-----------------
                 |                | 1292 MB
(1 row)

Let's compress again.

SELECT compress_chunk(c) FROM show_chunks('test_signals') c;
SELECT pg_size_pretty(before_compression_total_bytes) AS before_compress, 
  pg_size_pretty(after_compression_total_bytes) AS after_compress, 
  pg_size_pretty(hypertable_size('test_signals')) AS hypertable_size
FROM hypertable_compression_stats('test_signals');

Finally, expected values from the view (though slightly different from table size)

 before_compress | after_compress | hypertable_size 
-----------------+----------------+-----------------
 1292 MB         | 66 MB          | 67 MB
(1 row)

[jflambert]

Still a problem in 2.18.2 FYI

[jflambert]

@antekresic to be clear, the bug happens regardless of access method. I shouldn't have included that in my replication steps I suppose.