PostgreSQL LIMIT

PostgreSQL LIMIT Clause

The LIMIT clause is utilized to heavily restrict the amount of data returned. It specifically dictates the maximum number of rows the database should output.

It is critical when dealing with massive tables holding millions of entries. Returning millions of rows at once would instantly crash your web application.

The Basic Syntax

The LIMIT clause is placed at the very end of your SQL statement. You simply type the keyword followed by the maximum integer you want to fetch.

If the table has fewer rows than your limit, it just returns what is available. It serves as an excellent safety net for unoptimized frontend queries.

-- Fetch only the first 3 customer records found
SELECT * FROM Customers
LIMIT 3;

Combining LIMIT with ORDER BY

Using LIMIT by itself grabs records somewhat unpredictably based on table storage. To get meaningful data, you usually combine LIMIT with an ORDER BY clause.

This combination allows you to extract "Top N" or "Bottom N" records efficiently. For example, you can easily fetch the top 5 most expensive products.

-- Fetch the top 2 most expensive products
SELECT * FROM Products
ORDER BY Price DESC
LIMIT 2;

The OFFSET Clause

Sometimes you want to skip a specific number of rows before applying the limit. The OFFSET clause allows you to jump ahead in the sorted result set.

This is the fundamental backbone of building pagination in web applications. You skip the first page of results to cleanly display the second page.

-- Skip the first 3 records, and then fetch the next 3 records
SELECT * FROM Customers
LIMIT 3 OFFSET 3;

Performance Benefits

Adding a LIMIT clause significantly reduces the database's processing time. It stops searching the table immediately once the requested threshold is met.

This drastically reduces the amount of network bandwidth consumed. Always use LIMIT when prototyping or testing large unknown queries.

Summary

The LIMIT clause protects your application from enormous data payloads. Always pair it with ORDER BY to guarantee consistent, predictable results.

Use OFFSET alongside it to implement scalable website pagination easily.