From: Marc Morin <marc@sandvine.com>
To: Bucky Jordan <bjordan@lumeta.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Decreasing BLKSZ
Date: Tue, 26 Sep 2006 17:36:04 -0400
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C20113070C@mailserver.sandvine.com> (raw)
In-Reply-To: <78ED28FACE63744386D68D8A9D1CF5D4209ADC@MAIL.corp.lumeta.com>
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]
> 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
>
> > > 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
>
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-performance@postgresql.org
Cc: marc@sandvine.com, bjordan@lumeta.com, tgl@sss.pgh.pa.us, schabi@logix-tt.com
Subject: Re: Decreasing BLKSZ
In-Reply-To: <2BCEB9A37A4D354AA276774EE13FB8C20113070C@mailserver.sandvine.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