Subject: Re: [GENERAL] Large databases, performance
From: Ron Johnson <ron.l.johnson@cox.net>
To: pgsql-performance@postgresql.org
In-Reply-To: <20021008072047.GB558@svana.org>
References: <3DA1E50F.4394.107FF85D@localhost>
	<3DA2BE03.3674.13CF7DD1@localhost>  <20021008072047.GB558@svana.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 08 Oct 2002 08:50:52 -0500
Message-Id: <1034085052.1094.14.camel@haggis>
Mime-Version: 1.0

On Tue, 2002-10-08 at 02:20, Martijn van Oosterhout wrote:
> On Tue, Oct 08, 2002 at 11:14:11AM +0530, Shridhar Daithankar wrote:
> > On 7 Oct 2002 at 11:21, Tom Lane wrote:
> > 
> > > "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > > > I say if it's a char field, there should be no indicator of length as
> > > > it's not required. Just store those many characters straight ahead..
> > > 
> > > Your assumption fails when considering UNICODE or other multibyte
> > > character encodings.
> > 
> > Correct but is it possible to have real char string when database is not 
> > unicode or when locale defines size of char, to be exact?
> > 
> > In my case varchar does not make sense as all strings are guaranteed to be of 
> > defined length. While the argument you have put is correct, it's causing a disk 
> > space leak, to say so.

Not only that, but you get INSERT, UPDATE, DELETE and SELECT performance
gains with fixed length records, since you don't get fragmentation.

For example:
TABLE T
F1    INTEGER;
F2    VARCHAR(200)

INSERT INTO T VALUES (1, 'FOO BAR');
INSERT INTO T VALUES (2, 'SNAFU');

Next,
UPDATE T SET F2 = 'WIGGLE WAGGLE WUMPERSTUMPER' WHERE F1 = 1;

Unless there is a big gap on disk between the 2 inserted records, 
postgresql must then look somewhere else for space to put the new
version of T WHERE F1 = 1.

With fixed-length records, you know exactly where you can put the
new value of F2, thus minimizing IO.

> Well, maybe. But since 7.1 or so char() and varchar() simply became text
> with some length restrictions. This was one of the reasons. It also
> simplified a lot of code.

How much simpler can you get than fixed-length records?  

Of course, then there are 2 code paths, 1 for fixed length, and
1 for variable length.

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "they love our milk and honey, but preach about another    |
|  way of living"                                            |
|    Merle Haggard, "The Fighting Side Of Me"                |
+------------------------------------------------------------+