How to implement a high performing non incremental ID in postgresql?

Asked Mar 31 '17 at 04:53

Active Mar 31 '17 at 04:53

Viewed 59 times

I'd like to move away from incremental IDs that can easily be guessed and implement something truly random, but still highly indexable to the tune of millions of records.

I thought about using random alphanumeric ID like the ones used in short links (s36rb94kpw59v0h3w6s) but I have a suspicion it'll result in an extremely bloated index and poor optimization.

I also consider UUIDS but i'm not sure how they perform in terms of SELECT. I still want the speed of fetching incremental ID but I just want some sort of non-guessable ID format that wouldn't quadruple my server costs.

P.S. non postgresql solutions are out of the question unfortunately.

asked Mar 31 '17 at 04:53

CodeOverload

45,400
52
128
215

Look at the `pseudo_random` function on the PostgreSQL wiki. For bonus performance points re-implement it as a C extension. – Craig Ringer Mar 31 '17 at 05:01
Or: http://stackoverflow.com/questions/33760630/generate-unique-random-numbers-in-postgresql-with-fixed-length – a_horse_with_no_name Mar 31 '17 at 05:48
I don't think a UUID is that much slower then an integer. But it apparently depends on your workload and use-case – a_horse_with_no_name Mar 31 '17 at 05:50

How to implement a high performing non incremental ID in postgresql?

0 Answers0