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?

Solution 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.

Solution 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.

Comments

[Eir Nym]

You can store UUID as 16-byte binary

[CommonsWare]

@EirNym: That's good to know, though I fail to see what it has to do with the question, the answer, or your apparent downvote of my answer.

[Eir Nym]

you tell that ASCII representation is only one for UUID in SQLite3, and tell drawbacks for this. The binary representation has no such drawbacks: it is fast enough for devices with Android 2.3+ which was recommended minimum at that year 2013. Today we have faster devices and we can use not only SQLite3 as database engine, while it will stay common.

[CommonsWare]

@EirNym: "you tell that ASCII representation is only one for UUID in SQLite3, and tell drawbacks for this" -- I fail to see anything in my answer that fits your description. For example, the term "ASCII" appears only in your comment and in this reply comment. The answer is focused on the problems with the requirements of CursorAdapter, which your solution does not address either.

[Eir Nym]

This is BLOB-like type and you have to use text representation for direct value comparison, but it is fast enough to use as primary/foreign keys. Yes, you can shoot in your leg and use varchar primary key, but you can explain that binary representation could be used with less drawbacks. The code for Android will not changed.

[CommonsWare]

@EirNym: "but you can explain that binary representation could be used with less drawbacks" -- you are welcome to your opinion. I disagree. "The code for Android will not changed" -- yes, it will, at least with trying to use CursorAdapter with it.