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

On Tue, 2002-10-08 at 09:38, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
> > Not only that, but you get INSERT, UPDATE, DELETE and SELECT performance
> > gains with fixed length records, since you don't get fragmentation.
> 
> That argument loses a lot of its force when you consider that Postgres
> uses non-overwriting storage management.  We never do an UPDATE in-place
> anyway, and so it matters little whether the updated record is the same
> size as the original.

Must you update any relative indexes, in order to point to the
new location of the record?

> >> 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?  
> 
> It's not simpler: it's more complicated, because you need an additional
> input item to figure out the size of any given column in a record.

With fixed-length, why?  From the metadata, you can compute the intra-
record offsets.  That's how it works with the commercial RDBMS that
I use at work.

On that system, even variable-length records don't need record-size
fields.  Any repeating text (more that ~4 chars) is replaced with
run-length encoding.  This includes the phantom spaces at the end
of the field.

> Making sure that that info is available every place it's needed is one
> of the costs of supporting a feature like this.

-- 
+------------------------------------------------------------+
| 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"                |
+------------------------------------------------------------+