DB design document

Patrick Spinler spinler.patrick@mayo.edu
Thu, 14 Dec 2000 09:54:44 -0600


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.

-- Pat

-- 
      This message does not represent the policies or positions
	     of the Mayo Foundation or its subsidiaries.
  Patrick Spinler			email:	Spinler.Patrick@Mayo.EDU
  Mayo Foundation			phone:	507/284-9485