cstore_fdw

Important notice: Columnar storage is now part of Citus Columnar storage is now part of the Citus extension, which uses the table access method API to give a much more native experience. It also supports streaming replication, archival, rollback, and simplifies pg_upgrade. You can use Citus just for columnar storage on a single PostgreSQL server, or combine it with distributed tables to parallelize queries locally or across a cluster of PostgreSQL servers. Migration is simple, and you'll typically see improved compression thanks to zstd: -- After adding adding shared_preload_libraries = 'citus' -- to postgresql.conf and restarting: CREATE EXTENSION IF NOT EXISTS citus; -- Create a table using the columnar access method, with the same columns -- as an existing cstore_fdw table CREATE TABLE customer_reviews_am ( LIKE customer_reviews_fdw INCLUDING ALL ) USING columnar; -- Copy data from an old cstore_fdw table to an access method table INSERT INTO customer_reviews_am SELECT * FROM customer_reviews_fdw; -- cstore_fdw data size SELECT pg_size_pretty(cstore_table_size('customer_reviews_fdw')); ┌────────────────┐ │ pg_size_pretty │ ├────────────────┤ │ 100 MB │ └────────────────┘ -- Citus Columnar data size SELECT pg_size_pretty(pg_table_size('customer_reviews_am')); ┌────────────────┐ │ pg_size_pretty │ ├────────────────┤ │ 64 MB │ └────────────────┘ Read more about it in the Citus columnar blog post by Jeff Davis. cstore_fdw Cstore_fdw is an open source columnar store extension for PostgreSQL. Columnar stores provide notable benefits for analytics use cases where data is loaded in batches. Cstore_fdw’s columnar nature delivers performance by only reading relevant data from disk, and it may compress data 6x-10x to reduce space requirements for data archival. Cstore_fdw is developed by Citus Data and can be used in combination with Citus, a postgres extension that intelligently distributes your data and queries across many nodes so your database can scale and your queries are fast. If you have any questions about how Citus can help you scale or how to use Citus in combination with cstore_fdw, please let us know. Join the Mailing List to stay on top of the latest developments for Cstore_fdw. Introduction This extension uses a format for its data layout that is inspired by ORC, the Optimized Row Columnar format. Like ORC, the cstore format improves upon RCFile developed at Facebook, and brings the following benefits: Compression: Reduces in-memory and on-disk data size by 2-4x. Can be extended to support different codecs. Column projections: Only reads column data relevant to the query. Improves performance for I/O bound queries. Skip indexes: Stores min/max statistics for row groups, and uses them to skip over unrelated rows. Further, we used the Postgres foreign data wrapper APIs and type representations with this extension. This brings: Support for 40+ Postgres data types. The user can also create new types and use them. Statistics collection. PostgreSQL's query optimizer uses these stats to evaluate different query plans and pick the best one. Simple setup. Create foreign table and copy data. Run SQL. Building cstore_fdw depends on protobuf-c for serializing and deserializing table metadata. So we need to install these packages first: # Fedora 17+, CentOS, and Amazon Linux sudo yum install protobuf-c-devel # Ubuntu 10.4+ sudo apt-get install protobuf-c-compiler sudo apt-get install libprotobuf-c0-dev # Ubuntu 18.4+ sudo apt-get install protobuf-c-compiler sudo apt-get install libprotobuf-c-dev # Mac OS X brew install protobuf-c Note. In CentOS 5, 6, and 7, you may need to install or update EPEL 5, 6, or 7 repositories. See this page for instructions. Note. In Amazon Linux, the EPEL repository is installed by default, but not enabled. See these instructions for how to enable it. Once you have protobuf-c installed on your machine, you are ready to build cstore_fdw. For this, you need to include the pg_config directory path in your make command. This path is typically the same as your PostgreSQL installation's bin/ directory path. For example: PATH=/usr/local/pgsql/bin/:$PATH make sudo PATH=/usr/local/pgsql/bin/:$PATH make install Note. cstore_fdw requires PostgreSQL version from 9.3 to 12. It doesn't support earlier versions of PostgreSQL. Usage Before using cstore_fdw, you need to add it to shared_preload_libraries in your postgresql.conf and restart Postgres: shared_preload_libraries = 'cstore_fdw' # (change requires restart) The following parameters can be set on a cstore foreign table object. filename (optional): The absolute path to the location for storing table data. If you don't specify the filename option, cstore_fdw will automatically choose the $PGDATA/cstore_fdw directory to store the files. If specified the value of this parameter will be used as a prefix for all files created to store table data. For example, the value /cstore_fdw/my_table could result in the files /cstore_fdw/my_table and /cstore_fdw/my_table.footer being used to manage table data. compression (optional): The compression used for compressing value streams. Valid options are none and pglz. The default is none. stripe_row_count (optional): Number of rows per stripe. The default is 150000. Reducing this decreases the amount memory used for loading data and querying, but also decreases the performance. block_row_count (optional): Number of rows per column block. The default is 10000. cstore_fdw compresses, creates skip indexes, and reads from disk at the block granularity. Increasing this value helps with compression and results in fewer reads from disk. However, higher values also reduce the probability of skipping over unrelated row blocks. To load or append data into a cstore table, you have two options: You can use the COPY command to load or append data from a file, a program, or STDIN. You can use the INSERT INTO cstore_table SELECT ... syntax to load or append data fr