Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Edson Richter]

Em 17/05/2016 11:07, Merlin Moncure escreveu:

On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback
 wrote:

It is not difficult to simulate column store in a row store system if
you're willing to decompose your tables into (what is essentially)
BCNF fragments.  It simply is laborious for designers and programmers.

I could see a true column store having much better performance than tricking
a row based system into it.  Just think of the per-row overhead we currently
have at 28 bytes per row.  Breaking up data manually like that may help a
little, but if you don't have a very wide table to begin with, it could turn
out you save next to nothing by doing so.  A column store wouldn't have this
issue, and could potentially have much better performance.

FYI tuple header is 23 bytes, not 28 bytes
(http://www.postgresql.org/docs/9.5/static/storage-page-layout.html).
Personally I think column stores are a bit overrated.  They are faster
at certain things (in some cases much faster) but tend to put pretty
onerous requirements on application design so that they are very much
a special case vehicle.

merlin



+1 (to not change current defaults).

I would tend to avoid columnar store "as default" because this would 
badly affect hundred of thousands of applications around the world. 
Columnar store should have its own niche, but certainly doesn't fit my 
needs.


Would you give a "option to change the store" is another history.

As I work with objects at programming side, and ORM works just so well, 
it is a really waste of time (and other resources) to change systems 
that are working well in the past 10 or more years.


Just my 2c,

Edson Richter





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Merlin Moncure]

On Mon, Apr 25, 2016 at 8:48 PM, Adam Brusselback
 wrote:
>>It is not difficult to simulate column store in a row store system if
>>you're willing to decompose your tables into (what is essentially)
>>BCNF fragments.  It simply is laborious for designers and programmers.
>
> I could see a true column store having much better performance than tricking
> a row based system into it.  Just think of the per-row overhead we currently
> have at 28 bytes per row.  Breaking up data manually like that may help a
> little, but if you don't have a very wide table to begin with, it could turn
> out you save next to nothing by doing so.  A column store wouldn't have this
> issue, and could potentially have much better performance.

FYI tuple header is 23 bytes, not 28 bytes
(http://www.postgresql.org/docs/9.5/static/storage-page-layout.html).
Personally I think column stores are a bit overrated.  They are faster
at certain things (in some cases much faster) but tend to put pretty
onerous requirements on application design so that they are very much
a special case vehicle.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Bráulio Bhavamitra]

Alvaro, is this related or dependent on
https://www.pgcon.org/2016/schedule/events/920.en.html ?

On Mon, Apr 25, 2016 at 11:20 AM Alvaro Herrera 
wrote:

> Bráulio Bhavamitra wrote:
> > Hi all,
> >
> > 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?
>
> This is a pretty interesting question.  I wrote an answer, then thought
> it would make a good blog post, so it's at
> http://blog.2ndquadrant.com/column-store-plans/
> I reproduce it below.
>
> Completely replacing the current row-based store wouldn't be a good
> idea: it has served us extremely well and I’m pretty sure that replacing
> it entirely with a columnar store would be disastrous performance-wise
> for OLTP use cases.
>
> That doesn't mean columnar stores are a bad idea in general -- because
> they aren't. They just have a more limited use case than "the whole
> database". For analytical queries on append-mostly data, a columnar
> store is a much more appropriate representation than the regular
> row-based store, but not all databases are analytical.
>
> However, in order to attain interesting performance gains you need to do
> a lot more than just change the underlying storage: you need to ensure
> that the rest of the system can take advantage of the changed
> representation, so that it can execute queries optimally; for instance,
> you may want aggregates that operate in a SIMD mode rather than
> one-value-at-a-time as it is today. This, in itself, is a large
> undertaking, and there are other challenges too.
>
> As it turns out, there's a team at 2ndQuadrant working precisely on
> these matters. We posted a patch last year, but it wasn’t terribly
> interesting -— it only made a single-digit percentage improvement in
> TPC-H scores; not enough to bother the development community with (it
> was a fairly invasive patch). We want more than that.
>
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.
>
> We don’t have a timescale yet. Stay tuned.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Bruce Momjian]

On Mon, Apr 25, 2016 at 11:20:11AM -0300, Alvaro Herrera wrote:
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.

I am coming late to this thread, but one item not discussed about
columnar storage is the use of compression of identical column values
across rows.  Existing Postgres storage only compresses single values,
not values across rows.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[George Neuner]

On Mon, 25 Apr 2016 21:48:44 -0400, Adam Brusselback
 wrote:

