On Thursday 04 October 2007 06:20:19 Pavel Stehule wrote: > > I did good experience with 2 variant. PostgreSQL needs 24bytes for > head of every row, so isn't too much efective store one field to one > row. You can simply do transformation between array and table now. But then you'll make all SQL operations complex, you will have problems using aggregators, etc. For example, with a normalized design one can query the average value of a specific serie using simple commands and given the use of indices this could be highly optimized. Now, using an array, he'll be doing a seqscan on every row because he needs to find if there was a value for the given series, then he'll need extracting those values and finally calculating the average (I know you can select an element of the array, but it won't be easy on the planner or the loop to calculate the average because they'll need to do all that and on every row). I'd use the same solution that he was going to: normalized table including a timestamp (with TZ because of daylight saving times...), a column with a FK to a series table and the value itself. Index the two first columns (if you're searching using the value as a parameter, then index it as well) and this would be the basis of my design for this specific condition. Having good statistics and tuning autovacuum will also help a lot on handling new inserts and deletes. -- Jorge Godoy <jgodoy@xxxxxxxxx> ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
