Re: [HACKERS] Columnar storage support

Unfortunately C-Store doesn't allow to take all advantages of columnar 
store: you still not be able to perform vector operation.s
C-Store allows to reduce size of data read from the disk because of
1. fetching only columns which are used in the query,
2. data compression.

It will lead to some benefits in query execution speed for cold data 
(when it is not loaded in cache).
For warm data there is almost no difference (except very huge tables 
which can not fit in memory).

But the main advantage of vertical data format - vector data processing 
- is possible only with specialized executor.
There is prototype of vector executor for C-Store:
https://github.com/citusdata/postgres_vectorization_test
It provides 3-4x speedup of some queries, but it is really prototype and 
research project, for from practical usage.

I have also developed two columnar store extensions for Postgres:
IMCS (In-Memory-Columnar-Store): https://github.com/knizhnik/imcs.git
VOPS (Vectorized Operations): https://github.com/postgrespro/vops.git

First one is more oriented on in-memory databases (although support 
spilling data to the disk) and requires to use special functions to 
manipulate with columnar data.
In this case columnar store is copy of main (horizontal) store (normal 
Postgres tables).

VOPS is more recent work, allowing to use more or less normal SQL (using 
foreign data wrapper and user defined types/operators).
In VOPS data is stored inside normal Postgres tables, but using vectors 
(tiles) instead of scalars.

Both IMCS and VOPS provides 10-100 times speed improvement on queries 
like Q1 in TPC-H (sequential scan with filtering and aggregation).
In queries involving joins there is almost no benefit comparing with 
normal Postgres.

There is also columnar storage extension developed by Fujitsu:
https://www.postgresql.org/message-id/CAJrrPGfaC7WC9NK6PTTy6YN-NN+hCy8xOLAh2doYhVg5d6HsAA@mail.gmail.com
But the published patch is only first step in this direction and it is 
not possible neither to use it in practice, neither perform some 
experiments measuring possible improvement of performance.


On 09.10.2017 23:06, Joshua D. Drake wrote:
> On 10/09/2017 01:03 PM, legrand legrand wrote:
>> Is there a chance that pluggable storage permits creation of a 
>> columnar rdbms
>> as monetDB in PostgreSQL ?
>> Thanks un advance for thé answer
>
> The extension C-Store from Citus is probably what you are looking for.
>
> jD
>
>>
>>
>>
>> -- 
>> Sent from: 
>> http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
>>
>>
>
>

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



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