Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Decreasing BLKSZ 
Date: Tue, 26 Sep 2006 17:36:04 -0400
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C20113070C@mailserver.sandvine.com>
In-Reply-To: <78ED28FACE63744386D68D8A9D1CF5D4209ADC@MAIL.corp.lumeta.com>
Thread-Topic: [PERFORM] Decreasing BLKSZ 
Thread-Index: Acbg8csHNP15KgLFSYaUIxW+9sCeywAEOehQACudwFAAAJGDYA==
From: "Marc Morin" <marc@sandvine.com>
To: "Bucky Jordan" <bjordan@lumeta.com>,
	"Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Markus Schaber" <schabi@logix-tt.com>, <pgsql-performance@postgresql.org>

Yes, that is our application.   We have implemented both scenarios...

1- partitions loaded without indexes on them.. And build index "when
partition is full".  Slow to drill down into incomplete partitions.
2- paritions with index as loaded.  Slow, on insert (problem mentioned)
but good to drill down....

So, I'd like my cake and eat it too... :-)

I'd like to have my indexes built as rows are inserted into the
partition so help with the drill down...

> -----Original Message-----
> From: Bucky Jordan [mailto:bjordan@lumeta.com]=20
> Sent: Tuesday, September 26, 2006 5:26 PM
> To: Marc Morin; Tom Lane
> Cc: Markus Schaber; pgsql-performance@postgresql.org
> Subject: RE: [PERFORM] Decreasing BLKSZ=20
>=20
> > > The bottom line here is likely to be "you need more RAM" :-(
> >=20
> > Yup.  Just trying to get a handle on what I can do if I=20
> need more than=20
> > 16G Of ram... That's as much as I can put on the installed based of=20
> > servers.... 100s of them.
> >=20
> > >
> > > I wonder whether there is a way to use table partitioning to make=20
> > > the insert pattern more localized?  We'd need to know a lot more=20
> > > about your insertion patterns to guess how, though.
> > >
> > > 			regards, tom lane
> >=20
> > We're doing partitioning as well.....
> > >
> I'm guessing that you basically have a data collection=20
> application that sends in lots of records, and a reporting=20
> application that wants summaries of the data? So, if I=20
> understand the problem correctly, you don't have enough ram=20
> (or may not in the future) to index the data as it comes in.=20
>=20
> Not sure how much you can change the design, but what about=20
> either updating a summary table(s) as the records come in=20
> (trigger, part of the transaction, or do it in the=20
> application) or, index periodically? In otherwords, load a=20
> partition (say a day's worth) then index that partition all=20
> at once. If you're doing real-time analysis that might not=20
> work so well though, but the summary tables should.=20
>=20
> I assume the application generates unique records on its own=20
> due to the timestamp, so this isn't really about checking for=20
> constraint violations? If so, you can probably do away with=20
> the index on the tables that you're running the inserts on.
>=20
> - Bucky
>=20