Columnar storage
- SQL course
- Expert
- Lesson 32/45
- 5 min
Learn column-oriented storage.
Columnar storage
Columnar storage keeps data by column, not row. Values from the same column sit together; this speeds up aggregate queries like SUM and AVG.
CREATE TABLE events (id INT, ts TIMESTAMP)
WITH (format = 'columnar');Columnar speeds up aggregate queries.
The table events is created.
Columnar storage — details
Row-based storage suits OLTP; columnar suits OLAP. Columnar reads only the columns it needs; this makes wide-scan, few-column queries very fast.
Practice Columnar storage
Create the events table as columnar: WITH (format = 'columnar').
Starter code
-- columnar storage
CREATE TABLE events (id INT, ts TIMESTAMP)Sample solution
CREATE TABLE events (id INT, ts TIMESTAMP) WITH (format = 'columnar');Expected output
CREATE TABLE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Columnar storage: match the terms
| Term | Meaning |
|---|---|
columnar |
columnar |
row |
row |
OLAP |
report |
OLTP |
transaction |
2 Columnar format
Question
WITH (format = '____')
Answer
WITH (format = 'columnar')Explanation: columnar is the right answer.
3 Which query does columnar storage speed up?
- Reading a single row
- Aggregates Correct answer
- Writes
- Updates
Explanation: Columnar storage speeds up aggregate queries.