10 ms·

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator op

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, especially a line-of-business application. Use UUIDv7 only as an identifier for data that is inherently timecoded, otherwise it leaks information (even if timeshifted). Do not use hashids - they have no cryptographic qualities and are less friendly to everyday humans than the integers they represent; you may as well just use the sequence ID. As for the encoding, do not use base64 or other hyphenated alphabets, nor any identifier scheme that can produce a leading ‘0’ (zero) or ‘+’ (plus) when encoded (for the day your stuff is pasted via Excel). Generally, the principles of separation of concerns and mechanical sympathy should be top of mind when designing a lasting and purposeful database schema. Finally, since folks often say “I like stripe’s typed random IDs” in these kind of threads: Stripe are lying when they say their IDs are random. They have some random parts but when analyzed in sets, a large chunk of the binary layout is clearly metadata, including embedded timestamps, shard and reference keys, and versioning, in varying combinations depending on the service. I estimate they typically have 48-64 bits of randomness. That’s still plenty for most systems; you can do the same. Personally I am very fond of base58-encoded AES-encrypted bigserial+HMAC locators with a leading type prefix and a trailing metadata digit, and you can in a pinch even do this inside the database with plv8.

vbezhenar 2y ago
IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.
- inopinatus 2y ago
  Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.
  - vbezhenar 2y ago
    What architecture? Both amd64 and ARM64 can work with 32-bit integers just fine.
    - DEADMINCE 2y ago
      Not optimally.
      - vbezhenar 2y ago
        It is not optimal to use 8-byte integers instead of 4-bytes. CPU works just as fast with both, however your CPU cache is limited and you'll put more 4-byte integers into your L1. I don't really understand what you want to convey. CPU is very fast with any kind of integer size. There's no performance penalty to use 1-byte integer compared to 8-byte integer. And there's performance penalty when your L1 or L2 or L3 cache is busted and you need to go to the next memory layer.
        marcosdumay 2y ago
        > CPU works just as fast with both They don't. CPUs have as many issues with data alignment as with cache sizes.
        adrian_b 2y ago
        1-byte integers or any other integers smaller than 8 bytes (64 bits) save space only when they are parts of arrays or structures. As individual variables, they take exactly the same space as 64-bit integers, both in registers and in memory (i.e. in the stack used for automatic variables or in the heap used for dynamically-allocated variables), because of the alignment requirements. Therefore it never makes sense to use other integers than 64-bit, except as members of aggregate data types. A database table is an aggregate data type, so the use of small integers in some columns may be justified, but such types shall not be used for variables in the programs that process data from the tables, where misuse of the small integers may cause overflows and there is no advantage from using smaller integer sizes.
- riku_iki 2y ago
  there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..
  - sgarland 2y ago
    It’s double the size. 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB. That is quite a difference for an index, not to mention the table overhead. You’re going to know well in advance before hitting this limit becomes an issue, and you’ll have plenty of time to either take a bit of downtime and do a column conversion, or do an online migration.
    - riku_iki 2y ago
      > 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB I didn't get your point. When it is 2^31, you definitely need bigint already. When it is much smaller, it will be much smaller overhead. Also, per docs (https://www.postgresql.org/docs/current/storage-page-layout.html https://www.postgresql.org/docs/current/storage-page-layout....), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvement.
      - sgarland 2y ago
        Not all tables need even the capacity for 2^30 rows, much less 2^31, or 2^63. If you have a reference table with things like timezone information, color schemes, etc. and are using anything other than a SMALLINT (2^15), you're probably wasting space. As to the maximal 8.6 GB mentioned, that's not nothing, _especially_ for RAM. Disk is cheap, but RAM isn't. If you have a smaller instance – say, an r6i.xlarge on AWS (4 vCPU, 32 GB RAM), that's 1/4 of the entire DB's memory.
        riku_iki 2y ago
        so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?
