Re: Columnar store as default for PostgreSQL 10?

On Apr 22, 2016, at 15:03 , Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra <brauliobo(at)gmail(dot)com> wrote:
>> I'm finally having performance issues with PostgreSQL when doing big
>> analytics queries over almost the entire database of more than 100gb of
>> data.
>>
>> And what I keep reading all over the web is many databases switching to
>> columnar store (RedShift, Cassandra, cstore_fdw, etc) and having great
>> performance on queries in general and giant boosts with big analytics
>> queries.
>>
>> I wonder if there is any plans to move postgresql entirely to a columnar
>> store (or at least make it an option), maybe for version 10?
>>
>> The current extensions are rather limited (types support for example) and
>> require quite some configuration and data migration to work, besides they
>> don't work in services like AWS RDS.
>
> Column stores are better at one case (selecting a few columns from a
> very wide table) and worse at just about every other case. Also,
> beware database benchmarks -- as they say, there is no free lunch
> There is a reason why databases store things in rows.
>
> Analytics in traditional postgres tables is definitely possible, but
> you have to be smart.

There are tradeoffs; a column store is faster at queries that select a subset of columns. The *big* tradeoff is that insert time increases linearly with the number of columns. Queries that pull a large subset of the columns can also be slower.

I would quite like to set a table to columnar in Postgres, but really you can achieve much the same thing with multiple tables in a 1:1 relationship, so I don't think this would be worth putting much effort into.