Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

[Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>]

Bill Thoen wrote:

> What I'm wondering is how in PostgreSQL do you select only the first 10 
> records from a selection, then the next 10, then the next, and possibly 
> go back to a previous 10?

LIMIT with OFFSET has already been mentioned. There's another option if 
your web app is backed by an application server or some other 
environment that can retain resources across client queries: You can use 
a scrollable database cursor to access the results.

This won't do you much (any?) good if your web app has to establish a 
connection or get one from the pool for every request. It's only really 
useful if you can store the connection in the user's session information.

Using cursors probably isn't very good for very high user counts, 
because abandoned sessions will hold their database connections until 
the session times out and is destroyed. For more complex apps with fewer 
users, though, cursors could be a big win.

Note that OFFSET isn't free either. The database server must still 
execute all of the query up to OFFSET+LIMIT results. With a high offset, 
that can get very slow. A cursor will be OK here if you still start from 
the beginning, but if you ever begin with a high offset you'll want to 
look into using one of the methods suggested in other replies that 
permit you to use an index.

--
Craig Ringer