Re: COLUMNAR postgreSQL ?

Hi,

2011/9/21 Tomas Vondra <tv(at)fuzzy(dot)cz>:
>> Columnar store is good if:
>> - you are selecting less than 60% of the total row size (our table has
>> 400 cols and usual query needs 5 - 10 cols)
>> - aggregates: count(*), avg(), ...
>
> Where did those numbers come from? What columnar database are you using?
> What options were used (e.g. compression)?

Aster's nCluster and Greenplum with no and maximum compression (there
was no difference between compression level 5 and 9 but hoge
difference between compression level 0 and 9) and partitioned.

>> In some cases columnar store is able to beat Postgres + High IOPS
>> (250k+) SSD card
>
> What do you mean by "in some cases"? If that means a DWH/DSS workloads,
> then it's apples to oranges I guess.

> SSDs are great for OLTP workloads (with a lot of random I/O). With DWH/DSS
> workloads, the performance gain is much smaller (not worth the money in
> some cases).

Yes, our DB is hybrid: we need OLAP solution with OLTP performance.
Schema si very simple star schema and is multitenant. So "random io"
to "seq io" is 80% : 20% but most of the queries are simple aggregates
and select queries (drill downs, dicing, slicing, summaries, and
queries generated by machine learning algos). Users are anxious if
they have to wait for more than 30 sec.

> With this kind of workload the IOPS is not that important, the sequential
> reads is. And SSDs are not significantly faster in case of sequential I/O
> - you can usually achieve the same sequential performance with spinners
> for less money).

yes, you are right:
seq IO: FusionIO is 3-5 times faster than our Hitachi SAN. SAN is 5-10
times faster than local SAS 15k drive. Random IO is completely
different story.

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)