chunk_compression_stats() Community

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

Required Arguments

NameDescription
hypertable(REGCLASS) Name of the hypertable

Returns

ColumnDescription
chunk_schema(NAME) Schema name of the chunk
chunk_name(NAME) 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(NAME) nodes on which the chunk is located, applicable only to distributed hypertables

Sample Usage

  1. SELECT * FROM chunk_compression_stats('conditions')
  2.   ORDER BY chunk_name LIMIT 2;
  4. -[ RECORD 1 ]------------------+----------------------
  5. chunk_schema                   | _timescaledb_internal
  6. chunk_name                     | _hyper_1_1_chunk
  7. compression_status             | Uncompressed
  8. before_compression_table_bytes |
  9. before_compression_index_bytes |
  10. before_compression_toast_bytes |
  11. before_compression_total_bytes |
  12. after_compression_table_bytes  |
  13. after_compression_index_bytes  |
  14. after_compression_toast_bytes  |
  15. after_compression_total_bytes  |
  16. node_name                      |
  17. -[ RECORD 2 ]------------------+----------------------
  18. chunk_schema                   | _timescaledb_internal
  19. chunk_name                     | _hyper_1_2_chunk
  20. compression_status             | Compressed
  21. before_compression_table_bytes | 8192
  22. before_compression_index_bytes | 32768
  23. before_compression_toast_bytes | 0
  24. before_compression_total_bytes | 40960
  25. after_compression_table_bytes  | 8192
  26. after_compression_index_bytes  | 32768
  27. after_compression_toast_bytes  | 8192
  28. after_compression_total_bytes  | 49152
  29. node_name                      |

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

  1. SELECT pg_size_pretty(after_compression_total_bytes) AS total
  2.   FROM chunk_compression_stats('conditions')
  3.   WHERE compression_status = 'Compressed';
  5. -[ RECORD 1 ]--+------
  6. total | 48 kB