How to deal with id gap when your query is like from id > XX to id < XXX

I have a large table of around 50M data. I am trying to get data with something like

select id,name,address,city from table_name where id > 50000000 and id < 50000050  order by id; 

But, this strategy could work only when there is no deleted data. However, In my case, I am soft-deleting data so whenever a row is deleted, its column deleted_at will be filled with current timestamp i.e when deleted deleted_at is not null.

Now, how can I tackle this situation? Please, suggest me what would be my best bet for this.

I am ready to:

  • create the index with an extra column(if possible) and reorder on every deletion

  • create a temp table with extra table and reorder it on every deletion

  • Or same trick with view

  • or some other way.