15 ms·

UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

204 points by lhenk 5y ago

ainar-g 5y ago
I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?
- jeff-davis 5y ago
  You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING https://www.postgresql.org/docs/current/plpgsql-control-stru...
  - megous 5y ago
    That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.
    - jeff-davis 5y ago
      I should have been more clear. Here are the details: If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance: CREATE TABLE u(i INT8 UNIQUE); -- insert random unique value in the range 0..n -- into table u, retrying if it's already present -- -- NOTE: this will not terminate if 0..n are all -- present CREATE OR REPLACE FUNCTION insert_uniq(n INT8) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE x INT8; BEGIN <<retry_loop>> LOOP BEGIN x := (random() * n)::int8; INSERT INTO u VALUES(x); RAISE NOTICE 'inserted unique value %', x; EXIT retry_loop; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'collision with value %; retrying', x; END; END LOOP; END; $$; This will obviously loop forever if 0..n are all occupied, but if you choose n as (2::numeric^63 - 1)::int8, that won't happen.
      - megous 5y ago
        Thank you.
- NicoJuicy 5y ago
  The point of guids/uuids is no collision... Ever... Including for large datasets. Even when you merge multiple together in 10 years. For your use-case you could use incremental IDs.
- kstrauser 5y ago
  That’s the UUID approach, but worse. According to the birthday problem[1], you’re 50% likely to get a collision in 65 bit numbers after about 5 billion insertions. That’s not an awful lot. Replace that with a 128-bit UUID and you’d have to insert 22,000,000,000,000,000,000 rows to get a 50% chance. That’s probably less likely than a cosmic ray flipping a random bit in RAM and corrupting the index that way. [1] https://en.wikipedia.org/wiki/Birthday_problem#Probability_table https://en.wikipedia.org/wiki/Birthday_problem#Probability_t...
  - jeff-davis 5y ago
    The post qualified as <= 10,000,000 total records. For that number of records, there's about a chance of about 0.00001 that you get a collision, assuming good randomness.
    - kstrauser 5y ago
      Sure, but stuff always grows, and the experiment gets run a bunch of times. Why not go with the built-in solution and then not have to worry about it?
      - jeff-davis 5y ago
        I'm just answering the poster's question directly; but in the general case, I agree with you. The cognitive overhead of dealing with the various "what ifs" usually aren't worth the couple bytes or cycles that you could save.
  - oconnore 5y ago
    Getting a collision with this approach doesn’t matter — the whole point is to loop if you do get a collision. The only issue is getting a long string of sequential collisions, which is highly unlikely.
    - kstrauser 5y ago
      But now you’ve tried code complexity for a few bytes if storage. That’s just not worth it.
- leephillips 5y ago
  I have a web service where I need to generate access tokens of five characters. I just generate a random one and check to see if it’s already in use. Been working fine for years.
- dragonwriter 5y ago
  > if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? What’s the use case for this where UUIDv4 or sequential ID isn’t better? Because it sounds like a solution in search of a problem. > Are there any downsides besides making the application side a tiny bit more complex? Are there any upsides to warrant the complexity?
  - nirvdrum 5y ago
    I wouldn't say it's a particularly great upside, but I've found tooling doesn't tend to work as well with UUID keys as it does integer keys. E.g., Hasura won't map UUID keys to the GraphQL ID type [1], which makes working with them unnecessarily difficult. Arguably, the issue should be fixed with the tool (or a different tool chosen), but there's only so many battles to pick and sometimes that decision is out of your control. [1] -- https://github.com/hasura/graphql-engine/issues/3578 https://github.com/hasura/graphql-engine/issues/3578
  - kortex 5y ago
    UUIDv4 has performance implications. But I agree, if you are already coupled to the DB (due to the check loop), generally you might as well use sequential. Seems too easy to screw up.
    - rswail 5y ago
      Database sequences comply with the ACID properties of the transactional processing. Generating your own IDs and adding "a simple for loop" means that you lose that capability for no good reason. If you have 10 million rows, you're looking at 16MB for the storage of a UUID, vs 8MB for storage of a 64 bit int. Both of those are entirely cacheable.
- staticassertion 5y ago
  Is the goal here to save space?
  - ainar-g 5y ago
    The goal is to have a non-sequential ID (for example, to hide the information about the actual size of the data set) in a situation where one needs to support a storage that doesn't have a native support for UUIDs (that isn't just “convert to TEXT”) and reuse as much of the schema as possible without [ab]using ORMs. For example, an application that has to support SQLite as well as PostgreSQL and maybe some other storages.
  - PeterisP 5y ago
    Reducing space is less about pure storage amount but rather about the fact that having a not-too-large datatype (e.g. native integer) for keys generally improves all kinds of performance as the indexes are more compact and better fit in caches, comparison is trivial so joins are faster, etc.
- stouset 5y ago
  If you have this situation, it’s not even worth the time or effort to not just use UUIDs. You’re adding complication for no gain whatsoever.
magicpointer 5y ago
About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID generators.
- tehlike 5y ago
  Mentioned this in a sibling comment: There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).
  - Benjammer 5y ago
    I think you can probably get the same "batching benefits" if you use a global ID generation service of some sort, with sequential IDs to improve indexing. Using sequential IDs doesn't necessitate using auto-generated sequential IDs.
    - tehlike 5y ago
      correct, but global service need local caching. Some "HiLo" type identity generation could work. Sequential-UUID simplifies it as you don't need that service, so it's preferable. HiLo explanation below: A client simply gets a range of ids to be used, exclusive to them. Then can use it without any roundtrip to server. This can be achieved with a "hi" stored on service, and a batchSize that's constant in the system. Each time a "hi" is requested, it's incremented by 1. Now the client can generate (hi * batchsize, (hi+1) * batchsize - 1).
  - zepolen 5y ago
    Another benefit to UUIDs is merging tables/databases without key conflict.
rossmohax 5y ago
Another alternative is ULID, which can be stored as UUID on a Postgres side, but is more b-tree friendly.
- pm90 5y ago
  Are there articles/examples on how to use ULIDs in postgres?
  - rossmohax 5y ago
    ULID and UUID are same size, passing ULID as UUID to PostgreSQL works seamlessly.
