Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[David Steele]

On 3/4/17 8:33 AM, Peter Eisentraut wrote:
> On 3/3/17 16:16, David Steele wrote:
>> While this looks like it could be a really significant performance
>> improvement, I think the above demonstrates that it needs a lot of work.
>>  I know this is not new to the 2017-03 CF but it doesn't seem enough
>> progress has been made since posting to allow it to be committed in time
>> for v10.
>>
>> I recommend moving this patch to the 2017-07 CF.
> 
> I think the patch that was in 2017-01 was given some feedback that put
> the fundamental approach in question, which the author appeared to agree
> with.  So I don't know why this patch appeared in this CF at all.

Then it sounds like it should be marked RWF.  Haribabu can resubmit when
there's a new candidate patch.

-- 
-David
da...@pgmasters.net


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Peter Eisentraut]

On 3/3/17 16:16, David Steele wrote:
> While this looks like it could be a really significant performance
> improvement, I think the above demonstrates that it needs a lot of work.
>  I know this is not new to the 2017-03 CF but it doesn't seem enough
> progress has been made since posting to allow it to be committed in time
> for v10.
> 
> I recommend moving this patch to the 2017-07 CF.

I think the patch that was in 2017-01 was given some feedback that put
the fundamental approach in question, which the author appeared to agree
with.  So I don't know why this patch appeared in this CF at all.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[David Steele]

On 2/13/17 8:59 PM, Haribabu Kommi wrote:

> The current patch that I shared doesn't contains the plan and executor
> changes to show
> the performance benefit of the clustered index. we used custom plan to
> generate the plan
> for the clustered index. Currently I am working on it to rebase it to
> current master and
> other necessary changes.
> 
> In the current state of the patch, I cannot take any performance tests,
> as it needs some
> major changes according to the latest PostgreSQL version. I have an old
> performance
> report that is took on 9.5 attached for your reference.
> 
> The current patch that is shared is to find out the best approach in
> developing a columnar
> storage in PostgreSQL, by adopting Index access methods + additional
> hooks or pluggable
> storage access methods?

While this looks like it could be a really significant performance
improvement, I think the above demonstrates that it needs a lot of work.
 I know this is not new to the 2017-03 CF but it doesn't seem enough
progress has been made since posting to allow it to be committed in time
for v10.

I recommend moving this patch to the 2017-07 CF.

-- 
-David
da...@pgmasters.net


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Haribabu Kommi]

On Tue, Feb 14, 2017 at 2:57 AM, Konstantin Knizhnik <
k.knizh...@postgrespro.ru> wrote:

