Skip site navigation (1) Skip section navigation (2)

Re: Decreasing BLKSZ

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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-09-26 22:09:56
Subject: Re: Merge Join vs Nested Loop
Previous:From: Bucky JordanDate: 2006-09-26 21:25:40
Subject: Re: Decreasing BLKSZ

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group