conradfr 5y ago
UUIDs are great when you use the id "publicly" but using an incremental value would be too revealing for different reasons. So it's good to know that performances are not bad.
- tehlike 5y ago
  There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).
- hu3 5y ago
  On the other hand, if you can get away with incremental ids it makes debugging much easier during development.
  - kaliszad 5y ago
    Not really. You should develop better tooling to visualize debugging information. Today's serious systems (this in my opinion includes e.g. collaborative rich text editors) are just too complicated to just eyeball. Pavel, a colleague of mine is developing a new collaborative rich text editor for OrgPad and here is, how we do some testing currently https://www.youtube.com/watch?v=VeVcNmNFzmc https://www.youtube.com/watch?v=VeVcNmNFzmc We use UUIDs for basically everything. It is simple, we have good tools for working with UUIDs. When we present any IDs to users, those are URL-safe BASE64 encoded UUIDs. In some places, we have "short" links that are about half the length - they cannot really be guessed but are much shorter to type/ Copy&Paste/ visually compare for our customers, who are not always super computer literate. :-)
    - throwawayboise 5y ago
      You're not wrong, but (as I suspect is the case with a lot of us) the vast majority of my work is CRUD and I don't reach for heavyweight debugging tools unless printf() or the equivalent fails me (which is rare). Integer IDs work great in this situation.
vbsteven 5y ago
I’m currently prototyping a little database+api+cli todo app and I want identifiers that can be abbreviated in the same way as partial git commit hashes can be used on the command line. What should I use? I was thinking of generating random character strings and simply retry when the db throws duplicate key error on insert. No sharding is necessary and I’d like to have efficient foreign keys. Any thoughts?
- mutatio 5y ago
  You could use a serial int and just hex-encode when interacting with the CLI? You could then use range queries to match short hashes by zeroing out the remaining bytes and using >=
- alexis2b 5y ago
  You could try NanoID[0]? Seems available in many languages. [0] https://blog.bibekkakati.me/nanoid-alternative-to-uuid https://blog.bibekkakati.me/nanoid-alternative-to-uuid
- adav 5y ago
  Check out linear congruential generators or other pseudorandom number generators. Then map the resulting number to letters.
staticassertion 5y ago
Another benefit of using sequential integers is that you can leverage a number of optimizations. For one thing you can represent a range of data more efficiently by just storing offsets. This means that instead of having to store a 'start' and 'end' at 8 + 8 bytes you can store something like 'start' and 'offset', where offset could be based on your window size, like 2 bytes. You can leverage those offsets in metadata too. For example, I could cache something like 'rows (N..N+Offset) all have field X set to null' or some such thing. Now I can query my cache for a given value and avoid the db lookup, but I can also store way more data in the cache since I can encode ranges. Obviously which things you cache are going to be data dependent. Sequential ints make great external indexes for this reason. Maybe I tombstone rows in big chunks to some other data store - again, I can just encode that as a range, and then given a lookup within that range I know to look in the other datastore. With a uuid approach I'd have to tombstone each row individually. These aren't universal optimizations but if you can leverage them they can be significant.
- lgas 5y ago
  Doesn't the offset approach run into trouble when sequence values get skipped due to rollbacks?
  - staticassertion 5y ago
    It's going to be an optimization that assumes some constraints on how you interact with your database.
deleted 5y ago
[deleted]
pmontra 5y ago
Meta: this company wrote an impressive number of articles about PostgreSQL since 2013. List at https://www.cybertec-postgresql.com/en/tag/postgresql/ https://www.cybertec-postgresql.com/en/tag/postgresql/
- aidos 5y ago
  I just had to do a double take as I was reading a stack overflow post at the same time and recognised it as the same author.
  - lhenk 5y ago
    Laurenz (the author) was Postgres person of the week not too long ago: https://postgresql.life/post/laurenz_albe/ https://postgresql.life/post/laurenz_albe/
- lhenk 5y ago
  Also, here's a list of blog posts from Laurenz Albe (the author of the OP post): https://www.cybertec-postgresql.com/en/author/cybertec_albe/ https://www.cybertec-postgresql.com/en/author/cybertec_albe/ His blog posts are a great read, I'd recommend checking them out!
barrkel 5y ago
Another point: if there's any temporal locality to your future access patterns - if you're more likely to access multiple rows which were inserted at roughly the same time - then allocating sequential identifiers brings those entries closer together in the primary key index. I used to work on a reconciliation system which inserted all its results into the database. Only the most recent results were heavily queried, with a long tail of occasional lookups into older results. We never had a problem with primary key indexes (though this was in MySQL, which uses a clustered index on the primary key for row storage, so it's an even bigger benefit); the MD5 column used for identifying repeating data, on the other hand, would blow out the cache on large customers' instances.
- vinayan3 5y ago
  To add on. If you are joining against a table where you are joining on a UUID the join becomes quite slow with very large tables, like >10 million rows. PG will say it's doing a hash look up and you'd think it'd be fast but it will take quite sometime relative to joining two large tables with integer IDs. With UUIDS PG will give up doing a hash look up sometimes and try to do table scans unless you adjust random_page_cost. In general joining on UUIDs for large tables is a bad idea. It can be great if you are joining a single row to another row.
topspin 5y ago
I just started a little side project and chose to use UUID for Postgresql keys. The schema is highly generic and I anticipate the possibility of merging instances. UUID precludes collisions in such a case.
- RedShift1 5y ago
  That includes foreign keys?
  - topspin 5y ago
    Yes.
cratermoon 5y ago
Postgres (and other relational DBs) really need to implement something like snowflake[1] or ksuid[2] 1 https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing... 2 https://segment.com/blog/a-brief-history-of-the-uuid/ https://segment.com/blog/a-brief-history-of-the-uuid/
- deleted 5y ago
  [deleted]
zzzeek 5y ago
> You are well advised to choose a primary key that is not only unique, but also never changes during the lifetime of a table row. This is because foreign key constraints typically reference primary keys, and changing a primary key that is referenced elsewhere causes trouble or unnecessary work. in one sense I agree with the author that things are generally just easier when you use surrogate primary keys, however they really should note here that the FOREIGN KEY constraint itself is not a problem at all as you can just use ON UPDATE CASCADE.
- dragonwriter 5y ago
  ON UPDATE CASCADE avoids much developer impact, but it isn’t free and has (potentially quite large) performance impacts.
simonw 5y ago
Something I really like about integer incrementing IDs is that you can run ad-hoc "select * from table order by id desc limit 10" queries to see the most recently inserted rows. I end up doing this a lot when I'm trying to figure out how my applications are currently being used. Strictly incrementing UUIDs can offer the same benefit.
3pt14159 5y ago
> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row when dealing with a request. Or the table's requirements change and now you need to track two different kinds of data or data that is missing the field. Me, I always just have the table make its own ID. It is just simpler, even when you think it is overkill.
- wwweston 5y ago
  It does seem that a "natural key" is frequently just a really foreign key in a database you and your org don't manage.
  - jeff-davis 5y ago
    That's a good observation. The only meaningful distinction between a natural key and a surrogate key is whether the number ever escapes the original system. For instance, a driver's license number is printed on the card itself, so a human sees it. Therefore, it's a natural key, just like a name. When you decide that whatever natural keys already exist aren't good enough for your organization, and you make a new key, it's not good to think of that as a surrogate key. The number will make it out somehow (as a "record locator" in a customer support call or something), and eventually become a natural key. It's best to just plan for any new key to be a natural key, which means using best practices for natural keys. That means it should be something reasonable to print, read, say, and hear; and it should also follow a pattern so it can be distinguished from other special numbers. Auto-increment is a shortcut, but usually not great in the long term unless it's something that will be well-contained inside the database as an implementation detail (e.g. a join key designed to refer to rarely-accessed fields of a wide table).
- tomnipotent 5y ago
  I've been bitten by using natural keys on several occasions, but I can' think of a time surrogate keys failed me beyond the tediousness of implementation.
panny 5y ago
It seems like int vs bigint is brushed off rather quickly here. bigint is twice the size of int, therefore indexing will be larger as well. Furthermore, all the FK storage and indexing will also be bloated by this choice. If you design a customer table with a bigint PK, and everything will point to customer (invoices, billing statements, etc), then that's not an insignificant amount of space. While most of us may want to have "billions served" like McDonald's, the reality is my company and your company will never have 2 billion customer accounts, even in the wildest of imaginations. If you ever did reach this point, it's "a good problem to have" and relatively easy to move from int -> bigint. Moving in the reverse direction is likely difficult or impossible. It would be nice to see real benchmarking on millions of rows to compare the three, but my gut tells me you use int by default, bigint if you outgrow int, and UUID if you have plenty of money for hardware and need distribution capabilities a UUID would enable.
- JshWright 5y ago
  Speaking from personal experience, just use bigint... If you aren't dealing with billions of rows, the size difference isn't that big a deal, and if you are dealing with billions of rows, the int -> bigint migration is definitely not "relatively easy". One of the most memorable anecdotes of my professional career is a production environment going down because we hit maxint on an important (and busy) table. The dirty hack we used to get the site back up (hint: int is _signed_), and the weeks it took to plan, test, and execute the migration.
- twhitmore 5y ago
  In datamodelling, tables can often be categorized by lifetime. 'Business Relationships' eg. customers, suppliers, products have a fairly long lifetime; whereas 'Business Transactions' are created on a much higher frequency. I'm generally fairly comfortable using int for business relationships, and bigint (long) for transaction data. For performance, insertion speed often seems to be dominated by 'commit latency' to sync to the disk; rather than by record size. I would agree that record size affects table scan, but for many datamodels keying may often be a relatively small proportion compared to the size of text fields and other data. I like to model keyspaces to work for 200 years, for the largest forseeable market growth, times at least a factor of 10 for safety.
rsync 5y ago
I have no particular expertise with modern databases and it has been decades since I did any work as a DBA. However, I cannot imagine creating table entries without a datestamp. No matter what else you are doing, or what you index by, I would want YYYY-MM-DD_HH-MM-SS in every row. Maybe I'm just weird that way ...
- NoNotTheDuo 5y ago
  And ideally there is a created time stamp and a last updated time stamp.
  - BenjiWiebe 5y ago
    (at least in my cases) Ideally nothing ever gets updated, there's just a newer version of the row.
- BatteryMountain 5y ago
  Same. Every entity always gets a created column at the minimum, that way when we query later we can order by created to see the last few days worth of data first. Can't do that if you don't know when something was created.
strangeattractr 5y ago
This is making me reconsider how I do IDs. I thought the performance of sequential IDs was significantly better. So my approach was to use a standard auto-increment primary ID and then obfuscate by id * p mod m where p and m are coprime and very large. then i get back the original ID using the mod inverse. Should I just be using UUID?
- eloff 5y ago
  I would use uuid in this case. If p and m are too large you get overflow. If they are too small your keys are guessable. If it matters, use uuid and don't waste time and mental energy on it.
eric4smith 5y ago
Simple rules: Use integer primary keys internally for identifiers and relationships. Use English/Other Language permalinks for URL's Use UUID's in places like API's one-time action links and "private" links that you only want to share with other people. Worked fine for me for many, many years.
- sk5t 5y ago
  A vote here against integer/serial PKs, not only because they leak information, but also because they can result in incorrect joins. IME it's much more often I've quickly made a table with a serial PK and later wished it were uuid; just about never made a uuid and later wished for the compactness or natural clustering of bigint. Maybe for a table of millions and millions of time-ordered events.
  - runeks 5y ago
    > […] but also because they can result in incorrect joins. Side question: can I get Postgres to throw an error if I try to join on two IDs where neither of the IDs have a foreign key reference to the other?
    - sk5t 5y ago
      I wouldn't think so, and this sounds like a mighty footgun!
  - eric4smith 5y ago
    Note I said "internal use". But how can primary keys result in incorrect joins? Unless you're changing a foreign key, joins will always be correct. Unless I'm doing something wrong in the last 30 years of using SQL.
    - efxhoy 5y ago
      If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.
foresto 5y ago
I once pondered how I might generate IDs that were as compact as a machine word, without a value (or small set of values) revealing the size of the data set. One application might be user-visible customer numbers that don't easily reveal how many customers there are. I eventually came across the idea of using maximal period linear-feedback shift registers to transform an integer variable through every possible value (minus one), but in a non-incremental sequence that depends on the LFSR arrangement. I never ended up putting the idea to use, but I've always been curious about people who have and how it worked out for them. [Edit to clarify: It was meant for obfuscation, not security against a determined attacker.]
- slver 5y ago
  The problem is that if your encoding algorithm leaks, it’s game over.
- dpifke 5y ago
  I've used a small block cipher like Skip32 or Speck to obfuscate database sequences, either on INSERT or as part of the encoding scheme. This works well against the German Tank Problem when there's no oracle allowing an attacker to guess lots of IDs quickly (such as when there are reasonable rate limits). It does not provide enough entropy when such an oracle exists (especially an offline one). For something like a password reset token, it still needs to be paired with suitably random bytes.
- BatteryMountain 5y ago
  Please see my previous comment, feel free to give feedback. So far I haven't encountered any problems in the short term by using the approach described.
foobarbazetc 5y ago
Always, always use a bigserial. (Actually, all serials are bigserial’s but the “base type” they add to the table differs, and it’ll always come back to bite you later. Ask me how I know…)
pritambarhate 5y ago
A little late to comment here. But for database IDs, I have found that Instagram's technique to generate IDs works very well: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-... They are not serially incrementing but still sortable. Thus prevent index fragmentation issues observed with UUIDS. Are 8 bytes in length. So index size is smaller compared to UUIDs. So you get all benefits of serial IDs but they are not easily guessable thus preventing sequential access attacks.
- orangepanda 5y ago
  > With more than 25 photos and 90 likes every second What unimaginable scale
  - giansegato 5y ago
    That was in 2012, when they "only" had 15M users Today, a decade later, they're at 1.074B
- codeflo 5y ago
  > they are not easily guessable I don't see how that's true. From reading the article you linked, you only need a valid shard ID (which you can extract from known IDs), the millisecond (which is guessable) and a 10-bit sequence (which you can easily brute-force). (And that's completely fine if their security model doesn't require unguessable IDs.)
  - pritambarhate 5y ago
    >> which you can easily brute-force It will results in a very high number of 404s. These can be monitored and the origin IPs can be banned.
    - codeflo 5y ago
      2^10 is 1024, so hundreds of requests. Not a very high number. And since it’s a counter, even less. Easy to disguise; the official app is likely to do more requests in a one-minute session. And obviously, sophisticated attackers aren’t limited to one IP. I’m suspecting you meant “easily guessable” in the human sense, not the cryptographic/security sense. My bad if I misunderstood you. Again, I’m not saying Instagram has any security problem, I’m just saying that this ID scheme in particular isn’t a security feature.
BatteryMountain 5y ago
I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers for keys either as I've been burnt by them before. I also doubt any software I build today or have built in the last 10 years will be used 100 years from now. Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite, and my dislike of int's got me thinking. I have a random string generator (have used it for years for things like OTP's and other reference numbers), where I give it an alphabet + length of the desired string. Using 8 to 12 characters, I can get a few million unique permutations. That is, if used as a primary key, few million per database table. Then I hear in the back of my head, guys from work who would argue I would run out of unique combinations or would have to do lookups to see if they exist. So I decided slap the year and month on it as a prefix, so a key might look like this: 2105HSUAMWPA. This gets indexed really well too and there is some inherent information that can be seen from looking at the key: Year 21, Month 5 and then the unique bits.It's basically 4 lines of code that gets called on every new database entity. I think it will be easy to shard/partition the data too if the need arise in the future, by simply looking at the first 4 digits. Thus to summarize: Data is sliced by entity type (customer, invoice, etc), then by date (2105 for May 2021) then by unique string. What do you guys think about this approach? Anyone been burnt by something like this?
- hardwaresofton 5y ago
  > Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)? [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/
hardwaresofton 5y ago
Yeah, just use a UUID unless the bits to store the UUID really are your driving limitation (they're not), having a UUID that is non-linear is almost always the most straight-forward option for identifying things, for the tradeoff of human readability (though you can get some of that back with prefixes and some other schemes). I'm not going to rehash the benefits that people have brought up for UUIDs, but they're in this thread. At this point what I'm concerned about is just... what is the best kind of UUID to use -- I've recently started using mostly v1 because time relationship is important to me (despite the unfortunate order issues) and v6[0] isn't quite so spread yet. Here's a list of other approaches out there worth looking at - isntauuid[1] (mentioned in this thread, I've given it a name here) - timeflake[2] - HiLo[3][4] - ulid[5] - ksuid[6] (made popular by segment.io) - v1-v6 UUIDs (the ones we all know and some love) - sequential interval based UUIDs in Postgres[7] Just add a UUID -- this almost surely isn't going to be what bricks your architecture unless you have some crazy high write use case like time series or IoT or something maybe. [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/ [1]: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-... [2]: https://github.com/anthonynsimon/timeflake https://github.com/anthonynsimon/timeflake [3]: https://en.wikipedia.org/wiki/Hi/Lo_algorithm https://en.wikipedia.org/wiki/Hi/Lo_algorithm [4]: https://www.npgsql.org/efcore/modeling/generated-properties.html#hilo-autoincrement-generation https://www.npgsql.org/efcore/modeling/generated-properties.... [5]: https://github.com/edoceo/pg-ulid https://github.com/edoceo/pg-ulid [6]: https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid [7]: https://www.2ndquadrant.com/en/blog/sequential-uuid-generators https://www.2ndquadrant.com/en/blog/sequential-uuid-generato...
rini17 5y ago
I'm a fan of generating primary key by copying natural key (if it's one integer) or hash of natural key. This is done only once when row is created and is never updated, even if natural key changes. In this case you are left with valuable bit of information that something happened to natural key.

204 points by lhenk 5y ago

I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?

jeff-davis 5y ago
You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING https://www.postgresql.org/docs/current/plpgsql-control-stru...
- megous 5y ago
  That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.
  - jeff-davis 5y ago
    I should have been more clear. Here are the details: If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance: CREATE TABLE u(i INT8 UNIQUE); -- insert random unique value in the range 0..n -- into table u, retrying if it's already present -- -- NOTE: this will not terminate if 0..n are all -- present CREATE OR REPLACE FUNCTION insert_uniq(n INT8) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE x INT8; BEGIN <<retry_loop>> LOOP BEGIN x := (random() * n)::int8; INSERT INTO u VALUES(x); RAISE NOTICE 'inserted unique value %', x; EXIT retry_loop; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'collision with value %; retrying', x; END; END LOOP; END; $$; This will obviously loop forever if 0..n are all occupied, but if you choose n as (2::numeric^63 - 1)::int8, that won't happen.
    - megous 5y ago
      Thank you.
NicoJuicy 5y ago
The point of guids/uuids is no collision... Ever... Including for large datasets. Even when you merge multiple together in 10 years. For your use-case you could use incremental IDs.
kstrauser 5y ago
That’s the UUID approach, but worse. According to the birthday problem[1], you’re 50% likely to get a collision in 65 bit numbers after about 5 billion insertions. That’s not an awful lot. Replace that with a 128-bit UUID and you’d have to insert 22,000,000,000,000,000,000 rows to get a 50% chance. That’s probably less likely than a cosmic ray flipping a random bit in RAM and corrupting the index that way. [1] https://en.wikipedia.org/wiki/Birthday_problem#Probability_table https://en.wikipedia.org/wiki/Birthday_problem#Probability_t...
- jeff-davis 5y ago
  The post qualified as <= 10,000,000 total records. For that number of records, there's about a chance of about 0.00001 that you get a collision, assuming good randomness.
  - kstrauser 5y ago
    Sure, but stuff always grows, and the experiment gets run a bunch of times. Why not go with the built-in solution and then not have to worry about it?
    - jeff-davis 5y ago
      I'm just answering the poster's question directly; but in the general case, I agree with you. The cognitive overhead of dealing with the various "what ifs" usually aren't worth the couple bytes or cycles that you could save.
- oconnore 5y ago
  Getting a collision with this approach doesn’t matter — the whole point is to loop if you do get a collision. The only issue is getting a long string of sequential collisions, which is highly unlikely.
  - kstrauser 5y ago
    But now you’ve tried code complexity for a few bytes if storage. That’s just not worth it.
leephillips 5y ago
I have a web service where I need to generate access tokens of five characters. I just generate a random one and check to see if it’s already in use. Been working fine for years.
dragonwriter 5y ago
> if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? What’s the use case for this where UUIDv4 or sequential ID isn’t better? Because it sounds like a solution in search of a problem. > Are there any downsides besides making the application side a tiny bit more complex? Are there any upsides to warrant the complexity?
- nirvdrum 5y ago
  I wouldn't say it's a particularly great upside, but I've found tooling doesn't tend to work as well with UUID keys as it does integer keys. E.g., Hasura won't map UUID keys to the GraphQL ID type [1], which makes working with them unnecessarily difficult. Arguably, the issue should be fixed with the tool (or a different tool chosen), but there's only so many battles to pick and sometimes that decision is out of your control. [1] -- https://github.com/hasura/graphql-engine/issues/3578 https://github.com/hasura/graphql-engine/issues/3578
- kortex 5y ago
  UUIDv4 has performance implications. But I agree, if you are already coupled to the DB (due to the check loop), generally you might as well use sequential. Seems too easy to screw up.
  - rswail 5y ago
    Database sequences comply with the ACID properties of the transactional processing. Generating your own IDs and adding "a simple for loop" means that you lose that capability for no good reason. If you have 10 million rows, you're looking at 16MB for the storage of a UUID, vs 8MB for storage of a 64 bit int. Both of those are entirely cacheable.
staticassertion 5y ago
Is the goal here to save space?
- ainar-g 5y ago
  The goal is to have a non-sequential ID (for example, to hide the information about the actual size of the data set) in a situation where one needs to support a storage that doesn't have a native support for UUIDs (that isn't just “convert to TEXT”) and reuse as much of the schema as possible without [ab]using ORMs. For example, an application that has to support SQLite as well as PostgreSQL and maybe some other storages.
