Hydrolix can execute queries in a massively parallel fashion, thanks in part to its distributed nature and time-based partitioning. With time series data, time filters are often the preferred method for query optimization, but there are cases where they may not be applicable. In this post, you’ll learn how to use the unique capabilities of Hydrolix’s catalog as an alternative approach to efficiently query the latest N rows, giving you real-time insights on your latest data. On production-scale datasets, this optimization can reduce latest N rows query times from minutes to less than a second.
Why Query the Latest N Rows?
Querying the latest N rows is a common requirement for a variety of applications, from real-time analytics and monitoring to debugging. In many Hydrolix production use cases, there are spikes in data. A quiet minute might receive 1M rows, while a busy one might get ten times that. Filtering on a specific time range (like the last minute) won’t always suit your needs.
Benefits of Latest N Rows Queries
Querying the latest N rows offers a level of granularity and control that time-based queries can’t always provide, including:
- Specific row count: You can specify exactly how many rows you want, making it ideal for applications like real-time dashboards, metrics calculations, and alerting systems.
- Adaptability: You get a consistent amount of data regardless of its density. This is especially helpful when comparing data that has different levels of density over different time periods.
Example Scenario: Real-Time Alerting
Imagine you’re responsible for maintaining a high-traffic website and you need to monitor the health of incoming page requests. Specifically, you want to trigger an alert if there’s a sudden spike in 400-level errors, such as “Bad Request” or “Unauthorized,” within the most recent N page requests.
Querying a fixed time range like the last minute won’t capture the granularity you need. For instance, if the website experiences a sudden surge in traffic, the last minute could contain thousands of requests, diluting the impact of a small number of 400-level errors. On the flip side, a quiet minute might not provide enough data to accurately assess the situation.
By focusing on the latest N page requests, you can ensure that your alerting system is both responsive and precise, capturing the most relevant data for your specific monitoring needs. To make this request with Hydrolix, you can leverage the Hydrolix catalog to efficiently fetch the exact data you need for real-time alerting.
For more real-world use cases, jump to Other Real-World Use Cases for Latest N Row Queries.
Traditional Approach to Latest N Row Queries
In a traditional setup, you might use a ORDER BY timestamp LIMIT N like the following to fetch the latest 100 page requests that resulted in 400-level errors:
1 2 3 4 5 | SELECT timestamp, request_url, status_code FROM page_requests WHERE status_code >= 400 AND status_code < 500 ORDER BY timestamp DESC LIMIT 100; |
However, while this query is straightforward, it may run slowly on the Hydrolix platform at production scale. To understand why this isn’t the right query for the job, you first need to understand the basics of how the catalog works.
Hydrolix’s Catalog-Driven Optimization
The Hydrolix catalog is a specialized table that provides metadata about the partitions in your Hydrolix database. It contains minimal information for each partition, such as their min and max timestamps, and the number of rows each partition contains. Importantly, the catalog is typically many orders of magnitude smaller than a production-sized table, making it much more efficient to query.
When you make a time-filtered query to Hydrolix, the query head consults the catalog to efficiently select which partitions are relevant. By using the catalog’s metadata—the min and max timestamps—the query head performs partition pruning, narrowing down the number of partitions handled by the query peers. This makes the query process highly efficient by focusing only on the partitions that fall within the queried time frame.
The next image illustrates how this process works.
Now that you have a better understanding of how the catalog works, let’s take another look at the basic ORDER BY query discussed previously:
1 2 3 4 5 | SELECT timestamp, request_url, status_code FROM page_requests WHERE status_code >= 400 AND status_code < 500 ORDER BY timestamp DESC LIMIT 100; |
What happens when you use an ORDER BY query that has no time filter? Without the time filter, the query head must consider all partitions and the query peers must scan each partition, sending back every timestamp the table contains to the query head for sorting, which could potentially be trillions of rows in a production scale table. When you only need a small subset of the latest data, this is not an efficient approach.
In the next section, you’ll learn how to make your query much more efficient by utilizing the rows metadata in the catalog instead.
Optimizing a Latest N Rows Query
To efficiently query the latest N rows in Hydrolix, you can leverage the fact that Hydrolix exposes a special .catalog table view which provides metadata, allowing you to optimize your main table query within the same SQL statement. Unlike most data storage solutions, with Hydrolix, you can directly interact with the catalog, making queries like this possible. You can use this catalog view to find partitions with the most recent timestamps, counting rows as you go, and selecting only those partitions that collectively contain the latest N rows. Since the catalog table is much smaller than the main data table, this approach is highly efficient. Here’s the fully-optimized query. To learn how the query works, keep reading.
The Optimized Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | WITH ( WITH 100 AS LATEST SELECT [min(min_timestamp), max(max_timestamp)] AS min_max FROM ( SELECT *, any(running_row_count) OVER (ORDER BY max_timestamp DESC, min_timestamp ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_running_row_count, (running_row_count > LATEST) AND (prev_running_row_count < LATEST) AS is_boundary_row FROM ( SELECT min_timestamp, max_timestamp, sum(rows) OVER (ORDER BY max_timestamp DESC, min_timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_row_count FROM site_metrics.`page_requests#.catalog` ORDER BY max_timestamp DESC, min_timestamp ASC ) ORDER BY max_timestamp DESC, min_timestamp ASC ) WHERE (running_row_count < LATEST) OR is_boundary_row ) AS min_max — You can now use min_max to bound the query like so: SELECT timestamp, request_url, status_code FROM site_metrics.page_requests WHERE timestamp BETWEEN min_max[1] AND min_max[2] — Optimized! AND status_code >= 400 AND status_code < 500 ORDER BY timestamp DESC LIMIT 100; |
This query may look intimidating at first, so let’s take a closer look at how the query works, starting with the outer shell and then breaking down the inner query.
Understanding the Optimized Query: The Outer Shell
Before delving into the complexities of the optimized query, let’s start with its outer shell.
1 2 3 4 5 6 7 8 9 10 11 12 13 | WITH ( WITH 100 AS LATEST_ROWS SELECT [min(min_timestamp), max(max_timestamp)] AS min_max FROM ( –– Inner Query on site_metrics.`page_requests#.catalog` ) ) AS min_max SELECT timestamp, request_url, status_code FROM site_metrics.page_requests WHERE timestamp BETWEEN min_max[1] AND min_max[2] AND status_code >= 400 AND status_code < 500 ORDER BY timestamp DESC LIMIT 100; |
The outer shell defines a Common Table Expression (CTE) that sets LATEST_ROWS to 100. The Inner Query identifies the partitions that collectively contain a number of rows that is equal to or just exceeds the latest 100 rows. The query then determines the minimum and maximum timestamps for these selected partitions and focuses the search to partitions in this time range.
Note that the main query includes a time range filter. This is a significant improvement over a typical ORDER BY timestamp LIMIT 100 query, which has to scan all partitions because it lacks a time range filter. Two queries will be executed: one on the catalog and one on the main table. Because the Hydrolix catalog is substantially smaller than the main table, often by several orders of magnitude, the overall operation is much more efficient.
Understanding the Inner Query
Now let’s break down the inner query logic. As shown in the next code block, the inner query uses window functions to calculate a running row count, ordered by min_timestamp in descending order. Because this operation is performed on the Hydrolix catalog, which is significantly smaller than the main table, it’s much faster.
1 | sum(rows) OVER (ORDER BY max_timestamp DESC, min_timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_row_count |
Next, the query uses boundary rows to identify partitions that collectively contain a number of rows that is equal to or just exceeds 100 rows, ensuring that only the partitions that meet or slightly exceed the row count criteria are queried.
1 | (running_row_count > LATEST) AND (prev_running_row_count < LATEST) AS is_boundary_row |
Other Latest N Rows Use Cases
- Paginating real-time dashboards: If you’re building a dashboard that needs to display the latest 10,000 rows across 10 pages, you could use this optimization to quickly pull those 10,000 rows and then use LIMIT/OFFSET queries for efficient pagination.
- Rolling metrics calculation: If you need to calculate the average response time over the last 1,000 requests, this approach allows you to focus only on the most recent data points.
- Time-bounded analytics: When dealing with fluctuating data rates, you can adapt your analytics queries to only consider the most recent data for more accurate insights. You can then compare metrics over the first N rows versus the latest N rows to measure improvement.
- Real-time leaderboards: If you’re running a platform which ranks users, you could update the leaderboard based on the most recent scores or activities.
- Ad-hoc debugging for ingestion changes: After making changes to your data ingestion pipeline, you can use this query to quickly verify the most recent data points to ensure the changes took effect as expected, eliminating guesswork from how many rows came in the last few minutes.
Next Steps
Hydrolix’s catalog-based optimization gives you greater control and precision for querying your data—all while giving you extremely low latency and flexible scalability. If you’re ingesting a terabyte of data or more every day and you don’t have Hydrolix yet, sign up for a free trial. With Hydrolix, you get industry-leading data compression, lightning-fast queries, and unmatched scalability for your observability data. Learn more about Hydrolix.
