char or int vs varchar

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: char or int vs varchar
Date: 2005-04-22 20:20:51
Message-ID: 20050422200053.M57618@narrowpathinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi All,

I am building a new table and I would appreciate your thoughts on a data
storage and performance question

I need to store a (at the moment) tristate variable. Now this could be done
as a char "L", "S", "T" as an int2 1, 2, 3 or as a varchar(8) "loose",
"standard", "tight" among possibly other ways.

For absolute storage minimum char would be the best at 1 byte but the
documentation indicates that it is an internal type so I do not know whether
or not I can really use it. If not then character(1) would have to be used
and it looks like that would use up 5 bytes.

For readability varchar(8) obviously wins. The documentation assures me that
other than using 12 bytes there is "no performance differences between these
three types".

Finally there is the smallint at 2 bytes. Almost the smallest in terms of
disk usage I wonder how it will affect performance since in most cases I would
probably have to use a CASE statement to convert to something a human would
intuitively understand.

Kind Regards,
Keith

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message ruel.cima 2005-04-22 20:38:29 SSL use
Previous Message Keith Worthington 2005-04-22 19:29:33 CHECK vs BEFORE trigger