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@xxxxxxxxxx] > Sent: Tuesday, September 26, 2006 5:26 PM > To: Marc Morin; Tom Lane > Cc: Markus Schaber; pgsql-performance@xxxxxxxxxxxxxx > 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 >
