Skip to content

Query Efficiency

The Hydrolix platform and the underlying data format are designed to optimize storage for very large, high cardinality, high dimensionality datasets written to a distributed object storage like S3 or Google Cloud Storage.

System design⚓︎

To support high cardinality, dimensionality, and raw data volume, the Hydrolix system includes micro-indexes, tight data and index compression, and automated partitioning built on a columnar data structure.

This design enables Hydrolix to store full fidelity data for years. By comparison, traditional data systems are often limited due to sampling, roll-ups, or other restrictions in storage retention. Full fidelity data in many systems can only be kept for a short window, for example 48 hours or two weeks. These boundaries don't exist with Hydrolix.

Efficient query concepts⚓︎

Hydrolix is built to handle querying massive datasets stored in object storage in the cloud. Many basic queries perform well out-of-the-box.

Some query patterns common to online transaction processing (OLTP) databases, like PostgreSQL and MySQL, can lead to unexpectedly slow or memory-heavy queries. Most queries can be rewritten to perform very well using Hydrolix and its underlying ClickHouse SQL engine.

When tuning Hydrolix queries, keep the following concepts in mind:

  • Each query retrieves raw data from object storage. Controlling the data volume and frequency of object storage retrieval operations increases query performance and helps manage costs.
  • Data is stored by column. All values for a particular column are stored contiguously in memory. Operating on columns using ClickHouse functions is almost always faster than operating on rows using traditional SQL methods.
  • The SQL JOIN is better suited to row-oriented databases. With Hydrolix, JOINs between two or more large sets should be avoided.

This page contains tips and guidance for improving query efficiency at query time, with the singular exception of the section titled Use storage mapping. Storage mapping influences the physical layout of the data and partitions in object storage and can have a large effect on query performance.

Use storage mapping⚓︎

Improve the performance of queries by controlling data locality and avoiding resource bottlenecks when designing the table and its underlying storage. Use Hydrolix table and storage settings to improve efficiency of read and write times.

In contrast to other guidance on this page, features like column-value mapping, shard key, and spread list are ingestion time features. These features influence where and how the ingestion system stores the partitions.

Guidance: Use table settings to control object storage selection and distribute data.

Constrain by timestamp⚓︎

A crucial dimension in a Hydrolix table is time. Time is the central partitioning strategy used for storing data. Always limit queries to time ranges.

When not constrained by the primary timestamp, a query may end up scanning all data from all time, even if the requesting client is only interested in the last 10 minutes.

Guidance: Always use the primary, timestamp column in a query's WHERE clause.

Read only necessary columns⚓︎

The Hydrolix HDX data format is a columnar-based data format, which means that data is stored as columns rather than rows. This kind of structure is more efficient when querying large and wide datasets where disparate columns are required to satisfy a query.

When executing, a query retrieves all columns named anywhere in the query whether used in the returned columns or not.

Guidance: Refer only to necessary columns when writing a query.

Avoid wildcard SELECT⚓︎

The common SELECT * pattern and using wildcards in queries is inefficient in columnar data systems.

A wildcard request will retrieve all columns across the whole dataset, whether the column is needed or not.

Guidance: Return only necessary columns, even in subqueries, when writing a query.

Rewrite to avoid wildcard SELECT⚓︎

In this query, the nested SELECT requests all columns between two dates, then gets the first 10,000 rows and passes this to the top SELECT statement for processing. The top statement then runs another filter, WHERE countryCode=4.

If the dataset's width was 100 columns, this wildcard SELECT causes a huge amount of unused data to be retrieved from object storage into the Hydrolix cluster.

A better approach would be to name only the columns needed in the nested query and move the countrycode = 4 predicate in the top query into the nested query for more efficiency:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
        toStartOfDay(timestamp) AS day,
        countryCode,
        avg(performance) AS perf