> Hi,
>
> I wonder if it is possible to somehow benchmark your clustered index
> implementation.
> I tried to create VCI index for lineitem table from TPC and run Q6 query.
> After index creation Postgres is not using parallel execution plan any
> more but speed of sequential plan is not changed
> and nothing in query execution plan indicates that VCI index is used:
>
>
> postgres=# explain select
> sum(l_extendedprice*l_discount) as revenue
> from
> lineitem_projection
> where
> l_shipdate between '1996-01-01' and '1997-01-01'
> and l_discount between 0.08 and 0.1
> and l_quantity < 24;
>
>  QUERY
> PLAN
>
>
> 
> 
> ---
> 
> -
>  Finalize Aggregate  (cost=608333.85..608333.86 rows=1 width=4)
>->  Gather  (cost=608333.23..608333.84 rows=6 width=4)
>  Workers Planned: 6
>  ->  Partial Aggregate  (cost=607333.23..607333.24 rows=1 width=4)
>->  Parallel Seq Scan on lineitem_projection
> (cost=0.00..607024.83 rows=61680 width=8)
>  Filter: ((l_shipdate >= '1996-01-01'::date) AND
> (l_shipdate <= '1997-01-01'::date) AND (l_discount >= '0.08'::double
> precision) AN
> D (l_discount <= '0.1'::double precision) AND (l_quantity < '24'::double
> precision))
> (6 rows)
>
> postgres=# select
> sum(l_extendedprice*l_discount) as revenue
> from
> lineitem_projection
> where
> l_shipdate between '1996-01-01' and '1997-01-01'
> and l_discount between 0.08 and 0.1
> and l_quantity < 24;
>revenue
> -
>  6.2e+08
> (1 row)
>
> Time: 1171.324 ms (00:01.171)
>
> postgres=# create index vci_idx on lineitem_projection using
> vci(l_shipdate,l_quantity,l_extendedprice,l_discount,l_tax,
> l_returnflag,l_linestatus);
> CREATE INDEX
> Time: 4.705 ms
>
>
> postgres=# explain select
> * from
> lineitem_projection
> where
> l_shipdate between '1996-01-01' and '1997-01-01'
> and l_discount between 0.08 and 0.1
> and l_quantity < 24;
>
> QUERY
> PLAN
>
> 
> 
> ---
> ---
>  Seq Scan on lineitem_projection  (cost=0.00..382077.00 rows=1 width=22)
>Filter: ((l_shipdate >= '1996-01-01'::date) AND (l_shipdate <=
> '1997-01-01'::date) AND (l_discount >= '0.08'::double precision) AND
> (l_discount <= '
> 0.1'::double precision) AND (l_quantity < '24'::double precision))
> (2 rows)
>
> postgres=# select
>
>
> sum(l_extendedprice*l_discount) as revenue
> from
> lineitem_projection
> where
> l_shipdate between '1996-01-01' and '1997-01-01'
> and l_discount between 0.08 and 0.1
> and l_quantity < 24;
>   revenue
> 
>  6.2112e+08
> (1 row)
>
> Time: 4304.355 ms (00:04.304)
>
>
> I wonder if there is any query which can demonstrate advantages of using
> VCI index?
>

The current patch that I shared doesn't contains the plan and executor
changes to show
the performance benefit of the clustered index. we used custom plan to
generate the plan
for the clustered index. Currently I am working on it to rebase it to
current master and
other necessary changes.

In the current state of the patch, I cannot take any performance tests, as
it needs some
major changes according to the latest PostgreSQL version. I have an old
performance
report that is took on 9.5 attached for your reference.

The current patch that is shared is to find out the best approach in
developing a columnar
storage in PostgreSQL, by adopting Index access methods + additional hooks
or pluggable
storage access methods?

The only problem I can think of pluggable storage methods is, to use the
proper benefits of
columnar storage, the planner and executor needs to be changed to support
vector processing,
But whereas in the current model, we implemented the same with custom plan
and additional
hooks. The same may be possible with pluggable storage methods also.


Regards,
Hari Babu
Fujitsu Australia


VCI_DBT3_Query_Performance.xlsx
Description: MS-Excel 2007 spreadsheet

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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Konstantin Knizhnik]

Hi,

I wonder if it is possible to somehow benchmark your clustered index 
implementation.

I tried to create VCI index for lineitem table from TPC and run Q6 query.
After index creation Postgres is not using parallel execution plan any 
more but speed of sequential plan is not changed

and nothing in query execution plan indicates that VCI index is used:


postgres=# explain select
sum(l_extendedprice*l_discount) as revenue
from
lineitem_projection
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
QUERY PLAN

---
-
 Finalize Aggregate  (cost=608333.85..608333.86 rows=1 width=4)
   ->  Gather  (cost=608333.23..608333.84 rows=6 width=4)
 Workers Planned: 6
 ->  Partial Aggregate  (cost=607333.23..607333.24 rows=1 width=4)
   ->  Parallel Seq Scan on lineitem_projection 
(cost=0.00..607024.83 rows=61680 width=8)
 Filter: ((l_shipdate >= '1996-01-01'::date) AND 
(l_shipdate <= '1997-01-01'::date) AND (l_discount >= '0.08'::double 
precision) AN
D (l_discount <= '0.1'::double precision) AND (l_quantity < '24'::double 
precision))

(6 rows)

postgres=# select
sum(l_extendedprice*l_discount) as revenue
from
lineitem_projection
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
   revenue
-
 6.2e+08
(1 row)

Time: 1171.324 ms (00:01.171)

postgres=# create index vci_idx on lineitem_projection using 
vci(l_shipdate,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus);

CREATE INDEX
Time: 4.705 ms


postgres=# explain select
* from
lineitem_projection
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
QUERY PLAN

---
---
 Seq Scan on lineitem_projection  (cost=0.00..382077.00 rows=1 width=22)
   Filter: ((l_shipdate >= '1996-01-01'::date) AND (l_shipdate <= 
'1997-01-01'::date) AND (l_discount >= '0.08'::double precision) AND 
(l_discount <= '

0.1'::double precision) AND (l_quantity < '24'::double precision))
(2 rows)

postgres=# select
sum(l_extendedprice*l_discount) as revenue
from
lineitem_projection
where
l_shipdate between '1996-01-01' and '1997-01-01'
and l_discount between 0.08 and 0.1
and l_quantity < 24;
  revenue

 6.2112e+08
(1 row)

Time: 4304.355 ms (00:04.304)


I wonder if there is any query which can demonstrate advantages of using 
VCI index?


On 06.02.2017 04:26, Haribabu Kommi wrote:



On Fri, Feb 3, 2017 at 8:28 PM, Konstantin Knizhnik 
> wrote:


On 30.12.2016 06:55, Haribabu Kommi wrote:


Hi All,

Fujitsu was interested in developing a columnar storage extension
with minimal
changes the server backend.


We  in PostgresPRO are also very interested in developing vertical
storage (VS) for Postgres.
And after considering many alternatives, we came to the conclusion
that approach based on representing columnar store as access
method (index)
is the most promising one.

It allows to:
1. Implement VS as extension without affecting Postgres core.
2. Have both ROS and WOS.
3. Create multiple projections (as in Vertica).
4. Optimize insert speed by support batch inserts and use flexible
recovery model for VS.

So it is very similar with your approach. But there are few
differences:

1. Our intention is to completely eliminate changes in Postgres core.

You wrote:

Yes, it is a mix of both index and table access methods. The
current design
of Vertical clustered index needs both access methods, because of
this reason
we used both access methods.

But I still do not completely understand why it is not possible to
use VS in index only scans without any changes and standard
Postgres executor?
Why it is not possible to rely on standard rules of applying
indexes in Postgres optimizer based on costs provided by our AM
implementation?


In our storage design, we used TID-CRID map to identify a record in heap
to columnar storage. Because of HOT update, the new data will not be 
inserted
into indexes, but this will give problem to the columnar storage, so 
we added

a hook to insert index data even if the update is HOT.

And also we added another hook for initializing the parameters during the
execution.

Most of the other added hooks 

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Haribabu Kommi]

On Fri, Feb 3, 2017 at 8:28 PM, Konstantin Knizhnik <
k.knizh...@postgrespro.ru> wrote:

> On 30.12.2016 06:55, Haribabu Kommi wrote:
>
>
> Hi All,
>
> Fujitsu was interested in developing a columnar storage extension with
> minimal
> changes the server backend.
>
>
> We  in PostgresPRO are also very interested in developing vertical storage
> (VS) for Postgres.
> And after considering many alternatives, we came to the conclusion that
> approach based on representing columnar store as access method (index)
> is the most promising one.
>
> It allows to:
> 1. Implement VS as extension without affecting Postgres core.
> 2. Have both ROS and WOS.
> 3. Create multiple projections (as in Vertica).
> 4. Optimize insert speed by support batch inserts and use flexible
> recovery model for VS.
>
> So it is very similar with your approach. But there are few differences:
>
> 1. Our intention is to completely eliminate changes in Postgres core.
>
> You wrote:
>
> Yes, it is a mix of both index and table access methods. The current design
> of Vertical clustered index needs both access methods, because of this
> reason
> we used both access methods.
>
> But I still do not completely understand why it is not possible to use VS
> in index only scans without any changes and standard Postgres executor?
> Why it is not possible to rely on standard rules of applying indexes in
> Postgres optimizer based on costs provided by our AM implementation?
>

In our storage design, we used TID-CRID map to identify a record in heap
to columnar storage. Because of HOT update, the new data will not be
inserted
into indexes, but this will give problem to the columnar storage, so we
added
a hook to insert index data even if the update is HOT.

And also we added another hook for initializing the parameters during the
execution.

Most of the other added hooks can be replaced with existing hooks and adding
some extra code.


> 2. You are accessing VS pages through Postgres buffer manager. It
> certainly have a lot of advantages. First of all it significantly
> simplifies implementation of VS and allows to reuse Postgres cache and lock
> managers.
> But is all leads to some limitation:
> - For VS it is preferable to have larger pages (in Vertica size of page
> can be several megabytes).
> - VS is optimized for sequential access, so caching pages in buffer
> manager is no needed and can only cause leaching of other useful pages from
> cache.
> - It makes it not possible to implement in-memory version of VS.
> - Access to buffer manager adds extra synchronization overhead which
> becomes noticeable at MPP systems.
>
> So I wonder if you have considered approach with VS specific
> implementation of storage layer?
>

Currently, we are just using the existing the PostgreSQL buffer manager
and didn't evaluate any columnar storage specific storage implementation.

we are having some plan of evaluating dynamic shared memory.


> 3. To take all advantages of vertical model, we should provide vector
> execution.
> Without it columnar store can only reduce amount of fetched data by
> selective fetch of accessed columns and better compression of them.
> But this is what existed cstore_fdw extension for Postgres also does.
>
> We are going to use executor hooks or custom nodes to implement vector
> operations for some nodes (filter, grand aggregate, aggregation with group
> by,...).
> Something similar with  https://github.com/citusdata/
> postgres_vectorization_test
>
> What is your vision of optimizing executor to work with VS?
>

Yes, we implemented similar like above by copy/paste the most of the
aggregate and etc code
into the extension for providing the vector execution support.

Without this vector execution and parallelism support, there will not be
much performance
benefit.

4. How do you consider adding parallelism support to VS? Should it be
> handled inside VS implementation? Or should we use standard Postgres
> parallel execution (parallel index-only scan)?
>
>
Currently we implemented our own parallelism in columnar storage with some
base infrastructure
of OSS, but we are planning to change/integrate according to the OSS
implementation.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Konstantin Knizhnik]

On 30.12.2016 06:55, Haribabu Kommi wrote:


Hi All,

Fujitsu was interested in developing a columnar storage extension with 
minimal

changes the server backend.


We  in PostgresPRO are also very interested in developing vertical 
storage (VS) for Postgres.
And after considering many alternatives, we came to the conclusion that 
approach based on representing columnar store as access method (index)

is the most promising one.

It allows to:
1. Implement VS as extension without affecting Postgres core.
2. Have both ROS and WOS.
3. Create multiple projections (as in Vertica).
4. Optimize insert speed by support batch inserts and use flexible 
recovery model for VS.


So it is very similar with your approach. But there are few differences:

1. Our intention is to completely eliminate changes in Postgres core.

You wrote:
Yes, it is a mix of both index and table access methods. The current 
design
of Vertical clustered index needs both access methods, because of this 
reason

we used both access methods.
But I still do not completely understand why it is not possible to use 
VS in index only scans without any changes and standard Postgres executor?
Why it is not possible to rely on standard rules of applying indexes in 
Postgres optimizer based on costs provided by our AM implementation?



2. You are accessing VS pages through Postgres buffer manager. It 
certainly have a lot of advantages. First of all it significantly 
simplifies implementation of VS and allows to reuse Postgres cache and 
lock managers.

But is all leads to some limitation:
- For VS it is preferable to have larger pages (in Vertica size of page 
can be several megabytes).
- VS is optimized for sequential access, so caching pages in buffer 
manager is no needed and can only cause leaching of other useful pages 
from cache.

- It makes it not possible to implement in-memory version of VS.
- Access to buffer manager adds extra synchronization overhead which 
becomes noticeable at MPP systems.


So I wonder if you have considered approach with VS specific 
implementation of storage layer?


3. To take all advantages of vertical model, we should provide vector 
execution.
Without it columnar store can only reduce amount of fetched data by 
selective fetch of accessed columns and better compression of them.

But this is what existed cstore_fdw extension for Postgres also does.

We are going to use executor hooks or custom nodes to implement vector 
operations for some nodes (filter, grand aggregate, aggregation with 
group by,...).
Something similar with 
https://github.com/citusdata/postgres_vectorization_test


What is your vision of optimizing executor to work with VS?

4. How do you consider adding parallelism support to VS? Should it be 
handled inside VS implementation? Or should we use standard Postgres 
parallel execution (parallel index-only scan)?


Thanks in advance,
Kosntantin




The columnar store is implemented as an extension using index access 
methods.
This can be easily enhanced with pluggable storage methods once they 
are available.


A new index method (VCI) is added to create columnar index on the table.

The following is the basic design idea of the columnar extension,

This has the on-disk columnar representation. So, even after crash,
the columnar format is recovered to the state when it was crashed.

To provide performance benefit for both read and write operations,
the data is stored in two formats

1) write optimized storage (WOS)
2) read optimized storage (ROS).

