Subject: Re: Zedstore - compressed in-core columnar storage
To: Ashwin Agrawal <aagrawal@pivotal.io>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: 
 <CALfoeiuF-m5jg51mJUPm5GN8u396o5sA2AF5N97vTRAEDYac7w@mail.gmail.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <bf4b8f5f-90c9-025d-013c-87cf998204ad@postgrespro.ru>
Date: Tue, 9 Apr 2019 17:09:21 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: 
 <CALfoeiuF-m5jg51mJUPm5GN8u396o5sA2AF5N97vTRAEDYac7w@mail.gmail.com>
Content-Type: multipart/mixed;
 boundary="------------D6CCD1DCC011ADA75F1050A8"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------D6CCD1DCC011ADA75F1050A8
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

On 09.04.2019 3:27, Ashwin Agrawal wrote:
> Heikki and I have been hacking recently for few weeks to implement
> in-core columnar storage for PostgreSQL. Here's the design and initial
> implementation of Zedstore, compressed in-core columnar storage (table
> access method). Attaching the patch and link to github branch [1] to
> follow along.

Thank you for publishing this patch. IMHO Postgres is really missing 
normal support of columnar store and table access method
API is the best way of integrating it.

I wanted to compare memory footprint and performance of zedstore with 
standard Postgres heap and my VOPS extension.
As test data I used TPC-H benchmark (actually only one lineitem table 
generated with tpch-dbgen utility with scale factor 10 (~8Gb database).
I attached script which I have use to populate data (you have to to 
download, build and run tpch-dbgen yourself, also you can comment code 
related with VOPS).
Unfortunately I failed to load data in zedstore:

postgres=# insert into zedstore_lineitem_projection (select 
l_shipdate,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag::"char",l_linestatus::"char" 
from lineitem);
psql: ERROR:  compression failed. what now?
Time: 237804.775 ms (03:57.805)


Then I try to check if there is something in 
zedstore_lineitem_projection table:

postgres=# select count(*) from zedstore_lineitem_projection;
psql: WARNING:  terminating connection because of crash of another 
server process
DETAIL:  The postmaster has commanded this server process to roll back 
the current transaction and exit, because another server process exited 
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.
psql: server closed the connection unexpectedly
     This probably means the server terminated abnormally
     before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 145710.828 ms (02:25.711)


Backend consumes 16GB of RAM and 16Gb of swap and was killed by OOM 
killer (undo log?)
Subsequent attempt to run the same command is failed with the following 
error:

postgres=# select count(*) from zedstore_lineitem_projection;
psql: ERROR:  unexpected level encountered when descending tree


So the only thing I can do at this moment is report size of tables on 
the disk:

postgres=# select pg_relation_size('lineitem');
  pg_relation_size
------------------
       10455441408
(1 row)


postgres=# select pg_relation_size('lineitem_projection');
  pg_relation_size
------------------
        3129974784
(1 row)

postgres=# select pg_relation_size('vops_lineitem_projection');
  pg_relation_size
------------------
        1535647744
(1 row)

postgres=# select pg_relation_size('zedstore_lineitem_projection');
  pg_relation_size
------------------
        2303688704
(1 row)


But I do not know how much data was actually loaded in zedstore table...
Actually the main question is why this table is not empty if INSERT 
statement was failed?

Please let me know if I can somehow help you to reproduce and 
investigate the problem.

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


--------------D6CCD1DCC011ADA75F1050A8
Content-Type: application/sql;
 name="vstore_bench.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="vstore_bench.sql"

