Re: Columnar store as default for PostgreSQL 10?

On 21 April 2016 at 17:08, David G. Johnston <david.g.johnston@gmail.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"

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