This is useful for the users where there is a great chance of data 
modification

that is newly added instead of the old data.

WOS


write optimized storage is the data of all columns that are part of 
VCI are

stored in a row wise format. All the newly added data is stored in WOS
relation with xmin/xmax information also. If user wants to 
update/delete the

newly added data, it doesn't affect the performance much compared to
deleting the data from columnar storage.

The tuples which don't have multiple copies or frozen data will be moved
from WOS to ROS periodically by the background worker process or autovauum
process. Every column data is stored separately in it's relation file. 
There

is no transaction information is present in ROS. The data in ROS can be
referred with tuple ID.

In this approach, the column data is present in both heap and columnar
storage.

ROS


This is the place, where all the column data is stored in columnar format.
The data from WOS to ROS is converted by background workers 
continously based
on the tuple visibility check. Whenever the tuple is frozen and it 
gets moved

from WOS to ROS.

The Data in ROS is stored in extents. One extent contains of 262,144 
rows. Because
of fixed number of records in an extent it is easy to map the heap 
record to the columnar

record with TID to CRID map.

Insert
=

The insert operation is just like inserting a data into an index.

Select
=

Because of two storage formats, 

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Michael Paquier]

On Fri, Dec 30, 2016 at 12:55 PM, Haribabu Kommi
 wrote:
