Ivan,
> Or, more generally, is there some set of circumstances under which the
> catastrophic scenario will happen?
Yes:
SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 100000
This is the "high offset" problem, and affects all databases which
support applications with paginated results, including non-relational
ones like SOLR. The basic problem is that you can't figure out what is
OFFSET 100000 without first sorting the first 100000 results.
The easiest solution is to limit the number of pages your users can
"flip through". Generally anyone asking for page 10,000 is a bot
screen-scraping your site, anyway.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com