Should I use UUID or something else?

Question

Very often I have a task, where I need to collect an object and I need to know it's ID before saving to DB (PostgreSQL).

I can do this with UUID but it has lots of disadvantages:
- less perfomance when selecting or including
- less perfomance with joins
- need more space

So question is: How can I generate an ID for the object beforehand and minimize UUID negative consequences?

Answer 1

We faced this issue with a project. I ran some tests (about 4M rows, if I remember right) which indicated that uuids didn't really hit PG's performance too badly compared with ints. Having used uuids as primary keys for some time now I wouldn't hesitate to do so again. Although, I must add the caveat we have yet to see how this performs in production on a large scale.

Check this out: http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

The nice thing about using uuids is you never have to worry about clashes. Not nice thing: they are a bit cumbersome if you're manually entering a query for a test.

If you end up selecting based on a large list of uuids use this trick: https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/

Hope this helps,

Adam.