FROM
        (
        SELECT
                *,
                countIf(performance > 10 and user = 'normal') AS slow_normal,
                countIf(performance > 10 and user = 'admin') AS slow_admin
        FROM
                sample.performance_logs
        WHERE
                timestamp BETWEEN '2021-11-01 00:00:00' AND '2021-11-05 00:00:00'
        LIMIT 10000) AS subquery
WHERE
        countryCode = 4
GROUP BY
        day,
        countryCode,
        perf
ORDER BY
        day

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
        toStartOfDay(timestamp) AS day,
        countryCode,
        avg(performance) AS perf
FROM
        (
        SELECT
                timestamp,
                countryCode,
                performance
        FROM
                sample.performance_logs
        WHERE
                timestamp BETWEEN '2021-11-01 00:00:00' AND '2021-11-05 00:00:00'
        AND
                countryCode = 4
        LIMIT 10000) AS subquery
GROUP BY
        day,
        countryCode
ORDER BY
        day

Use predicates⚓︎

Hydrolix uses a unique indexing strategy in which all columns are indexed by default. Speed up queries by using predicates. Smaller amounts of data will be retrieved from object storage.

The more predicates (WHERE column = x) you can use, the faster your query will be, ensuring only the data required to answer is actually retrieved from storage.

Guidance: Decrease data volume returned by adding more predicate filters to WHERE clauses.

Use LIMIT on small results⚓︎

Relying on LIMIT can be dangerous. Simple queries can use LIMIT to avoid retrieving more rows than necessary.

If a query includes GROUP BY, ORDER BY, or any aggregation function, the database execution engine must retrieve all matching rows from object storage and perform the sorting or aggregation before applying the LIMIT. The LIMIT only applies to the number of rows returned in the result set.

Guidance: Refine the query to reduce the working set before LIMIT applies.

Rewrite to constrain working set⚓︎

This query requests all rows and columns for all time from object storage, applies the grouping operation, and returns the first row in the set.

Reducing the queried time range or the columns decreases the amount of raw data required from object storage. In this example, the query result needs all columns, so uses a wildcard SELECT. Constraining the time window decreases the amount of raw data fetched.

1
2
3
4
5
6
7
8
SELECT
  *
FROM
  sample.performance_logs
GROUP BY
  error_level
LIMIT 
  1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
  *
FROM
  sample.performance_logs
WHERE
  timestamp > (NOW() - INTERVAL 1 HOUR)
GROUP BY
  error_level
LIMIT
  1

Use query caching⚓︎

The query system supports an optional query result cache. When the use_query_cache query option is set, Hydrolix caches the results for query_cache_ttl seconds and returns the result from cache if an identical query arrives before the results expire.

When the query option is specified and the result is in the cache, the database engine returns the cached result, improving query response time. When the result isn't in the cache, the database engine executes the query, returns the result, and populates the cache.

1
2
3
4
SELECT
  COUNT(*)
FROM hydro.logs
WHERE timestamp BETWEEN '2026-06-30 00:00:00' AND '2026-07-01 00:00:00'

1
2
3
4
5
SELECT
  COUNT(*)
FROM hydro.logs
WHERE timestamp BETWEEN '2026-06-30 00:00:00' AND '2026-07-01 00:00:00'
SETTINGS use_query_cache = true, query_cache_ttl=60

Confirm cache usage by measuring query latency. When using the HTTP Query API, the exec_time stat in the X-Hdx-Query-Stats HTTP response header records total query execution time. When returning from the query cache, the response time is a few milliseconds at most.

Unlike most query options, these query options are only recognized in the SETTINGS clause of a query.

Read more about this feature in the ClickHouse documentation.

Guidance: Use query caching for frequent queries. Set the TTL to balance execution cost and data recency.

Use circuit breakers⚓︎

Hydrolix offers circuit breakers that can protect clients from unmanageably large results and the query system from some inefficient queries. See Query Options Reference.

Guidance: Use circuit breakers to prevent runaway queries.