From: Heikki Linnakangas <heikki@enterprisedb.com>
To: genekhart@gmail.com
Cc: tgl@sss.pgh.pa.us
Cc: jonah.harris@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCHES] Maintaining cluster order on insert
Date: Thu, 10 Aug 2006 09:23:39 +0100
Message-ID: <44DAED0B.8010007@enterprisedb.com> (raw)
In-Reply-To: <430d92a20608092218u7f44f8baob801aea8b136e5dd@mail.gmail.com>
References: <44DA31B1.3090700@enterprisedb.com>
<28914.1155154622@sss.pgh.pa.us>
<36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com>
<430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com>
<9461.1155178183@sss.pgh.pa.us>
<430d92a20608092218u7f44f8baob801aea8b136e5dd@mail.gmail.com>
Gene wrote:
> You are correct the main part I'm worried about is the updates, being
> so far from the originals.
Yeah, you won't benefit from the patch at all.
> The reason I'm doing the clustering is I was hoping that with the
> "stable" non-updating partitions I could execute a CLUSTER at night
> (slow...) and it would compact the tables into their most efficient
> state for querying which always involves a date range. bad idea? In
> this fillfactor feature, will you be able to set it to 100% once you
> know that no more updates will occur? Or will doing a cluster
> effectively do this? Will the fill factor only apply for inserts?
That sounds like a good approach. CLUSTER obeys the fillfactor, so
you'll want to set it to 100 for the older partitions before you CLUSTER.
You might want to experiment with the fillfactor. You might get the best
performance if you just set it to 100 even for the latest partition, if
your queries usually have to scan most of it anyway. Fillfactor 100 will
help to keep it dense and in memory, so it won't matter so much if it's
disorganized.
> "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."
>
> This sounds interesting, I could create a RULE/INSERT on the unstable
> table, I will know during the update if it is ready to be put in the
> stable table. What would be an efficient way to do the transfer? Since
> the updates occur somewhat randomly, wouldnt the tuples in the stable
> table then be out of natural timestamp order?
I'm not sure I understand the last sentence. I thought the updates
usually occur within 30 minutes of the insert. So if you transfer the
rows to the stable table after 30 minutes, there won't be updates to the
stable table.
- Heikki
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: heikki@enterprisedb.com, genekhart@gmail.com, tgl@sss.pgh.pa.us, jonah.harris@gmail.com
Subject: Re: [PATCHES] Maintaining cluster order on insert
In-Reply-To: <44DAED0B.8010007@enterprisedb.com>
* 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