- PeterisP 5y ago
  Reducing space is less about pure storage amount but rather about the fact that having a not-too-large datatype (e.g. native integer) for keys generally improves all kinds of performance as the indexes are more compact and better fit in caches, comparison is trivial so joins are faster, etc.
stouset 5y ago
If you have this situation, it’s not even worth the time or effort to not just use UUIDs. You’re adding complication for no gain whatsoever.

magicpointer 5y ago

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID generators.

tehlike 5y ago
Mentioned this in a sibling comment: There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).
- Benjammer 5y ago
  I think you can probably get the same "batching benefits" if you use a global ID generation service of some sort, with sequential IDs to improve indexing. Using sequential IDs doesn't necessitate using auto-generated sequential IDs.
  - tehlike 5y ago
    correct, but global service need local caching. Some "HiLo" type identity generation could work. Sequential-UUID simplifies it as you don't need that service, so it's preferable. HiLo explanation below: A client simply gets a range of ids to be used, exclusive to them. Then can use it without any roundtrip to server. This can be achieved with a "hi" stored on service, and a batchSize that's constant in the system. Each time a "hi" is requested, it's incremented by 1. Now the client can generate (hi * batchsize, (hi+1) * batchsize - 1).
- zepolen 5y ago
  Another benefit to UUIDs is merging tables/databases without key conflict.

