---
title: "Postgres Sorting at Scale Needs More Than ORDER BY"
published: 2026-06-24T09:06:04.000-04:00
updated: 2026-06-24T09:06:04.000-04:00
excerpt: "At scale, Postgres ORDER BY on time-series data spills to disk and slows queries. See how time_bucket and continuous aggregates cut a 4s query to 9ms."
tags: PostgreSQL
authors: NanoHertz Communications
---

> **TimescaleDB is now Tiger Data.**

Your dashboard query ran in 180ms at 10 million rows. Six months later, at 500 million rows, it takes 4 seconds. Nothing changed in the schema. The index is still there. So what happened?

The index on `(device_id, ts DESC)` looks like it covers the query. And it does, up to a point. The trouble starts when that index outgrows your [shared buffer pool](https://www.tigerdata.com/blog/understanding-why-os-ram-postgres-buffer-cache-compete) and random I/O takes over. At that point, `ORDER BY ts DESC` stops being a cheap index walk and becomes a disk-backed sort the planner has no way around.

This guide shows you how to find Sort node bottlenecks using `EXPLAIN` output, rewrite dashboard queries with `time_bucket()` to remove them, and push the work even further upstream with continuous aggregates. The rewrites in this article dropped a 4-second query to 9ms on a 500M-row test table.

## What You Will Do

By the end of this guide, you'll be able to identify Sort nodes in your slowest `ORDER BY ts` queries using `pg_stat_statements` and `EXPLAIN (ANALYZE)`, rewrite those queries using `time_bucket()` to skip the row-level sort entirely, and set up a continuous aggregate so your dashboards never touch the raw table at all.

This guide applies to aggregate reads: hourly averages, per-minute counts, and daily totals. If your query needs individual rows ordered by time (the last 50 readings for a specific device, for instance), the fix is different: use a [covering index](https://www.tigerdata.com/learn/postgresql-performance-tuning-optimizing-database-indexes) with a tight `LIMIT` clause and avoid large time ranges.

## Before You Start

You'll need three things: the `pg_stat_statements` extension enabled on your database (`CREATE EXTENSION IF NOT EXISTS pg_stat_statements`), the TimescaleDB extension installed (Tiger Data includes this by default), and a [hypertable](https://www.tigerdata.com/blog/scale-postgresql-via-partitioning-hypertables) with at least 100M rows. The Sort node problems this guide addresses do not appear at smaller scales.

If you're on a fresh Tiger Data instance, you can load sample time-series data using the [getting started guide](https://www.tigerdata.com/docs/get-started) to reach the row counts where these issues show up.

## Why ORDER BY Stops Being Free

When a table is small, a B-tree index on `(device_id, ts DESC)` handles `ORDER BY ts DESC` efficiently. Postgres walks the index in order, fetches the matching heap pages, and returns sorted rows without a dedicated Sort step. Run `EXPLAIN (ANALYZE)` on a 10M-row table and there's no Sort node in the plan.

That changes once the index grows past what fits in `shared_buffers`. Reading index pages in sorted order now means fetching pages not in cache. Each fetch becomes a random disk read. The more rows your time range touches, the more random I/O the query generates. At that point, the planner falls back to a Bitmap Heap Scan followed by an explicit Sort. If the data being sorted exceeds `work_mem`, the sort spills to disk.

Here's what that looks like. Run this on a 500M-row table:

```SQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, ts, value
FROM device_metrics
WHERE device_id = 42
  AND ts > now() - interval '1 hour'
ORDER BY ts DESC;
```

The `EXPLAIN` output shows the Sort node using external merge (disk-backed), sorting over 500,000 rows:

```SQL
Sort  (cost=52341.08..53591.31 rows=500492 width=20)
      (actual time=3981.04..4241.17 rows=500492 loops=1)
  Sort Key: ts DESC
  Sort Method: external merge  Disk: 12408kB
  ->  Bitmap Heap Scan on device_metrics
        (actual time=124.03..901.44 rows=500492 loops=1)
Planning Time: 2.44 ms
Execution Time: 4312.88 ms
```

That 4.3-second execution time is mostly the Sort node paying for 12MB of disk I/O. The index is present and used, but it can no longer deliver rows in sort order from memory, so the planner sorts after the fact.

The [anchor article on Postgres performance limits](https://www.tigerdata.com/blog/postgres-optimization-treadmill) covers why B-tree index bloat compounds at high insert rates. For time-ordered data, new inserts concentrate on the rightmost leaf pages of the timestamp index, causing repeated page splits and leaving older pages sparse. The index can grow faster than the data it covers, which makes buffer pressure worse over time.

If you added [time-based partitioning](https://www.tigerdata.com/learn/when-to-consider-postgres-partitioning) (a common step on the optimization path), the Sort problem compounds further. When a query spans multiple partitions, Postgres plans a MergeAppend node, an operator that merges sorted output streams from each partition's index scan. At 500 partitions, the merge cost grows with both partition count and result set size, and the anchor article notes that planning time alone can exceed execution time at that scale.

## The Rewrite: Ask for Buckets, Not Rows

The root cause is that `ORDER BY ts DESC` asks the database to return individual rows in timestamp order. For most dashboards, that's not what you actually need. You need one aggregate value per time interval.

`time_bucket()`, provided by the [TimescaleDB extension](https://www.tigerdata.com/docs/learn/data-lifecycle/time-buckets/about-time-buckets) that Tiger Data is built on, changes the shape of the request. Instead of millions of ordered rows, you get one aggregate row per interval. The Sort node on raw rows disappears because there are no raw rows to sort.

```SQL
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_value
FROM device_metrics
WHERE device_id = 42
  AND ts > now() - interval '7 days'
GROUP BY time_bucket('1 hour', ts), device_id
ORDER BY bucket DESC;
```

Run `EXPLAIN (ANALYZE, BUFFERS)` on this version and compare:

```SQL
Sort  (cost=142.11..142.53 rows=168 width=20)
      (actual time=8.74..8.77 rows=168 loops=1)
  Sort Key: bucket DESC
  Sort Method: quicksort  Memory: 36kB
  ->  HashAggregate  (cost=131.44..133.12 rows=168 width=20)
        (actual time=7.91..8.04 rows=168 loops=1)
        Group Key: time_bucket('01:00:00'::interval, ts), device_id
        ->  Index Scan using idx_metrics_device_time on device_metrics
              (actual rows=504000 loops=1)
Planning Time: 1.88 ms
Execution Time: 9.11 ms
```

The Sort node is still there, but it's sorting 168 aggregate rows in memory (quicksort, 36kB) rather than 500,000 raw rows on disk (external merge, 12MB). Execution time drops from 4,312ms to 9ms.

The query returns one row per hour over 7 days (168 rows) instead of half a million individual readings. The `ORDER BY bucket DESC` at the end still exists, but it's negligible when the input is 168 rows rather than millions.

## The Best Option: Continuous Aggregates

`time_bucket()` cuts the work at query time. Continuous aggregates cut it at ingest time.

A continuous aggregate is a materialized view backed by a dedicated summary table that TimescaleDB refreshes incrementally as new data arrives. Rather than re-aggregating over raw rows at query time, your [dashboard reads pre-computed results](https://www.tigerdata.com/blog/how-to-quickly-build-dashboards-with-time-series-data) from a compact summary table. The Sort on raw data doesn't run slower. It doesn't run at all.

```SQL
-- Create the continuous aggregate
CREATE MATERIALIZED VIEW device_hourly_avg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', ts) AS bucket,
  device_id,
  avg(value) AS avg_value
FROM device_metrics
GROUP BY time_bucket('1 hour', ts), device_id;

-- Refresh automatically as new data arrives
SELECT add_continuous_aggregate_policy('device_hourly_avg',
  start_offset     => INTERVAL '2 days',
  end_offset       => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');
```

Dashboard queries read from `device_hourly_avg` directly:

```SQL
SELECT bucket, avg_value
FROM device_hourly_avg
WHERE device_id = 42
  AND bucket > now() - interval '7 days'
ORDER BY bucket DESC;
```

This query touches no raw data. Planning cost is negligible. Execution cost matches the size of the summary table, typically a few hundred rows for a 7-day window, not the billions of rows in the underlying hypertable.

The refresh policy handles everything automatically. New data lands in `device_metrics`, the policy runs on the configured schedule, and the view stays current within your `end_offset` window. For near-real-time dashboards, set `end_offset` to a few minutes. To skip unnecessary refreshes on historical data, configure `timescaledb.ignore_invalidation_older_than`. See the [Tiger Data continuous aggregates docs](https://www.tigerdata.com/docs/learn/continuous-aggregates) for the full policy options.

## How to Validate the Improvement

Start with [`pg_stat_statements`](https://www.postgresql.org/docs/current/pgstatstatements.html) to find your slowest time-ordered queries:

```SQL
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time / 1000 AS total_seconds
FROM pg_stat_statements
WHERE query ILIKE '%ORDER BY%ts%'
  AND query NOT ILIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 10;
```

Take the slowest query and run it with `EXPLAIN (ANALYZE, BUFFERS)`. Look for a Sort node. If Sort Method shows external merge with a nonzero Disk value, the sort is spilling to disk and that's where your latency is going.

Rewrite with `time_bucket()` and run `EXPLAIN (ANALYZE, BUFFERS)` again. Compare the `Sort Method` line and the actual rows count at the Sort node. Those two numbers tell you exactly how much work the rewrite removed.

For dashboard queries that run repeatedly, build a continuous aggregate and point the query at the summary view. The external merge sort on raw data goes away permanently.

## Next Step

Pull the five slowest `ORDER BY ts` queries from `pg_stat_statements`. Run `EXPLAIN (ANALYZE)` on each and look for Sort nodes with `Sort Method: external merge`. Those are your rewrite targets.

Start with the `time_bucket() rewrite`, measure the before-and-after execution time, then set up a continuous aggregate if your dashboards hit that query more than a few times per minute. The [Tiger Data free trial](https://console.cloud.timescale.com/signup) gives you access to both on your own data.