- njtransit 2y ago
  Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!
  - incrudible 2y ago
    If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.
    - afavour 2y ago
      Conversely: if using bigserial for a primary key introduces crippling performance problems to your system, maybe that is a problem you need to address either way.
      - DEADMINCE 2y ago
        Conversely? Who mentioned there being a performance impact?
        afavour 2y ago
        It’s the only reason I could think of for why you wouldn’t use a bigserial column. Maybe there is another?
        lazide 2y ago
        Information leakage - it leaks how many records have been created [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem]
        afavour 2y ago
- rangerelf 2y ago
  I read your post and hear echoes of "Who would ever need more than 2 digits for the year in this timestamp column?" Never again.
  - vbezhenar 2y ago
    Using 2 digits for year is as wrong as using 8 bytes for year.
    - riku_iki 2y ago
      scale of damage is very different, so "as" is up to discussion.
    - lazide 2y ago
      Eh, using 2 digits for year is a lot ‘more wrong’ than 8 bytes. Since all (current) known use cases can fit in 8 bytes for the conceivable lifespan of human civilization. 2 digit years don’t even support the use case of ‘store the current year’ fully.
    - rangerelf 2y ago
      Don't wake me up when year 9,223,372,036,854,775,807 rolls by please.
- deleted 2y ago
  [deleted]
- paulddraper 2y ago
  The problems from unnecessary bigserial is nothing compared to the pain of insufficient serial. This is especially true for PostgreSQL which increments sequences for upserts (even if no records are inserted). That's how I've hit 32-bit limits on tables that had only a couple million rows. --- I would only use 32-bit for very selective items that is used in a lot of FKs, like a tenant ID.
  - vbezhenar 2y ago
    I didn't know about upserts incrementing sequence. That makes sense. I don't think I ever used upsert, but that's a good reason to use bigint id (or at least make update count a factor to consider), thanks for pointing out.
- mixmastamyk 2y ago
  Postgres doesn't support unsigned ints last I looked, so it's actually a ~2 billion limit. Secondly, it's not XXXk rows currently-you have to consider the complete lifetime of the table. When rows are deleted/created and how often. So what you've said is true, but the set of appropriateness for smallint is a much smaller than expected.
  - blackenedgem 2y ago
    You can start the sequence at -2b, or wrap it around when it gets close to the signed limit. Hopefully you haven't depended on it not wrapping around by that point. For queue tables you can even use `CYCLE` to do that automatically.
- orf 2y ago
  This is simply bad advice. There are very, very few cases where you shouldn’t use a bigserial/bigint as a primary key. Perhaps the only case is where it is impossible for the rows to grow beyond a specific bound: an “AWS region” table or a “countries” table. These are almost always lookup tables of some kind, and the domain makes it impossible for it to grow past a certain bound. Outside of that, put absolutely no thought into it and pick a bigint/bigserial. Trying to be clever about this ignores the fact that tables and their usage change, and what you believe now will not be correct later. And when you realise you’ve been too clever, it’s 3am on a Sunday, your production database has exploded and nothing works. You now need to rewrite 30 tables and their foreign keys ASAP with upper management breathing down your neck.
- Merad 2y ago
  An int PK does not give you 4 billion rows. For example, in Postgres every time an insert transaction has to rollback, the id's that would have been used by that insert are discarded and gone forever. Likewise people often don't realize that Postgres' very convenient upsert syntax (insert on conflict do update) will consume id's from the sequence _every time it runs_. If you do an upsert with 100 rows, it will peel off 100 new id's just in case they're needed... if they aren't used, they're discarded. This can chew through 4 billion id's really, really fast. Personally I would only use an int PK in a table where you know the bounds of the data with very high confidence. Like, at my last job the software involved working with our client's physical store locations. If we managed to capture the entire North American market for the type of business that we worked with, we'd be looking at about 50k stores.
GGO 2y ago
I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.
- dajtxx 2y ago
  The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.
  - inopinatus 2y ago
    It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level. Just goes to show you can’t inadvertently disclose anything these days.
