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

NameTypeDescription
hypertableREGCLASSName of the hypertable

Returns

ColumnTypeDescription
chunk_schemaTEXTSchema name of the chunk
chunk_nameTEXTName of the chunk
number_compressed_chunksINTEGERthe number of chunks used by the hypertable that are currently compressed
before_compression_table_bytesBIGINTSize of the heap before compression (NULL if currently uncompressed)
before_compression_index_bytesBIGINTSize of all the indexes before compression (NULL if currently uncompressed)
before_compression_toast_bytesBIGINTSize the TOAST table before compression (NULL if currently uncompressed)
before_compression_total_bytesBIGINTSize of the entire chunk table (table+indexes+toast) before compression (NULL if currently uncompressed)
after_compression_table_bytesBIGINTSize of the heap after compression (NULL if currently uncompressed)
after_compression_index_bytesBIGINTSize of all the indexes after compression (NULL if currently uncompressed)
after_compression_toast_bytesBIGINTSize the TOAST table after compression (NULL if currently uncompressed)
after_compression_total_bytesBIGINTSize of the entire chunk table (table+indexes+toast) after compression (NULL if currently uncompressed)
node_nameTEXTnodes 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