Re: Datatypes and performance

From: "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>
To: Maksim Likharev <mlikharev(at)aurigin(dot)com>
Cc: PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Datatypes and performance
Date: 2003-07-04 21:30:41
Message-ID: Pine.LNX.4.21.0307042226240.29474-100000@ponder.fairway2k.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


That's "long values" that are stored elsewhere. I believe the length has to be
a good portion of a page size, normally 4KB, before it's considered for placing
in the toast table. I'm not sure if the details are in the documentation but
I'm sure they'll appear in a search of the archive. Of course, someone like
Tom, Bruce etc. while no doubt pop up with the specifics.

--
Nigel J. Andrews

On Fri, 4 Jul 2003, Maksim Likharev wrote:

> Ok, what I see here tells me that text is slower then fixed len varchar,
> due to stored in separate table ( but how else you can store long fields
> ).
> so postgres has to read another page(s) in order to get long value.
> Story about boundary checks for varchar just does not count,
> just nothing with comparing with disk reads/writes.
>
>
> -----Original Message-----
> From: Andrew Sullivan [mailto:andrew(at)libertyrms(dot)info]
> Sent: Friday, July 04, 2003 1:24 PM
> To: PostgreSQL List
> Subject: Re: [GENERAL] Datatypes and performance
>
>
> On Fri, Jul 04, 2003 at 01:14:52PM -0700, Maksim Likharev wrote:
> > How postgres internally stores text fields, in a separate table?
>
> I believe it gets stored in a separate table just in case it's too
> long (read the docs on TOAST if you want more about this). But
> normally, no. Here's what the docs have to say about it:
>
> ---cut here---
> The storage requirement for data of these types is 4 bytes plus the
> actual string, and in case of character plus the padding. Long
> strings are compressed by the system automatically, so the physical
> requirement on disk may be less. Long values are also stored in
> background tables so they don't interfere with rapid access to the
> shorter column values. In any case, the longest possible character
> string that can be stored is about 1 GB. (The maximum value that will
> be allowed for n in the data type declaration is less than that. It
> wouldn't be very useful to change this because with multibyte
> character encodings the number of characters and bytes can be quite
> different anyway. If you desire to store long strings with no
> specific upper limit, use text or character varying without a length
> specifier, rather than making up an arbitrary length limit.)
> ---cut here---
>
> A
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2003-07-04 21:54:08 Re: Datatypes and performance
Previous Message Tim Conrad 2003-07-04 21:28:32 Re: PostgreSQL vs. MySQL