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

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