DBAtlas · Database operations atlas

Columnar storage / analytics

Oracle

-- Database In-Memory: keep the table in memory in columnar format
ALTER TABLE sale INMEMORY;
-- With priority and compression:
ALTER TABLE sale INMEMORY PRIORITY HIGH
  MEMCOMPRESS FOR QUERY HIGH;

Oracle Database In-Memory (12.1.0.2+) speeds up analytic queries by keeping tables in memory in columnar format (In-Memory Column Store, IM) while preserving the row format on disk (dual format). INMEMORY is a separately licensed option. The inmemory_size parameter sizes the IM area.

PostgreSQL

-- Core PostgreSQL is row-based; columnar storage needs an extension:
-- Citus columnar access method:
CREATE EXTENSION IF NOT EXISTS citus;
CREATE TABLE sale_columnar (...) USING columnar;
-- Alternatives: the Hydra/cstore_fdw extensions.

Core PostgreSQL offers only row-based (heap) storage. For columnar storage, the Citus 'columnar' access method (USING columnar), Hydra or the older cstore_fdw extensions are used; they provide compression and scan speed for analytics/data warehouse workloads.

MySQL

-- Not applicable: core InnoDB does not offer columnar storage.
-- For analytics: MySQL HeatWave (cloud, in-memory columnar)
-- or integration with external ClickHouse/Apache Doris.

Standard MySQL (InnoDB) is row-based and has no built-in columnstore. For analytic acceleration, MySQL HeatWave (an in-memory columnar accelerator in Oracle Cloud) is used; on-premises solutions typically use external columnar engines such as ClickHouse or Apache Doris.

SQL Server

-- Clustered columnstore index (for analytic tables):
CREATE CLUSTERED COLUMNSTORE INDEX cci_sale ON dbo.sale;
-- Nonclustered columnstore on a rowstore table (HTAP):
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_sale
  ON dbo.sale (city, product, amount, created_at);

SQL Server columnstore indexes (read-only nonclustered in 2012; updatable clustered in 2014; updatable nonclustered in 2016) store data column-wise with high compression; with batch mode they give large speedups for aggregation/scan-heavy analytic queries. Clustered suits data warehouses, nonclustered suits HTAP.

MongoDB

-- Not applicable: MongoDB is document (row-like) based.
-- For analytics: dedicated Analytics Nodes in Atlas
-- or export to a columnar format ($out)/an external engine.

MongoDB stores documents as row-like BSON; there is no built-in columnar (columnstore) storage. For analytic workloads, dedicated Analytics Nodes in an Atlas replica set, Atlas Data Federation, or exporting data to a columnar system (e.g. Parquet) are used.

Redis

-- Not applicable: there is no concept of columnar storage.

Redis is an in-memory key-value store; there is no row/column storage distinction. Analytic aggregations are done with special structures such as Sorted Set, HyperLogLog (approximate counting) or RedisTimeSeries; it is not suitable for large-scale OLAP.

Elasticsearch

# doc_values: fields are stored column-wise on disk (enabled by default).
# Required for sorting/aggregation; active on non-text fields.
PUT sale
{ "mappings": { "properties":
  { "amount": { "type": "double", "doc_values": true } } } }

For sorting and aggregations, Elasticsearch keeps fields in a disk-based columnar structure called doc_values (enabled by default on numeric/keyword/date fields). This columnar layout speeds up analytic/aggregation queries; it is disabled by default on text fields.

Open in DBAtlas →