- whynotmaybe 2y ago
  As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the time information.
  - TeeWEE 2y ago
    Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed data creation.
nextaccountic 2y ago
Why string encoded column? Is it just to make the table bigger? Why not just use the UUID type??
solidsnack9000 2y ago
One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?
- codetrotter 2y ago
  > not form ridiculous words Depends on what you mean by ridiculous. For example https://sqids.org/ https://sqids.org/ ensures that there are no profanities in the generated ids. And it allows you to add additional words that you want to avoid.
  - solidsnack9000 2y ago
    It does but it allows common names, the name of the deity, common words, &c. I suspect you have to do something like an earlier poster suggested -- strip out all the vowels to start with...
- dmurray 2y ago
  Take all the Roman alphabet apart from the vowels - 21 characters and length 6 gives you 100 million possibilities which is plenty for most applications. You can still get vaguely offensive sequences like FKNNGR or BLKCNT, but at some point you have to put this down not to your software being offensive or hateful but to humans finding patterns in randomness.
  - thwarted 2y ago
    Use Crockford encoding to reduce this. It has readability and dictation considerations too which makes it ideal for things humans will interface with.
  - solidsnack9000 2y ago
    Removing Y turns out to be important, as well...
brycelarkin 2y ago
For postgres, you want to use “bigint generated always as identity” instead of bigserial.
- westurner 2y ago
  Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type? Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#DATATYPE-UUID https://www.postgresql.org/docs/current/datatype-uuid.html#D... django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uuidfield https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... : > class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and MariaDB 10.7+, this stores in a uuid datatype, otherwise in a char(32) > [...] Lookups on PostgreSQL and MariaDB 10.7+: Using iexact, contains, icontains, startswith, istartswith, endswith, or iendswith lookups on PostgreSQL don’t work for values without hyphens, because PostgreSQL and MariaDB 10.7+ store them in a hyphenated uuid datatype type. From the sqlalachemy.types.Uuid docs: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Uuid https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqla... : > Represent a database agnostic UUID datatype. > For backends that have no “native” UUID datatype, the value will make use of CHAR(32) and store the UUID as a 32-character alphanumeric hex string. > For backends which are known to support UUID directly or a similar uuid-storing datatype such as SQL Server’s UNIQUEIDENTIFIER, a “native” mode enabled by default allows these types will be used on those backends. > In its default mode of use, the Uuid datatype expects Python uuid objects, from the Python uuid module From the docs for the uuid Python module: https://docs.python.org/3/library/uuid.html https://docs.python.org/3/library/uuid.html : > class uuid.SafeUUID: Added in version 3.7. > safe: The UUID was generated by the platform in a multiprocessing-safe way And there's not yet a uuid.uuid7() in the uuid Python module. UUIDv7 leaks timing information ( https://news.ycombinator.com/item?id=40886496 https://news.ycombinator.com/item?id=40886496 ); which is ironic because uuids are usually used to avoid the "guess an autoincrement integer key" issue
  - avg_dev 2y ago
    Just noting, the commenter you replied to said: > use “bigint generated always as identity” instead of bigserial. The commenter you are replying to was not saying anything about whether to use UUIDs or not; they just said "if you are going to use bigserial, you should use bigint generated always as identity instead".
