what is most efficient way to retrieve next and previous items based on ordering and current item only
In MySQL it would be simple. Given an @id value, you can easily find the next or previous rows - and efficiently assuming there is an index on (id) and another index on (created_at):

-- find next item
select t.*
from tablename as t
     join
     ( select created_at
       from tablename
       where id = @id
     ) as current
     on t.created_at &gt; current.created_at
order by t.created_at asc
limit 1 ;   -- N next items


To find the previous items, you only have to change &gt; to &lt; and asc to desc.

If there can be rows with same created_at, then you need to modify:

-- find next item
select t.*
from tablename as t
     join
     ( select created_at, id
       from tablename
       where id = @id
     ) as current
     on t.created_at &gt; current.created_at
     or t.created_at = current.created_at and t.id &gt; current.id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items


The whole current subquery is not truly needed as the values @id and @created_at will likely be available from the previous run so the query can be simplified:

-- find next item
select t.*
from tablename as t
where   t.created_at &gt; @created_at
     or t.created_at = @created_at and t.id &gt; @id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items