Skip to content

Instantly share code, notes, and snippets.

@SoMuchForSubtlety

SoMuchForSubtlety/timescale-chunk-stats.sql

Last active May 8, 2023 12:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save SoMuchForSubtlety/28caf08940f3dd5c9e7eba6452284179 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save SoMuchForSubtlety/28caf08940f3dd5c9e7eba6452284179 to your computer and use it in GitHub Desktop.
Download ZIP
Get timescale hypertable chunk info with compression stats
Raw
timescale-chunk-stats.sql
select c.chunk_name,
to_char(range_start, 'YYYY-MM-DD HH24:MI') start,
to_char(AGE(range_end, range_start), 'DD "days" HH24 "hours"') as interval,
is_compressed,
pg_size_pretty(
CASE
WHEN cs.before_compression_total_bytes is null THEN cds.total_bytes
ELSE cs.before_compression_total_bytes END
) as before_total,
pg_size_pretty(after_compression_total_bytes) as after_total,
before_compression_total_bytes / after_compression_total_bytes as compression_ratio
from timescaledb_information.chunks as c
left join chunk_compression_stats('table_name') as cs on c.chunk_name = cs.chunk_name
left join chunks_detailed_size('table_name') cds on c.chunk_name = cds.chunk_name
where c.hypertable_name = 'table_name'
order by c.range_start desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment