2

I've an ever-growing table(~20 Million rows in a year).

The table has a status column which is enum type with 10 states like:

ENQUEUED, DELIVERED, FAILED, ...

Since Postgres enum type takes 4 bytes space, I decided to alter the column to something less needed space like smallint or character(1) or "char".

enum           takes 4 bytes
smallint       takes 2 bytes
character(1)   takes 2 bytes
"char"         takes 1 byte

I want to use "char" with this values:

E, D, F, ...

Because "char" takes less space than smallint or character(1)

Is it good idea to use "char" as a less space replacement for enum?

Any ideas would be great appreciated.

Hamed Kamrava
  • 12,359
  • 34
  • 87
  • 125
  • 5
    Due to [alignment and padding](https://stackoverflow.com/a/7431468/330315) it probably won't make a difference –  Apr 03 '18 at 07:41
  • 2
    Agree with a_horse, still enum is the best choice for readability and code maintenance. – klin Apr 03 '18 at 07:44
  • `character(1)` is 5 bytes, by the way – Nick Barnes Apr 03 '18 at 10:58
  • Use whatever is easiest for you to code and maintain. Speed and disks get cheaper and cheaper.. 1 byte, even multiplied 20 million times is not going to change anything you'll notice. – Joe Love Apr 03 '18 at 19:45
  • In addition to the already mentioned, I would always prefer Enums, because they are easier to understand (e.g. the meaning of `DELIVERED` is clearer than the meaning of `D`). They are ordered (e. g. `ENQUEUED` is less than `DELIVERED`) and you can often take advantage of this. – clemens Apr 04 '18 at 05:07
  • @NickBarnes source? Everything I'm reading says 1 byte overhead. Running `pg_column_size` on `CHAR(1)` appears to support this (every result is 2) – Krusty the Clown Aug 15 '23 at 15:37
  • @Krusty: The source of the overhead is the `varlena` header. I'm still getting 5: https://onecompiler.com/postgresql/3zhmnt4bv – Nick Barnes Aug 15 '23 at 16:15
  • @NickBarnes interesting, probably a version / encoding difference. I'm running Postgres 10; didn't see a version on there (I probably didn't look hard enough) – Krusty the Clown Aug 15 '23 at 16:18
  • @Krusty: It's version 14, though it was the same on 9.6: http://sqlfiddle.com/#!17/9eecb/108943/0 . Encoding shouldn't factor into it, as the same `varlena` header is carried by all values of variable-length types (like `numeric`, arrays, etc.) – Nick Barnes Aug 15 '23 at 16:27

0 Answers0