X-Original-To: pgsql-patches-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 3A5DF9FB2CB for ; Thu, 10 Aug 2006 02:18:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 35727-07 for ; Thu, 10 Aug 2006 05:18:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.196]) by postgresql.org (Postfix) with ESMTP id 4265A9FB2D5 for ; Thu, 10 Aug 2006 02:18:03 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id z31so25955nzd for ; Wed, 09 Aug 2006 22:18:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SxFDeMa7ENVK7yh65hq65sH49wUins6hFSQZCz0S8aBIla4+ZXLCicjDkqtKe9bebi/8/ejRpVp2LQ/c+F8Va3ucRbnwtlR1PrUwKEto9GgcODO0dgwveven6ZV8id7x/n4uQq0KY9Y97h2jOYq4Yomu2P1Jw5sDiF4rVr10drs= Received: by 10.64.241.3 with SMTP id o3mr1774489qbh; Wed, 09 Aug 2006 22:18:02 -0700 (PDT) Received: by 10.65.115.12 with HTTP; Wed, 9 Aug 2006 22:18:00 -0700 (PDT) Message-ID: <430d92a20608092218u7f44f8baob801aea8b136e5dd@mail.gmail.com> Date: Thu, 10 Aug 2006 01:18:00 -0400 From: Gene Reply-To: genekhart@gmail.com To: "Tom Lane" Subject: Re: [HACKERS] Maintaining cluster order on insert Cc: "Jonah H. Harris" , pgsql-hackers@postgresql.org, "Heikki Linnakangas" , pgsql-patches@postgresql.org In-Reply-To: <9461.1155178183@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19675_22021962.1155187080172" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.551 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, SPF_PASS X-Spam-Level: * X-Archive-Number: 200608/140 X-Sequence-Number: 20656 ------=_Part_19675_22021962.1155187080172 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline You are correct the main part I'm worried about is the updates, being so far from the originals. fyi I am partitioning the tables by the timestamp column,vacuum analyzing once per hour, creating one child partition per day in a cron job. Because I'm using hibernate for database abstraction (stateless sessions), I can only have one RULE since having more than one insert rule will not return the correct number of updated rows which confuses hibernate. The one rule just directs inserts to the latest child partition which seems to work well. 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? "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? thanks for all of your help and comments! it is greatly appreciated! Gene Hart On 8/9/06, Tom Lane wrote: > > Gene 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 > -- Eugene Hart ------=_Part_19675_22021962.1155187080172 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline You are correct the main part I'm worried about is the updates, being so far from the originals. fyi I am partitioning the tables by the timestamp column,vacuum analyzing once per hour, creating one child partition per day in a cron job. Because I'm using hibernate for database abstraction (stateless sessions), I can only have one RULE since having more than one insert rule will not return the correct number of updated rows which confuses hibernate. The one rule just directs inserts to the latest child partition which seems to work well.

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?

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

thanks for all of your help and comments! it is greatly appreciated!
Gene Hart

On 8/9/06, Tom Lane <tgl@sss.pgh.pa.us > wrote:
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



--
Eugene Hart ------=_Part_19675_22021962.1155187080172--