> 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....), each postgres row has 23 bytes overhead, so your transition from 8->4 bytes will bring marginal improvement.

With multiple tables and the same IDs being references more than once, this kind of stuff can really add up.

For example I have a table that has about a billion rows and uses bigserial, but that table references about 6 other much smaller tables that use serial. I'm saving 48 bytes per row, or 90GB in total. It's a fairly significant save, and that's just on this one table: I have a bunch of tables like this. If I had bigint'd everything my disk space would be roughly double. And all of that is not even considering the extra index size and memory requirements.

Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems. There will never be billions of countries. I can probably get away with smallint for some of those, but I felt the extra headroom for that was worth the extra bytes.

This is why "bigint by default" is just bad advice IMHO. You will always need to think about it. Of course you shouldn't prematurely optimize integer sizes, but it's also not true that it doesn't matter, because it does. "Better safe than sorry" also applies in the other direction: "zomg we're wasting tons of disk space and spend much more money on servers than we need to" just because someone unthinkingly applied some stupid dogma they read on HN and didn't spend 4 seconds thinking about it.

> I'm saving 48 bytes per row

you saving 24 bytes per row: downsizing 6 columns from 8 bytes to 4, which is fraction of your table size. If your system is sensitive to such change, you likely should optimize something else.

> Using bigint here would add absolutely nothing. I'm never going to have billions of users. I'm never going to have billions of different operating systems.

I think you cherry picked some fictional examples which you won't combine in one table, and your real entities are different. It adds process safety: you don't need to play mind quizzes to estimate how system can grow/change in a N years.

> cherry picked some fictional example

What an incredibly rude and dismissive accusation. Here's my table: https://github.com/arp242/goatcounter/blob/master/db/schema.... – number of IDs is actually 7, not 6.

I can give a lot more details and context on all of that and why it works the way it works and the savings are certainly not insignificant and theoretical, but save me real money in server costs every month. But I have little interest in talking to you further since you're just going to insult everyone who tries to inject the slightest nuance into "always use bigserial"-extremism. So good day to you.

I’d just like to congratulate you on perhaps the nicest “fuck off” I’ve ever read.

Unrelated, I quite enjoyed reading your blog posts. Cheers!

No, my point is you accused me of lying at the drop of a hat for no good reason, and that this is demonstrably not true. Don't try and spinelessly slime your way out of that. And no, they I won't run out of IDs for them. "Potentially" anything can happen, but it won't happen here. After five year the furthest sequence is 0.86% on its way of being full.

> If your system is sensitive to such change, you likely should optimize something else.

This isn’t even optimization, it’s just understanding your tools and needs. It’s akin to profiling your app under load, seeing that at worst it needs 1 GiB of RAM allocated, and then giving it 8 EiB just in case.

By all means, if you can reasonably predict that a given table will near or pass 2^31 rows in the near future, just set it to BIGINT now. But as has been pointed out, most apps are never, ever going to have close to 2 billion users, or for that matter 2 billion anything. The only thing I can think of that might reasonably run up against that would be invoices for large scales, or object metadata for something like Notion.

> But as has been pointed out, most apps are never, ever going to have close to 2 billion users, or for that matter 2 billion anything. T

all computers will be fine with 640kb of ram

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.

so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?

It’s orthogonal, and also technically incorrect – the row overhead itself is indeed 23 bytes (less NULL bitmap), but it gets aligned to 24 bytes. Run pg_column_size(ROW()) if you’d like to check.

The fact that this overhead exists has nothing to do with the 8.6 GB of wasted space on the INTs.

> It’s orthogonal

I disagree its orthogonal and explained why. I guess lets agree on disagree.

Is that an innate property or a current implementation detail?

It’s the current implementation as of PG 8.2, I think. It’s [0] been there for a long time, in any case, and is unlikely to change in the near future.

[0]: https://www.postgresql.org/docs/current/storage-page-layout....

price for various features, like ACID.

Postgres pads tuples to 8 bytes alignment so an indexed single-column int takes the same space as an indexed bigint. That's the usual case for indexed foreign keys.

Differences can appear in multicolumn indexes because two ints takes 8 bytes while two bigints takes 16, however the right layout of columns for an index is not always the layout that minimizes padding.

Postgres doesn't necessarily pad to 8 bytes; it depends on the next column's type. EDB has a good writeup on this (https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/), but also here's a small example:

  CREATE TABLE foo
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid INT NOT NULL);

  CREATE TABLE bar
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  CREATE TABLE baz
    (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  -- fill each with 1,000,000 rows, then index

  CREATE INDEX {foo,bar,baz}_iid_idx ON {foo,bar,baz}(iid);

  SELECT table_name,
         pg_size_pretty(pg_table_size(quote_ident(table_name))) "table_size",
         pg_size_pretty(pg_indexes_size(quote_ident(table_name))) "index_size"
  FROM information_schema.tables
  WHERE table_schema = 'public';

   table_name | table_size | index_size
  ------------+------------+------------
   foo        | 35 MB      | 43 MB
   bar        | 42 MB      | 43 MB
   baz        | 42 MB      | 43 MB

`foo` has an INT followed by an INT, and its table size is 35 MB. `bar` has an INT followed by a BIGINT, and its table size is 43 MB; this is the same size for `baz`, despite `baz` being a BIGINT followed by a BIGINT.

You seem to think you're disagreeing with me but afaict you're just demonstrating my point, unless your point is just about how (int, int) will get packed. That's what I meant about the column order of indexes. If you have two ints and a bigint, but you need to index it like (int, bigint, int), then you aren't gaining anything there either.

As your example shows, there is no benefit in index size (e.g for supporting FKs) in going from int to bigint for a single key. You end up with the same index size no matter what, not twice the size which was what I took your original post to mean.

I misunderstood your post, I think. I re-ran some experiments with a single-column index on SMALLINT, INT, and BIGINT. I'm still not sure why, but there is a significant difference in index size on SMALLINT (7.3 MB for 1E6 rows) vs. INT and BIGINT (21 MB for each), while the latter two are the exact same size. I could get them to differ if I ran large UPDATEs on the table, but that was it.