PostgreSQL Top K Query Optimization: Fast Top-N Results On Large Tables

PostgreSQL top K query optimization comes down to one goal: let the database read only the K rows you need instead of the whole table. A query like ORDER BY price DESC LIMIT 10 is fast when an index already holds rows in that order, because PostgreSQL walks the index and stops after ten rows. Without a matching index it has to read every row that passes the filter. Then it sorts them and throws almost all of them away. On a table with a few thousand rows nobody notices. At tens of millions, the same query goes from milliseconds to seconds.

Below are the index patterns that make top-K queries fast, how to handle top-N per group and pagination, what changes in PostgreSQL 18 and 19, and an infrastructure lesson from 2026, when a Linux kernel upgrade halved PostgreSQL throughput on some servers before anyone touched a query.

Why Top-K Queries Get Slow

A top-K query asks for the first K rows in some order: the 20 newest orders, the 10 biggest price drops today, the 5 best-selling products in a category. The SQL is short, which is part of the problem. It looks cheap, so it ends up on dashboards and API endpoints that run thousands of times an hour.

PostgreSQL has two ways to answer it. With a suitable B-tree index it reads rows already in order and stops once it has K of them. Without one, it scans every matching row and keeps the best K in memory with a top-N heapsort. The heapsort itself is efficient. The scan in front of it is what grows with your table.

Read The Plan Before You Change Anything

Run the query with EXPLAIN (ANALYZE, BUFFERS) and look at the top few lines.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, price
FROM products
WHERE category_id = 42
ORDER BY price DESC
LIMIT 10;

A healthy top-K plan starts with Limit directly above an Index Scan or Index Only Scan, and the actual row count on the index node is close to K. A plan that needs work shows Sort with Sort Method: top-N heapsort above a Seq Scan or a Bitmap Heap Scan that touched hundreds of thousands of rows. The BUFFERS numbers tell you how much data was read to produce those ten rows, which is usually the most honest measure of the problem.

Index Patterns That Make Top-K Fast

Match The ORDER BY Exactly

A B-tree can be read in either direction, so a plain index on price serves both ORDER BY price and ORDER BY price DESC. Mixed directions across columns are where it breaks: ORDER BY score DESC, created_at ASC needs an index declared in that same mix. NULLS FIRST and NULLS LAST have to match as well, otherwise PostgreSQL falls back to sorting.

Filter Columns First, Sort Column Last

For a query that filters on category_id and sorts on price, the index that works is (category_id, price DESC). PostgreSQL jumps to category 42 and reads its rows already ordered by price. An index on price alone forces it to walk the whole price order and skip every row from other categories, which can be worse than a sequential scan.

CREATE INDEX CONCURRENTLY products_cat_price_idx
  ON products (category_id, price DESC)
  INCLUDE (title);

INCLUDE adds columns the query returns without making them part of the sort key, so PostgreSQL can often answer from the index alone. CONCURRENTLY builds the index without blocking writes, which is what you want on a production table.

Partial Indexes For Hot Subsets

If dashboards only ever rank active items, index only those rows: CREATE INDEX ... ON products (price DESC) WHERE status = 'active'. The index is smaller, stays in memory more easily, and the planner uses it whenever the query repeats the same condition.

Add A Tiebreaker

When many rows share the same sort value, results can shift between runs and pages. Add a unique column as the last sort key, typically id, both in the ORDER BY and in the index. It costs almost nothing and makes pagination predictable.

Top-N Per Group Without Scanning Everything

"Top 3 products in every category" is where a lot of code reaches for a window function:

SELECT * FROM (
  SELECT p.*, row_number() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products p
) t
WHERE rn <= 3;

It's correct and readable, and on a large table it numbers every row before filtering. When the number of groups is modest, a LATERAL join with the composite index from above is usually far faster, because each group becomes its own tiny top-K lookup:

