Conversation

Notices

1. Embed this notice
   Ayo (ayo@lonely.town)'s status on Sunday, 22-Dec-2024 17:35:36 JST Ayo

   I often struggle to disable the micro-optimization-addicted part of my brain when designing SQL schemas.

   VNDB has a table with 4 enum columns, each supporting 5-6 possible values. Postgres encodes enums as 4 bytes, so those 4 columns occupy 16 bytes on disk. But the same information would comfortably fit in just 2 bytes!

   Of course, I could merge the columns into a single smallint with some fancy bit twiddling, but that comes at the cost of awkward queries and complicating the codebase. The benefits aren't worth it either: space savings will likely be insignificant if you factor in other columns and row overhead, and it's unlikely to make much of a difference in terms of performance.

   BUT STILL THAT INFORMATION COULD BE 8 TIMES SMALLER! :blobcatreeeeeee:

   • Haelwenn /элвэн/ :triskell: and gidi like this.
   • Embed this notice
     Haelwenn /элвэн/ :triskell: (lanodan@queer.hacktivis.me)'s status on Sunday, 22-Dec-2024 18:31:25 JST Haelwenn /элвэн/ :triskell:

     in reply to

     • Wolf480pl
     @ayo @wolf480pl And I guess they screwed up the varint size for encoding the length of the string? (should be encoding for 254 bytes with 1 byte, then 65533 with 2 bytes, …).
   • Embed this notice
     Ayo (ayo@lonely.town)'s status on Sunday, 22-Dec-2024 18:31:26 JST Ayo

     in reply to

     • Wolf480pl

     @wolf480pl Yup, it's essentially a varchar(n) with automatic padding.

   • Embed this notice
     Wolf480pl (wolf480pl@mstdn.io)'s status on Sunday, 22-Dec-2024 18:31:27 JST Wolf480pl

     in reply to

     @ayo (inb4 it's toast)

   • Embed this notice
     Wolf480pl (wolf480pl@mstdn.io)'s status on Sunday, 22-Dec-2024 18:31:27 JST Wolf480pl

     in reply to

     @ayo
     > The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string,

     :(

   • Embed this notice
     Wolf480pl (wolf480pl@mstdn.io)'s status on Sunday, 22-Dec-2024 18:31:28 JST Wolf480pl

     in reply to

     @ayo how long is char(1)?

   • Embed this notice
     Wolf480pl (wolf480pl@mstdn.io)'s status on Sunday, 22-Dec-2024 18:31:29 JST Wolf480pl

     in reply to

     @ayo omg it's using 4 WHOLE BYTES for an Enum? That's crazy...

     what if you made a separate table with enum values and 1-byte PK, and replaced the enum columns with 1-byte foreign keys?

   • Embed this notice
     Ayo (ayo@lonely.town)'s status on Sunday, 22-Dec-2024 18:31:29 JST Ayo

     in reply to

     • Wolf480pl

     @wolf480pl I know, right?

     Sadly the only 1-byte type in core Postgres is a boolean, which is... not quite suitable. Using the FK approach with smallints still halves the storage requirements, but meh.

     I'm almost considering hacking in support for 1-byte enums into Postgres. Even have an idea on how to do that within its current enum framework, but I doubt there's much enthusiasm among postgres devs to add the complexity unless I can motivate it with some strong use cases and benchmarks.