Demystifying the columnar storage model

Data compression is a crucial part of data storage and processing, and the columnar storage model has become popular largely because of how well it enables it, particularly in databases and analytical SQL workloads.

The reason comes down to something quite basic. A table is a two-dimensional thing, but storage is one-dimensional. Something has to decide the order in which cells get written down, and there are only two sensible answers.

What the columnar storage model is

In traditional storage models, data is stored in rows, with each row holding all the fields belonging to that record. In the columnar storage model, data is stored by column, with each column holding all the values of that field across every row.

Consider a table of customer information with columns for name, age, address, and phone number. Under a row-based model, all the data for a given customer is stored together in one row. Under a columnar model, every customer name is stored together, then every age, then every address, then every phone number.

Same data, same schema. The difference is purely in the layout — and nearly every property people attribute to "columnar databases" falls out of it.

Benefits and drawbacks

Benefits:

• Efficient compression. Storing repeated values within a column makes it far easier to compress using algorithms that exploit repeating patterns, giving a higher compression ratio than row-based storage. Values in a column are the same type and usually drawn from a small range. A million rows of a country field are a million values drawn from perhaps two hundred distinct strings, which dictionary encoding turns into a million small integers. A million rows of timestamp are nearly sorted, which delta encoding turns into a million tiny numbers. Neither trick is available when a string, a float, and a boolean sit adjacent in memory.
• Improved query performance. Because all values for a column are stored together, aggregate functions and queries touching a specific column become much cheaper. SELECT AVG(price) on a forty-column table touches one column; row storage must read every row in full and discard thirty-nine fortieths of what it read.
• Faster I/O. Sequential I/O is easier because the query engine only reads the columns the query actually needs, rather than reading whole rows and filtering out irrelevant fields afterwards.
• Parallel processing. Each column is independent of the others, so work parallelises naturally across columns, reducing the wall-clock time to complete a query.
• Scans vectorise. A column is a contiguous run of a single type, which is exactly what SIMD instructions and CPU prefetchers want. Row storage interleaves types and defeats both.

Drawbacks:

• Not suitable for unstructured data. The benefits all derive from a column being homogeneous. Text blobs and images have no well-defined schema to organise into columns and nothing for these techniques to exploit.
• Slower updates and inserts. Adding or removing a single row means touching every column — one write per field instead of one write per record. Deleting is worse. This is why columnar systems tend to be append-only, batch-loaded, or paired with a row-oriented write buffer that is periodically merged.
• Reconstructing a whole row costs a join. SELECT * FROM t WHERE id = 5 is the case row storage was designed for and the case column storage handles worst.

The split is roughly OLTP against OLAP: many small transactions touching whole records, versus few large queries touching few fields of many records. Ultimately the right choice depends on the specific requirements of your workload. Careful consideration of the data's structure, the expected query patterns, and the processing requirements will tell you whether columnar layout is the right fit.

An example in action

To demonstrate the effect, here is a Go program that generates a dataset of 100,000 records, each containing an integer ID, a string Name, a float Price, and a boolean InStock, then stores it both ways and compares.

package main
 
import (
	"bytes"
	"encoding/gob"
	"fmt"
	"math/rand"
	"time"
)
 
type ProductRow struct {
	ID      int
	Name    string
	Price   float64
	InStock bool
}
 
type ProductColumn struct {
	IDs      []int
	Names    []string
	Prices   []float64
	InStocks []bool
}
 
