Re: Datatype sizes; a space and speed issue?

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Joel Matthew <rees(at)ddcom(dot)co(dot)jp>
Cc: linux(at)alteeve(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: Datatype sizes; a space and speed issue?
Date: 2004-06-23 04:34:05
Message-ID: 20040622212727.T37757@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Wed, 23 Jun 2004, Joel Matthew wrote:

> > What effect is there if I specify "TEXT" instead of
> > say "VARCHAR(255)"?
>
> Well, theoretically, in the latter case, the database will allocate 256
> (257? 259?) bytes for that field in the record itself. That is, that
> field will consume 256 bytes for each record stored.

Both text and varchar are stored in PostgreSQL as length + string so
varchar(256) doesn't not require storage of the unused bytes. char space
pads so it's the oddball (requiring length + padded string).

> > How much benefit is there do being more aggresive
> > and say cutting it in half again by using "VARCHAR(128)"?
>
> Well, that would reduce the storage requirements for that field by half.
> It might also prevent you from storing necessary information. That's
> easily 128 characters if you're only using US-ASCII in UTF-8, but it's
> only 32 characters of Unicode in UTF-32, and it could be anywhere
> between 128 and 32 in Unicode UTF-8. Making a good prediction about the
> tradeoff is one of the things a database engineer is paid for.

varchar lengths should be in characters not bytes, so that should be 128
characters in any of the encodings, but the actual number of bytes that
those 128 characters can take up may vary.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joel Matthew 2004-06-23 04:47:09 Re: Datatype sizes; a space and speed issue?
Previous Message Madison Kelly 2004-06-23 04:27:06 Re: Datatype sizes; a space and speed issue?