That’s why the original comment suggested both bigserial and a separate UUID for public exposure. More to the point the person I was replying to said:
> IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows
The implication I took from that was that they were suggesting using serial over bigserial. My comment was pushing back on that.
My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in.
Thus, not only would DB ids in principle never show in the UI, they wouldn't even be stable between sessions.
This would protect against a number of security attacks. (I don't know what I'd do about bookmarking, though I suspect most apps have a 80/20 solution they could deploy.) DB ids going out to a user really ought to be considered at least a smell, but it's pretty ingrained into our culture.
I wrote that style of session mapping for a project long ago. It was fairly easy, but a massive pain in the ass to debug. Ended up needing to record the mappings in the backend for a period of time.
I too coded that in my server. It's not hard to do, and debugging can be harder at times (but not excessively so.) As you say it's just a case of logging when debugging.
When anchors are needed I use a different column for that- not the primary index. (Usually some other unique, already-present, value like the sku or whatever.
The security implications though are substantial. I don't leak primary key info even if the PK is serialized. (These days I'm all-in on UIDS but I have older legacy systems to deal with too.)
That wouldn't work for integration scenarios where the other system need to store the ID so it can refer to it later. How would you provide IDs for integration purposes?
