Timescale Helpers

Timescale_Chunk_Compression.sql

CREATE FUNCTION public.show_all_chunks_detailed_size()
    RETURNS TABLE(
        hypertable text,
        chunk text,
        time_range tstzrange,
        total_bytes bigint,
        total_size text,
        table_size text,
        index_size text,
        toast_size text,
        compression_savings numeric
     ) AS
$func$
BEGIN
    RETURN QUERY EXECUTE (
      SELECT string_agg(format('
            SELECT
                %L AS hypertable,
                s.chunk_schema || ''.'' || s.chunk_name AS chunk,
                tstzrange(c.range_start, c.range_end) AS time_range,
                s.total_bytes,
                pg_size_pretty(s.total_bytes) AS total_size,
                pg_size_pretty(s.table_bytes) AS table_size,
                pg_size_pretty(s.index_bytes) AS index_size,
                pg_size_pretty(s.toast_bytes) AS toast_size,
                round(100 * (1 - p.after_compression_total_bytes::numeric / p.before_compression_total_bytes::numeric), 2) AS compression_savings
            FROM
                chunks_detailed_size(%L) s
                LEFT JOIN chunk_compression_stats(%L) p USING (chunk_name)
                LEFT JOIN timescaledb_information.chunks c USING (chunk_name)
            ', tbl, tbl, tbl
        ), ' UNION ALL ')
        FROM (
            SELECT hypertable_schema || '.' || hypertable_name AS tbl
            FROM timescaledb_information.hypertables
            ORDER BY 1
        ) sub
   );
END
$func$ LANGUAGE plpgsql;