VarChars

[Christopher Browne]

On Thu, 14 Dec 2000 09:54:44 CST, the world broke into rejoicing as
Patrick Spinler <spinler.patrick@mayo.edu>  said:
> David Merrill wrote:
> > You can't avoid having a limit on text fields, but you can make them
> > very large.
>
> The only way to get "unlimited" text fields (or a reasonable
> approximation of them) is if we're willing to sacrifice database
> engine portability and probable some performance at this stage of
> the specification.  Some databases support a not quite unbounded
> "text" data type.  Postgres is one of them.  I think the limit on a
> text field is 2k, but I'm not certain of this.
>
> The other tradeoffs of this (besides loosing database portability) are:
> 
>   *) Records are (often) stored more compactly.  In postgres, for ex.,
> text data types only store as much as is actually inserted.   But- this
> means that:
>   *) Updating records takes longer since it may have to relocate data,
> and relocation does nasty things to table data on disk, by leaving
> non-reusable holes as data is relocated, and:
>   *) Some database engines may only relocate the text field, not the
> entire record, which does nasty things to data locality, and thus
> retreive time.
> 
> In short, if you plan on having updatable data, use varchar() columns,
> for which most databases preallocate space in the record.  This means
> some wastage of data storage, but you get a lot of positive performance
> boosts because of it.

Throw in Another Substantial Problem with VARCHAR:

--> Some DBs do not allow indexing on a VARCHAR field.

As a result, while it may be fine to use VARCHARs for what might
loosely be termed "memo" fields, it is _not_ going to be AOK to use
VARCHARs for everything.

That being said, I'd think it quite appropriate for the interfaces to
allow text of arbitrary size, and then have the engine gripe about
those fields that someone tries to pack _too_ full.  That decouples
the behaviour so that if field sizes _were_ to change, there aren't
vast numbers of cascading changes hitting GUI, engine, and everywhere
else.

--
(concatenate 'string "cbbrowne" "@ntlug.org")
<http://www.hex.net/~cbbrowne/>
Spontaneous Order
"The Austrian Economists discovered that control and chaos were
actually on the same side, and that neither is a viable way to get
smart."
-- Mark Miller