How LIMIT and OFFSET works behind the Scenes in PostgreSQL

When we talk about pagination, what is the first approach that comes to your mind? In my case, I immediately think of ‘LIMIT’ and ‘OFFSET’, so I use them. Is it good, right? Well, in my case, I realized it was inefficient for large paginations.

Let’s delve a bit deeper into this.

1. Setup

I’ve created a simple demo with a ‘users’ table populated with 10 million rows.

-- Create the user table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    gender VARCHAR(10)
);

-- Generate and insert 10 million rows using generate_series
INSERT INTO users (name, gender)
SELECT
    'User ' || gs.num AS name,
    CASE WHEN gs.num % 2 = 0 THEN 'Male' ELSE 'Female' END AS gender
FROM generate_series(1, 10000000) AS gs(num);

2. SQL LIMIT and OFFSET

LIMIT and OFFSET allows you to retrieve a subset of rows of the query.

Let’s write the SQL query for pagination.

    SELECT * FROM users ORDER BY id LIMIT 500 OFFSET 1000;

For me, this looks like magic. How does Postgres know how to skip those records? Well, it actually doesn’t know how to skip those records.

Let’s analyze the SQL query with the ‘EXPLAIN ANALYZE’ command.

EXPLAIN ANALYZE SELECT * FROM users ORDER BY id LIMIT 500 OFFSET 1000;

After executing the SQL query, we obtained the query plan and execution statistics.

Let’s dive into the query plan in more detail.

The query plan consists of two parts:

Firstly, we notice that Postgres chooses an Index Scan on the primary key as we’re ordering the results based on the ‘id’ using the ORDER BY clause. Secondly, Postgres then limits the results to return the required rows.

If we examine the actual time and rows fetched by Postgres, we can see that it’s actually 1500 instead of 500.

This means that using LIMIT and OFFSET doesn’t skip the records, it fetches all the records into memory and relies on Postgres to filter them.

If we refer to the PostgreSQL documentation, we find that this is indeed the case. ‘The rows skipped by an OFFSET clause still have to be computed inside the server; therefore, a large OFFSET might be inefficient’.

We can say that LIMIT and OFFSET perform well with small offsets, for larger offsets, is there a better approach?

3. Is there a better approach for a large OFFSET?

We can take advantage of the sequential ID to obtain the same result with more efficiency.

SELECT * FROM users WHERE id between 1001 and 1500;

Let’s analyze the query

EXPLAIN ANALYZE SELECT * FROM users WHERE id between 1001 and 1500;

After executing the query, we can see that it retrieves only the required 500 rows.

4. Conclusion

We can conclude that while using LIMIT and OFFSET for pagination can work well for small offsets, for larger offsets, it might be inefficient since it has to pull that in memory and filter the rows. There are other ways to achieve the same result. Taking advantage of ordered columns and indexes could be a better approach for larger offsets.