saaspirant 2y ago
Naive question. Above comment suggests using bigserial as internal identifier and uuid as public facing ID. Now let's say there's a user table and post table. Both will have only uuid available in the APIs. So every time API requests a post of the user or user of the post, we will find the the relevant row using uuid right? Since uuid will be sent by the public facing APIs? How would bigserial be used here? I don't know much databases in general BTW.. Edit: Can I use uuidv4 as PK since it doesn't leak timestamp data? Why bigserial + uuid7 is better?
- jpollock 2y ago
  Each object has an external key and an internal key. This separation allows you to migrate to other layouts, technologies, etc. without breaking your customer's links or records. Internally, your database looks like: User ID - uint128 external_id - UUID (of some sort) name - string Post ID - uint128 UserId - uint128 (User.ID) external_id - UUID ... Then you have secondary indices on the external_id columns in both tables, and the UserId column on Post. You can then join from one to the other using the ID column. e.g. SELECT count(*) FROM Post JOIN User ON User.ID = Post.UserID WHERE User.external_id = <%USERID_FROM_REQUEST%>; // Don't forget to bind USER_ID_FROM_REQUEST instead of // concating the string, no Johny-Tables here! There should be significant performance benefits from using int's for the joining keys (at least compared to strings), but my experience might be old.
  - TeeWEE 2y ago
    So it can’t use the internal id index, result: slow lookups for external ids.
    - bfung 2y ago
      > secondary indices on the external_id columns in both tables pick your poison, slower lookup or more disk usage
      - masklinn 2y ago
TeeWEE 2y ago
If you have distributed data creation. (Creating data on the client). And a CRDT style mechanism for syncing, then you can’t use bigserial because of the simple fact that it is sequential. The best solution here is uuidv7. Since you can generate these at the client even when offline.
- arp242 2y ago
  That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.
  - azinman2 2y ago
    But if you’re distributed or offline incrementing by an arbitrary amount can still create collisions unless you’re willing to increment by very very large amounts at random, in which case you’ve effectively reinvented uuid
    - arp242 2y ago
      No, every server will have its own series: Server 1: [1 4 7] Server 2: [2 5 8] Server 3: [3 6 9] Or whatever is appropriate. You can even add/change this later on. Again, I'm not saying it's necessarily the best solution, I'm just saying it's possible. I'm not really interested in a long discussion about uuid vs. serial because it's been done to death a million times. Previous poster said it's impossible and that's just flat-out wrong.
darby_nine 2y ago
You really have to understand the usage patterns of a program to give a recommendation. Anyone who doesn't is just a grifter. I don't trust anyone who gives recommendations sight unseen.
- bruce511 2y ago
  100% this. Context is everything, and advice without understanding context should be suspect at best. That said, I've found that contexts can change. Personally I started with the database being local (send single). It the migrated to remote (web), after that to distributed and from there to mobile. As contexts gave changed I've found we needed to switch to UUID (and the switch was a pain.) A lot of our problems would simply not exist if we'd used uuid from the start. So now my "default advice" is uuid, unless there's a -specific- reason not to. For me, performance is not that reason (your database outlives multiple generations of hardware, and faster hardware will dwarf optimisation-via-data-type.) And don't get me started on natural versus surrogate identifiers- that ship sailed decades ago.)
necovek 2y ago
I've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too. Though I would wonder why go with UUIDs in that case at all?
- mjevans 2y ago
  Offhand, I remember _reading_ about that but haven't ever used it in practice so please test and confirm... # Postgres can Index Function Results (including what you described) # Postgres does not magically know to USE those results. To use them you must E.G. JOIN (probably works for WHERE?) or ORDER or LIMIT by the same parameter that went into the Index (which is how it's matched up). Generally, the Primary Key should either be 'small' (BigInt can count as such) and only used internally or be something strongly relevant to how the records in that table can be limited for most (or the most) queries.
  - necovek 2y ago
    Yeah, it works for "partial indexes" very well. There are limits to what gets matched, for sure. In the old times, if you created a "partial index" with a condition of "field_foo IS TRUE" it would not match queries asking for "field_foo = TRUE" (it did actual text matching). Probably some of that is improved today.
olalonde 2y ago
> They have some random parts but when analyzed in sets, a large chunk of the binary layout is clearly metadata, including embedded timestamps, shard and reference keys, and versioning, in varying combinations depending on the service. Could you share this analysis? Seems interesting.
pin24 2y ago
Just out of curiosity, why string-encoded UUIDs? A native/binary UUID column type should not take more than 16 bytes, whereas its hexadecimal string representation requires 36 bytes (if we assume 1 byte per character). That would result in an index size more than twice as big as required.
rattray 2y ago
> Stripe are lying when they say their IDs are random Where does Stripe make that claim? I think most of the value of Stripe-style IDs is in their other properties, like the information they bear, readability, copy-pasteability (which uuids lack - double-click to select often stops at hyphens), etc.

