Re: Columnar store as default for PostgreSQL 10?

On Thu, Apr 21, 2016 at 1:39 PM Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> On 21 April 2016 at 17:08, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> > I have little experience (and nothing practical) with columnar store but
> at
> > a high level I don't see the point. I would hope that anyone interested
> in
> > working on a columnar store database would pick an existing one to
> improve
> > rather than converting a very successful row store database into one.
> And I
> > don't immediately understand how a dual setup would even be viable - it
> > seems like you'd have to re-write so much
> > of the code the only thing left would be the SQL parser.
>
> To be fair, I'd say that this "only thing" would be pretty huge. The
> cost of changing databases is often prohibitive (or nearly so) because
> the parser isn't _quite_ the same, and if the sort of gains that are
> bandied about could really be achieved just by choosing columnar
> storage for certain tables without having to rewrite large chunks of
> code that would be a very big win.
>
> I certainly agree that changing the store to columnar-only makes
> little sense though, because it would alienate a lot (I would suggest
> the majority) of users whose data fits far better into a row model.
>
> FWIW, looking at the cstore_fdw extension did get me quite excited
> (because I have an inkling that quite a lot of our queries might
> benefit from such a feature) until I saw that DELETEs aren't possible,
> which would invalidate most of the wins for us because of the
> subsequent massive cost of modifying data.
>
> There's also an interesting document from the monet_db guys about how
> the wins to be gained just by using cstore_fdw (rather than moving to
> a native column-store) aren't as high as you would hope. I have a
> feeling that would remain the case even if the store were integrated.
>
>
> https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout
> " the margin by which MonetDB outperforms cstore_ftw shows that only
> switching storage models alone is probably not enough"
>
I think the gains are really high as with big data caching is usually not
really possible.
But of course cstore_fdw should perform better when caching is feasible.

>
> Geoff
> (Disclaimer: I've no connection to MonetDB in any way)
>