Re: Decreasing BLKSZ

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
>