chunk_compression_stats()

Get chunk-specific statistics related to hypertable compression. All sizes are in bytes.

This view shows the cached size of chunks. The cached sizes are computed when compress_chunk is executed, or when a compression policy touches the chunk. An insert into a compressed chunk does not update the cached sizes. For more information about how to compute exact sizes, rather than cached sizes, see the chunks_detailed_size section.

Required arguments

Name Type Description
hypertable REGCLASS Name of the hypertable

Returns

Column Type Description
chunk_schema TEXT Schema name of the chunk
chunk_name TEXT Name of the chunk
number_compressed_chunks INTEGER the number of chunks used by the hypertable that are currently compressed
before_compression_table_bytes BIGINT Size of the heap before compression (NULL if currently uncompressed)
before_compression_index_bytes BIGINT Size of all the indexes before compression (NULL if currently uncompressed)
before_compression_toast_bytes BIGINT Size the TOAST table before compression (NULL if currently uncompressed)
before_compression_total_bytes BIGINT Size of the entire chunk table (table+indexes+toast) before compression (NULL if currently uncompressed)
after_compression_table_bytes BIGINT Size of the heap after compression (NULL if currently uncompressed)
after_compression_index_bytes BIGINT Size of all the indexes after compression (NULL if currently uncompressed)
after_compression_toast_bytes BIGINT Size the TOAST table after compression (NULL if currently uncompressed)
after_compression_total_bytes BIGINT Size of the entire chunk table (table+indexes+toast) after compression (NULL if currently uncompressed)
node_name TEXT nodes on which the chunk is located, applicable only to distributed hypertables

Sample usage

2. SELECT * FROM chunk_compression_stats('conditions')

4. ORDER BY chunk_name LIMIT 2;

6. -[ RECORD 1 ]------------------+----------------------

8. chunk_schema                   | _timescaledb_internal

10. chunk_name                     | _hyper_1_1_chunk

12. compression_status             | Uncompressed

14. before_compression_table_bytes |

16. before_compression_index_bytes |

18. before_compression_toast_bytes |

20. before_compression_total_bytes |

22. after_compression_table_bytes  |

24. after_compression_index_bytes  |

26. after_compression_toast_bytes  |

28. after_compression_total_bytes  |

30. node_name                      |

32. -[ RECORD 2 ]------------------+----------------------

34. chunk_schema                   | _timescaledb_internal

36. chunk_name                     | _hyper_1_2_chunk

38. compression_status             | Compressed

40. before_compression_table_bytes | 8192

42. before_compression_index_bytes | 32768

44. before_compression_toast_bytes | 0

46. before_compression_total_bytes | 40960

48. after_compression_table_bytes  | 8192

50. after_compression_index_bytes  | 32768

52. after_compression_toast_bytes  | 8192

54. after_compression_total_bytes  | 49152

56. node_name                      |

Use pg_size_pretty get the output in a more human friendly format.

2. SELECT pg_size_pretty(after_compression_total_bytes) AS total

4. FROM chunk_compression_stats('conditions')

6. WHERE compression_status = 'Compressed';

8. -[ RECORD 1 ]--+------

10. total | 48 kB