Schedule - PGConf.EU 2014

cstore_fdw, a columnar store for PostgreSQL

Date: 2014-10-22
Time: 17:20–18:10
Room: Medici I
Level: Intermediate

cstore_fdw is an open source columnar store by Citus Data for PostgreSQL. Its file layout is based on the Optimized Row Columnar (ORC) format, which brings the following benefits:

• Compression: Reduces the on-disk data size by 2-4x,
• Column Projections: Only read column data relevant to query, improves the performance for I/O bound queries,
• Skip Indexes: Keeps min/max statistics for row groups and uses them to skip over unrelated rows.

We developed cstore_fdw using the foreign data wrapper framework. cstore_fdw uses PostgreSQL’s binary data format for storing values, and you can use every data type supported by PostgreSQL. You can use the same SQL syntax that PostgreSQL provides to query cstore_fdw tables.

In the benchmarks we did on TPC-H data, most queries saw a performance improvement of 20%-30%. When we enabled skip-indexes by sorting the input data, we saw a further ~30% performance improvement.

Speaker