Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?)

share|improve this question
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 '11 at 6:07
    
According to docs: postgresql.org/docs/current/static/datatype-uuid.html (say 128 bits, but not integer) –  mamcx Dec 16 '11 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 '11 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 '11 at 17:22

1 Answer 1

up vote 1 down vote accepted

Of course, integer columns are smaller and faster to operate on. UUIDs 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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