3

A Ticket has the statuses:

new
in_progress
on_hold
closed

I could make ticket.status a string (on_hold) or an unique int(2). It is indexed.

Int pros: smallest size in index

Int con: low clarity for BI and evolving schema (3 compared to closed)

String pro: clear data navigation

String con: takes more space to index, gives less performance for same RAM

I imagine a string index wont take too much space over an int index if its low cardinality. Is it premature optimization to choose integer if the field has low cardinality and is not part of a compound index?

I use Postgres with SQLAlchemy, Python ORM.

  • 1
    It sounds premature optimization, yes. How big is your table? How often will it be queried? What is the use case (you mention BI without any further details)? Have you tested the costs of joining a lookup table? How your schema will/can evolve? And what do you mean by 'clear data navigation'? – dezso Oct 21 '15 at 8:18
4

I would definitely normalize for big tables. You could use an integer or int2 column with a FK constraint to a lookup table.

For small tables either of your ideas is ok. If in doubt, stick to the guidelines of the project.

I would use neither. Instead:

  • For small tables: use an enum. The documentation:

    An enum value occupies four bytes on disk.

  • For big tables: use a "char" field (1 byte) as FK to a lookup table. A good choice for very small sets of lookup values. And each value can be a mnemonic for the referenced state:

    status_id | status
    ----------+------------
    n         | new
    i         | in_progress
    o         | on_hold
    c         | closed
    

It also depends on the complete picture. You only actually save space (and gain performance) in table and indexes if it's not lost to alignment padding anyway. More:

For character types (except for "char"), collation rules may be relevant, too. More:

  • 1
    I've the greatest respect for you Erwin - your answers are always correct, impeccably documented and sometimes inspirational to the point of tears (well, let's not go too far :-) ), but what do you think of this post about the enum type? I find the arguments very persuasive. – Vérace Oct 23 '15 at 2:30
  • @Vérace: The page you refer to discusses MySQL enums and several points do not (completely) apply in Postgres (at least the points 2., 4., 6. and 7.) The Postgres implementation is better there. But you still caught me on this one, because I hardly ever actually use enum columns myself. I would rather go straight for my second suggestion. I just think Postgres enums are better than working with de-normalized strings. (And thank you, btw.) – Erwin Brandstetter Oct 23 '15 at 2:54
  • Enum is great although char seems to readily integrate with SQLAlchemy ORM. I guess char(4) occupies 4 bytes like an int32 right? One-byte mnemonics too extreme for me! – Jesvin Jose Oct 23 '15 at 6:28
  • @aitchnyu: Note that "char" is not the same as char or char(4). I never use the latter. Follow the links I provided and read this: stackoverflow.com/a/20334221/939860. char(4) occupies 5 bytes (1 byte overhead) on disk and 8 bytes in RAM (4 bytes overhead) for ASCII characters, more for multibyte characters. – Erwin Brandstetter Oct 23 '15 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.