cstore_fdw
A columnar storage extension for PostgreSQL built as a foreign data wrapper, optimized for analytics and data archival.
What is cstore_fdw?
cstore_fdw is a columnar storage extension for PostgreSQL built as a foreign data wrapper. It optimizes PostgreSQL for analytical workloads by storing data in a column-oriented format, which improves query performance through column projections and compression. It solves the problem of inefficient I/O and high storage costs for read-heavy, batch-loaded data.
Database administrators and developers working with PostgreSQL who need efficient analytics, data warehousing, or archival solutions. It is particularly useful for those handling large datasets where query performance and storage compression are critical.
Developers choose cstore_fdw because it integrates seamlessly with PostgreSQL as a foreign data wrapper, offering native SQL support and compatibility with existing tools. Its columnar format provides significant performance gains for analytical queries and reduces storage requirements through compression, all without requiring a separate database system.
Overview
Columnar storage extension for Postgres built as a foreign data wrapper. Check out https://github.com/citusdata/citus for a modernized columnar storage implementation built as a table access method.
Use Cases
Best For
- Running analytical queries on large datasets in PostgreSQL
- Reducing storage costs for historical data archival
- Improving query performance for column-specific scans
- Batch loading and analyzing time-series or log data
- Integrating columnar storage into existing PostgreSQL workflows
- Data warehousing scenarios where compression and I/O efficiency are priorities
Not Ideal For
- Projects requiring frequent updates, deletes, or single-row inserts
- Users seeking long-term support and modern PostgreSQL features beyond version 12
- Environments needing high availability with streaming replication or seamless pg_upgrade
- Teams preferring minimal setup without compiling dependencies like protobuf-c
Pros & Cons
Pros
Stores data by column rather than row, which reduces I/O for analytical queries that scan specific columns, as shown in the example queries that benefit from column projections.
Uses optional compression codecs like PGLZ to reduce data size by 2-4x, saving storage costs for batch-loaded and archival data, evidenced by the compression feature in the key details.
Built as a foreign data wrapper, it supports over 40 PostgreSQL data types, statistics collection, and standard SQL operations like COPY and ANALYZE, making it easy to integrate into existing workflows.
Stores min/max statistics for row groups to skip unrelated rows during scans, improving query speed for sorted data, as explained in the skip indexes section of the README.
Cons
The README explicitly notes that columnar storage is now part of the Citus extension, which offers a more native experience, better compression with zstd, and features like streaming replication, making cstore_fdw a legacy option.
Does not support UPDATE, DELETE, or single-row INSERT operations, limiting it to read-only, batch-loaded scenarios, as admitted in the usage section.
Requires manual compilation with dependencies like protobuf-c and specific PostgreSQL versions, which can be error-prone compared to standard extension installations, as detailed in the building section.
Only compatible with PostgreSQL versions 9.3 to 12, excluding newer releases and potentially lacking security updates, as stated in the building notes, which restricts its use in modern deployments.
Frequently Asked Questions
Related Projects
RocksDBA library that provides an embeddable, persistent key-value store for fast storage.
MongoDB source codeThe MongoDB Database
rethinkdbThe open-source database for the realtime web.
postgresMirror of the official PostgreSQL GIT repository. Note that this is just a *mirror* - we don't work with pull requests on github. To contribute, please see https://wiki.postgresql.org/wiki/Submitting_a_Patch
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.