Re: In-Memory Columnar Store

Hello!

Implementation of IMCS itself took me about two months (with testing and 
writing documentation).
But huge part of the code was previously written by me for other 
projects, so I have reused them.
Most of the time I have spent in integration of this code with 
PostgreSQL (I was not so familiar with it before).

Certainly implementations of columnar store for Oracle (Oracle Database 
In-Memory Option), DB2 (BLU Acceleration), ... are more convenient for 
users: them can execute normal SQL queries and do not require users to 
learn new functions and approach. But it requires complete redesign of 
query engine (or providing alternative implementation). I was not able 
to do it.

This is why I try to provide advantages of vertical data representation 
(vector operation, parallel execution, data skipping) as well as 
advantages of fast access to in-memory data as standard PostgreSQL 
extension. There are obviously some limitations and queries look more 
complicated than in case of standard SQL...

But from the other side it is possible to write queries which are hardly 
to be expressed using standard SQL.
For example calculating split-adjusted prices can not be done in SQL 
without using stored procedures.
To make usage of IMCS functions as simple as possible I defined a larger 
number of various operators for most popular operations.
For example Volume-Weighted-Average-Price can be calculated just as:

select Volume//Close as VWAP from Quote_get();

It is even shore than analog SQL statement:

select sum(Close*Volume)/sum(Volume) as VWAP from Quote;


Concerning integration with PostgreSQL, there were several problems. 
Some of them seems to have no easy solution, but other are IMHO 
imperfections in PostgreSQL which I hope will be fixed sometime:

1. Calls in PL/pgSQL are very slow - about 1-2 micsroseconds at my 
computer. Just defining insertion per-row trigger with empty procedure 
increase time of insertion of 6 million records twice - from 7 till 15 
seconds. If trigger procedure is not empty, then time is increased 
proportionally number of performed calls.
In my case inserting data with propagation it in columnar store using 
trigger takes about 80 seconds. But if I first load data without 
triggers in PostgreSQL table and then
insert it in columnar store using load function (implemented in C), then 
time will be 7+9=16 seconds.

Certainly I realize that plpgsql is interpreted language. But for 
example also interpreted Python is able to do 100 times more calls per 
second.
Unfortunately profiler doesn;t show some bottleneck - looks like long 
calltime is caused by large overhead of initializing and resetting 
memory context and copying arguments data.

2. Inefficient implementation of expanding composite type columns using 
(foo()).* clause. In this case function foo() will be invoked as much 
times as there are fields in the returned composite type. Even in case 
of placing call in FROM list (thanks to lateral joins in 9.3), 
PostgreSQL still sometimes performs redundant calls which can be avoided 
using hack with adding "OFFSET 1" clause.

3. 256Gb limit for used shared memory segment size at Linux.

Concerning last problem - I have included in IMCS distributive much 
simpler patch which just set MAP_HUGETLB flags when
a) is it defined in system headers
b) requested memory size is larger than 256Gb

In this case right now PostgreSQL will just fail to start.
But certainly it is more correct to trigger this flag through 
configuration parameter, because large pages can minimize MMU overhead 
and so increase speed even if size of used memory is less than 256Gb 
(this is why Oracle is widely using it).



. Вызов функции занимает прядка 2 микросекунд. Т.е. если я напишу 
триггер с пустой процедурой, то вставка 6 миллионов объектов займёт 15 
секунд. Это при том, что без триггера вставка занимает всего 7 секунд...


On 12/11/2013 06:33 PM, Merlin Moncure wrote:
> On Mon, Dec 9, 2013 at 1:40 PM, knizhnik <knizhnik@garret.ru> wrote:
>> Hello!
>>
>> I want to annouce my implementation of In-Memory Columnar Store extension
>> for PostgreSQL:
>>
>>       Documentation: http://www.garret.ru/imcs/user_guide.html
>>       Sources: http://www.garret.ru/imcs-1.01.tar.gz
>>
>> Any feedbacks, bug reports and suggestions are welcome.
>>
>> Vertical representation of data is stored in PostgreSQL shared memory.
>> This is why it is important to be able to utilize all available physical
>> memory.
>> Now servers with Tb or more RAM are not something exotic, especially in
>> financial world.
>> But there is limitation in Linux with standard 4kb pages  for maximal size
>> of mapped memory segment: 256Gb.
>> It is possible to overcome this limitation either by creating multiple
>> segments - but it requires too much changes in PostgreSQL memory manager.
>> Or just set MAP_HUGETLB flag (assuming that huge pages were allocated in the
>> system).
>>
>> I found several messages related with MAP_HUGETLB flag, the most recent one
>> was from 21 of November:
>> http://www.postgresql.org/message-id/20131125032920.GA23793@toroid.org
>>
>> I wonder what is the current status of this patch?
> I looked over your extension.  I think it's a pretty amazing example
> of the postgres extension and type systems -- up there with postgis.
> Very well done.  How long did this take you to write?
>
> MAP_HUGETLB patch was marked 'returned with feedback'.
> https://commitfest.postgresql.org/action/patch_view?id=1308.  It seems
> likely to be revived, perhaps in time for 9.4.
>
> Honestly, I think your efforts here provide more argument for adding
> huge tbl support.
>
> merlin