> Any Comments on the approach?

I have moved this patch to CF 2017-03.
-- 
Michael


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Jim Nasby]

On 1/16/17 10:09 PM, Haribabu Kommi wrote:

Yes, that' correct. Currently with this approach, it is not possible to
ditch the
heap completely. This approach is useful for the cases, where the user wants
to store only some columns as part of clustered index.


Ahh, that's unfortunate. Billion row+ tables are becoming rather common, 
and that 24GB of overhead starts becoming very painful. It's actually a 
lot worse considering there will be at least one index on the table, so 
100GB+ of overhead isn't that uncommon.



Another complication is that one of the big advantages of a CSTORE
is allowing analysis to be done efficiently on a column-by-column
(as opposed to row-by-row) basis. Does your patch by chance provide
that?

Not the base patch that I shared. But the further patches provides the
data access
column-by-column basis using the custom plan methods.


Great, that's something else that a column store really needs to be 
successful. Something else I suspect is necessary is a faster/better way 
to eliminate chunks of rows from scans.


Just as an example, with my simple array-based approach, you can store a 
range type along with each array that contains the min and max values 
for the array. That means any query that wants values between 50 and 100 
can include a clause that filters on range types that overlap with 
[50,100]. That can be indexed very efficiently and is fast to run checks 
against.



