Subject: Re: Zedstore - compressed in-core columnar storage
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Ashwin Agrawal <aagrawal@pivotal.io>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
References: 
 <CALfoeiuF-m5jg51mJUPm5GN8u396o5sA2AF5N97vTRAEDYac7w@mail.gmail.com>
 <bf4b8f5f-90c9-025d-013c-87cf998204ad@postgrespro.ru>
Message-ID: <cd2a0ed0-698f-85c8-a775-e25caa3ceb5a@postgrespro.ru>
Date: Tue, 9 Apr 2019 18:00:39 +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: <bf4b8f5f-90c9-025d-013c-87cf998204ad@postgrespro.ru>
Content-Type: multipart/mixed;
 boundary="------------3E18CA31B27DC9A88E9EA76F"
Content-Language: en-US
Precedence: bulk

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


On 09.04.2019 17:09, Konstantin Knizhnik wrote:
> 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.
>

Looks like the original problem was caused by internal postgres 
compressor: I have not configured Postgres to use lz4.
When I configured Postgres --with-lz4, data was correctly inserted in 
zedstore table, but looks it is not compressed at all:

postgres=# select pg_relation_size('zedstore_lineitem_projection');
  pg_relation_size
------------------
        9363010640

No wonder that zedstore shows the worst results:

lineitem                                      6240.261 ms
lineitem_projection                    5390.446 ms
zedstore_lineitem_projection   23310.341 ms
vops_lineitem_projection             439.731 ms


Updated version of vstore_bench.sql is attached (sorry, there was some 
errors in previous version of this script).

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