rossmohax 5y ago

Another alternative is ULID, which can be stored as UUID on a Postgres side, but is more b-tree friendly.

pm90 5y ago
Are there articles/examples on how to use ULIDs in postgres?
- rossmohax 5y ago
  ULID and UUID are same size, passing ULID as UUID to PostgreSQL works seamlessly.

conradfr 5y ago

UUIDs are great when you use the id "publicly" but using an incremental value would be too revealing for different reasons. So it's good to know that performances are not bad.

tehlike 5y ago
There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).
hu3 5y ago
On the other hand, if you can get away with incremental ids it makes debugging much easier during development.
- kaliszad 5y ago
  Not really. You should develop better tooling to visualize debugging information. Today's serious systems (this in my opinion includes e.g. collaborative rich text editors) are just too complicated to just eyeball. Pavel, a colleague of mine is developing a new collaborative rich text editor for OrgPad and here is, how we do some testing currently https://www.youtube.com/watch?v=VeVcNmNFzmc https://www.youtube.com/watch?v=VeVcNmNFzmc We use UUIDs for basically everything. It is simple, we have good tools for working with UUIDs. When we present any IDs to users, those are URL-safe BASE64 encoded UUIDs. In some places, we have "short" links that are about half the length - they cannot really be guessed but are much shorter to type/ Copy&Paste/ visually compare for our customers, who are not always super computer literate. :-)
  - throwawayboise 5y ago
    You're not wrong, but (as I suspect is the case with a lot of us) the vast majority of my work is CRUD and I don't reach for heavyweight debugging tools unless printf() or the equivalent fails me (which is rare). Integer IDs work great in this situation.

