Re: [GENERAL] Large databases, performance

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(at)persistent(dot)co(dot)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(dot)l(dot)johnson(at)cox(dot)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" |
+------------------------------------------------------------+