42 ms·

Choosing a Postgres primary key

• jpalomaki 4y ago
  Size also matters. If you are running cheaper instances on cloud, they have limited IO and can be short on memory. Smart data types for keys, enums instead of varchars etc helps to keep indexes small.
• ThePhysicist 4y ago
  I've had good success with using auto-incrementing BIGINTs as internal IDs and creating an additional BYTEA field as external IDs. Foreign keys would be based on the internal IDs, anything user-facing would use external IDs. I think it's a good compromise as it keeps foreign key size small and still allows hiding internal structure from users.
  • silvestrov 4y ago
    I agree, this is the way to do it. Anything originating from the outside is bound to change, being email address, social security numbers, ... The post also completely ignores foreign keys. It is an absolute advantage to size and speed to have foreign keys to be int4/int8 and not an email address or UUID.
  • jetpackjoe 4y ago
    Rather than use an extra column, I’ve taken to hashing the internal key (with a salt based on the entity type and some secret) to create the external facing ID.
    • xaferel 4y ago
      That's a good idea but doesn't it need 2 round trips if you use an auto-increment primary key? First insert and then update by hashing the new id.
      • grncdr 4y ago
        Not the poster you’re replying to, but with this approach you generally don’t store the hashed identifier. Just encode/decode at the application boundaries.
        • Too 4y ago
          How do you decode a hash?
          • grncdr 4y ago
            ah sorry, I was not very precise about "hashed". What I meant (and have done in the past) is to encrypt/decrypt the auto-incrementing ID at the application boundaries. If the OP is really using a one-way hash then yes, you would have to store both IDs.
    • filleokus 4y ago
      That's really clever. Have you encountered any problems with it in practice?
      • Ankhers 4y ago
        Not the person you replied to, but I have had some issues with this in the past. Though it is more with how it was done than the approach itself. In this project I do not believe the IDs were always encrypted when being sent to the user. So we sometimes had to guess whether we received an encrypted ID vs a regular integer ID because it is possible for the encryption algo that was used to return a sequence of numbers.
    • ThePhysicist 4y ago
      Interesting approach, will consider that in the future! Though not sure if it's a good idea as that couples internal & external IDs, i.e. it's not possible to change one without also changing the other (but also not sure if that's really an issue).
    • aeyes 4y ago
      If it's hashed, how do you get back to the internal ID if you only have the external ID? I used encryption instead because I can reverse it.
    • arp242 4y ago
      One thing I've done is using the customer name codepoints of every character + object ID formatted as base-36. So with a customer email of 'martin@arp242.net' and an object ID of 52 you end up with 1563 + 52 = 1615, or 18v in base-36. You can add a "base number" to make it a but larger, e.g. 50,000 so it becomes "13tr". I'm sure people can figure this scheme out with enough effort; it's certainly not cryptographically secure, but it's "hidden enough" for many purposes, not much longer than numeric IDs (shorter in many cases), doesn't require any special DB-fu, and is reversible if you know the customer (which you usually do).
    • hot_gril 4y ago
      This seems expensive and requires you to really know what you're doing with the cryptography. Why not just use a random external facing ID?
  • hot_gril 4y ago
    This is the standard way of doing it.
• traceroute66 4y ago
  Honestly that's a poor blog post. Randomly concludes "the best time-based ID seems to be xid" without saying why or comparing to others e.g. ksuid, UUIDv7 etc ("xid" is only mentioned twice in the entire blog, first in the above statement and second a link to the reference implementation). Equally unfortunate that they picked "xid" as their supposed "best" because Postgres has an internal identifier that is also called "xid" and is very much NOT to be used as a primary key ! Downplays the many issues with "serial", including somehow thinking the word might "might" has a place next to the words "not want to expose them to the world though" .... you DON'T, full stop. Exposing predictable identifiers to the world is never a good thing. I'm not really sure what that blog post is supposed to be achieving really. I didn't learn anything.
  • ldjb 4y ago
    Collaborative databases (Wikidata, TheMovieDB, VNDB, etc.) all use serial identifiers. What is the problem with this? These websites don't want to hide how many entries they have (they tend to promote them), and it doesn't really matter if you iterate through all the numbers – the data is available through open licences anyway. I think there are many situations where you don't want to expose predictable identifiers, but there are also examples where predictable identifiers may actually be beneficial.
    • indymike 4y ago
      > What is the problem with this? It causes meetings with people who think all predictable identifiers are a problem.
    • aniforprez 4y ago
      If your data is public and can be scraped anyway, obviously a serial identifier doesn't matter. If your data is sequestered between accounts or has tenants all sharing the same database or API, that's just one accidental permission error away from being able to scrape every single record. If your customer's data is meant to be private, simply easier to generate unique IDs for each record. Plus it makes it incredibly easy for competitors to see the size of your business simply by signing up for an account and looking at the IDs.
• jbverschoor 4y ago
  Pretty uninformative post. Goes from count(*) to some uuids, but fails to see the bigger picture. Depending on the data, but assuming most data isn't big data: - Use integers internally, maybe suffixed by a shard-id to prevent collision, but keep order. - Use (random) external ids to access from the outside. Note that certain uuids will still leak some information: time between records, number of machines, etc.
• rco8786 4y ago
  Good intro article. I'd always heard that serial ints aren't guaranteed to be ordered but never knew why (because they are generated non-transactionally..so if an INSERT transaction rolls back the id that would have been used is effectively consumed/skipped). What I see a lot in practice is a bigint numeric id for internal use (better for joins, FKs) and also a textual token for public use, perhaps with a typed prefix indicate the type of record it's identifying (U-AS234FDS for User, etc)
  • okl 4y ago
    I guess mean "consecutive" or "sequential" instead of "ordered"
    • rco8786 4y ago
      Oops yes I sure did. Thanks
• lysecret 4y ago
  My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your normalisation. Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation. Of course if you work on something where you don’t know what it is yet (actually humans are a good example for that) uuid or int might make sense but I hear so many times picking non semantic as a default.
  • silvestrov 4y ago
    Semantics tend to change over time, so you will now have to change the meaning of your key. Did you know that sometimes the same social security number is assigned to multiple persons? In this case a person can change social security number. Good luck updating all of your database foreign keys in this case.
    • williamdclt 4y ago
      Updating foreign keys in a database is at least doable; updating external systems that have a reference to this entity is impossible
  • williamdclt 4y ago
    I'd heavily push for the exact opposite. Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. I find that there actually rarely is something defining the thing you're working on. The concept of "immutable identity" is rarely a useful thing in digitalized systems: - being able to create a new digital entity for the same real-life entity is almost always useful and expected ("the setup of this user is all messed up, just disable it and create a new one") - attributes that you thought were immutable actually are not ("surely the 'originally scheduled time' of an event is an immutable property" - except when you have a bug and events are scheduled at the wrong time and you need to fix data) - the concept of "immutable identity" is often pretty subjective in the real world. We generally agree that a person has an immutable identity, sure, but is a 9am appointment that's moved to a week later the same appointment, or a new one? Depends on who you ask, depends on what purposes you need this concept of "identity" for.
• ammmir 4y ago
  While this is a good overview of the options for primary key generation, there's no silver bullet here. Most projects that are using SQL should just use the gold standard: an auto-incrementing integer for an internal primary key. And then decouple the public-facing primary key from it into a separate column, whether it be ULID, UUID, or a random-project-slug-123. Also, during debugging, it's a lot nicer to look at short primary keys than to have UUIDs flooding the screen.
  • cameronh90 4y ago
    Why would you use an integer primary key and a public facing UUID? That seems like it's the worst of both worlds: ugly externally visible identifiers, record bloat, a database that you can't easily merge in the event of backups or DR, and having to roundtrip to the DB before you know the ID of a record. I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmarked performance reasons not to.
    • boloust 4y ago
      You're right to point at performance as the main motivator for this setup. The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size. UUID PKs are also more susceptible to fragmentation.
      • tpetry 4y ago
        Thats not how PostgreSQL works. The primary key is only included in every secondary key for MySQL. PostgreSQL secondary indexes directly point at the page and rowid.
        • dspillett 4y ago
          It works this way in SQL Server too, and some other DBs, if you have a clustered index (usually recommended). The clustering key is included in all non-clustered indexes on the table. Not that this doesn't mean NCIs inherit any extra fragmentation potential from the clustering key, as it is effectively INCLUDed and not considered by of the key of the supporting index. Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.
• nargella 4y ago
  Disclaimer: not a dba so my terms might not be appropriate I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting. [inspiration](https://github.com/tvondra/sequential-uuids/blob/master/sequential_uuids.c https://github.com/tvondra/sequential-uuids/blob/master/sequ...)
  • djbusby 4y ago
    I use ULID, 128 bits, time and great sorting https://github.com/ulid/spec https://github.com/ulid/spec
    • dchuk 4y ago
      Is there any way to have the database generate these automatically vs your application?
      • cpburns2009 4y ago
        The common databases don't support natively support generating ULIDs to my knowledge. You can usually find extensions if you prefer generating them in the database instead of the application. I generate them in the application, and store them as a UUID in PostgreSQL to avoid needing any database extensions.
      • djbusby 4y ago
        Yea, there are a few extensions for PG, in C and Go that give a ulid_create() function that can be used as column default, just like serial.
    • lawrjone 4y ago
      Wrote about my experience using ulids in Postgres if people are considering it: https://blog.lawrencejones.dev/ulid/ https://blog.lawrencejones.dev/ulid/
      • 4y ago
• conaclos 4y ago
  Hybrid Logical Clock [1] could be of interest for readers. This is a monotonically increasing clock based on a physical clock. Combined with a machine identifier you can obtain globally unique identifiers that are totally ordered. [1] https://cse.buffalo.edu/~demirbas/publications/hlc.pdf https://cse.buffalo.edu/~demirbas/publications/hlc.pdf
• deleted 4y ago
  [deleted]
• philliphaydon 4y ago
  I’ve been using HiLo for so long this isn’t something I think about. I don’t see a point in trying to hide the ID. Either it’s public or it’s private and should be verified before being accessed.
  • yencabulator 4y ago
    > I don’t see a point in trying to hide the ID. Exposing sequential numbers tells your competition the size of your company's user base, user activity levels, and growth. https://en.wikipedia.org/wiki/German_tank_problem#Historical_example_of_the_problem https://en.wikipedia.org/wiki/German_tank_problem#Historical...
• say_it_as_it_is 4y ago
  Probably worth revising this post to include BIGINT and BIGSERIAL, and then changing the entire Supabase schema to use them as well.
• lawrjone 4y ago
  Funny this keeps coming up! I wrote about my experience using ulids the other day, specifically with Postgres and some of the dis/advantages you get with it. It's a deeper dive into ulids than this article is, and shows some real world issues that crop up: https://blog.lawrencejones.dev/ulid/ https://blog.lawrencejones.dev/ulid/ That said, and spoiler alert: I'd probably go with bigint-sequence backed text IDs if I were choosing this over again.
• gregwebs 4y ago
  There is an umentioned security aspect that you should be aware of for adopting timestamp-based ids: you are leaking information about time, and this information could be sensitive. This is how I would summarize a security perspective. * autoincrement id: leaks information about the system as a whole. Users can attack each other. Might be suitable for an internal-only application or an application that doesn't care about leaking this information and goes to great effort to be resilient to users attacking each other. * timestamp + random id: leaks information about the time the individual record was created. An attacker can attempt to learn sensitive information about an individual. Suitable for a record that is already publicly shared with its time (e.g. a tweet). Might be suitable otherwise if ids are not public. That is only the record creator can view the id and you don't send out links with the ids to the user (particularly over insecure channels such as email). * random id: does not leak information. suitable for any use case that is okay with the performance implications (of a non-sortable fragemented index). I am wary of how they call xid the best time-based id. It just removes all (run-time) randomness and thus performs the best. xid seems to be the same as MongoDB's oid. It is designed to be a conflict-free timestamp that can be used in a distributed system, and it is good at that. But in terms of protecting users for some use cases it could be worse than an auto-increment id because cross-user attacks are still possible (they will take many, many more attempts though) and it leaks information about time.
  • voxic11 4y ago
    Another thing about autoincrements, they can leak rate information. You can do something like create a new user, wait a day, create a second user, then the difference in the userId tells you the rate at which new users are being created.
• quartz 4y ago
  Given the advantages of sortable UUIDs and this post's conclusion that XID is the best option right now, is Supabase planning to add pg_idkit to their list of supported extensions?
  • kiwicopple 4y ago
    >pg_idkit to their list of supported extensions For some of these simpler extensions, we're looking at using AWS's TLE (https://github.com/aws/pg_tle https://github.com/aws/pg_tle), which would allow user-contributed extensions. If we can pull that off, we'll probably look again at the current set of extensions we offer and then see which ones can be ported to a TLE instead
• glacials 4y ago
  The author misses one advantage of UUIDs: if you’re working in high-throughput distributed systems, serial IDs create a bottleneck and single point of failure in the service handing out IDs. With UUIDs any service can generate an ID itself and tell downstream services about it in parallel—even if one of them is down, slow, or needs retrying.
  • abujazar 4y ago
    This can also be achieved in distributed systems by having each node skip IDs equivalent to the number of nodes in the cluster. E.g. node 1 in a 5 node cluster assigns ids 1, 6, 11 and node 2 assigns 2, 7, 12 and so on.
    • bob1029 4y ago
      True, but then you have to plan ahead quite a bit. The other advantage of UUIDs is in completely decoupled environments that need to be able to share entities with each other. In this situation, serialization of activity is not a concern at all - we simply wish to prevent collisions of keys across the way.
    • remram 4y ago
      You can also hand out ranges. Instead of asking the system for one ID, you ask it for 1000 contiguous IDs, and you ask for another range when you run out. This will reduce the load on the ID-creating-system by 1000x (or your choice of number) and has the advantage that systems don't need to know how many peers they have. (beware of the herd though, if you don't persist it every system will come asking at startup)
  • cyclotron3k 4y ago
    Another great advantage of UUIDs is that it can help prevent you shooting yourself in the foot when you accidentally join the wrong tables. E.g. `DELETE FROM users WHERE id IN (SELECT id FROM user_orders);`
  • Too 4y ago
    This also enables treating inserts as upserts, allowing safe retries without risk of creating duplicates. Speaking of. Does pg have any mechanism to protect double creates while using auto increments? Is there any way to provide a request-id from the client?
• jdwyah 4y ago
  For times when you need distributed generation, I’ve worked with a system that I liked. Server kept track of a sequence. Clients pull out batches of 1000 or so and then use them up. When the client starts to get low on available numbers it fetches another batch. The ids generated are nice readable integers. Generally in sorted order, though not a guarantee, and you end up with gaps sometimes if a client doesn’t give out all its numbers before it’s restarted. Would anyone be interested in a super robust version of that as a service?
• geophile 4y ago
  What does "SORT terribly" mean? That there is no semantically useful ordering? Well of course not, that's not what they are designed for. If you want ordering by time, then include a time-based column and sort on it. Does it mean that sorting performance is bad on UUID columns? Why? And what does "index terribly" mean? You can index UUID columns just fine, so is it a performance concern? What is the concern?
  • perrygeo 4y ago
    Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. There is no "hot page" in the cache representing recent records; the index node you need for any given uuid is random and makes your internal index pages effectively uncacheable. The result is increased IO and cache thrashing.
    • avinassh 4y ago
      > Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`
      • yencabulator 4y ago
        Consider inserting 1000 rows. If those rows have keys that sort near each other, you're changing a few pages. If those rows have keys that are all over the keyspace, you're changing roughly 1000*k pages.
      • perrygeo 4y ago
        Rows are generally written to disk in the order they're inserted, whether you have a timestamp or not. It's just how database IO works. If your index happens to be in a different order (the position in the index is uncorrelated to the position of the row), you're gonna be thrashing pages. This isn't unique to UUID keys - text keys or anything other than current timestamp or serial id are going to have the same problem. It's really only an issue once your index size exceeds available shared RAM. If you can fit your table's index completely in memory, you probably won't notice. But once you exceed that threshold, performance starts falling off a cliff.
• orf 4y ago
  Not a fantastic post, which is a shame as Supabase is quite an interesting company. The answer is almost always "use biginteger identity", and almost never "use integer serial". UUIDs have a place but are often better suited in larger, distributed and more complex data stores than postgres. Using `xid` is such a poor choice I'm surprised it was even mentioned. The "key" thing to remember is you don't have to expose your primary key to the world. Use UUIDs or shortcodes or whatever for external representations. Use bigints internally. This will prevent a world of pain.
• jeffomatic 4y ago
  There's some skepticism in the comments around the recommendation for xid. I'm curious if anyone here is using it in production at scale, and can comment on the practical realities. I saw xid make the rounds about a year ago, and the promise of a pseudo-sortable 12-byte identifier that is "configuration free" struck me as a bit far-fetched. In particular, I wondered if the xid scheme gives you enough entropy to be confident you wouldn't run into collisions. UUIDv4 doesn't eat a full 16 bytes of entropy for nothing. For example, if you look at the machine ID component of xid, it does some version of random assignment (either pulling the first three bytes from /etc/machine-id, or from a hash of the hostname). 3 bytes is 16777216 values, i.e., with 600 hosts you have a 1% chance of running into a collision. Probably too close for comfort? There are settings where you can build some defense-in-depth against ID collisions, like a uniqueness constraint in your DB (effectively a centralized ticketing system). But there are many settings where that kind of thing wouldn't be practical. Off the top of my head, I'm thinking of monitoring-type applications like request or trace IDs.
• deepsun 4y ago
  One more note: uuid is not easily copy-pastable, due to dash `-` in it. I prefer to use it's raw bytes and encode with base32, which is copy-pastable.
  • antifa 4y ago
    If the goal is copy-pastable, why not base36 or base62?
    • deepsun 4y ago
      Base32 is already implemented in most stdlibs, and I'm lazy to write it myself :)
    • yencabulator 4y ago
      Because typing them or reading them back over the phone is much less human-friendly. So far https://philzimmermann.com/docs/human-oriented-base-32-encoding.txt https://philzimmermann.com/docs/human-oriented-base-32-encod... has the best trade-offs I've ever seen. (Though for encoding numbers, where small numbers deserve a shorter representation, Base58 can be nice too.. z-base-32's alphabet is still more user-friendly. https://github.com/tv42/base58 https://github.com/tv42/base58 )
• deepsun 4y ago
  As for DB ids I prefer to have some integer internal DB ID, only for foreign keys, and additionally something like UUID for client-side.
• je42 4y ago
  One note regarding uuid. It doesn't need to imply it is random. That's specific v4. V5 are predictable uuids. That combine a ns uuid and a string, via sha1 based one way mapping resulting in a uuid.
• waspight 4y ago
  I only use uuid as PK in Postgres. So many benefits, it is easy to migrate data between environments, client can generate its own ids etc. I don’t know about performance but I think in most cases that is not a big concern anyway.
• atombender 4y ago
  One major aspect of primary keys not mentioned in the article is the performance of queries. I did some benchmark recently, where serial (32 bits) was significantly faster than the native Postgres UUID type. The worst scheme you can use is a string; if you encode something like ksuid as a plain string rather than an efficiently packed byte sequence, query performance becomes significantly worse. I didn't benchmark sorting, but I assume it's similarly impacted.