Open source column-oriented storage engine for PostgreSQL?

Question

Are there any open source projects in the works to create a column-oriented storage engine for PostgreSQL? I know Yahoo created one in-house, and that there are commercial products built on PostgreSQL that are column-oriented.

Undercoating

I'm very interested in this topic, so hopefully we'll see some movements in this area soon enough. I just wanted to leave link here - Fujitsu has developed some in-memory columnstore engine for PostgreSQL, postgresql.fastware.com/blog/… – Laboratory

Answer 1

Citus Data has developed an open source columnar store extension for PostgreSQL. It is available under the Apache License v2.0. It supports PostgreSQL 9.3 and higher.

First, creation the extension and a foreign server:

CREATE EXTENSION cstore_fdw;

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

Next, create some foreign tables:

CREATE FOREIGN TABLE customer_reviews
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER cstore_server
OPTIONS(filename '/opt/citusdb/3.0/cstore/customer_reviews.cstore',
        compression 'pglz');

Finally, COPY data into the table:

COPY customer_reviews FROM '/home/user/customer_reviews_1998.csv' WITH CSV;

Foreign tables can be queried like any other table. You can even join them with regular tables.

More examples and information are available in a related blog post and the project's home page.

Related

Answer 2

The lack of responses here and my own research seems to indicate that there are indeed no open source initiatives to add column storage to PostgreSQL.

There was some talk in 2008 about Yahoo possibly outsourcing Everest (their column store back end for PostgreSQL), so here's hoping that they'll release it.

Undercoating

Answer 3

Greenplum has created a column-oriented storage engine for PostgreSQL.

Equiangular

Do you have a link to their column-oriented project? While Greenplum is PostgreSQL-based, I don't believe they offer their technology in an open source fashion. – Stubstad

Here is described how to setup a table with column-oriented storage in the "open source" distribution, as simple as CREATE TABLE bar (a int, b text) WITH (appendoptimized=true, orientation=column) DISTRIBUTED BY (a);, although I didn't test it yet. – Hellenize

Answer 4

I was looking for the same kind of extension/implementation while I was playing with monetDB. After finding cstore_ftw from Citus Data I came into this post from monetDB: https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout

Since cstore_ftw is using PostgreSQL's volcano-style query processor, we immediately suspected that this component would be the limiting factor to its performance.

I have not tested myself but (IMO) MonetDB are serious with their stuff. I think it will be perfect if MonetDB creates an extension/implementation for PostgreSQL. Right now I still working with monetDB while looking for new features on PostgreSQL.

Tonie