Android: Use UUID as primary key in SQLite

Question

My app needs to get synced with other app users (on there own devices). I also want to support offline editing, that are synchronized to the other collaborative users when the user gets connected to the internet.

So the User A changes (while he is offline) some data (in ohter words he would update database entries) or add new records to the database. When User A gets connected to the internet, all changes and new records are delivered to the other collaborative Users. So User B will get the changes/updates and can insert/update them into User Bs local device database.

But I need to ensure that the ids of the database entries are unique along the whole system. Therefore I need to use something like UUID.

My question: Is it a bad idea to use a UUID (String / Varchar) as primary key in a android sqlite database table instead of an integer that would be auto incremented?

I guess there would be performance issues by using strings (a UUID has 36 characters) as primary key.

I guess indexing uuids instead of integers takes longer (comparing string vs. comparing integers). I also guess that when Im using UUID, every time a new database record/entry has been inserted the database needs to reindex the primary key column, since they primary key index is not in a sorted order anymore (which would be when I would use integer auto increment primary key, because every future record is added at the end, because the new auto incremented primary key is always the greatest number so far, so the index will automatically be in sorted order). What i also need to do is JOINS over 2 - 3 tables. I also guess that comparing strings on JOINS instead of integer would slow down the database query.

However I cant see any other possibility to implement such a collaborative syncing system, so I must use UUID, right?

Another possibility would be to use a integer auto increment primary key and to use a second column uuid. So to work on the users local device, i would use this primary key (integer) for JOINS etc., while I would use the uuid column for syncing with the other users.

What do you guys think about that approach or is it in your opinion to much work, since you wont expect a big significant performance issue by ussing UUID directly as primary key?

Any other suggestions?

Answer 1

Is it a bad idea to use a UUID (String / Varchar) as primary key in a android sqlite database table instead of an integer that would be auto incremented?

The only for-certain problem that I can think of is that you will not be able to use CursorAdapter and its subclasses for displaying the results of queries on that table. CursorAdapter requires a unique integer _id column in the Cursor, and presumably you will not have one of those. You would have to create your own adapter, perhaps extending BaseAdapter, that handles it.

I guess there would be performance issues by using strings (a UUID has 36 characters) as primary key.

Possibly, but I will be somewhat surprised if it turns into a material problem on device-sized databases.

However I cant see any other possibility to implement such a collaborative syncing system, so I must use UUID, right?

You need some sort of UUID for your network protocol. Presumably, you will need that UUID in your database. Whether that UUID needs to be the primary key of a table, I can't say, because I don't know your schema.

Another possibility would be to use a integer auto increment primary key and to use a second column uuid. So to work on the users local device, i would use this primary key (integer) for JOINS etc., while I would use the uuid column for syncing with the other users.

Correct. You would have a UUID->local integer ID mapping table, use the UUIDs in your network protocol, and keep the local database mostly using the local integer IDs. Whether or not this will be a significant performance improvement (particularly given the increased database schema complexity), I can't say.

What do you guys think about that approach or is it in your opinion to much work, since you wont expect a big significant performance issue by ussing UUID directly as primary key?

IMHO, either run some performance tests so you get some concrete comparable data, or only worry about it if your database I/O seems sluggish.

Answer 2

One set of performance results for UUIDs as binary and text can be found in somewhat related UUID/SQLite question: https://stackoverflow.com/a/11337522/3103448

Per the results, both binary and string UUIDs can be efficient in SQLite for Create and Query when indexed. A separate trade-off is whether a human readable string is preferred to the smaller data size of binary file size.