Re: Primary key efficiency

Russell Shaw <rjshaw(at)iprimus(dot)com(dot)au> writes:
> Tom Lane wrote:
>> Almost certainly you want varchar(40), so as not to be wasting huge
>> amounts of space on padding blanks.

> I'd assumed databases would save the number of blanks needed in any
> position, and restore/add the blanks when the field was retrieved.

In Postgres, if the field is really wide (like kilobytes) then
compression will kick in and pretty much eliminate runs of spaces,
or runs of anything else for that matter. But I doubt it would get
applied to 40-byte fields.

> I assumed that for some reason it would be faster or more efficient
> than something of unfixed length or else why does it exist? Maybe it
> is only kept for compatability purposes?

Didn't you notice the contradiction to your previous assumption? Either
the field is fixed-width or it's not, you don't get to have it both
ways. It is true that there are certain optimizations that can be
applied to fixed-width fields, but they are relatively minor in
Postgres. (In databases that do overwrite-in-place, it can be a
significant win to ensure that *all* the fields of a record are fixed
width and so the total record size is fixed, but Postgres doesn't do
that anyway.) In any case, CHAR(n) is never considered a fixed-width
type in Postgres, because N is measured in characters not bytes and so
the physical width is variable anyway, at least when using
variable-width character set encodings.

Bottom line is that there are no efficiency advantages to CHAR(n) in
Postgres, although there can be some in old-line databases. You should
only use it if your data actually has a semantic constraint to a fixed
width --- postal codes are a common example of something that really is
appropriate for CHAR(n).

regards, tom lane