LIMIT and OFFSET pagination in PostgreSQL

Introduction

LIMIT and OFFSET help you get a small part of a big list from a database. This makes it easier to show data page by page.

Showing 10 products per page on an online store.

Displaying 5 recent messages at a time in a chat app.

Loading 20 user comments per page on a blog post.

Fetching 15 search results at a time in a search engine.

Syntax

PostgreSQL

SELECT column_names FROM table_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;

LIMIT sets how many rows to get.

OFFSET skips a number of rows before starting to return rows.

Examples

Get the first 5 employees from the table.

PostgreSQL

SELECT * FROM employees LIMIT 5;

Skip the first 10 employees, then get the next 5.

PostgreSQL

SELECT * FROM employees LIMIT 5 OFFSET 10;

Get the first 3 product names (OFFSET 0 means start from the beginning).

PostgreSQL

SELECT name FROM products LIMIT 3 OFFSET 0;

Sample Program

This creates a small table of fruits, adds 5 fruits, then gets 2 fruits starting from the second one.

PostgreSQL

CREATE TABLE fruits (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO fruits (name) VALUES ('Apple'), ('Banana'), ('Cherry'), ('Date'), ('Elderberry');
SELECT * FROM fruits LIMIT 2 OFFSET 1;

Important Notes

OFFSET can slow down queries on big tables because it still scans skipped rows.

Use ORDER BY with LIMIT and OFFSET to get consistent results.

Summary

LIMIT controls how many rows you get.

OFFSET skips rows before starting to return data.

Together, they help show data page by page.