vbezhenar 2y ago

IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.

inopinatus 2y ago
Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.
- vbezhenar 2y ago
  What architecture? Both amd64 and ARM64 can work with 32-bit integers just fine.
  - DEADMINCE 2y ago
    Not optimally.
    - vbezhenar 2y ago
      It is not optimal to use 8-byte integers instead of 4-bytes. CPU works just as fast with both, however your CPU cache is limited and you'll put more 4-byte integers into your L1. I don't really understand what you want to convey. CPU is very fast with any kind of integer size. There's no performance penalty to use 1-byte integer compared to 8-byte integer. And there's performance penalty when your L1 or L2 or L3 cache is busted and you need to go to the next memory layer.
      - marcosdumay 2y ago
        > CPU works just as fast with both They don't. CPUs have as many issues with data alignment as with cache sizes.
      - adrian_b 2y ago
        1-byte integers or any other integers smaller than 8 bytes (64 bits) save space only when they are parts of arrays or structures. As individual variables, they take exactly the same space as 64-bit integers, both in registers and in memory (i.e. in the stack used for automatic variables or in the heap used for dynamically-allocated variables), because of the alignment requirements. Therefore it never makes sense to use other integers than 64-bit, except as members of aggregate data types. A database table is an aggregate data type, so the use of small integers in some columns may be justified, but such types shall not be used for variables in the programs that process data from the tables, where misuse of the small integers may cause overflows and there is no advantage from using smaller integer sizes.
riku_iki 2y ago
there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..
- sgarland 2y ago
  It’s double the size. 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB. That is quite a difference for an index, not to mention the table overhead. You’re going to know well in advance before hitting this limit becomes an issue, and you’ll have plenty of time to either take a bit of downtime and do a column conversion, or do an online migration.
  - riku_iki 2y ago
    > 4 bytes * 2^31 (because Postgres doesn’t allow have unsigned ints, unlike MySQL) is 8.6 GB I didn't get your point. When it is 2^31, you definitely need bigint already. When it is much smaller, it will be much smaller overhead. Also, per docs (https://www.postgresql.org/docs/current/storage-page-layout.html https://www.postgresql.org/docs/current/storage-page-layout....), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvement.
    - sgarland 2y ago
      Not all tables need even the capacity for 2^30 rows, much less 2^31, or 2^63. If you have a reference table with things like timezone information, color schemes, etc. and are using anything other than a SMALLINT (2^15), you're probably wasting space. As to the maximal 8.6 GB mentioned, that's not nothing, _especially_ for RAM. Disk is cheap, but RAM isn't. If you have a smaller instance – say, an r6i.xlarge on AWS (4 vCPU, 32 GB RAM), that's 1/4 of the entire DB's memory.
      - riku_iki 2y ago
        so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?