--------------3E18CA31B27DC9A88E9EA76F
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
Y3Rpb24oCiAgIGxfc2hpcGRhdGUgZGF0ZSBub3QgbnVsbCwKICAgbF9xdWFudGl0eSBmbG9h
dDQgbm90IG51bGwsCiAgIGxfZXh0ZW5kZWRwcmljZSBmbG9hdDQgbm90IG51bGwsCiAgIGxf
ZGlzY291bnQgZmxvYXQ0IG5vdCBudWxsLAogICBsX3RheCAgICAgIGZsb2F0NCBub3QgbnVs
bCwKICAgbF9yZXR1cm5mbGFnICJjaGFyIiBub3QgbnVsbCwKICAgbF9saW5lc3RhdHVzICJj
aGFyIiBub3QgbnVsbAopIHVzaW5nIHplZHN0b3JlOwoKCmluc2VydCBpbnRvIGxpbmVpdGVt
X3Byb2plY3Rpb24gKHNlbGVjdCBsX3NoaXBkYXRlLGxfcXVhbnRpdHksbF9leHRlbmRlZHBy
aWNlLGxfZGlzY291bnQsbF90YXgsbF9yZXR1cm5mbGFnOjoiY2hhciIsbF9saW5lc3RhdHVz
OjoiY2hhciIgZnJvbSBsaW5laXRlbSk7Ci0tIFRpbWU6IDEwNDA2Ny43MTQgbXMgKDAxOjQ0
LjA2OCkKCnNlbGVjdCBwb3B1bGF0ZShkZXN0aW5hdGlvbiA6PSAndm9wc19saW5laXRlbV9w
cm9qZWN0aW9uJzo6cmVnY2xhc3MsIHNvdXJjZSA6PSAnbGluZWl0ZW1fcHJvamVjdGlvbic6
OnJlZ2NsYXNzLCBzb3J0IDo9ICdsX3JldHVybmZsYWcsbF9saW5lc3RhdHVzJyk7Ci0tIFRp
bWU6IDE0NTIwNy4wMTIgbXMgKDAyOjI1LjIwNykKCmluc2VydCBpbnRvIHplZHN0b3JlX2xp
bmVpdGVtX3Byb2plY3Rpb24gKHNlbGVjdCBsX3NoaXBkYXRlLGxfcXVhbnRpdHksbF9leHRl
bmRlZHByaWNlLGxfZGlzY291bnQsbF90YXgsbF9yZXR1cm5mbGFnOjoiY2hhciIsbF9saW5l
c3RhdHVzOjoiY2hhciIgZnJvbSBsaW5laXRlbSk7Ci0tIFRpbWU6IDM4MzMxMC4xNTAgbXMg
KDA2OjIzLjMxMCkKCnNldCBtYXhfcGFyYWxsZWxfd29ya2Vyc19wZXJfZ2F0aGVyID0gNDsK
c2V0IG1heF9wYXJhbGxlbF93b3JrZXJzID0gNDsKCi0tIE9yaWdpbmFsIHRhYmxlCnNlbGVj
dAogICAgbF9yZXR1cm5mbGFnLAogICAgbF9saW5lc3RhdHVzLAogICAgc3VtKGxfcXVhbnRp
dHkpIGFzIHN1bV9xdHksCiAgICBzdW0obF9leHRlbmRlZHByaWNlKSBhcyBzdW1fYmFzZV9w
cmljZSwKICAgIHN1bShsX2V4dGVuZGVkcHJpY2UqKDEtbF9kaXNjb3VudCkpIGFzIHN1bV9k
aXNjX3ByaWNlLAogICAgc3VtKGxfZXh0ZW5kZWRwcmljZSooMS1sX2Rpc2NvdW50KSooMSts
X3RheCkpIGFzIHN1bV9jaGFyZ2UsCiAgICBhdmcobF9xdWFudGl0eSkgYXMgYXZnX3F0eSwK
ICAgIGF2ZyhsX2V4dGVuZGVkcHJpY2UpIGFzIGF2Z19wcmljZSwKICAgIGF2ZyhsX2Rpc2Nv
dW50KSBhcyBhdmdfZGlzYywKICAgIGNvdW50KCopIGFzIGNvdW50X29yZGVyCmZyb20KICAg
IGxpbmVpdGVtCndoZXJlCiAgICBsX3NoaXBkYXRlIDw9ICcxOTk4LTEyLTAxJwpncm91cCBi
eQogICAgbF9yZXR1cm5mbGFnLAogICAgbF9saW5lc3RhdHVzCm9yZGVyIGJ5CiAgICBsX3Jl
dHVybmZsYWcsCiAgICBsX2xpbmVzdGF0dXM7Ci0tIFRpbWU6IDYyNDAuMjYxIG1zICgwMDow
Ni4yNDApCgotLSBTdGFuZGFyZCBwcm9qZWN0aW9uCnNlbGVjdAogICAgbF9yZXR1cm5mbGFn
LAogICAgbF9saW5lc3RhdHVzLAogICAgc3VtKGxfcXVhbnRpdHkpIGFzIHN1bV9xdHksCiAg
ICBzdW0obF9leHRlbmRlZHByaWNlKSBhcyBzdW1fYmFzZV9wcmljZSwKICAgIHN1bShsX2V4
dGVuZGVkcHJpY2UqKDEtbF9kaXNjb3VudCkpIGFzIHN1bV9kaXNjX3ByaWNlLAogICAgc3Vt
KGxfZXh0ZW5kZWRwcmljZSooMS1sX2Rpc2NvdW50KSooMStsX3RheCkpIGFzIHN1bV9jaGFy
Z2UsCiAgICBhdmcobF9xdWFudGl0eSkgYXMgYXZnX3F0eSwKICAgIGF2ZyhsX2V4dGVuZGVk
cHJpY2UpIGFzIGF2Z19wcmljZSwKICAgIGF2ZyhsX2Rpc2NvdW50KSBhcyBhdmdfZGlzYywK
ICAgIGNvdW50KCopIGFzIGNvdW50X29yZGVyCmZyb20KICAgIGxpbmVpdGVtX3Byb2plY3Rp
b24Kd2hlcmUKICAgIGxfc2hpcGRhdGUgPD0gJzE5OTgtMTItMDEnCmdyb3VwIGJ5CiAgICBs
X3JldHVybmZsYWcsCiAgICBsX2xpbmVzdGF0dXMKb3JkZXIgYnkKICAgIGxfcmV0dXJuZmxh
ZywKICAgIGxfbGluZXN0YXR1czsKLS0gVGltZTogNTM5MC40NDYgbXMgKDAwOjA1LjM5MCkK
Ci0tIFplZHN0b3JlIHByb2plY3Rpb24Kc2VsZWN0CiAgICBsX3JldHVybmZsYWcsCiAgICBs
X2xpbmVzdGF0dXMsCiAgICBzdW0obF9xdWFudGl0eSkgYXMgc3VtX3F0eSwKICAgIHN1bShs
X2V4dGVuZGVkcHJpY2UpIGFzIHN1bV9iYXNlX3ByaWNlLAogICAgc3VtKGxfZXh0ZW5kZWRw
cmljZSooMS1sX2Rpc2NvdW50KSkgYXMgc3VtX2Rpc2NfcHJpY2UsCiAgICBzdW0obF9leHRl
bmRlZHByaWNlKigxLWxfZGlzY291bnQpKigxK2xfdGF4KSkgYXMgc3VtX2NoYXJnZSwKICAg
IGF2ZyhsX3F1YW50aXR5KSBhcyBhdmdfcXR5LAogICAgYXZnKGxfZXh0ZW5kZWRwcmljZSkg
YXMgYXZnX3ByaWNlLAogICAgYXZnKGxfZGlzY291bnQpIGFzIGF2Z19kaXNjLAogICAgY291
bnQoKikgYXMgY291bnRfb3JkZXIKZnJvbQogICAgemVkc3RvcmVfbGluZWl0ZW1fcHJvamVj
dGlvbgp3aGVyZQogICAgbF9zaGlwZGF0ZSA8PSAnMTk5OC0xMi0wMScKZ3JvdXAgYnkKICAg
IGxfcmV0dXJuZmxhZywKICAgIGxfbGluZXN0YXR1cwpvcmRlciBieQogICAgbF9yZXR1cm5m
bGFnLAogICAgbF9saW5lc3RhdHVzOwotLSBUaW1lOiAyMzMxMC4zNDEgbXMgKDAwOjIzLjMx
MCkKCi0tIFZPUFMgcHJvamVjdGlvbgpzZWxlY3QKICAgIGxfcmV0dXJuZmxhZywKICAgIGxf
bGluZXN0YXR1cywKICAgIHN1bShsX3F1YW50aXR5KSBhcyBzdW1fcXR5LAogICAgc3VtKGxf
ZXh0ZW5kZWRwcmljZSkgYXMgc3VtX2Jhc2VfcHJpY2UsCiAgICBzdW0obF9leHRlbmRlZHBy
aWNlKigxLWxfZGlzY291bnQpKSBhcyBzdW1fZGlzY19wcmljZSwKICAgIHN1bShsX2V4dGVu
ZGVkcHJpY2UqKDEtbF9kaXNjb3VudCkqKDErbF90YXgpKSBhcyBzdW1fY2hhcmdlLAogICAg
YXZnKGxfcXVhbnRpdHkpIGFzIGF2Z19xdHksCiAgICBhdmcobF9leHRlbmRlZHByaWNlKSBh
cyBhdmdfcHJpY2UsCiAgICBhdmcobF9kaXNjb3VudCkgYXMgYXZnX2Rpc2MsCiAgICBjb3Vu
dCgqKSBhcyBjb3VudF9vcmRlcgpmcm9tCiAgICB2b3BzX2xpbmVpdGVtX3Byb2plY3Rpb24K
d2hlcmUKICAgIGxfc2hpcGRhdGUgPD0gJzE5OTgtMTItMDEnOjpkYXRlCmdyb3VwIGJ5CiAg
ICBsX3JldHVybmZsYWcsCiAgICBsX2xpbmVzdGF0dXMKb3JkZXIgYnkKICAgIGxfcmV0dXJu
ZmxhZywKICAgIGxfbGluZXN0YXR1czsKLS0gVGltZTogNDM5LjczMSBtcwoK
--------------3E18CA31B27DC9A88E9EA76F--