Generally speaking, I do think the idea of adding support for this
as an "index" is a really good starting point, since that part of


... as discussed elsewhere in the thread, adding a bunch of hooks is 
probably not a good way to do this. :/



That would be a great way to gain knowledge on what users would want
to see in a column store, something else I suspect we need. It would
also be far less code than what you or Alvaro are proposing. When it
comes to large changes that don't have crystal-clear requirements, I
think that's really important.

The  main use case of this patch is to support mixed load environments,
where both OLTP and OLAP queries are possible. The advantage of
proposed patch design is, providing good performance to OLAP queries
without affecting OLTP.


Yeah, that's a big part of what I was envisioning with my array-based 
approach. In simple terms, there would be a regular row-based table, and 
an array-based table, with a view that allows seamless querying into 
both (re-presenting the array-storage on a per-row basis). There would 
be a periodic process that moves entire sets of rows from the row 
storage into the array storage.


If you updated or deleted a row that was part of an array, the contents 
of the entire array could be moved back into row-based storage. After a 
period of time, rows would get moved back into array storage. Or the 
array could be modified in place, but you need to be very careful about 
bloating the array storage if you do that.


The big missing piece here is getting the planner to intelligently 
handle a mixed row/column store. As I mentioned, you can easily add 
range type fields to greatly increase performance, but they won't do any 
good unless the appropriate filters get added. It's not THAT hard to do 
that by hand, but it'd be great if there was a more automated method. 
Such a method might also be very useful for transforming expressions 
like date_part('quarter', ...) into something that could use existing 
indexes.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Haribabu Kommi]