njtransit 2y ago
Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!
- incrudible 2y ago
  If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.
  - afavour 2y ago
    Conversely: if using bigserial for a primary key introduces crippling performance problems to your system, maybe that is a problem you need to address either way.
    - DEADMINCE 2y ago
      Conversely? Who mentioned there being a performance impact?
      - afavour 2y ago
        It’s the only reason I could think of for why you wouldn’t use a bigserial column. Maybe there is another?
        lazide 2y ago
        Information leakage - it leaks how many records have been created [https://en.m.wikipedia.org/wiki/German_tank_problem https://en.m.wikipedia.org/wiki/German_tank_problem]
        afavour 2y ago
rangerelf 2y ago
I read your post and hear echoes of "Who would ever need more than 2 digits for the year in this timestamp column?" Never again.
- vbezhenar 2y ago
  Using 2 digits for year is as wrong as using 8 bytes for year.
  - riku_iki 2y ago
    scale of damage is very different, so "as" is up to discussion.
  - lazide 2y ago
    Eh, using 2 digits for year is a lot ‘more wrong’ than 8 bytes. Since all (current) known use cases can fit in 8 bytes for the conceivable lifespan of human civilization. 2 digit years don’t even support the use case of ‘store the current year’ fully.
  - rangerelf 2y ago
    Don't wake me up when year 9,223,372,036,854,775,807 rolls by please.
deleted 2y ago
[deleted]
paulddraper 2y ago
The problems from unnecessary bigserial is nothing compared to the pain of insufficient serial. This is especially true for PostgreSQL which increments sequences for upserts (even if no records are inserted). That's how I've hit 32-bit limits on tables that had only a couple million rows. --- I would only use 32-bit for very selective items that is used in a lot of FKs, like a tenant ID.
- vbezhenar 2y ago
  I didn't know about upserts incrementing sequence. That makes sense. I don't think I ever used upsert, but that's a good reason to use bigint id (or at least make update count a factor to consider), thanks for pointing out.
mixmastamyk 2y ago
Postgres doesn't support unsigned ints last I looked, so it's actually a ~2 billion limit. Secondly, it's not XXXk rows currently-you have to consider the complete lifetime of the table. When rows are deleted/created and how often. So what you've said is true, but the set of appropriateness for smallint is a much smaller than expected.
- blackenedgem 2y ago
  You can start the sequence at -2b, or wrap it around when it gets close to the signed limit. Hopefully you haven't depended on it not wrapping around by that point. For queue tables you can even use `CYCLE` to do that automatically.
orf 2y ago
This is simply bad advice. There are very, very few cases where you shouldn’t use a bigserial/bigint as a primary key. Perhaps the only case is where it is impossible for the rows to grow beyond a specific bound: an “AWS region” table or a “countries” table. These are almost always lookup tables of some kind, and the domain makes it impossible for it to grow past a certain bound. Outside of that, put absolutely no thought into it and pick a bigint/bigserial. Trying to be clever about this ignores the fact that tables and their usage change, and what you believe now will not be correct later. And when you realise you’ve been too clever, it’s 3am on a Sunday, your production database has exploded and nothing works. You now need to rewrite 30 tables and their foreign keys ASAP with upper management breathing down your neck.
Merad 2y ago
An int PK does not give you 4 billion rows. For example, in Postgres every time an insert transaction has to rollback, the id's that would have been used by that insert are discarded and gone forever. Likewise people often don't realize that Postgres' very convenient upsert syntax (insert on conflict do update) will consume id's from the sequence _every time it runs_. If you do an upsert with 100 rows, it will peel off 100 new id's just in case they're needed... if they aren't used, they're discarded. This can chew through 4 billion id's really, really fast. Personally I would only use an int PK in a table where you know the bounds of the data with very high confidence. Like, at my last job the software involved working with our client's physical store locations. If we managed to capture the entire North American market for the type of business that we worked with, we'd be looking at about 50k stores.

GGO 2y ago

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.

dajtxx 2y ago
The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.
- inopinatus 2y ago
  It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level. Just goes to show you can’t inadvertently disclose anything these days.
whynotmaybe 2y ago
As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the time information.
- TeeWEE 2y ago
  Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack… If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below) UUIDv7 has a random part, can be created distributedly, and indexes well. It’s the best choice for modern application that support distributed data creation.

nextaccountic 2y ago

Why string encoded column? Is it just to make the table bigger? Why not just use the UUID type??

solidsnack9000 2y ago

One challenge with PNR is actually restricting the alphabet appropriately. They sure are easy to say aloud -- just five or six letters in many cases -- but how do you ensure you have (a) enough letters to get a reasonable bitwidth and (b) not form ridiculous words?

