4

For a new SASS app with postgres 9, that will support disconnected clients.

The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other platforms. THe client database will be sqlite.

Each customer will use their own schema, so hopefully not will exist a too big table, but still because this is for invoicing & financial stuff a lot of data will be generated, and because the cloud hosting cost are tied to CPU/RAM overage charges make sense to have the best performance from the start (plus faster responses times is good for the end-user).

So,I need to use a UUID for support in the client to create new records. Now, I'm debating if use the UUID ONLY for the sync part and regular IDs for JOINS & primary keys.

However, also I feel is duplication, and if I will store the uuid why not drop the int keys at all in the server, but worry if this will give me performance issues later and if which index type I need to use (btree will be good enough?)

Improve this question
5
  • 1
    I am not 100% on this, but I believe that uuid is stored as a 128 bit integer, and is probably indexed in a similar way, meaning the strings you see are simply a representation.
    – xenoterracide
    Dec 16, 2011 at 6:07
  • According to docs: postgresql.org/docs/current/static/datatype-uuid.html (say 128 bits, but not integer)
    – mamcx
    Dec 16, 2011 at 14:45
  • That's true, but it also says it provides storage and comparison functions, I suspect this means that it's not stored or compared by its standardly displayed strings.
    – xenoterracide
    Dec 16, 2011 at 17:18
  • 1
    I'll also be honest and say that even if they are 10 times slower, they may not be your bottleneck. Worrying about this sounds a bit like premature optimization
    – xenoterracide
    Dec 16, 2011 at 17:22
  • You have a natural unique composite key (CustonerID, RecordKey) two 32 bit intergers is still smaller than 128 bit interger. I do not know the database schema but if all the records from different customer go into shared tables you have to sort out the different customers any ways, So use the data you have to create unquie key Unique Keys can be a composite column, I also suggest move up to later version of PG
    – zsheep
    Dec 19, 2019 at 15:10

2 Answers 2

Reset to default
3

Of course, integer columns are smaller and faster to operate on. The data type uuid has a size of 16 bytes, integer has 4 bytes. Smaller indexes, faster JOIN operations, etc. But not a dramatic difference.

If you only ever query by UUID, additional integer IDs will give you nothing. Also, if your tables are small, the difference is negligible and you will do just as well without additional integer IDs.

If you have to join multiple tables on the ID, if you have foreign keys referencing the ID or if your tables grow huge (as well as your indexes), then operate with integer IDs internally.

Improve this answer
1

Adding to what Erwin has stated

The push now is to use BigSerial 64bit integer as it not uncommon today to use up a 32 bit integer..

Integers are created serial, so the order the records are created is easy to see, easy to read, easy to see missing data.

UUIDs: are not create serial (except V3 of UUID) its a random number displayed in Hex extremely difficult to read and there is no natural order and is extremely difficult to see missing data, Select UUID order by UUID on a heavy write database will always be a random result.

With Postgresql need to decide which UUID generator to use

but Lets backup and remember why UUID are used in Databases

20+ years ago it was common to have database scatter around, and every so many hours or days the data would be sent back to the Main DataCenter During the merging of the databases there were collisions on the Index keys that required manual fixing..

So people started using Global Unique ID it failed because there where still collisions although fewer what verision is UUID on now 5

Another big push to use UUID was the webserver apps need unique keys that could be generated at the webserver and would not collide with a peer webserver.

Unless the database is going to have to be merged on regular bases with another DB, UUID draw backs out way its positives at least that is my take.

Readability of the data is extremely important when one has to work with it all day long.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.