Hacker News Reader

Comment by selecsosi | Hacker News Reader

They could be using something like application generated time sortable UUID7s or some other sortable key

[edit] CreatedAt timestamp could be something from the client when the post is submitted or tagged from the ingest server and not when they actually are processed and hit the database

View on HN

For sure, but still why not use that in the first place?

And I agree I shouldn't have said "auto increment".

It depends on the resolution of the timestamp. With 1000s of posts a second coming in, the fallback would be that it is at the same "timestamp" and then we fallback to a greater identifier. My guess is that the resolution of the timestamp allows for more efficient postgres index usage then the id index which my guess is larger and a string so not as efficiently searchable as the underlying identifier

(my guess is time based index offer faster search performance or lower overhead than a string based search index which doesn't understand it is representing encoded time data)

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.

Reply on news.ycombinator.com