DBA [SQLite] Primary Key discussion - Use guid or int ids ?

[Blackstareye]

For large projects I am used to create guid for my data models.
In Sqllite, there seems to be the normal convention to use (autoincrementing) integers for that or just use rowid.

So I asked my self, also regarding drift, what is your opinion about this ?
Should a medium/large project still use guids or is it smarter to use the provided ints ?

I am not so experienced with sqlite to answer that question my self but I thought maybe someone here has a better understanding about sqlite and this question :)

Thx in advance! :)

[simolus3]

The sqlite3 documentation page on rowids is somewhat ambivalent on this. On the one hand, it suggests that using a single-integer primary key (aka the rowid) can be beneficial for performance:

Access to records via rowid is highly optimized and very fast.

On the other hand, it also offers this:

In a perfect world, there would be no such thing as a "rowid" and all tables would following the standard semantics implemented as WITHOUT ROWID tables, only without the extra "WITHOUT ROWID" keywords.

While drift supports WITHOUT ROWID tables (by overriding withoutRowId on the table), this is not the default.

I don't think it really depends on the size of the project, but rather the semantic meaning of the primary key and what you're comfortable with. If you have attributes sensibly forming a natural key (or have an existing guid key on a backend), use that one and perhaps disable rowids on the table. If you need a synthetic key, why not use the rowid for this since it exists and is a commonly used and optimized idiom in SQLite?

[Blackstareye]

Thanks for your quick response :)

so it would be better to get rid of id columns and use rowid
OR
add withoutRowId on it, right ?

The rowid is indexed automatically right ?

I don't think it really depends on the size of the project, but rather the semantic meaning of the primary key and what you're comfortable with. If you have attributes sensibly forming a natural key (or have an existing guid key on a backend), use that one and perhaps disable rowids on the table.

Yeah it's really usecase related. But if you insert data very often from different places or have a distributed system you need guids. So if I have the ability to use guids, that is my default choice. But you are right, if sqlite prefers optimized rowids that should be the default way as long as it aligns to the usecase and as long guids are not required.

Thx :)

[simolus3]

so it would be better to get rid of id columns and use rowid

If you have a table with a single-column primary key that's an integer, sqlite will automatically turn that column into a rowid alias. So you can keep using those ids as well.

[Blackstareye]

thx, that is awesome! :>