Search Postgresql Archives

Re: Design Question (Time Series Data)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2007/10/4, Jorge Godoy <jgodoy@xxxxxxxxx>:
> 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.
>

It's depend on work. Somewhere normalised solution can be better,
somewhere not. But I belive, if you have lot of timeseries, than
arrays is better. But I repeat, it's depend on task.

Pavel

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux