Postgresql – Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

postgresqlsqlite

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

Best Answer

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.