Thread: enum for performance?

I have a column which only has six states or values.

Is there a size advantage to using an enum for this data type?
Currently I have it defined as a character(1).

This table has about 600 million rows, so it could wind up making a
difference in total size.

Thanks,
Whit

Whit Armstrong <armstrong.whit@gmail.com> writes:
> I have a column which only has six states or values.
> Is there a size advantage to using an enum for this data type?
> Currently I have it defined as a character(1).

Nope.  enums are always 4 bytes.  char(1) is going to take 2 bytes
(assuming those six values are simple ASCII characters), at least
as of PG 8.3 or later.

Depending on what the adjacent columns are, the enum might not actually
cost you anything --- the difference might well disappear into alignment
padding anyway.  But it's not going to save.

Another possibility is to look at the "char" (not char) type, which also
stores single ASCII-only characters.  That's just one byte.  But again,
it might well not save you anything, depending on alignment
considerations.

            regards, tom lane

On Wed, Jun 17, 2009 at 6:06 PM, Whit Armstrong<armstrong.whit@gmail.com> wrote:
> I have a column which only has six states or values.
>
> Is there a size advantage to using an enum for this data type?
> Currently I have it defined as a character(1).
>
> This table has about 600 million rows, so it could wind up making a
> difference in total size.

Here is what enums get you:
*) You can skip a join to a detail table if one char is not enough to
sufficiently describe the value to the user.
*) If you need to order by the whats contained in the enum, the gains
can be tremendous because it can be inlined in the index:

create table bigtable
(
  company_id bigint,
  someval some_enum_t,
  sometime timestamptz,
);

create index bigindex on bigtable(company_id, someval, sometime);

select * from bigtable order by 1,2,3 limit 50;
-- or
select * from bigtable where company_id = 12345 order by 2,3;

The disadvantage with enums is flexibility.  Sometimes the performance
doesn't matter or you need that detail table anyways for other
reasons.

Also, if you use "char" vs char(1), you shave a byte and a tiny bit of speed.

merlin