Comment by dzaima | Hacker News Reader

Comment by dzaima | Hacker News Reader

Parent Full thread

That's assuming all of those are stored in packed formats, but even then it's not that low.

I already mentioned that the user IDs are 36 base64 chars, or 27 bytes if you store them max-packed; YT video IDs are 11 base64 chars, so 66 bits, doesn't quite fit in 8 bytes (not to mention that trying to pack the video IDs would mean your db becomes useless if youtube suddenly added a new video ID format). IP needs another bit somewhere for ipv4 vs ipv6, so likely 24 bytes (or just a string could be used).

Then you have some overhead for padding and string field lengths, and whatever overhead for packing the data for the disk storage (padding for all entires to stay within a page? maintaining a percentage of free space to ensure the B-tree property?). Then you have a copy of the fields in indexes, with whatever overhead those come with.

Granted, even that's probably around 200 bytes, not 500, in a reasonable db, but who's to say that the db used used is a reasonable & well-configured one; of course it's possible that a bunch more metadata is stored for user trustworthiness statistics or something, or duplicated tables where relations would work.

> user IDs are 36 base64 chars, or 27 bytes if you store them max-packed

Stupid. You aren't going to have 2^288 users, why do you need that many user IDs? A 64-bit integer is already overkill.

>YT video IDs are 11 base64 chars, so 66 bits, doesn't quite fit in 8 bytes (not to mention that trying to pack the video IDs would mean your db becomes useless if youtube suddenly added a new video ID format)

Your video table has a 64-bit integral row ID. You have a column that is a foreign key to it. Join on them.

>IP needs another bit somewhere for ipv4 vs ipv6, so likely 24 bytes (or just a string could be used).

All IPv4 addresses can be encoded as IPv6 addresses so this only requires 16 bytes.

>Then you have some overhead for padding and string field lengths,

None of these things are strings.

>and whatever overhead for packing the data for the disk storage (padding for all entires to stay within a page? maintaining a percentage of free space to ensure the B-tree property?).

This is pretty low overhead. It won't take you to hundreds of bytes per row.

>Granted, even that's probably around 200 bytes, not 500, in a reasonable db, but who's to say that the db used used is a reasonable & well-configured one; of course it's possible that a bunch more metadata is stored for user trustworthiness statistics or something, or duplicated tables where relations would work.

If it stores IDs as strings then the DB probably won't be set up correctly either. That would be clearly wrong and wrong people are usually wrong about other things too.

Unfortunately, nice as it would be, your level of perfectionism is unfortunately not particularly common; indeed, it's possible to do things much more efficiently, but for most purposes "it works" is enough; and when it starts to not be you already have terabytes of db and just adding more disk is much easier than the hassle of migrating the entire thing to something different.

Reply on news.ycombinator.com