Ask HN: GUID, int, or both for primary key?

5 pointsFsp2WFuH7y ago4 comments

If both I'd just use int as primary key, and short guid as unique indexed key.

I like being able to get last n records if my primary key is int. At the same time I don't want to show to the public my auto incrementing int, I'd rather do a form of short guid. Reason is to prevent someone form randomly incrementing to next record or something similar. Most of my tables also have a created datestamp. Not sure, any thoughts on this?

4 comments

coreyp_17y ago

You actually answered your own question, which I'm glad for because that means that you are thinking about security.

Use numeric IDs internally (for foreign keys, etc.), and GUIDs for anything public-facing. NEVER expose the internal IDs to the end user, but, conversely, only use IDs internally in the database. As long as you are consistent, then it will never be a problem.

[edit: added this sentence] The ID will be the primary key, but the GUID can be a key as well, but it does not have to be part of the primary key.

As a side note, it's not just about a person being able to increment to the next record, but that it exposes information about your system. If a person has just joined your website, and they know that their ID is 7, then they know that there is nobody else on the site (and that's potentially catostrophic). If their order # is 5, then they know that nobody else is buying from you. Essentially, exposing IDs is, in essence, an information leak.

eesmith7y ago

Various discussion about something similar at https://news.ycombinator.com/item?id=16946557 . This concerned the essay "User IDs probably shouldn't be passed around as ints" at https://rachelbythebay.com/w/2018/04/27/uid/ .

The author argues that auto incrementing ints have additional failure modes that sparse integers don't have, even if those sequential values aren't user-facing.

If they have a created datestamp then if you index them then it's easy to get last-n.

daleholborow7y ago

There are additional arguments against using ints as ids in the database, if you ever plan to shard etc, as in "you'd still have to do a lot of work to migration data from an integer-key-based-system to something distributed". Various articles describe this. Keep in mind that many RDBMS systems now support some concept of a "sequential guid" - but keep in mind that they all store these in different ways. Plug: here's some code I pulled together, with links to the reference material, that will generate sequential guids for various databases:

https://github.com/daleholborow/iayos.sequentialguid

Speaking from personal experience, in theory I would always choose a (sequential)GUID as PK.. BUT... they ARE a pain in the bollocks to work with when you are developing and writing sql by hand to query data. E.g.

a) select * from Thingies where Id = 5

b) select * from Thingies where Id = '12331-432Aafds-32424-df'

jstewartmobile7y ago

If you do GUID PK or clustered index in an RDBMS, probably want to turn it into a monotonic GUID.

Many DBs order the table by PK. If the GUID value moves backward, that's a slow insert into the middle instead of a fast append to the end.

https://dba.stackexchange.com/a/17212

j / k navigate · click thread line to collapse