Re: COLUMNAR postgreSQL ?

Hi,

On 20 September 2011 18:16, Simon Riggs <simon(at)2ndquadrant(dot)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(), ...

In some cases columnar store is able to beat Postgres + High IOPS
(250k+) SSD card

> Would you be able to give some viewpoints and measurements on that?

Check this: http://www.greenplum.com/community/forums/showthread.php?499-enable_mergejoin-and-random_page_cost&p=1553#post1553

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