Skip to content

Catalog Metadata

Every Hydrolix table exposes metadata through a catalog backed by a PostgreSQL table that you can query through any query interface. The catalog contains metadata about the partitions and indexes in the table. Each row corresponds to one partition, and includes min_timestamp and max_timestamp columns that give the time window of data in that partition.

Catalog⚓︎

The catalog contains one row for each partition in its associated table. Each row includes that partition's filesystem path, the span of time-series data it contains, and the amount of storage space it takes up.

Catalog columns⚓︎

The catalog holds one row for each partition of a table's data. Each partition has these attributes:

Column Type Purpose
partition String Filesystem path to the partition, relative to the table's cloud-storage location.
min_timestamp DateTime Earliest timestamp of the partition's contiguous time-series data.
max_timestamp DateTime Latest timestamp of the partition's contiguous time-series data.
manifest_size UInt64 Size of the partition's manifest file, in bytes.
data_size UInt64 Size of the partition's data file, in bytes.
index_size UInt64 Size of the partition's index file, in bytes.
rows UInt64 Number of rows in the partition.
mem_size UInt64 Memory required to fully load the partition, in bytes.
root_path String Storage root path that the partition path is relative to.
shard_key String Shard key value assigned to the partition, if the table uses a shard key.
uncompressed_mem_size UInt64 Memory needed to hold the decoded, non-indexed data during decompression, in bytes.
raw_size UInt64 Size of the raw customer data in the partition, in bytes.
billing_bytes UInt64 Size of the raw, billable customer data in the partition, in bytes, with no compression, and no
indexing or other metadata overhead. This value is used in per-byte billing calculations.
Prior to v6.4, this column was named billing_byte.
storage_id String UUID of the Hydrolix storage location holding the partition data.

Query a table's catalog⚓︎

Use custom views syntax and the suffix .catalog to interact with the catalog. Concatenate the table and view with # (table_name#.catalog) and enclose the combined table and view name inside backticks. (`table_name#.catalog`)

The sample queries demonstrate how to query the catalog of the cluster's table hydro.logs.

1
SELECT * FROM hydro.`logs#.catalog` LIMIT 10

To view the total table size in bytes, use

1
2
3
SELECT
  sum(manifest_size + data_size + index_size) as total_size
FROM hydro.`logs#.catalog`

Use catalog access to analyze the effects of configuration changes, especially in the ingestion software. Hydrolix recommends new partitions are created between 1 GB and 3 GB in size. The merge system compacts partitions to reach optimal storage size of 2 GB and 4 GB per partition.

Visualize the distribution of partition sizes per day for the last ten days with this query.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
WITH 1048576 as mb, 1073741824 as gb
SELECT
  toStartOfDay(min_timestamp) as time,
  count(rows) as partitions,
  countIf(mem_size < 1 * mb) as "< 1MB",
  countIf(mem_size >= 1 * mb and mem_size < 5 * mb) as  "< 5MB",
  countIf(mem_size >= 5 * mb and mem_size < 50 * mb) as " < 50MB",
  countIf(mem_size >= 50 * mb and mem_size < 500 * mb) as " < 500MB",
  countIf(mem_size >= 500 * mb and mem_size < gb) as " < 1 GB",
  countIf(mem_size >= gb and mem_size < 2 * gb) as " < 2 GB",
  countIf(mem_size >= 2 * gb and mem_size < 3 * gb) as " < 3 GB",
  countIf(mem_size >= 3 * gb and mem_size < 4 * gb) as " < 4 GB"
FROM hydro."logs#.catalog"
where min_timestamp >= now() - INTERVAL 10 DAY
GROUP BY time
ORDER BY time DESC