vbsteven 5y ago

I’m currently prototyping a little database+api+cli todo app and I want identifiers that can be abbreviated in the same way as partial git commit hashes can be used on the command line. What should I use? I was thinking of generating random character strings and simply retry when the db throws duplicate key error on insert. No sharding is necessary and I’d like to have efficient foreign keys. Any thoughts?

mutatio 5y ago
You could use a serial int and just hex-encode when interacting with the CLI? You could then use range queries to match short hashes by zeroing out the remaining bytes and using >=
alexis2b 5y ago
You could try NanoID[0]? Seems available in many languages. [0] https://blog.bibekkakati.me/nanoid-alternative-to-uuid https://blog.bibekkakati.me/nanoid-alternative-to-uuid
adav 5y ago
Check out linear congruential generators or other pseudorandom number generators. Then map the resulting number to letters.

staticassertion 5y ago

Another benefit of using sequential integers is that you can leverage a number of optimizations. For one thing you can represent a range of data more efficiently by just storing offsets. This means that instead of having to store a 'start' and 'end' at 8 + 8 bytes you can store something like 'start' and 'offset', where offset could be based on your window size, like 2 bytes. You can leverage those offsets in metadata too. For example, I could cache something like 'rows (N..N+Offset) all have field X set to null' or some such thing. Now I can query my cache for a given value and avoid the db lookup, but I can also store way more data in the cache since I can encode ranges. Obviously which things you cache are going to be data dependent. Sequential ints make great external indexes for this reason. Maybe I tombstone rows in big chunks to some other data store - again, I can just encode that as a range, and then given a lookup within that range I know to look in the other datastore. With a uuid approach I'd have to tombstone each row individually. These aren't universal optimizations but if you can leverage them they can be significant.

lgas 5y ago
Doesn't the offset approach run into trouble when sequence values get skipped due to rollbacks?
- staticassertion 5y ago
  It's going to be an optimization that assumes some constraints on how you interact with your database.

deleted 5y ago

[deleted]

pmontra 5y ago

Meta: this company wrote an impressive number of articles about PostgreSQL since 2013. List at https://www.cybertec-postgresql.com/en/tag/postgresql/ https://www.cybertec-postgresql.com/en/tag/postgresql/

aidos 5y ago
I just had to do a double take as I was reading a stack overflow post at the same time and recognised it as the same author.
- lhenk 5y ago
  Laurenz (the author) was Postgres person of the week not too long ago: https://postgresql.life/post/laurenz_albe/ https://postgresql.life/post/laurenz_albe/
lhenk 5y ago
Also, here's a list of blog posts from Laurenz Albe (the author of the OP post): https://www.cybertec-postgresql.com/en/author/cybertec_albe/ https://www.cybertec-postgresql.com/en/author/cybertec_albe/ His blog posts are a great read, I'd recommend checking them out!

barrkel 5y ago

