Re: Datatypes and performance

From: "Maksim Likharev" <mlikharev(at)aurigin(dot)com>
To: "PostgreSQL List" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Datatypes and performance
Date: 2003-07-04 21:22:39
Message-ID: 56510AAEF435D240958D1CE8C6B1770A016D2D8E@mailc03.aurigin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Conrad 2003-07-04 21:28:32 Re: PostgreSQL vs. MySQL
Previous Message Brian Tarbox 2003-07-04 21:14:01 Re: PostgreSQL vs. MySQL