Column store

CrateDB lets you fine-tune data storage options per column, much like you can with indexing. The most important setting is whether a column uses the Column Store.

Quick Reference

Setting	Default	Purpose	Trade-offs
columnstore = true	Yes	Stores all values of a column together for fast aggregations, groupings, and sorting	Requires indexing or columnstore; imposes length limit for TEXT (32,766 bytes)
columnstore = false	No	Disables column store for the column	Removes TEXT length limit but slows aggregations/groupings/sorting
INDEX OFF	No	Disables indexing for the column	Can be combined with columnstore = false for large text storage
INDEX OFF + columnstore = false	No	Stores raw data only	Long text allowed, but no index, slower queries

---

Column Store Overview

By default, CrateDB:

1. Stores row data as-is

2. Indexes each value

3. Also stores each value in a Column Store

The Column Store groups all values of a column together, which makes:

• Global aggregations much faster

• Groupings more efficient

• Sorting possible without scanning entire rows

Limitations

• For TEXT columns, Column Store imposes a maximum length of 32,766 bytes

• Turning off both Column Store and indexing removes this limit

• Cannot disable Column Store on partition columns (they are stored differently)

---

Example: Disabling Column Store for Long Text

sqlCopierModifiercr> CREATE TABLE t1 (
...   id INTEGER,
...   url TEXT INDEX OFF STORAGE WITH (columnstore = false)
... );
CREATE OK, 1 row affected  (... sec)

What this does:

• Disables indexing and column store for url

• Allows storing strings longer than 32,766 bytes

• Slows queries using aggregations, groupings, or sorting on url

---

Supported Data Types for Column Store Control

Data Type	Column Store Control	Notes
TEXT	Yes	Disabling removes length limit
Numeric (INTEGER, LONG, DOUBLE, etc.)	Yes	Fully supported
TIMESTAMP WITH TIME ZONE	Yes	Fully supported
Other primitives & POINT	No (always enabled)	Cannot be disabled
Container types (ARRAY, OBJECT)	No	Not stored in Column Store
Geometric shapes (GEO_SHAPE, GEO_LINESTRING, etc.)	No	Not stored in Column Store

---

When to Use or Avoid Column Store

Scenario	Recommendation	Reason
Analytical queries with heavy aggregations or groupings	Keep Column Store ON	Maximizes query performance
Sorting large result sets by a column	Keep Column Store ON	Speeds up ordering
Storing very large text fields (over 32 KB)	Turn Column Store OFF + Index OFF	Removes size restriction
Column rarely queried, not used in aggregations or sorting	Consider turning OFF	Saves storage overhead
Partition column	Cannot turn OFF	Always indexed and stored for partitioning

As a general rule, if a column is part of frequent aggregations, joins, or sort operations, keep the Column Store enabled. Only disable it for rare-use, very large text, or archival fields.