I'm only speculating here, but it doesn't seem right to me at all to end up with a solution like that. I don't think there would be a reason where this ID would perform so bad as an index that it would be worth what I would consider odd reasoning and tech debt if I'm understanding things correctly. And clearly the query is tech debt because now it did cause such an issue. But also I think current solution seems like tech debt to me, having high odds of causing issues in the future, if the DB driver was to be changed or something about the way DB resolves indexes was to change, etc.
And with fallback they would end up reusing that index anyway.
This could also come up easily if generating queries to back a paginated UI, where the user can choose a sort order by user-meaningful metadata. So, the user might ask to sort by CreatedAt and then the app/service would need to add the Id tie-breaker.
That is possible, although I would say there are other potential challenges there in this case, and arguably there should be a specific index for that case, and a lot would depend on what exactly is being sorted, how frequently new rows are being inserted, etc.
But here the case should be batch indexing, processing, so it seems like auto incr with a timeout of assignment if those auto incr ranges are cached would still be suitable.
Like as I understand the problem, there is one service (ElasticSearch) that is working on indexing, and it's getting batched rows from postgres, to then index, but make sure at the same time to not miss any in those batches. And it's fine that it doesn't immediately at this second or minute do the indexing, so it should be fine to wait for the IDs to have been allocated.
And with fallback they would end up reusing that index anyway.