🚀 Understanding OFFSET in PostgreSQL

📌 What is OFFSET?

The OFFSET clause in PostgreSQL is used to skip a specific number of rows before starting to return rows from a query. It’s often used along with the LIMIT clause to implement pagination — a technique to fetch a small chunk of data at a time.
🔧 Syntax

SELECT column1, column2
FROM table_name
OFFSET number_of_rows_to_skip;

Usually, it's paired with LIMIT like this:

SELECT column1, column2
FROM table_name
LIMIT page_size OFFSET number_of_rows_to_skip;

📊 Real-World Example

Let’s say you have a table called students:

id  name    grade
1   Alice   A
2   Bob B
3   Charlie C
4   Diana   B
5   Edward  A

Example 1: Simple OFFSET

SELECT * FROM students OFFSET 2;

Output:

id  name    grade
3   Charlie C
4   Diana   B
5   Edward  A

This query skips the first 2 rows and returns the rest.

Example 2: LIMIT with OFFSET (Pagination)

Let’s say you want to show 2 students per page:

-- Page 1
SELECT * FROM students LIMIT 2 OFFSET 0;

-- Page 2
SELECT * FROM students LIMIT 2 OFFSET 2;

-- Page 3
SELECT * FROM students LIMIT 2 OFFSET 4;

This way, you only load a few rows at a time, which is better for performance and user experience.
🛠 When to Use OFFSET

• To implement pagination in web applications.
• To load large datasets incrementally.
• To skip over records during testing or debugging. ⚠️ Caveats

1. Performance: Using large OFFSET values can slow down performance because PostgreSQL still scans through skipped rows internally.

2. Duplicate or Missing Records: If the data changes between paginated queries (like inserts or deletes), you might get duplicates or miss some records.

   ✅ Pro Tip: For large datasets, consider using keyset pagination (e.g., with WHERE id > last_seen_id) for better performance.

📚 Summary
Clause Purpose
OFFSET Skips N rows before fetching
LIMIT Restricts number of rows returned
Together, they form the backbone of pagination in SQL queries.