On Wed, Jan 18, 2017 at 2:25 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 12/29/16 10:55 PM, Haribabu Kommi wrote:
> > Fujitsu was interested in developing a columnar storage extension with
> > minimal
> > changes the server backend.
> >
> > The columnar store is implemented as an extension using index access
> > methods.
> > This can be easily enhanced with pluggable storage methods once they are
> > available.
> >
> > A new index method (VCI) is added to create columnar index on the table.
>
> I'm confused.  You say that you are adding an index access method, for
> which we have a defined extension mechanism, but the code doesn't do
> that.  Instead, it sprinkles a bunch of hooks through the table access
> code.  So you are really adding ways to add alternatives to heap
> storage, except we have no way to know whether these hooks have been
> designed with any kind of generality in mind.  So is it an index access
> method or a table access method?
>

Yes, it is a mix of both index and table access methods. The current design
of Vertical clustered index needs both access methods, because of this
reason
we used both access methods.

Either way, you shouldn't need a new relkind.  Note that all indexes
> have the same relkind, even if they use different access methods.
>
> I think there are two ways to integrate column storage into PostgreSQL:
> One is to use the FDW interface.  That has been done before, see
> cstore_fdw.  The other is to define a storage manager extension
> interface.  That has been tried but has not been completed yet.  Adding
> a bunch of custom hooks all over the place seems worse than both of those.
>

Thanks for your suggestion. Yes, I also agree that the best way to integrate
column storage for a better performance is through storage manager extension
interface.

It is better first try to finish the pluggable storage interface and
integrate this
columnar store is a good way to proceed.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Peter Eisentraut]

On 12/29/16 10:55 PM, Haribabu Kommi wrote:
> Fujitsu was interested in developing a columnar storage extension with
> minimal
> changes the server backend.
> 
> The columnar store is implemented as an extension using index access
> methods.
> This can be easily enhanced with pluggable storage methods once they are
> available.
> 
> A new index method (VCI) is added to create columnar index on the table.

I'm confused.  You say that you are adding an index access method, for
which we have a defined extension mechanism, but the code doesn't do
that.  Instead, it sprinkles a bunch of hooks through the table access
code.  So you are really adding ways to add alternatives to heap
storage, except we have no way to know whether these hooks have been
designed with any kind of generality in mind.  So is it an index access
method or a table access method?

Either way, you shouldn't need a new relkind.  Note that all indexes
have the same relkind, even if they use different access methods.

I think there are two ways to integrate column storage into PostgreSQL:
One is to use the FDW interface.  That has been done before, see
cstore_fdw.  The other is to define a storage manager extension
interface.  That has been tried but has not been completed yet.  Adding
a bunch of custom hooks all over the place seems worse than both of those.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Haribabu Kommi]

On Sun, Jan 8, 2017 at 2:01 PM, Jim Nasby  wrote:

> On 12/29/16 9:55 PM, Haribabu Kommi wrote:
>
>> The tuples which don't have multiple copies or frozen data will be moved
>> from WOS to ROS periodically by the background worker process or autovauum
>> process. Every column data is stored separately in it's relation file.
>> There
>> is no transaction information is present in ROS. The data in ROS can be
>> referred with tuple ID.
>>
>
> Would updates be handled via the delete mechanism you described then?
>

Updates are handled similar like delete operations, but there are some extra
index insert operations occurs in this index even when the update is of HOT
type, because of TID-CRID mapping.


> In this approach, the column data is present in both heap and columnar
>> storage.
>>
>
> ISTM one of the biggest reasons to prefer a column store over heap is to
> ditch the 24 byte overhead, so I'm not sure how much of a win this is.
>

Yes, that' correct. Currently with this approach, it is not possible to
ditch the
heap completely. This approach is useful for the cases, where the user wants
to store only some columns as part of clustered index.


Another complication is that one of the big advantages of a CSTORE is
> allowing analysis to be done efficiently on a column-by-column (as opposed
> to row-by-row) basis. Does your patch by chance provide that?
>

Not the base patch that I shared. But the further patches provides the data
access
column-by-column basis using the custom plan methods.


> Generally speaking, I do think the idea of adding support for this as an
> "index" is a really good starting point, since that part of the system is
> pluggable. It might be better to target getting only what needs to be in
> core into core to begin with, allowing the other code to remain an
> extension for now. I think there's a lot of things that will be discovered
> as we start moving into column stores, and it'd be very unfortunate to
> accidentally paint the core code into a corner somewhere.
>

