From: stark <stark@enterprisedb.com>
To: genekhart@gmail.com
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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: Thu, 10 Aug 2006 11:59:11 +0100
Message-ID: <87d5b87t6o.fsf@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 <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
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: stark@enterprisedb.com, genekhart@gmail.com, tgl@sss.pgh.pa.us, jonah.harris@gmail.com, heikki@enterprisedb.com, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
In-Reply-To: <87d5b87t6o.fsf@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