As fbonetti already said, LIMIT is the canonical way to do it with SQL databases (I would just add to their comment that you should ensure a positive page_size and offset, because "LIMIT -5" is invalid in at least MySQL, and to limit the page size to some sensible values).

For NoSQL, specifically (only?) CouchDB, you start iterating from a given key (in SQL, this would effectively be "WHERE id >= $start ORDER BY id ASC LIMIT n").

This difference is why I would recommend, especially for paginated REST APIs, to provide links to the next/prev pages, so API clients can follow these links without thinking about how pagination is accomplished exactly. To prevent people from guessing the inner workings and working around your links, you can encode the parameters in a "cursor" (so instead of ?pagesize=20&page=10 you could have ?cursor=base64encode("20,10") (SQL) or ?cursor=base64encode("$startKey") (NoSQL)). IIRC Facebook does it this way. Encrypting or authenticating the cursor is IMHO overblown here, given cursor values can be easily validated and rejected if fiddled with.

Using an opaque cursor also gives you the ability to change how pagination works without breaking existing API clients.

If you plan for lots of data and many pages, think twice before outputting links to all pages (on a website) or outputting the total number of elements (in an API). COUNT(*) on InnoDB is slow and I heard it's not the quickest thing in NoSQL databases either.