5 ms·

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 eve

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.

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.