PostgreSQL Top K Query Optimization | UData Blog

A deep-dive on optimizing Top K queries in PostgreSQL is making rounds in the developer community — and the findings are worth paying attention to. Teams running analytics dashboards, recommendation engines, or leaderboards on Postgres are often leaving massive performance gains on the table without realizing it.

What Is a Top K Query and Why Does It Hurt?

A Top K query is any query that fetches the top N rows by some ranking — the 10 most recent orders, the 100 highest-scoring users, the 5 latest log entries per customer. These patterns appear everywhere in production systems. They also happen to be among the most misunderstood from an optimization standpoint.

The naive approach — SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 — looks innocent. But when your table has 50 million rows and no appropriate index, Postgres performs a full sequential scan, sorts every row, then discards 49,999,990 of them. You paid for a stadium and used one seat.

The cost compounds in multi-tenant applications. Queries like "top 10 items per user" — a grouped Top K — are notoriously difficult to optimize and often written in ways that trigger repeated full scans or inefficient subqueries.

Proven Techniques That Actually Work

1. Index-Only Scans for Simple Top K

For simple ranked fetches, the right index transforms an O(n) scan into an O(log n) operation. A BTREE index on your sort column lets Postgres walk the index in reverse order and stop at row 10 — no sort, no full scan. For a 50M row table, this drops query time from seconds to milliseconds.

Example: CREATE INDEX idx_orders_created ON orders (created_at DESC);

If your query also filters — say, orders for a specific status — a composite index on (status, created_at DESC) eliminates even more work.

2. The Lateral Join Pattern for Grouped Top K

The "top N per group" problem is where most teams struggle. A common anti-pattern is using window functions with a WHERE rank <= 10 filter — this forces Postgres to rank every row before filtering. For tables with millions of rows across thousands of tenants, this is expensive.

The better approach is a LATERAL join: iterate over each group key and independently fetch the top N rows using an index. When you have an index on (user_id, score DESC), each lateral subquery becomes a fast index scan limited to 10 rows.

SELECT u.id, top_items.*
FROM users u,
LATERAL (
  SELECT * FROM items
  WHERE user_id = u.id
  ORDER BY score DESC
  LIMIT 10
) top_items;

This pattern scales linearly with the number of users, not with the total row count.

3. Partial Indexes for Common Filters

If your Top K query always includes a filter — active records, a specific date range, a tenant ID — a partial index dramatically reduces index size and scan cost. Instead of indexing every row, you index only the rows that qualify.

Example: CREATE INDEX idx_active_orders ON orders (created_at DESC) WHERE status = 'active';

Smaller index = faster scans = lower memory pressure. For high-traffic applications, partial indexes also reduce write overhead compared to full indexes.

4. EXPLAIN ANALYZE: Read Before You Optimize

Before changing anything, run EXPLAIN (ANALYZE, BUFFERS) on your slow query. Look for:

• Seq Scan on large tables — almost always worth indexing
• Sort nodes with high actual rows — a missing index on the ORDER BY column
• Hash Join with large hash batches — potential memory pressure
• High buffer hits vs reads ratio — cache efficiency signal

Optimizing without reading the query plan first is guesswork. The plan tells you exactly where time is going.

Real Numbers: What Optimization Buys You

Teams that apply these techniques consistently report significant improvements:

• Simple Top K with proper index: 95%+ query time reduction on large tables
• Grouped Top K switching to LATERAL: 60-80% reduction in multi-tenant workloads
• Partial indexes on filtered queries: 40-70% reduction depending on filter selectivity

For SaaS applications running hundreds of queries per second, these gains translate directly to infrastructure cost savings — fewer read replicas needed, lower CPU load, better headroom for traffic spikes.

How UData Helps

Database performance problems are rarely isolated issues. They reflect how data models, query patterns, and application architecture interact under real load. UData works with engineering teams to identify bottlenecks across the full stack — from query-level profiling to schema redesign and caching strategies.

Our data engineering team has optimized PostgreSQL workloads for e-commerce platforms, analytics dashboards, and SaaS products — handling tables from millions to billions of rows. We also staff experienced backend and data engineers on an outstaffing basis, so if your team needs deep Postgres expertise without a full-time hire, we can cover that gap.

Conclusion

PostgreSQL is capable of excellent Top K performance — but only when queries and indexes are designed together with that goal in mind. The patterns exist, they are well-understood, and applying them correctly is a matter of knowing where to look. Most slow ranked queries are one well-placed index or query rewrite away from being fast.

The teams that treat query optimization as a regular practice — not a crisis response — end up with systems that scale without expensive infrastructure changes. That's the compounding return of getting database fundamentals right.