SELECT c.id AS category_id, p.*
FROM categories c
CROSS JOIN LATERAL (
  SELECT id, title, price
  FROM products
  WHERE category_id = c.id
  ORDER BY price DESC
  LIMIT 3
) p;

For exactly one row per group, DISTINCT ON (category_id) ... ORDER BY category_id, price DESC is the shortest option and works well with the same index.

Pagination: Use Keyset Instead Of OFFSET

LIMIT 20 OFFSET 10000 still reads and discards 10,000 rows before returning 20, so every next page gets slower. Keyset pagination remembers where the last page ended and continues from there:

SELECT id, title, price
FROM products
WHERE category_id = 42
  AND (price, id) < (:last_price, :last_id)
ORDER BY price DESC, id DESC
LIMIT 20;

With an index on (category_id, price DESC, id DESC) every page costs about the same as the first. The trade-off is that users can't jump straight to page 500, which for feeds and infinite scroll rarely matters.

When An Index Isn't Enough: Precompute The Ranking

Some rankings can't be served from an index because the sort value is computed at query time: "most viewed in the last 24 hours", or a score built from several tables. Running that aggregation on every request is the expensive version. The usual fixes trade freshness for speed.

Pick by how stale the number is allowed to be. A daily sales ranking doesn't need sub-second updates. A live auction does.

PostgreSQL 18 And 19: What Changes For These Queries

PostgreSQL 18, released in September 2025, added skip scan for multicolumn B-tree indexes. A query that doesn't filter on the leading column of an index like (category_id, price) can now still use it in some cases, which softens the penalty for imperfect column order. It also introduced asynchronous I/O, which speeds up the scans that top-K queries are trying to avoid in the first place.

PostgreSQL 19 is close. The first release candidate shipped on October 15, 2026, and general availability is planned for November 19, 2026. For performance work the interesting additions are planner optimizations such as aggregating before joins, the pg_plan_advice extension for steering planner decisions, and REPACK (CONCURRENTLY) for reorganizing bloated tables without blocking reads and writes. Wait for GA and the first minor release before upgrading production.

The Linux 7.0 Lesson: Benchmark The Database Before Infrastructure Changes

In April 2026 an AWS engineer reported on the kernel mailing list that PostgreSQL 17 on a 96-vCPU Graviton4 instance delivered roughly half its previous throughput under the Linux 7.0 kernel: about 51,000 transactions per second instead of about 98,500. The cause was a deliberate kernel change that removed the old PREEMPT_NONE scheduling mode on modern architectures. Kernel maintainers declined to revert it and suggested PostgreSQL adopt a newer kernel mechanism instead.

The detail that matters most came a few days later. PostgreSQL developers traced the regression to servers running without huge pages. With huge_pages = on, which is good practice for large databases anyway, the drop largely disappears. Nothing in the application or its queries had changed; the regression came entirely from the layer underneath.

That's the part to keep after the news is forgotten. Before a kernel upgrade or a new instance family reaches your database servers, run through this:

• Pin the kernel version on database hosts and upgrade it deliberately, not as part of routine patching.
• Run pgbench or a replay of your real queries on a canary server with the new setup, and compare throughput and p95 latency of your heaviest queries against the current one.
• Check that huge pages are actually in use, not just configured: SHOW huge_pages_status; on PostgreSQL 17 and later.
• Roll out to replicas first and keep the previous kernel available for a quick rollback.

How Our Team Handles PostgreSQL Performance

High-volume data is one of our strongest areas. A lot of what we build is large-scale data collection and analytics: price monitoring platforms like RetailShake, competitive intelligence for retail like LiveTrend, and the dashboards on top of them. Those products live on exactly the queries in this guide: newest records, biggest changes, top items per segment.

Our default stack for this work is PostgreSQL with Redis, Python or Node.js on the backend, and Docker and Kubernetes on AWS, Azure or Google Cloud. Backend developers work at $25–$35 an hour, and a team starts within 3 to 7 days. If you need extra hands on an existing system, our software development outsourcing page explains how we plug into your team, and the full case list is on our projects page.