Postgres Sorting at Scale Needs More Than ORDER BY

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 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 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 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 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:

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

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 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 (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 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.

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

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 from a compact summary table. The Sort on raw data doesn't run slower. It doesn't run at all.

Dashboard queries read from device_hourly_avg directly:

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 for the full policy options.

How to Validate the Improvement

Start with pg_stat_statements to find your slowest time-ordered queries:

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 gives you access to both on your own data.