From: Tom Lane <tgl@sss.pgh.pa.us>
To: genekhart@gmail.com
Cc: Jonah H. Harris <jonah.harris@gmail.com>
Cc: pgsql-hackers@postgresql.org, "Heikki Linnakangas" <heikki@enterprisedb.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
Date: Wed, 09 Aug 2006 22:49:43 -0400
Message-ID: <9461.1155178183@sss.pgh.pa.us> (raw)
In-Reply-To: <430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com>
References: <44DA31B1.3090700@enterprisedb.com>
<28914.1155154622@sss.pgh.pa.us>
<36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com>
<430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com>
Gene <genekhart@gmail.com> writes:
> I have a table that inserts lots of rows (million+ per day) int8 as primary
> key, and I cluster by a timestamp which is approximately the timestamp of
> the insert beforehand and is therefore in increasing order and doesn't
> change. Most of the rows are updated about 3 times over time roughly within
> the next 30 minutes.
ISTM you should hardly need to worry about clustering that --- the data
will be in timestamp order pretty naturally.
The main problem you're going to have is the update-3-times bit. You
could keep updated rows on the same page as the original if you ran the
table at fillfactor 25% (which you'll be able to do in 8.2) ... but
while this might be sane for the leading edge of the table, you hardly
want such low storage density in the stable part.
You could reduce the fillfactor requirement if you could vacuum the
table constantly (every 10 minutes or so) but I assume the table is
large enough to make that unattractive. (Eventually we should have
a version of vacuum that understands where the dirty stuff is, which
might make this approach tenable ... but not in 8.2.)
Your best bet might be to partition the table into two subtables, one
with "stable" data and one with the fresh data, and transfer rows from
one to the other once they get stable. Storage density in the "fresh"
part would be poor, but it should be small enough you don't care.
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-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, genekhart@gmail.com, jonah.harris@gmail.com, heikki@enterprisedb.com, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
In-Reply-To: <9461.1155178183@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