codetrotter 2y ago
> not form ridiculous words Depends on what you mean by ridiculous. For example https://sqids.org/ https://sqids.org/ ensures that there are no profanities in the generated ids. And it allows you to add additional words that you want to avoid.
- solidsnack9000 2y ago
  It does but it allows common names, the name of the deity, common words, &c. I suspect you have to do something like an earlier poster suggested -- strip out all the vowels to start with...
dmurray 2y ago
Take all the Roman alphabet apart from the vowels - 21 characters and length 6 gives you 100 million possibilities which is plenty for most applications. You can still get vaguely offensive sequences like FKNNGR or BLKCNT, but at some point you have to put this down not to your software being offensive or hateful but to humans finding patterns in randomness.
- thwarted 2y ago
  Use Crockford encoding to reduce this. It has readability and dictation considerations too which makes it ideal for things humans will interface with.
- solidsnack9000 2y ago
  Removing Y turns out to be important, as well...

brycelarkin 2y ago

For postgres, you want to use “bigint generated always as identity” instead of bigserial.

westurner 2y ago
Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type? Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#DATATYPE-UUID https://www.postgresql.org/docs/current/datatype-uuid.html#D... django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uuidfield https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... : > class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and MariaDB 10.7+, this stores in a uuid datatype, otherwise in a char(32) > [...] Lookups on PostgreSQL and MariaDB 10.7+: Using iexact, contains, icontains, startswith, istartswith, endswith, or iendswith lookups on PostgreSQL don’t work for values without hyphens, because PostgreSQL and MariaDB 10.7+ store them in a hyphenated uuid datatype type. From the sqlalachemy.types.Uuid docs: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.Uuid https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqla... : > Represent a database agnostic UUID datatype. > For backends that have no “native” UUID datatype, the value will make use of CHAR(32) and store the UUID as a 32-character alphanumeric hex string. > For backends which are known to support UUID directly or a similar uuid-storing datatype such as SQL Server’s UNIQUEIDENTIFIER, a “native” mode enabled by default allows these types will be used on those backends. > In its default mode of use, the Uuid datatype expects Python uuid objects, from the Python uuid module From the docs for the uuid Python module: https://docs.python.org/3/library/uuid.html https://docs.python.org/3/library/uuid.html : > class uuid.SafeUUID: Added in version 3.7. > safe: The UUID was generated by the platform in a multiprocessing-safe way And there's not yet a uuid.uuid7() in the uuid Python module. UUIDv7 leaks timing information ( https://news.ycombinator.com/item?id=40886496 https://news.ycombinator.com/item?id=40886496 ); which is ironic because uuids are usually used to avoid the "guess an autoincrement integer key" issue
- avg_dev 2y ago
  Just noting, the commenter you replied to said: > use “bigint generated always as identity” instead of bigserial. The commenter you are replying to was not saying anything about whether to use UUIDs or not; they just said "if you are going to use bigserial, you should use bigint generated always as identity instead".

saaspirant 2y ago

Naive question. Above comment suggests using bigserial as internal identifier and uuid as public facing ID. Now let's say there's a user table and post table. Both will have only uuid available in the APIs. So every time API requests a post of the user or user of the post, we will find the the relevant row using uuid right? Since uuid will be sent by the public facing APIs? How would bigserial be used here? I don't know much databases in general BTW.. Edit: Can I use uuidv4 as PK since it doesn't leak timestamp data? Why bigserial + uuid7 is better?

jpollock 2y ago
Each object has an external key and an internal key. This separation allows you to migrate to other layouts, technologies, etc. without breaking your customer's links or records. Internally, your database looks like: User ID - uint128 external_id - UUID (of some sort) name - string Post ID - uint128 UserId - uint128 (User.ID) external_id - UUID ... Then you have secondary indices on the external_id columns in both tables, and the UserId column on Post. You can then join from one to the other using the ID column. e.g. SELECT count(*) FROM Post JOIN User ON User.ID = Post.UserID WHERE User.external_id = <%USERID_FROM_REQUEST%>; // Don't forget to bind USER_ID_FROM_REQUEST instead of // concating the string, no Johny-Tables here! There should be significant performance benefits from using int's for the joining keys (at least compared to strings), but my experience might be old.
- TeeWEE 2y ago
  So it can’t use the internal id index, result: slow lookups for external ids.
  - bfung 2y ago
    > secondary indices on the external_id columns in both tables pick your poison, slower lookup or more disk usage
    - masklinn 2y ago

TeeWEE 2y ago

If you have distributed data creation. (Creating data on the client). And a CRDT style mechanism for syncing, then you can’t use bigserial because of the simple fact that it is sequential. The best solution here is uuidv7. Since you can generate these at the client even when offline.

arp242 2y ago
That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.
- azinman2 2y ago
  But if you’re distributed or offline incrementing by an arbitrary amount can still create collisions unless you’re willing to increment by very very large amounts at random, in which case you’ve effectively reinvented uuid
  - arp242 2y ago
    No, every server will have its own series: Server 1: [1 4 7] Server 2: [2 5 8] Server 3: [3 6 9] Or whatever is appropriate. You can even add/change this later on. Again, I'm not saying it's necessarily the best solution, I'm just saying it's possible. I'm not really interested in a long discussion about uuid vs. serial because it's been done to death a million times. Previous poster said it's impossible and that's just flat-out wrong.

darby_nine 2y ago

You really have to understand the usage patterns of a program to give a recommendation. Anyone who doesn't is just a grifter. I don't trust anyone who gives recommendations sight unseen.

bruce511 2y ago
100% this. Context is everything, and advice without understanding context should be suspect at best. That said, I've found that contexts can change. Personally I started with the database being local (send single). It the migrated to remote (web), after that to distributed and from there to mobile. As contexts gave changed I've found we needed to switch to UUID (and the switch was a pain.) A lot of our problems would simply not exist if we'd used uuid from the start. So now my "default advice" is uuid, unless there's a -specific- reason not to. For me, performance is not that reason (your database outlives multiple generations of hardware, and faster hardware will dwarf optimisation-via-data-type.) And don't get me started on natural versus surrogate identifiers- that ship sailed decades ago.)

necovek 2y ago

I've never played enough with UUIDs in Postgres, but I wonder if you could publicly expose only the random bits (i.e. the second half if an UUIDv7) and have another non-b-tree index on SUBSTR(id, 16) for quick lookups based on that value. Similar is done for "short commit hashes" too. Though I would wonder why go with UUIDs in that case at all?

mjevans 2y ago
Offhand, I remember _reading_ about that but haven't ever used it in practice so please test and confirm... # Postgres can Index Function Results (including what you described) # Postgres does not magically know to USE those results. To use them you must E.G. JOIN (probably works for WHERE?) or ORDER or LIMIT by the same parameter that went into the Index (which is how it's matched up). Generally, the Primary Key should either be 'small' (BigInt can count as such) and only used internally or be something strongly relevant to how the records in that table can be limited for most (or the most) queries.
- necovek 2y ago
  Yeah, it works for "partial indexes" very well. There are limits to what gets matched, for sure. In the old times, if you created a "partial index" with a condition of "field_foo IS TRUE" it would not match queries asking for "field_foo = TRUE" (it did actual text matching). Probably some of that is improved today.

olalonde 2y ago

> They have some random parts but when analyzed in sets, a large chunk of the binary layout is clearly metadata, including embedded timestamps, shard and reference keys, and versioning, in varying combinations depending on the service. Could you share this analysis? Seems interesting.

pin24 2y ago

Just out of curiosity, why string-encoded UUIDs? A native/binary UUID column type should not take more than 16 bytes, whereas its hexadecimal string representation requires 36 bytes (if we assume 1 byte per character). That would result in an index size more than twice as big as required.

rattray 2y ago

> Stripe are lying when they say their IDs are random Where does Stripe make that claim? I think most of the value of Stripe-style IDs is in their other properties, like the information they bear, readability, copy-pasteability (which uuids lack - double-click to select often stops at hyphens), etc.