Yes, it is possible to add only the code that is required in the core and
keep the other part
as extension. Without providing the complete clustered index approach, I
doubt whether
the necessary hooks and it's code gets accepted to the core.


> As a side note, it's possible to get a lot of the benefits of a column
> store by using arrays. I've done some experiments with that and got an
> 80-90% space reduction, and most queries saw improved performance as well
> (there were a few cases that weren't better). The biggest advantage to this
> approach is people could start using it today, on any recent version of
> Postgres.


Interesting experiment.


> That would be a great way to gain knowledge on what users would want to
> see in a column store, something else I suspect we need. It would also be
> far less code than what you or Alvaro are proposing. When it comes to large
> changes that don't have crystal-clear requirements, I think that's really
> important.
>

The  main use case of this patch is to support mixed load environments,
where both OLTP and OLAP queries are possible. The advantage of
proposed patch design is, providing good performance to OLAP queries
without affecting OLTP.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Haribabu Kommi]

On Sun, Jan 8, 2017 at 4:20 AM, Bruce Momjian  wrote:

> On Fri, Dec 30, 2016 at 02:55:39PM +1100, Haribabu Kommi wrote:
> >
> > Hi All,
> >
> > Fujitsu was interested in developing a columnar storage extension with
> minimal
> > changes the server backend.
> >
> > The columnar store is implemented as an extension using index access
> methods.
> > This can be easily enhanced with pluggable storage methods once they are
> > available.
>
> Have you see this post from 2015:
>
> https://www.postgresql.org/message-id/20150831225328.GM2912%
> 40alvherre.pgsql
>


Thanks for the information.
Yes, I already checked that mail thread. The proposal in that thread was
trying to add
the columnar storage in the core itself. The patch that is proposed is an
extension to
provide columnar storage with the help of index.

May be we can discuss the pros and cons in adding columnar store in the
core itself
or a pluggable storage approach.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Jim Nasby]

On 12/29/16 9:55 PM, Haribabu Kommi wrote:

The tuples which don't have multiple copies or frozen data will be moved
from WOS to ROS periodically by the background worker process or autovauum
process. Every column data is stored separately in it's relation file. There
is no transaction information is present in ROS. The data in ROS can be
referred with tuple ID.


Would updates be handled via the delete mechanism you described then?


In this approach, the column data is present in both heap and columnar
storage.


ISTM one of the biggest reasons to prefer a column store over heap is to 
ditch the 24 byte overhead, so I'm not sure how much of a win this is.


Another complication is that one of the big advantages of a CSTORE is 
allowing analysis to be done efficiently on a column-by-column (as 
opposed to row-by-row) basis. Does your patch by chance provide that?


Generally speaking, I do think the idea of adding support for this as an 
"index" is a really good starting point, since that part of the system 
is pluggable. It might be better to target getting only what needs to be 
in core into core to begin with, allowing the other code to remain an 
extension for now. I think there's a lot of things that will be 
discovered as we start moving into column stores, and it'd be very 
unfortunate to accidentally paint the core code into a corner somewhere.


As a side note, it's possible to get a lot of the benefits of a column 
store by using arrays. I've done some experiments with that and got an 
80-90% space reduction, and most queries saw improved performance as 
well (there were a few cases that weren't better). The biggest advantage 
to this approach is people could start using it today, on any recent 
version of Postgres. That would be a great way to gain knowledge on what 
users would want to see in a column store, something else I suspect we 
need. It would also be far less code than what you or Alvaro are 
proposing. When it comes to large changes that don't have crystal-clear 
requirements, I think that's really important.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


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

Re: [HACKERS] [WIP]Vertical Clustered Index (columnar store extension)

[Bruce Momjian]

On Fri, Dec 30, 2016 at 02:55:39PM +1100, Haribabu Kommi wrote:
> 
> Hi All,
> 
> Fujitsu was interested in developing a columnar storage extension with minimal
> changes the server backend.
> 
> The columnar store is implemented as an extension using index access methods.
> This can be easily enhanced with pluggable storage methods once they are
> available.

Have you see this post from 2015:


https://www.postgresql.org/message-id/20150831225328.GM2912%40alvherre.pgsql

-- 
  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-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers