Select N rows before/after specified row Let's say I have a simple table in Postgres (11.3):

create table posts
(
    id serial not null,
    created_at timestamp(0)
    constraint posts_pkey
        primary key (id)
);


If a user requests id=5869, I need to be able to return the N rows before and the N rows after that row in a query that is ordered by the created_at column. If we're able to assume that the larger the id, the larger the created_at, we can do something relatively simple like this:

(select * from posts where id &lt; 5869 order by id limit 10)
union all
(select * from posts where id &gt;= 5869 order by id limit 11);


However, I am unable to assume that the higher id is most recently created and I'm wondering what the best way to retrieve that data is in that case. This method works, but is remarkably slow on a 100k row dataset:

WITH 
  boundaries AS (
    SELECT *,
           row_number() OVER (ORDER BY created_at DESC) AS rownum
    FROM posts
  ),
  target_boundary AS (
     SELECT *
     FROM boundaries
     WHERE boundaries.id = 5869
 )
SELECT posts.*, boundaries.rownum
FROM posts
LEFT JOIN boundaries ON posts.id = boundaries.id
JOIN target_boundary ON boundaries.rownum BETWEEN target_boundary.rownum - 10 AND target_boundary.rownum + 10


Running through that was taking upwards of 800 milliseconds which is far too slow on a dataset so small.

I have also tried a variation of the above using lead() and lag(), but that was even less efficient.

Is there a better way to do this query? Is there perhaps a window function I am missing in Postgres that would handle it?
WITH 
cte1 AS ( SELECT created_at, 10 N 
          FROM posts WHERE id=5869 ), /* parameters */
cte2 AS ( SELECT posts.created_at,
                 LAG(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () lbound, 
                 LEAD(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () ubound
          FROM posts, cte1 )
SELECT posts.* 
FROM posts, cte1, cte2
WHERE posts.created_at BETWEEN cte2.lbound AND cte2.ubound
  AND cte2.created_at = cte1.created_at
ORDER BY posts.id


sample fiddle (N set to 3, id set to 10).

PS. I understand that window in LAG/LEAD must be specified like ORDER BY posts.created_at ASC ROWS BETWEEN [UNBOUNDED | cte1.N] PRECEIDING AND [UNBOUNDED | cte1.N] FOLLOWING. May be added if needed really.