func main() {
	var rows []ProductRow
	for i := 0; i < 100000; i++ {
		rows = append(rows, ProductRow{
			ID:      i,
			Name:    fmt.Sprintf("Product %d", i),
			Price:   rand.Float64() * 100,
			InStock: rand.Intn(2) == 0,
		})
	}
 
	var bufRows bytes.Buffer
	gob.NewEncoder(&bufRows).Encode(rows)
	rowsSize := bufRows.Len()
 
	var columns ProductColumn
	for _, row := range rows {
		columns.IDs = append(columns.IDs, row.ID)
		columns.Names = append(columns.Names, row.Name)
		columns.Prices = append(columns.Prices, row.Price)
		columns.InStocks = append(columns.InStocks, row.InStock)
	}
 
	var bufColumns bytes.Buffer
	gob.NewEncoder(&bufColumns).Encode(columns)
	columnsSize := bufColumns.Len()
 
	fmt.Printf("Rows size: %d bytes\n", rowsSize)
	fmt.Printf("Columns size: %d bytes\n", columnsSize)
	fmt.Printf("Compression ratio: %.2f%%\n",
		100*float64(columnsSize)/float64(rowsSize))
 
	var decodedRows []ProductRow
	start := time.Now()
	for i := 0; i < 100; i++ {
		var buf bytes.Buffer
		gob.NewEncoder(&buf).Encode(rows)
		gob.NewDecoder(&buf).Decode(&decodedRows)
	}
	elapsedRows := time.Since(start)
 
	var decodedColumns ProductColumn
	start = time.Now()
	for i := 0; i < 100; i++ {
		var buf bytes.Buffer
		gob.NewEncoder(&buf).Encode(columns)
		gob.NewDecoder(&buf).Decode(&decodedColumns)
	}
	elapsedColumns := time.Since(start)
 
	fmt.Printf("Encoding and decoding rows took %s\n", elapsedRows)
	fmt.Printf("Encoding and decoding columns took %s\n", elapsedColumns)
}

Running it produces:

Rows size: 3156545 bytes
Columns size: 2755676 bytes
Compression ratio: 87.30%
Encoding and decoding rows took 2.382370118s
Encoding and decoding columns took 1.345502811s

The dataset stored as rows takes 3,156,545 bytes; stored as columns it takes 2,755,676. Columns are about 13% smaller and just under twice as fast to round-trip.

So the experiment demonstrates that layout affects cost. It does not demonstrate the compression case for columnar storage, and I would be overstating things if I claimed it did.

It is also worth noting that Gob, while convenient, is not the most efficient serialization format available in either space or time. A format such as Protocol Buffers or MessagePack would reduce the serialized size and improve encoding and decoding speed. To measure genuine columnar compression you would want a format built for it — Parquet or ORC — rather than one that merely serialises columns.

Optimization techniques

Columnar storage is widely used in data warehousing and analytics to improve query performance and reduce storage requirements. Several techniques push it further:

• Use fixed-size arrays rather than variable-length arrays or lists, to reduce memory overhead and keep a column a flat array with no indirection.
• Use a struct of arrays rather than an array of structs, to improve cache locality. This is the columnar idea itself, applied at the level of in-memory data structures.
• Use an efficient serialization format such as Protocol Buffers or MessagePack to reduce serialized size and speed up encoding and decoding.
• Apply a compression algorithm on top, chosen per column — what works for sorted timestamps is not what works for low-cardinality strings — especially when storing or transmitting over a network.
• Benchmark and profile. Use profiling tools and parallelisation to find where the time actually goes rather than assuming.

Applied together, these make the columnar model considerably more memory-efficient and better suited to large-scale analysis.

Conclusion

Data compression is an essential part of data storage and processing, and the columnar storage model provides significant benefits in that area. Storing data by column rather than by row enables efficient compression through algorithms that work well on the repeating patterns a homogeneous column naturally contains.

It is worth remembering that columnar storage is one of several available techniques, and not a compression technique in itself. Dictionary encoding, run-length encoding, and delta encoding each have their own strengths and weaknesses, and the columnar layout is what makes them work far better than they otherwise would. The right choice depends on the specific characteristics of the data being processed.

Both of the main benefits — better compression and cheaper queries — come from the same source: putting similar things next to each other. That is a much older idea than databases, and it turns up wherever memory latency dominates, which is increasingly everywhere.