On Wed, Jun 17, 2009 at 6:06 PM, Whit Armstrong<armstrong.whit@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