>>It is not difficult to simulate column store in a row store system if
>>you're willing to decompose your tables into (what is essentially)
>>BCNF fragments.  It simply is laborious for designers and programmers.
>
>I could see a true column store having much better performance than
>tricking a row based system into it.  Just think of the per-row overhead we
>currently have at 28 bytes per row.  Breaking up data manually like that
>may help a little, but if you don't have a very wide table to begin with,
>it could turn out you save next to nothing by doing so.  A column store
>wouldn't have this issue, and could potentially have much better
>performance.

A column store must be able to distinguish entries in the column
[which may be non-unique] as well as join the columns of the
fragmented virtual table to reconstruct its rows.  

These requirements dictate that a "column" be at least a triple:

{ id, table_row, data }

so there is no space saving WRT row store - the opposite in fact:
column store usually requires more space.

Column store enhances performance mainly by not fetching and not
caching unused data.  And standard practices like controlling the
physical locations of tables help both row and column store systems.

George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Adam Brusselback]

>It is not difficult to simulate column store in a row store system if
>you're willing to decompose your tables into (what is essentially)
>BCNF fragments.  It simply is laborious for designers and programmers.

I could see a true column store having much better performance than
tricking a row based system into it.  Just think of the per-row overhead we
currently have at 28 bytes per row.  Breaking up data manually like that
may help a little, but if you don't have a very wide table to begin with,
it could turn out you save next to nothing by doing so.  A column store
wouldn't have this issue, and could potentially have much better
performance.

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[George Neuner]

On Thu, 21 Apr 2016 09:08:22 -0700, "David G. Johnston"
 wrote:

>?I have little experience (and nothing practical) with columnar store but
>at a high level I don't see the point.  

At the high level, it's about avoiding fetching data you don't need.
In a row store system, in general you must fetch the whole row to
extract any of its columns.

It is not difficult to simulate column store in a row store system if
you're willing to decompose your tables into (what is essentially)
BCNF fragments.  It simply is laborious for designers and programmers.


>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.  

+1

>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.

If you are willing to go to BCNF and manage the physical location of
your tables [which any performance system will be doing anyway], then
any decent row store system can mix in "column" tables where desired.

IMO, the only real added value of a dedicated column store system is
to developers: the automagic table fragmentation and the ability to
query virtual tables rather than specify table fragments individually.
Convenient, but not necessary.

YMMV,
George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Merlin Moncure]

On Mon, Apr 25, 2016 at 9:20 AM, Alvaro Herrera
 wrote:
> Bráulio Bhavamitra wrote:
>> Hi all,
>>
>> 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?
>
> This is a pretty interesting question.  I wrote an answer, then thought
> it would make a good blog post, so it's at
> http://blog.2ndquadrant.com/column-store-plans/
> I reproduce it below.
>
> Completely replacing the current row-based store wouldn't be a good
> idea: it has served us extremely well and I’m pretty sure that replacing
> it entirely with a columnar store would be disastrous performance-wise
> for OLTP use cases.
>
> That doesn't mean columnar stores are a bad idea in general -- because
> they aren't. They just have a more limited use case than "the whole
> database". For analytical queries on append-mostly data, a columnar
> store is a much more appropriate representation than the regular
> row-based store, but not all databases are analytical.
>
> However, in order to attain interesting performance gains you need to do
> a lot more than just change the underlying storage: you need to ensure
> that the rest of the system can take advantage of the changed
> representation, so that it can execute queries optimally; for instance,
> you may want aggregates that operate in a SIMD mode rather than
> one-value-at-a-time as it is today. This, in itself, is a large
> undertaking, and there are other challenges too.
>
> As it turns out, there's a team at 2ndQuadrant working precisely on
> these matters. We posted a patch last year, but it wasn’t terribly
> interesting -— it only made a single-digit percentage improvement in
> TPC-H scores; not enough to bother the development community with (it
> was a fairly invasive patch). We want more than that.
>
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.
>
> We don’t have a timescale yet. Stay tuned.

Please keep us posted.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Bráulio Bhavamitra]

On Mon, Apr 25, 2016 at 11:20 AM Alvaro Herrera 
wrote:

