News for hackers. Stuff that matters.

A comment on PostgreSQL and UUID as Primary Key. Parent comment

Re:PostgreSQL and UUID as Primary Key by sgarland on Friday July 05, 2024 @11:01PM (#40887893)
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.

[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by riku_iki on Friday July 05, 2024 @11:09PM (#40887924)
so, what about my argument that PG has 23 bytes overhead per row and your space win is very small compared to that overhead?
[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by nirvdrum on Saturday July 06, 2024 @02:06AM (#40888458)
Is that an innate property or a current implementation detail?
[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by riku_iki on Saturday July 06, 2024 @02:11AM (#40888472)
price for various features, like ACID.
[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by sgarland on Saturday July 06, 2024 @09:51AM (#40890471)
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....

[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by sgarland on Saturday July 06, 2024 @09:48AM (#40890454)
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.

[ Parent | on HN ]
Re:PostgreSQL and UUID as Primary Key by riku_iki on Saturday July 06, 2024 @11:42AM (#40891084)
> It’s orthogonal

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

[ Parent | on HN ]