Y3JlYXRlIGV4dGVuc2lvbiB2b3BzOwoKY3JlYXRlIHRhYmxlIGxpbmVpdGVtKAogICBsX29y
ZGVya2V5IGludGVnZXIsCiAgIGxfcGFydGtleSBpbnRlZ2VyLAogICBsX3N1cHBrZXkgaW50
ZWdlciwKICAgbF9saW5lbnVtYmVyIGludGVnZXIsCiAgIGxfcXVhbnRpdHkgcmVhbCwKICAg
bF9leHRlbmRlZHByaWNlIHJlYWwsCiAgIGxfZGlzY291bnQgcmVhbCwKICAgbF90YXggcmVh
bCwKICAgbF9yZXR1cm5mbGFnICJjaGFyIiwKICAgbF9saW5lc3RhdHVzICJjaGFyIiwKICAg
bF9zaGlwZGF0ZSBkYXRlLAogICBsX2NvbW1pdGRhdGUgZGF0ZSwKICAgbF9yZWNlaXB0ZGF0
ZSBkYXRlLAogICBsX3NoaXBpbnN0cnVjdCBjaGFyKDI1KSwKICAgbF9zaGlwbW9kZSBjaGFy
KDEwKSwKICAgbF9jb21tZW50IGNoYXIoNDQpLAogICBsX2R1bW15IGNoYXIoMSkpOwoKLS0g
bGluZWl0ZW0udGJsJyBpcyBnZW5lcmF0ZWQgYnkgdHBjZy1nZW4gd2l0aCBzY2FsZSAxMDAK
Y29weSBsaW5laXRlbSBmcm9tICcvbW50L2RhdGEvbGluZWl0ZW0udGJsJyBkZWxpbWl0ZXIg
J3wnIGNzdjsKCmNyZWF0ZSB0YWJsZSBsaW5laXRlbV9wcm9qZWN0aW9uICgKICAgbF9zaGlw
ZGF0ZSBkYXRlIG5vdCBudWxsLAogICBsX3F1YW50aXR5IGZsb2F0NCBub3QgbnVsbCwKICAg
bF9leHRlbmRlZHByaWNlIGZsb2F0NCBub3QgbnVsbCwKICAgbF9kaXNjb3VudCBmbG9hdDQg
bm90IG51bGwsCiAgIGxfdGF4ICAgICAgZmxvYXQ0IG5vdCBudWxsLAogICBsX3JldHVybmZs
YWcgImNoYXIiIG5vdCBudWxsLAogICBsX2xpbmVzdGF0dXMgImNoYXIiIG5vdCBudWxsCik7
CgpjcmVhdGUgdGFibGUgdm9wc19saW5laXRlbV9wcm9qZWN0aW9uKAogICBsX3NoaXBkYXRl
IHZvcHNfZGF0ZSBub3QgbnVsbCwKICAgbF9xdWFudGl0eSB2b3BzX2Zsb2F0NCBub3QgbnVs
bCwKICAgbF9leHRlbmRlZHByaWNlIHZvcHNfZmxvYXQ0IG5vdCBudWxsLAogICBsX2Rpc2Nv
dW50IHZvcHNfZmxvYXQ0IG5vdCBudWxsLAogICBsX3RheCB2b3BzX2Zsb2F0NCBub3QgbnVs
bCwKICAgbF9yZXR1cm5mbGFnICJjaGFyIiBub3QgbnVsbCwKICAgbF9saW5lc3RhdHVzICJj
aGFyIiBub3QgbnVsbAopOwoKY3JlYXRlIHRhYmxlIHplZHN0b3JlX2xpbmVpdGVtX3Byb2pl
Y3Rpb24oCiAgIGxfc2hpcGRhdGUgdm9wc19kYXRlIG5vdCBudWxsLAogICBsX3F1YW50aXR5
IHZvcHNfZmxvYXQ0IG5vdCBudWxsLAogICBsX2V4dGVuZGVkcHJpY2Ugdm9wc19mbG9hdDQg
bm90IG51bGwsCiAgIGxfZGlzY291bnQgdm9wc19mbG9hdDQgbm90IG51bGwsCiAgIGxfdGF4
IHZvcHNfZmxvYXQ0IG5vdCBudWxsLAogICBsX3JldHVybmZsYWcgImNoYXIiIG5vdCBudWxs
LAogICBsX2xpbmVzdGF0dXMgImNoYXIiIG5vdCBudWxsCikgdXNpbmcgemVkc3RvcmU7CgoK
aW5zZXJ0IGludG8gbGluZWl0ZW1fcHJvamVjdGlvbiAoc2VsZWN0IGxfc2hpcGRhdGUsbF9x
dWFudGl0eSxsX2V4dGVuZGVkcHJpY2UsbF9kaXNjb3VudCxsX3RheCxsX3JldHVybmZsYWc6
OiJjaGFyIixsX2xpbmVzdGF0dXM6OiJjaGFyIiBmcm9tIGxpbmVpdGVtKTsKLS0gVGltZTog
MTA0MDY3LjcxNCBtcyAoMDE6NDQuMDY4KQoKc2VsZWN0IHBvcHVsYXRlKGRlc3RpbmF0aW9u
IDo9ICd2b3BzX2xpbmVpdGVtX3Byb2plY3Rpb24nOjpyZWdjbGFzcywgc291cmNlIDo9ICds
aW5laXRlbV9wcm9qZWN0aW9uJzo6cmVnY2xhc3MsIHNvcnQgOj0gJ2xfcmV0dXJuZmxhZyxs
X2xpbmVzdGF0dXMnKTsKLS0gVGltZTogMTQ1MjA3LjAxMiBtcyAoMDI6MjUuMjA3KQoKaW5z
ZXJ0IGludG8gemVkc3RvcmVfbGluZWl0ZW1fcHJvamVjdGlvbiAoc2VsZWN0IGxfc2hpcGRh
dGUsbF9xdWFudGl0eSxsX2V4dGVuZGVkcHJpY2UsbF9kaXNjb3VudCxsX3RheCxsX3JldHVy
bmZsYWc6OiJjaGFyIixsX2xpbmVzdGF0dXM6OiJjaGFyIiBmcm9tIGxpbmVpdGVtKTsKLS0g
VGltZTogMjM3ODA0Ljc3NSBtcyAoMDM6NTcuODA1KQo=
--------------D6CCD1DCC011ADA75F1050A8--