From: stark <stark@enterprisedb.com>
To: genekhart@gmail.com
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
 "Jonah H. Harris" <jonah.harris@gmail.com>,
	pgsql-hackers@postgresql.org,
	"Heikki Linnakangas" <heikki@enterprisedb.com>,
	pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
In-Reply-To: <430d92a20608092218u7f44f8baob801aea8b136e5dd@mail.gmail.com>
	(genekhart@gmail.com's message of "Thu, 10 Aug 2006 01:18:00 -0400")
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>
Date: Thu, 10 Aug 2006 11:59:11 +0100
Message-ID: <87d5b87t6o.fsf@enterprisedb.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Gene <genekhart@gmail.com> writes:

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

You may find it easier to handle some of the logic in a low level application
layer or layer of stored procedures rather than trying to make it entirely
transparent with rules. If you do want it to be transparent you might also
consider whether you want triggers instead of rules.

Another direction you might want to consider is whether the columns that
you're updating would be more normalized in a separate table. You might really
want to have a record of those past states as well. So you might find having
three records in this other table for each of your regular records in your
main table might actually work out better.

Even if you only have a 1-1 relationship sometimes this kind of horizontal
partitioning (or do people consider this vertical partitioning?) is still
worthwhile. If the columns being updated are very small or often not needed at
all then it may be reasonably efficient to look them up separately and still
let you store the bulk of the data efficiently and access it in a fast
sequential scan.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com