PostgreSQL LIMIT Clause

The LIMIT clause restricts how many rows a SELECT query returns. It is most commonly used for “top N” queries, sampling data, and pagination. For standard SQL portability, see the FETCH clause, which is equivalent.

Syntax

SELECT column_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count;

To skip rows before returning results, add OFFSET:

SELECT column_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count OFFSET rows_to_skip;

If row_count is 0, the query returns an empty set. If row_count is NULL, LIMIT has no effect — all rows are returned.

Practical Example

Create a sample table for a product catalog:

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(50)  NOT NULL,
  unit_price   NUMERIC(10,2) NOT NULL,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

INSERT INTO products (name, category, unit_price) VALUES
  ('Laptop Stand',       'Accessories', 49.99),
  ('USB-C Hub',          'Accessories', 39.99),
  ('Noise-Cancel Headset','Audio',      149.99),
  ('Webcam HD',          'Video',        79.99),
  ('Desk Lamp LED',      'Lighting',     29.99),
  ('Ergonomic Mouse',    'Accessories',  59.99),
  ('Monitor Arm',        'Furniture',    89.99),
  ('Keyboard Wrist Rest','Accessories',  19.99),
  ('Portable Speaker',   'Audio',        99.99),
  ('HDMI Switch',        'Video',        34.99);

Return the 3 most expensive products:

SELECT name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 3;

Expected result:

         name          | unit_price
-----------------------+------------
 Noise-Cancel Headset  |     149.99
 Monitor Arm           |      89.99
 Portable Speaker      |      99.99

Page through the catalog in batches of 4 — page 2 (rows 5–8):

SELECT product_id, name, unit_price
FROM products
ORDER BY product_id
LIMIT 4 OFFSET 4;

Confirm total row count to understand pagination:

SELECT COUNT(*) FROM products;

Why ORDER BY Is Required with LIMIT

PostgreSQL stores rows internally in heap order, which is not guaranteed to be consistent between queries. Without ORDER BY, LIMIT can return a different set of rows each time, especially after vacuums, updates, or autovacuum activity:

-- Risky: non-deterministic — different rows may appear on re-execution
SELECT name FROM products LIMIT 5;

-- Correct: deterministic — always the 5 cheapest products
SELECT name FROM products ORDER BY unit_price LIMIT 5;

LIMIT / OFFSET Pagination and Its Limits

LIMIT with OFFSET is simple but has a performance problem at large page numbers: PostgreSQL must read and discard all rows up to the offset before returning the page you want.

LIMIT 20 OFFSET 100000   → reads 100,020 rows, discards 100,000

For large tables, switch to keyset pagination:

-- Page 1
SELECT product_id, name, unit_price
FROM products
ORDER BY product_id
LIMIT 10;

-- Next page — use the last product_id from the previous result (e.g. 10)
SELECT product_id, name, unit_price
FROM products
WHERE product_id > 10
ORDER BY product_id
LIMIT 10;

Keyset pagination is O(1) per page because the WHERE clause leverages the index on product_id directly.

Testing with Vela

Pagination logic is one of the trickier areas to test, especially when migrating from OFFSET to keyset pagination. Use Vela’s database branching to clone your production database, run both implementations side by side, and compare the result sets before switching. This is especially valuable when your production table has millions of rows and the performance difference only becomes apparent at scale.

Production Tips

• Always pair LIMIT with ORDER BY — without it, results are non-deterministic and can vary between executions.
• An index on the ORDER BY column lets PostgreSQL use an index scan and stop early once N rows are found, avoiding a full table sort.
• For “latest N records” queries, a descending index on the timestamp column eliminates the sort step entirely.
• LIMIT 1 is a safe way to assert a subquery returns at most one row, but only makes sense with an explicit ORDER BY.
• Avoid exposing LIMIT/OFFSET pagination directly to end users for large datasets — use keyset pagination to eliminate the O(offset) performance cliff.
• Use EXPLAIN ANALYZE to verify the planner uses an index scan with a Limit node rather than a full sequential scan followed by sort.