Migrate to RunxBuild today!! Deploy More while Spending Less

Calculate your savings
unxBuild
Log in Get Started
Back to Blog Tutorial

PostgreSQL Select Last N Rows: Why You Need ORDER BY First

Sean

Platform Writer

Sep 28, 2026
8 min read

PostgreSQL has no concept of “last” until you add an ORDER BY clause. A table is an unordered set of rows internally, so SELECT * FROM orders LIMIT 10 with no ordering returns whichever ten rows the planner happens to produce first, not the ten most recently inserted ones.

PostgreSQL Select Last N Rows: Why You Need ORDER BY First

This trips people up because it often looks right on a small, freshly loaded table — rows come back in roughly insertion order because nothing has touched the table’s physical layout yet. Then a VACUUM, an UPDATE, or a parallel sequential scan reorders things, and “the last 10 rows” query starts returning rows from six months ago.

Table of contents

Why ‘last’ requires an ORDER BY

A relational table represents a set, not a sequence. PostgreSQL is free to store and return rows in whatever physical order is convenient — insertion order, the order a VACUUM compacted them into, or whatever order a parallel worker happened to finish scanning its chunk. None of that is a contract, and none of it is documented behavior you can rely on.

The only way to guarantee “last” means what you think it means is to pick a column that actually encodes order — a created_at timestamp, a bigserial id, or an explicit sequence — and sort by it explicitly. Anything else is coincidence that will eventually break, usually right after a deploy when nobody is looking at that query.

The correct query: ORDER BY DESC LIMIT n

The standard pattern is a descending sort on the order column with a LIMIT. This is correct, it uses an index if one exists on the sort column, and it is the query every experienced Postgres developer reaches for first.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

This returns the newest row first and the tenth-newest row last — newest-to-oldest order. If your application wants them displayed chronologically (oldest of the ten first), you need one more step, because reversing the sort direction changes which ten rows you get, not just their order.

Re-sorting the result back to ascending order

Wrap the descending query in a subquery and sort the outer result ascending. This keeps the correct set of rows (the ten most recent) while presenting them in the chronological order a UI usually wants.

SELECT *
FROM (
    SELECT * FROM orders
    ORDER BY created_at DESC
    LIMIT 10
) AS last_ten
ORDER BY created_at ASC;

A common mistake is trying to do this in one pass with ORDER BY created_at ASC LIMIT 10 — that returns the ten oldest rows, the exact opposite of what was asked for. The two-step subquery is not a workaround for a missing feature; it is the correct shape for “give me the last N, then show them in order.”

Last N rows per group: window functions and LATERAL

The single-table version is easy. “The last 5 orders for every customer” is a different problem, and there are two solid ways to solve it. The window-function version ranks rows within each group and filters on the rank:

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn <= 5;

The LATERAL join version runs a correlated subquery per row of the outer table, which lets the planner use an index scoped to each customer instead of ranking the entire orders table up front:

SELECT c.id, o.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT *
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC
    LIMIT 5
) o;

For a small number of customers, both perform similarly. For a large customers table where each customer has thousands of orders, LATERAL with the right composite index consistently wins, because it never has to rank rows for customers you did not ask about.

Index the order column, or none of this is fast

ORDER BY ... DESC LIMIT n is only cheap when Postgres can walk an index in the right direction instead of sorting the whole table. Create a plain index on the order column for the single-table query, or a composite index on the group key plus the order column for the per-group LATERAL version.

-- Single-table last-N
CREATE INDEX orders_created_at_idx ON orders (created_at DESC);

-- Per-group last-N with LATERAL
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

Without the composite index, the LATERAL version still runs, but each per-customer subquery falls back to a sequential scan filtered by customer_id, which is exactly the cost you built LATERAL to avoid in the first place.

The OFFSET pitfall

A pattern that shows up constantly in older answers is computing the offset by subtracting from a row count: LIMIT 5 OFFSET (SELECT COUNT(*) FROM orders) - 5. This is fragile in two separate ways.

  • The count and the main query are not atomic — a row inserted between the two reads shifts every offset by one, silently returning the wrong rows
  • OFFSET is O(n): Postgres still has to generate and discard every skipped row before it can return the ones you asked for, so this gets slower as the table grows, not just slower than the alternative

ORDER BY DESC LIMIT n sidesteps both problems entirely — it never needs to know the total row count, and with the right index it costs the same whether the table has a thousand rows or a hundred million.

Keyset pagination: the production-grade version of this problem

“Last N rows” is usually the first page of a bigger problem: paging through a live, growing table without the OFFSET slowdown or the duplicate-and-skip bugs that show up when rows are inserted between page loads. Keyset pagination (also called seek pagination) solves this by carrying the last seen value forward instead of counting pages.

-- First page: newest 20
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page: pass the last row's created_at and id from the client
SELECT * FROM orders
WHERE (created_at, id) < ('2026-09-20 10:00:00', 4821)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The tie-breaker column (id) matters when created_at values are not guaranteed unique — two orders in the same millisecond would otherwise let a row slip through the cursor boundary unnoticed. This pattern uses the same composite index from the section above and never gets slower as the table grows, which is the property OFFSET-based paging can never offer.

How this fits the rest of the stack

Once the query is right, the next bottleneck is usually the database itself under concurrent load, not the SQL. PostgreSQL Monitoring covers the metrics worth watching once this query is running in production, and the RunxBuild dashboard surfaces the connection and query stats that used to mean SSHing in to check. A managed Postgres instance with real connection pooling and headroom to add an index without a maintenance window matters more than people expect until they are the one debugging a slow dashboard at 2am. The RunxBuild hosting calculator is the fastest way to see what a Postgres instance sized for your actual traffic costs before you commit to a plan.

Useful related references:

FAQ

Does PostgreSQL guarantee row order without ORDER BY?

No. PostgreSQL makes no guarantee about the order rows are returned in unless you specify ORDER BY explicitly. Rows may appear to come back in insertion order on a small, untouched table, but that is an implementation detail, not a contract, and it changes after updates, vacuums, or parallel query execution.

What’s the fastest way to get the last row per group?

A LATERAL join with a composite index on (group_column, order_column DESC) is generally the fastest for large tables, because the planner can use the index to fetch just the needed rows per group instead of ranking the entire table. For smaller tables, a ROW_NUMBER() window function is simpler to write and performs comparably.

Why is LIMIT with OFFSET slow on large tables?

OFFSET does not skip rows for free. PostgreSQL still has to generate every row up to the offset and then discard it before returning the rows you asked for, so the cost grows linearly with the offset value. On a large table with a high offset, this can be far slower than a keyset-based approach using ORDER BY and a WHERE clause on the last seen value.

Can I use ROW_NUMBER instead of LATERAL for per-group last N rows?

Yes, and it is often the simpler choice to write and read. ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC) ranks every row within its group, and you filter to rn <= n in an outer query. It tends to lose to LATERAL only when the outer table is large relative to the rows you actually need per group.

Does adding ORDER BY slow down my query?

Only if there is no supporting index, in which case PostgreSQL has to sort the result set in memory or on disk. With an index on the sort column in the matching direction, ORDER BY with LIMIT is typically faster than an unordered scan of the same table, because the planner can stop as soon as it has enough rows.

#postgresql select last n rows#PostgreSQL#SQL#Window Functions#Database Performance