We're excited to announce that Readyset now supports SQL Window Functions. This powerful addition means you can now cache more of your complex queries without modifying a single line of application code, bringing the performance benefits of Readyset to an even broader range of use cases.

What Are Window Functions?

Window Functions are one of SQL's most powerful features for analytical queries. They let you perform calculations across rows that are related to the current row, without needing complex self-joins or subqueries. Think of them like aggregates that don’t collapse the group into a single row. They can be used to calculate running totals, moving averages, rankings, and percentiles.

Here are some common examples:

Running totals:

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders;

Product rankings:

SELECT
  product_name,
  revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) as rank
FROM product_sales;

Smart deduplication

SELECT
  customer_id,
  email,
  first_name,
  last_name,
  created_at
FROM (
    SELECT 
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
  FROM customers
) ranked
WHERE rn = 1;

How Readyset Makes Window Functions Fast

Readyset's partially-stateful streaming dataflow architecture is uniquely suited for Window Functions. Instead of recalculating entire result sets when data changes, Readyset intelligently updates only the affected portions of your cached results. To learn more about how Readyset works under the hood, refer to the overview and streaming dataflow pages in our documentation, and this blog post which summarizes the core ideas behind Readyset.

When you cache a query like this:

CREATE CACHE FROM
SELECT customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_spend
FROM orders 
WHERE region = ?;

Readyset automatically:

1. Partitions your data based on the Window Function's PARTITION BY clause
2. Tracks incremental changes as new orders come in
3. Updates only affected partitions instead of recalculating everything
4. Serves results instantly from the cache

Getting Started is Simple

Readyset supports the standard SQL Window Function syntax you already know:

FUNCTION OVER ([PARTITION BY column1, column2, ...] [ORDER BY column1, column2, ...])

Currently supported functions include:

• SUM(), COUNT(), AVG(), MIN(), MAX()
• ROW_NUMBER(), RANK(), DENSE_RANK()
• And more on the way!

To get started with Readyset, please refer to our documentation.

Performance Tips

While Readyset requires no code changes, there are a couple changes you can make to take advantage of the streaming nature of Readyset and unlock even more performance.

• Use ORDER BY in the OVER clause when possible: Queries with ordered partitions often perform better because Readyset can apply changes incrementally rather than recalculating entire partitions.
• Choose partition keys thoughtfully: Smaller, more targeted partitions generally update faster than large ones.

Keep an eye out for a follow-up blog post where we discuss implementation details and why these changes can reduce latency even more.

The Bottom Line

With Window Function support, Readyset eliminates the traditional trade-off between analytical power and performance. You get the rich SQL features you need for modern applications without sacrificing speed, adding complexity, or even rewriting your queries.

Get started with Readyset today and see how Window Functions can transform your application's performance.

1: https://readyset.io/docs/concepts/overview

2: https://readyset.io/docs/concepts/streaming-dataflow

3: https://readyset.io/blog/behind-the-magic-how-readyset-speeds-up-queries-with-streaming-dataflow

4: https://readyset.io/docs/get-started