title: how is using a LIMIT clause different from simply fetching N results?
body: <p>In my application, I have a table of responses to a topic. The structure is roughly as follows:</p>
  
  <pre><code>CREATE TABLE responses (
      id INT NOT NULL PRIMARY KEY,
      topic_id INT NOT NULL,
      author_id INT NOT NULL,
      response TEXT
  );
  </code></pre>
  
  <p><code>id</code> is an auto-increment field, <code>topic_id</code> and <code>author_id</code> are foreign keys, there are the appropriate indices etc.</p>
  
  <p>I always want to order by insertion time, usually by most recent. In most cases, I will be filtering by <code>topic_id</code>. A typical query looks like this:</p>
  
  <pre><code>SELECT * FROM responses WHERE topic_id=123 ORDER BY id DESC LIMIT 20;
  -- or, for pagination:
  SELECT * FROM responses WHERE topic_id=123 AND id &lt; 456789 ORDER BY id DESC LIMIT 20;
  </code></pre>
  
  <p>I want to implement a blocklist - each user has a list of <code>author_id</code>s they don't want to see. I need to retrieve top 20 results excluding those <code>author_id</code>s <em>and responses that reply to them</em>.</p>
  
  <p>Determining whether a row should be excluded is pretty complex, and while it would probably be possible to do this in the database (either in PL/SQL or by preprocessing), I want to keep the logic within the application. So I can do one of two things:</p>
  
  <ol>
  <li>Forget the LIMIT clause, leaving the query unbounded. Eat rows until I count 20 valid results, then close the query.</li>
  <li>Apply chunking - specify LIMIT 40 and hope that it's enough for 20 "good" results. If not, fetch next 40 and so on.</li>
  </ol>
  
  <p>What is the practical difference between the two? Esp. in terms of performance with many simultaneous users.</p>
  
  <p>I'm doing this in PostgreSQL, but I'm willing to switch to a different RDBMS. (i don't want to lose referential integrity, so i'm not looking into NoSQL solutions) Perhaps I'd have to tune some parameters of the database (such as prefetch sizes), to make the most of the unbounded query case?</p>