I ran my first big analytical query on a row-oriented database in 2019. It took 47 minutes. Honestly, I thought the system had crashed. Then a colleague moved the same table into a columnar engine. The query came back in under a second.
Here’s the thing. Analytics runs the data world now, and columnar storage is why your dashboards load fast. If you’re querying millions of records with hundreds of attributes, this matters.
So let me show you how it works, when it wins, and when it quietly hurts you 👇
30-Second Summary
📌 TL;DR: A columnar database stores data by column instead of by row. That means analytical queries read only the columns they need, compress far better, and cost less to run. Great for analytics and reporting. Poor for transactional SELECT * work. The names you'll meet most: Snowflake, BigQuery, Redshift, and ClickHouse.
What you’ll learn:
- What a columnar database is, and how column storage differs from row storage.
- Why selective reading and compression make analytical queries so much faster.
- Whether a columnar database counts as SQL or NoSQL.
- Where column storage wins, where it loses, and the examples worth knowing.
- The practices, mistakes, and metrics I use on real migrations.
I’ve run columnar migrations for several analytics teams. One of them I reversed.
What Is a Columnar Database?
A columnar database stores table data by column rather than by row. That one storage choice changes how analytical queries behave. It changes what they cost, too.
Row-oriented databases like MySQL or PostgreSQL keep records in sequence. Record A holds Name, Email, and Title together on disk. The next row follows right after. That’s ideal for transactions, where you want a whole record at once. Columnar flips the layout → all the Names together, all the Emails together, all the Titles together. Each column lives in its own block. Here’s the formal column-oriented DBMS overview.
Here’s my “aha” moment. We had a table with 50 million company records and more than 100 attributes. One simple question, “what’s the average revenue by industry?”, forced our row store to scan all of it. Phone numbers, addresses, executive names, every byte. On a columnar engine the same query touched two columns. It dropped from about 14 minutes to under a second.
How Does Column-Oriented Storage Work?
It groups values of the same type together, which buys you three things. First, selective reading. Queries touch only the columns they name. Second, better compression, because a block of one data type squeezes down beautifully. AWS documents the compression encodings Redshift uses. Third, vectorised execution, which means the engine crunches a batch of column values in one pass.
The compression tricks are worth knowing:
- Run-length encoding: turns
US, US, US, USintoUS:4. - Delta encoding: stores the gap between sorted values, not each full number.
- Dictionary encoding: swaps repeated strings for small integer codes.
I’ve watched a country column shrink by roughly 95% on run-length encoding alone.
Is a Columnar Database SQL or NoSQL?
It’s usually SQL, because column storage is a layout on disk, not a data model. You query Snowflake, BigQuery, Redshift, or ClickHouse with statements you already write. Only the bytes are arranged differently.
But the name confuses people, and fairly. A separate family called wide-column stores exists, with Cassandra and HBase as the usual examples. Those are NoSQL systems where each row carries its own column families. They share half a name with columnar engines, and almost nothing else.
And MySQL? Not columnar by default. It stores rows, though add-on column stores exist for MySQL-compatible setups.
When Should You Use a Columnar Database?
Use one when analytics and reporting drive your workload. That work is called online analytical processing, or OLAP, meaning queries that scan and aggregate many rows at once. If your queries total a few columns across huge tables, this is your tool.
Signs you’re in that camp:
- Queries aggregate or filter specific columns repeatedly.
- Tables have many columns, and each query touches few.
- Data volumes are large and still growing.
- Compression and storage cost matter to you.
- Query speed matters more than write speed.
Real-time reporting is the sweet spot. Ask for revenue by region this quarter and the engine reads three columns, not three hundred. The ClickHouse documentation explains why that gap gets so wide. I tested it on the NYC Taxi dataset 👇
| Metric | Row-oriented (PostgreSQL) | Column-oriented (ClickHouse) |
|---|---|---|
| Query time | 14.2 seconds | 0.8 seconds |
| Storage size | 50GB | 12GB |
| Cost per query | Higher | Lower |
That’s one workload on my own hardware, not a benchmark.
When Should You Avoid It?
Avoid column storage for transactional work, small tables, and heavy point lookups. That side has a name too, OLTP, or online transaction processing, where you read or write one record at a time.
The SELECT * trap is the big one. Asking for every column forces the engine to stitch rows back together from separate files. I migrated a customer portal onto a columnar system once. Page loads got worse, because every request pulled a full profile. We moved back within weeks. My rule → tables under a million rows rarely justify the move.
💡 Field note: Plenty of teams run both. A row store handles operations, a columnar store handles analytics. That's not indecision. It's matching the tool to the job.
Columnar Database Examples
The names split into cloud warehouses and open-source engines. Each fits a different budget. There’s no single best one, whatever the marketing says.
Snowflake keeps storage and compute separate, so you pay for each on its own. Zero-copy cloning and time travel are lovely. It gets expensive once several teams query all day. Google BigQuery is serverless, and its column-oriented design ties cost to the bytes a query scans. Kind if you name your columns, harsh if you don’t. Amazon Redshift suits AWS-native teams and speaks familiar PostgreSQL syntax, though it needs more cluster tuning. ClickHouse is open source and very fast, but it asks for real infrastructure skill.
And you don’t always need an engine. Sometimes a file format does the job. Apache Parquet stores data by column on object storage, so Athena or Spark can read it directly. Apache ORC and Apache Arrow fill related roles. These formats slot into a modern Data Architecture that leans on a Data Lake.
Back in my Hamburg agency years, around 2018, we kept a 40 million row event table in PostgreSQL. Monday reporting ate the whole morning. Our first fix was a bigger server, and it bought us nothing. So we dumped the table to Parquet on S3 and queried it with a columnar engine. Reporting dropped to minutes, and nobody rewrote a dashboard.
Two questions I get constantly. Is Snowflake a columnar database? Yes, it stores table data in columns. Is Databricks one? It’s a platform rather than a database, though its Delta Lake tables sit on columnar Parquet files.
Why Columnar Storage Changes Your Cloud Bill
Because most cloud warehouses charge by the bytes a query scans. Read fewer columns, scan fewer bytes, pay less. Speed gets the headlines. The bill is often the bigger prize.
Picture a wide table with 100 columns. A row-oriented scan reads all 100. A columnar query that names 3 reads a small slice of those bytes, and compression shrinks the slice further. Pricing that follows scanned bytes follows that ratio down.
I audited a client whose scan-heavy queries dominated their warehouse bill. Nobody named columns. Nothing was partitioned. We fixed both habits, and their monthly spend fell by roughly four fifths. Same dashboards, far less scanning. That’s what steady Database Management pays for.
PS: the cheapest query reads the least data.
Columnar Database Best Practices
Start with the habits that cost nothing. The six I refuse to skip:
- Select only the columns you need: every extra column is extra bytes off disk.
- Partition by the column you filter on most: usually a date, so old data goes unread.
- Sort or cluster on your second most common filter: that’s how an engine skips whole blocks.
- Batch your inserts: single-row writes create tiny files that slow every later read.
- Check the compression encoding the engine chose: a bad guess on a high-cardinality column wastes space.
- Keep a row store for the transactional path: your app still needs fast single-record reads.
🧠 Rule of thumb: Open your query history → sort by bytes scanned → take the single worst query → rewrite it to name its columns and add a date filter. One afternoon of that usually pays for itself.
What Should You Measure?
Measure the numbers that move your latency and your bill. Six are plenty:
- Bytes scanned per query: the best single proxy for cost.
- p95 query latency on your top ten dashboards: the speed 95% of queries beat.
- Compression ratio per table: a sudden drop points at a schema problem.
- Small-file or micro-partition count: a micro-partition is the storage chunk an engine reads or skips, and too many slow everything.
- Ingest lag: how far behind live your freshest table runs.
- Share of queries still using SELECT *: the easiest waste to fix.
Put all six on one dashboard. Read it monthly. That habit catches trouble early.
Common Columnar Database Mistakes
The biggest mistake is treating the engine choice as the strategy. Here’s the rest, all of it lived:
SELECT *everywhere: it undoes the advantage you migrated for.- Single-row inserts in a loop: batch them, or you build a small-file mess.
- Migrating a transactional workload: my portal story, where page loads got worse.
- Moving a table under a million rows: the overhead outweighs the gain.
- No partitioning at all: then every query scans the whole table, forever.
- Expecting sub-millisecond point lookups: that’s a row store’s job, not this one.
- Betting on the brand: your query patterns decide results, far more than the logo.
One honest limit. My numbers are single-workload measurements on my own data. So profile your own queries before copying anyone’s advice, mine included.
Where Is This All Heading?
Toward convergence. The wall between transactional and analytical systems keeps thinning. HTAP engines, short for hybrid transactional and analytical processing, serve both jobs at once. TiDB and SingleStore lead there.
I trialled SingleStore on a mixed workload. Writes held up fine, and analytical queries ran close to columnar speed. But it wasn’t as quick as a dedicated columnar engine, or as tight as a row store on transactions. It’s a middle path you pick for simplicity. Either way, sound Data Modeling still decides your results.
Related Concepts Worth Knowing
Two neighbours sit very close to this one. A Data Lakehouse puts columnar tables straight onto lake storage, so warehouse-style queries run without loading the data twice. On the other side sits an operational data store, which stays row-oriented on purpose because it feeds current records to applications.
Parquet, partitioning, and vectorised execution turned up earlier on this page. Those three ideas make the rest click. Learn them and the vendor names matter less.
Data Storage & Architecture Terms
- What is Data Architecture?
- What is Data Modeling?
- What are Data Lakes?
- What are Data Marts?
- What is a Data Vault?
- What is Data Lakehouse?
- What is Operational Data Store?
- What are Columnar Databases?
- What is Hierarchical Indexing?
- What is NoSQL?
Frequently Asked Questions
Is SQL a columnar database?
No. SQL is a query language, not a database type. It runs against row stores and columnar stores alike. The same statements work for Snowflake, BigQuery, and MySQL. Only the storage underneath differs.
Is Snowflake a columnar database?
Yes. Snowflake stores table data in columns. It’s a cloud data warehouse built for analytics. That column layout keeps scans cheap when you select carefully.
What is an example of a columnar format?
Apache Parquet is the most widely used columnar file format. Apache ORC and Apache Arrow are two more. They store data by column without a database engine, which suits analytics on object storage.
Is BigQuery a columnar database?
Yes. BigQuery is a serverless warehouse with column-oriented storage. The design lets it read only the columns a query names. Its pricing follows the bytes scanned, so fewer columns means a smaller bill.
Why is SELECT * slow on columnar databases?
Because it rebuilds whole rows from separate column files. That adds the I/O this layout was designed to avoid. Column storage rewards selective queries. Name your columns and the problem mostly disappears.
Is a columnar database SQL or NoSQL?
Most of them are SQL. Column storage describes how bytes sit on disk, not the data model, so relational engines use it happily. The confusion comes from wide-column NoSQL stores like Cassandra.
Is MySQL a columnar database?
No. MySQL stores data by row. It’s built for transactions, where you read and write whole records. Add-on column stores exist for MySQL-compatible setups.
You’ve Got This
Columnar databases changed analytics for good. Faster scans, smaller storage, cheaper bills, all from one layout decision. But they aren’t universal, and pretending otherwise costs you months.
So start with your query patterns. Look at which columns you actually read. That answer, not the hype, tells you whether to move. You’ve got this.