> Bráulio Bhavamitra wrote:
> > Hi all,
> >
> > 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?
>
> This is a pretty interesting question.  I wrote an answer, then thought
> it would make a good blog post, so it's at
> http://blog.2ndquadrant.com/column-store-plans/
> I reproduce it below.
>
> Completely replacing the current row-based store wouldn't be a good
> idea: it has served us extremely well and I’m pretty sure that replacing
> it entirely with a columnar store would be disastrous performance-wise
> for OLTP use cases.
>
> That doesn't mean columnar stores are a bad idea in general -- because
> they aren't. They just have a more limited use case than "the whole
> database". For analytical queries on append-mostly data, a columnar
> store is a much more appropriate representation than the regular
> row-based store, but not all databases are analytical.
>
> However, in order to attain interesting performance gains you need to do
> a lot more than just change the underlying storage: you need to ensure
> that the rest of the system can take advantage of the changed
> representation, so that it can execute queries optimally; for instance,
> you may want aggregates that operate in a SIMD mode rather than
> one-value-at-a-time as it is today. This, in itself, is a large
> undertaking, and there are other challenges too.
>
> As it turns out, there's a team at 2ndQuadrant working precisely on
> these matters. We posted a patch last year, but it wasn’t terribly
> interesting -— it only made a single-digit percentage improvement in
> TPC-H scores; not enough to bother the development community with (it
> was a fairly invasive patch). We want more than that.
>
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.
>
Nice Alvaro, I think that's the right approach.

Wish a good work for you on that :)

cheers,
bráulio

>
> We don’t have a timescale yet. Stay tuned.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Alvaro Herrera]

Bráulio Bhavamitra wrote:
> Hi all,
> 
> 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?

This is a pretty interesting question.  I wrote an answer, then thought
it would make a good blog post, so it's at
http://blog.2ndquadrant.com/column-store-plans/
I reproduce it below.

Completely replacing the current row-based store wouldn't be a good
idea: it has served us extremely well and I’m pretty sure that replacing
it entirely with a columnar store would be disastrous performance-wise
for OLTP use cases.

That doesn't mean columnar stores are a bad idea in general -- because
they aren't. They just have a more limited use case than "the whole
database". For analytical queries on append-mostly data, a columnar
store is a much more appropriate representation than the regular
row-based store, but not all databases are analytical.

However, in order to attain interesting performance gains you need to do
a lot more than just change the underlying storage: you need to ensure
that the rest of the system can take advantage of the changed
representation, so that it can execute queries optimally; for instance,
you may want aggregates that operate in a SIMD mode rather than
one-value-at-a-time as it is today. This, in itself, is a large
undertaking, and there are other challenges too.

As it turns out, there's a team at 2ndQuadrant working precisely on
these matters. We posted a patch last year, but it wasn’t terribly
interesting -— it only made a single-digit percentage improvement in
TPC-H scores; not enough to bother the development community with (it
was a fairly invasive patch). We want more than that.

In our design, columnar or not is going to be an option: you're going to
be able to say "Dear server, for this table kindly set up columnar
storage for me, would you? Thank you very much." And then you’re going
to get a table which may be slower for regular usage but which will rock
for analytics. For most of your tables the current row-based store will
still likely be the best option, because row-based storage is much
better suited to the more general cases.

We don’t have a timescale yet. Stay tuned. 

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Guyren Howe]

On Apr 22, 2016, at 15:03 , Merlin Moncure  wrote:
> 
> On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra  
> 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.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Merlin Moncure]

On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra  wrote:
> Hi all,
>
> 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.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[jje]

An interesting topic we have also discussed in our team.

Realistically - this is more about picking the right software for the job.

PostgreSQL has come so far up in it's performance for more general workloads
that it is fast becoming a bit of a darling in the world of cloud - being
able to handle lots of db's and their associated web (fast response
required) queries. So to start to take it down this path I believe would be
detrimental.

There are plenty of DB's out there designed for exactly what you are trying
to do - so maybe a better option is to build yourself someway of having data
stored in another system where you require this sort of data handling.

Some of us are already doing that with great success - so maybe asking how
you accomplish that would be a better question.

Jonathan




--
View this message in context: 
http://postgresql.nabble.com/Columnar-store-as-default-for-PostgreSQL-10-tp5899731p5899973.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Bráulio Bhavamitra]

On Thu, Apr 21, 2016 at 1:39 PM Geoff Winkless  wrote:

> On 21 April 2016 at 17:08, David G. Johnston 
> 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)
>

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Geoff Winkless]

On 21 April 2016 at 17:08, David G. Johnston  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)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[David G. Johnston]

On Thu, Apr 21, 2016 at 3:08 AM, Bráulio Bhavamitra 
wrote:

> Hi all,
>
> 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.
>

​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.

​
​David J.
​

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Francisco Olarte]

Hi Bráulio:

On Thu, Apr 21, 2016 at 12:08 PM, Bráulio Bhavamitra
 wrote:
> 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.

And have you read anything about the drawbacks of columnar? They are
there, but writing about them does not makes the headlines.

> 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?

An option may be good ( may, not sure because nothing is free. More
complex code, more bug surface, some time will be eaten managing the
extra complexity, less developer time available for each feature, ...
) , but IMHO a complete move would be bad. Columnar is not that good
for a lot of postgres usages. If columnar were the silver bullet
everybody would be doing it.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general