Another point: if there's any temporal locality to your future access patterns - if you're more likely to access multiple rows which were inserted at roughly the same time - then allocating sequential identifiers brings those entries closer together in the primary key index. I used to work on a reconciliation system which inserted all its results into the database. Only the most recent results were heavily queried, with a long tail of occasional lookups into older results. We never had a problem with primary key indexes (though this was in MySQL, which uses a clustered index on the primary key for row storage, so it's an even bigger benefit); the MD5 column used for identifying repeating data, on the other hand, would blow out the cache on large customers' instances.

vinayan3 5y ago
To add on. If you are joining against a table where you are joining on a UUID the join becomes quite slow with very large tables, like >10 million rows. PG will say it's doing a hash look up and you'd think it'd be fast but it will take quite sometime relative to joining two large tables with integer IDs. With UUIDS PG will give up doing a hash look up sometimes and try to do table scans unless you adjust random_page_cost. In general joining on UUIDs for large tables is a bad idea. It can be great if you are joining a single row to another row.

topspin 5y ago

I just started a little side project and chose to use UUID for Postgresql keys. The schema is highly generic and I anticipate the possibility of merging instances. UUID precludes collisions in such a case.

RedShift1 5y ago
That includes foreign keys?
- topspin 5y ago
  Yes.

cratermoon 5y ago

Postgres (and other relational DBs) really need to implement something like snowflake[1] or ksuid[2] 1 https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing... 2 https://segment.com/blog/a-brief-history-of-the-uuid/ https://segment.com/blog/a-brief-history-of-the-uuid/

deleted 5y ago
[deleted]

zzzeek 5y ago

> You are well advised to choose a primary key that is not only unique, but also never changes during the lifetime of a table row. This is because foreign key constraints typically reference primary keys, and changing a primary key that is referenced elsewhere causes trouble or unnecessary work. in one sense I agree with the author that things are generally just easier when you use surrogate primary keys, however they really should note here that the FOREIGN KEY constraint itself is not a problem at all as you can just use ON UPDATE CASCADE.

dragonwriter 5y ago
ON UPDATE CASCADE avoids much developer impact, but it isn’t free and has (potentially quite large) performance impacts.

simonw 5y ago

Something I really like about integer incrementing IDs is that you can run ad-hoc "select * from table order by id desc limit 10" queries to see the most recently inserted rows. I end up doing this a lot when I'm trying to figure out how my applications are currently being used. Strictly incrementing UUIDs can offer the same benefit.

3pt14159 5y ago

> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row when dealing with a request. Or the table's requirements change and now you need to track two different kinds of data or data that is missing the field. Me, I always just have the table make its own ID. It is just simpler, even when you think it is overkill.

wwweston 5y ago
It does seem that a "natural key" is frequently just a really foreign key in a database you and your org don't manage.
- jeff-davis 5y ago
  That's a good observation. The only meaningful distinction between a natural key and a surrogate key is whether the number ever escapes the original system. For instance, a driver's license number is printed on the card itself, so a human sees it. Therefore, it's a natural key, just like a name. When you decide that whatever natural keys already exist aren't good enough for your organization, and you make a new key, it's not good to think of that as a surrogate key. The number will make it out somehow (as a "record locator" in a customer support call or something), and eventually become a natural key. It's best to just plan for any new key to be a natural key, which means using best practices for natural keys. That means it should be something reasonable to print, read, say, and hear; and it should also follow a pattern so it can be distinguished from other special numbers. Auto-increment is a shortcut, but usually not great in the long term unless it's something that will be well-contained inside the database as an implementation detail (e.g. a join key designed to refer to rarely-accessed fields of a wide table).
tomnipotent 5y ago
I've been bitten by using natural keys on several occasions, but I can' think of a time surrogate keys failed me beyond the tediousness of implementation.

panny 5y ago

It seems like int vs bigint is brushed off rather quickly here. bigint is twice the size of int, therefore indexing will be larger as well. Furthermore, all the FK storage and indexing will also be bloated by this choice. If you design a customer table with a bigint PK, and everything will point to customer (invoices, billing statements, etc), then that's not an insignificant amount of space. While most of us may want to have "billions served" like McDonald's, the reality is my company and your company will never have 2 billion customer accounts, even in the wildest of imaginations. If you ever did reach this point, it's "a good problem to have" and relatively easy to move from int -> bigint. Moving in the reverse direction is likely difficult or impossible. It would be nice to see real benchmarking on millions of rows to compare the three, but my gut tells me you use int by default, bigint if you outgrow int, and UUID if you have plenty of money for hardware and need distribution capabilities a UUID would enable.

JshWright 5y ago
Speaking from personal experience, just use bigint... If you aren't dealing with billions of rows, the size difference isn't that big a deal, and if you are dealing with billions of rows, the int -> bigint migration is definitely not "relatively easy". One of the most memorable anecdotes of my professional career is a production environment going down because we hit maxint on an important (and busy) table. The dirty hack we used to get the site back up (hint: int is _signed_), and the weeks it took to plan, test, and execute the migration.
twhitmore 5y ago
In datamodelling, tables can often be categorized by lifetime. 'Business Relationships' eg. customers, suppliers, products have a fairly long lifetime; whereas 'Business Transactions' are created on a much higher frequency. I'm generally fairly comfortable using int for business relationships, and bigint (long) for transaction data. For performance, insertion speed often seems to be dominated by 'commit latency' to sync to the disk; rather than by record size. I would agree that record size affects table scan, but for many datamodels keying may often be a relatively small proportion compared to the size of text fields and other data. I like to model keyspaces to work for 200 years, for the largest forseeable market growth, times at least a factor of 10 for safety.

rsync 5y ago

I have no particular expertise with modern databases and it has been decades since I did any work as a DBA. However, I cannot imagine creating table entries without a datestamp. No matter what else you are doing, or what you index by, I would want YYYY-MM-DD_HH-MM-SS in every row. Maybe I'm just weird that way ...

NoNotTheDuo 5y ago
And ideally there is a created time stamp and a last updated time stamp.
- BenjiWiebe 5y ago
  (at least in my cases) Ideally nothing ever gets updated, there's just a newer version of the row.
BatteryMountain 5y ago
Same. Every entity always gets a created column at the minimum, that way when we query later we can order by created to see the last few days worth of data first. Can't do that if you don't know when something was created.

strangeattractr 5y ago

This is making me reconsider how I do IDs. I thought the performance of sequential IDs was significantly better. So my approach was to use a standard auto-increment primary ID and then obfuscate by id * p mod m where p and m are coprime and very large. then i get back the original ID using the mod inverse. Should I just be using UUID?

eloff 5y ago
I would use uuid in this case. If p and m are too large you get overflow. If they are too small your keys are guessable. If it matters, use uuid and don't waste time and mental energy on it.

eric4smith 5y ago

Simple rules: Use integer primary keys internally for identifiers and relationships. Use English/Other Language permalinks for URL's Use UUID's in places like API's one-time action links and "private" links that you only want to share with other people. Worked fine for me for many, many years.

sk5t 5y ago
A vote here against integer/serial PKs, not only because they leak information, but also because they can result in incorrect joins. IME it's much more often I've quickly made a table with a serial PK and later wished it were uuid; just about never made a uuid and later wished for the compactness or natural clustering of bigint. Maybe for a table of millions and millions of time-ordered events.
- runeks 5y ago
  > […] but also because they can result in incorrect joins. Side question: can I get Postgres to throw an error if I try to join on two IDs where neither of the IDs have a foreign key reference to the other?
  - sk5t 5y ago
    I wouldn't think so, and this sounds like a mighty footgun!
- eric4smith 5y ago
  Note I said "internal use". But how can primary keys result in incorrect joins? Unless you're changing a foreign key, joins will always be correct. Unless I'm doing something wrong in the last 30 years of using SQL.
  - efxhoy 5y ago
    If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.

foresto 5y ago

I once pondered how I might generate IDs that were as compact as a machine word, without a value (or small set of values) revealing the size of the data set. One application might be user-visible customer numbers that don't easily reveal how many customers there are. I eventually came across the idea of using maximal period linear-feedback shift registers to transform an integer variable through every possible value (minus one), but in a non-incremental sequence that depends on the LFSR arrangement. I never ended up putting the idea to use, but I've always been curious about people who have and how it worked out for them. [Edit to clarify: It was meant for obfuscation, not security against a determined attacker.]

slver 5y ago
The problem is that if your encoding algorithm leaks, it’s game over.
dpifke 5y ago
I've used a small block cipher like Skip32 or Speck to obfuscate database sequences, either on INSERT or as part of the encoding scheme. This works well against the German Tank Problem when there's no oracle allowing an attacker to guess lots of IDs quickly (such as when there are reasonable rate limits). It does not provide enough entropy when such an oracle exists (especially an offline one). For something like a password reset token, it still needs to be paired with suitably random bytes.
BatteryMountain 5y ago
Please see my previous comment, feel free to give feedback. So far I haven't encountered any problems in the short term by using the approach described.

foobarbazetc 5y ago

Always, always use a bigserial. (Actually, all serials are bigserial’s but the “base type” they add to the table differs, and it’ll always come back to bite you later. Ask me how I know…)

pritambarhate 5y ago

A little late to comment here. But for database IDs, I have found that Instagram's technique to generate IDs works very well: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-... They are not serially incrementing but still sortable. Thus prevent index fragmentation issues observed with UUIDS. Are 8 bytes in length. So index size is smaller compared to UUIDs. So you get all benefits of serial IDs but they are not easily guessable thus preventing sequential access attacks.

orangepanda 5y ago
> With more than 25 photos and 90 likes every second What unimaginable scale
- giansegato 5y ago
  That was in 2012, when they "only" had 15M users Today, a decade later, they're at 1.074B
codeflo 5y ago
> they are not easily guessable I don't see how that's true. From reading the article you linked, you only need a valid shard ID (which you can extract from known IDs), the millisecond (which is guessable) and a 10-bit sequence (which you can easily brute-force). (And that's completely fine if their security model doesn't require unguessable IDs.)
- pritambarhate 5y ago
  >> which you can easily brute-force It will results in a very high number of 404s. These can be monitored and the origin IPs can be banned.
  - codeflo 5y ago
    2^10 is 1024, so hundreds of requests. Not a very high number. And since it’s a counter, even less. Easy to disguise; the official app is likely to do more requests in a one-minute session. And obviously, sophisticated attackers aren’t limited to one IP. I’m suspecting you meant “easily guessable” in the human sense, not the cryptographic/security sense. My bad if I misunderstood you. Again, I’m not saying Instagram has any security problem, I’m just saying that this ID scheme in particular isn’t a security feature.

BatteryMountain 5y ago

I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers for keys either as I've been burnt by them before. I also doubt any software I build today or have built in the last 10 years will be used 100 years from now. Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite, and my dislike of int's got me thinking. I have a random string generator (have used it for years for things like OTP's and other reference numbers), where I give it an alphabet + length of the desired string. Using 8 to 12 characters, I can get a few million unique permutations. That is, if used as a primary key, few million per database table. Then I hear in the back of my head, guys from work who would argue I would run out of unique combinations or would have to do lookups to see if they exist. So I decided slap the year and month on it as a prefix, so a key might look like this: 2105HSUAMWPA. This gets indexed really well too and there is some inherent information that can be seen from looking at the key: Year 21, Month 5 and then the unique bits.It's basically 4 lines of code that gets called on every new database entity. I think it will be easy to shard/partition the data too if the need arise in the future, by simply looking at the first 4 digits. Thus to summarize: Data is sliced by entity type (customer, invoice, etc), then by date (2105 for May 2021) then by unique string. What do you guys think about this approach? Anyone been burnt by something like this?

hardwaresofton 5y ago
> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)? [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/

hardwaresofton 5y ago

Yeah, just use a UUID unless the bits to store the UUID really are your driving limitation (they're not), having a UUID that is non-linear is almost always the most straight-forward option for identifying things, for the tradeoff of human readability (though you can get some of that back with prefixes and some other schemes). I'm not going to rehash the benefits that people have brought up for UUIDs, but they're in this thread. At this point what I'm concerned about is just... what is the best kind of UUID to use -- I've recently started using mostly v1 because time relationship is important to me (despite the unfortunate order issues) and v6[0] isn't quite so spread yet. Here's a list of other approaches out there worth looking at - isntauuid[1] (mentioned in this thread, I've given it a name here) - timeflake[2] - HiLo[3][4] - ulid[5] - ksuid[6] (made popular by segment.io) - v1-v6 UUIDs (the ones we all know and some love) - sequential interval based UUIDs in Postgres[7] Just add a UUID -- this almost surely isn't going to be what bricks your architecture unless you have some crazy high write use case like time series or IoT or something maybe. [0]: http://gh.peabody.io/uuidv6/ http://gh.peabody.io/uuidv6/ [1]: https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c https://instagram-engineering.com/sharding-ids-at-instagram-... [2]: https://github.com/anthonynsimon/timeflake https://github.com/anthonynsimon/timeflake [3]: https://en.wikipedia.org/wiki/Hi/Lo_algorithm https://en.wikipedia.org/wiki/Hi/Lo_algorithm [4]: https://www.npgsql.org/efcore/modeling/generated-properties.html#hilo-autoincrement-generation https://www.npgsql.org/efcore/modeling/generated-properties.... [5]: https://github.com/edoceo/pg-ulid https://github.com/edoceo/pg-ulid [6]: https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid [7]: https://www.2ndquadrant.com/en/blog/sequential-uuid-generators https://www.2ndquadrant.com/en/blog/sequential-uuid-generato...

rini17 5y ago

I'm a fan of generating primary key by copying natural key (if it's one integer) or hash of natural key. This is done only once when row is created and is never updated, even if natural key changes. In this case you are left with valuable bit of information that something happened to natural key.