chunk_columnstore_stats()

CommunityCommunity functions are available under Timescale Community Edition. Click to learn more.

Retrieve statistics about the chunks in the columnstore

chunk_columnstore_stats returns the size of chunks in the columnstore, these values are computed when you call either:

add_columnstore_policy: create a job that automatically moves chunks in a hypertable to the columnstore at a specific time interval.
convert_to_columnstore: manually add a specific chunk in a hypertable to the columnstore.

Inserting into a chunk in the columnstore does not change the chunk size. For more information about how to compute chunk sizes, see chunks_detailed_size.

Since TimescaleDB v2.18.0

Samples

To retrieve statistics about chunks:

Show the status of the first two chunks in the conditions hypertable:


SELECT * FROM chunk_columnstore_stats('conditions')
  ORDER BY chunk_name LIMIT 2;

Returns:


-[ RECORD 1 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_1_1_chunk
compression_status             | Uncompressed
before_compression_table_bytes |
before_compression_index_bytes |
before_compression_toast_bytes |
before_compression_total_bytes |
after_compression_table_bytes  |
after_compression_index_bytes  |
after_compression_toast_bytes  |
after_compression_total_bytes  |
node_name                      |
-[ RECORD 2 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_1_2_chunk
compression_status             | Compressed
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes  | 8192
after_compression_index_bytes  | 32768
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 49152
node_name                      |

Use pg_size_pretty to return a more human friendly format:


SELECT pg_size_pretty(after_compression_total_bytes) AS total
  FROM chunk_columnstore_stats('conditions')
  WHERE compression_status = 'Compressed';

Returns:


-[ RECORD 1 ]--+------
total | 48 kB

Arguments

Name	Type	Default	Required	Description
`hypertable`	`REGCLASS`	-	✖	The name of a hypertable

Returns

Column	Type	Description
`chunk_schema`	TEXT	Schema name of the chunk.
`chunk_name`	TEXT	Name of the chunk.
`compression_status`	TEXT	Current compression status of the chunk.
`before_compression_table_bytes`	BIGINT	Size of the heap before compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`before_compression_index_bytes`	BIGINT	Size of all the indexes before compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`before_compression_toast_bytes`	BIGINT	Size the TOAST table before compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`before_compression_total_bytes`	BIGINT	Size of the entire chunk table (`before_compression_table_bytes` + `before_compression_index_bytes` + `before_compression_toast_bytes`) before compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`after_compression_table_bytes`	BIGINT	Size of the heap after compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`after_compression_index_bytes`	BIGINT	Size of all the indexes after compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`after_compression_toast_bytes`	BIGINT	Size the TOAST table after compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`after_compression_total_bytes`	BIGINT	Size of the entire chunk table (`after_compression_table_bytes` + `after_compression_index_bytes` + `after_compression_toast_bytes`) after compression. Returns `NULL` if `compression_status` == `Uncompressed`.
`node_name`	TEXT	DEPRECATED: nodes the chunk is located on, applicable only to distributed hypertables.

chunk_columnstore_stats()

Samples

Arguments

Returns

Related Content