OFFSET and LIMIT on complex query
For just the two pages, a compromise could be:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  200   -- 100 + 100 (outer limit + offset) per source_id
   ) p
ORDER  BY p.external_created_at DESC
OFFSET 100
LIMIT  100;


Which will be considerably slower than the first 100 rows, but still much faster than your original query in your previous question.

For just 107 rows total in your example, performance will be almost identical.

Performance decreases with every later page, obviously. OFFSET is a pain for performance, generally. But it becomes a PITA when combined with your difficult query.

I would consider a materialized view with pre-calculated row numbers or the more complex (but also more potent) solution here:


Can spatial index help a &quot;range - order by - limit&quot; query


Also related:


Improve performance for order by with columns from many tables