From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ron.l.johnson@cox.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Large databases, performance
Date: Tue, 08 Oct 2002 11:51:12 -0400
Message-ID: <2502.1034092272@sss.pgh.pa.us> (raw)
In-Reply-To: <1034090215.1094.42.camel@haggis>
References: <3DA1E50F.4394.107FF85D@localhost>
<3DA2BE03.3674.13CF7DD1@localhost>
<20021008072047.GB558@svana.org>
<1034085052.1094.14.camel@haggis>
<1928.1034087882@sss.pgh.pa.us>
<1034090215.1094.42.camel@haggis>
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Tue, 2002-10-08 at 09:38, Tom Lane wrote:
>> 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?
We make new index entries for the new record, yes. Both the old and new
records must be indexed (until one or the other is garbage-collected by
VACUUM) so that transactions can find whichever version they are
supposed to be able to see according to the tuple visibility rules.
>> 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.
Sure, but you need an additional item of metadata than you otherwise
would (this is atttypmod, in Postgres terms). I'm not certain that the
typmod is available everyplace that would need to be able to figure out
the physical width of a column.
> 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.
Interesting that you should bring that up in the context of an argument
for supporting fixed-width fields ;-). Doesn't any form of data
compression bring you right back into variable-width land?
Postgres' approach to data compression is that it's done per-field,
and only on variable-width fields. We steal a couple of bits from the
length word to allow flagging of compressed and out-of-line values.
If we were to make CHAR(n) fixed-width then it would lose the ability
to participate in either compression or out-of-line storage.
Between that and the multibyte-encoding issue, I think it's very
difficult to make a case that the general-purpose CHAR(n) type should
be implemented as fixed-width. If someone has a specialized application
where they need a restricted fixed-width string type, it's not that
hard to make a user-defined type that supports only a single column
width (and thereby gets around the typmod issue). So I'm satisfied with
saying "define your own type if you want this".
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ron.l.johnson@cox.net, pgsql-performance@postgresql.org
Subject: Re: [GENERAL] Large databases, performance
In-Reply-To: <2502.1034092272@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox