On 21 Září 2011, 0:13, Ondrej Ivanič wrote:
> Hi,
>
> On 20 September 2011 18:16, Simon Riggs <simon@2ndquadrant.com> wrote:
>> It would be useful to get some balanced viewpoints on this. I see you
>> have Alterian experience, so if you are using both it could be
>> valuable info. I've never heard anyone describe the downsides of
>> columnar datastores, presumably there are some?
>
> Inserts are slower. I haven't done proper benchmark because there is
> no need for thousands inserts per sec in our database.
>
>> My understanding is that columnar will work well for queries like this
>>
>> SELECT count(*)
>> FROM table
>> WHERE col1 AND col2 AND col3
>>
>> but less well when we include the columns in the SELECT clause.
>
> 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)?
> 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).
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).
So I wouldn't be surprised if a columnar store on a spinner beats
PostgreSQL on a SSD, when running a DWH/DSS workload. Actually I'd expect
that.
This is not a rant against columnar stores - I find them very interesting.
Tomas
