Column Stores and Vectorized Execution (OLAP) TODO
Concept
A row store appends whole records together, while a column store lays out values of the same column contiguously, so a query only reads the columns it needs and I/O drops sharply. Because values that land together share the same type and often a similar distribution, compression schemes like RLE, dictionary encoding, delta encoding, and bit-packing work much better, and it becomes possible to operate directly on compressed data. The execution engine also processes data in batches of thousands of values (vectors) instead of the tuple-at-a-time Volcano model, cutting function-call overhead and exploiting cache locality and SIMD. The tradeoff is that single-row lookups and frequent updates suffer, so OLTP paths still belong on row stores.
Throwing analytical queries straight at an operational database is often tens of times slower and more expensive, and once the workload shape and storage layout are mismatched, index tuning alone won't recover it.
Code & Formula
# 컬럼 스토어와 벡터화 실행(OLAP) — 로우 스토어와 컬럼 스토어의 I/O·압축 차이를 흉내낸다.
# 컬럼별로 값을 모으면 RLE 압축이 잘 듣고, 한 컬럼만 읽어도 되는 질의에서 I/O 가 줄어든다.
rows = [
{"country": "KR", "amount": 100},
{"country": "KR", "amount": 120},
{"country": "US", "amount": 90},
{"country": "US", "amount": 95},
{"country": "US", "amount": 80},
]
# 로우 스토어: "amount 합계"를 구하려면 레코드 전체를 훑어야 한다.
row_store_bytes_touched = sum(len(r) for r in rows) * 8 # 필드 전부를 스캔한다고 가정
# 컬럼 스토어: amount 컬럼만 연속 배치로 저장 → 그 컬럼만 읽으면 된다.
column_store = {
"country": [r["country"] for r in rows],
"amount": [r["amount"] for r in rows],
}
column_store_bytes_touched = len(column_store["amount"]) * 8 # amount 컬럼만 스캔
def run_length_encode(values):
out = []
for v in values:
if out and out[-1][0] == v:
out[-1][1] += 1
else:
out.append([v, 1])
return out
# 벡터화 실행: 튜플 하나씩이 아니라 컬럼 배열 전체에 한 번에 연산(sum)을 적용한다.
def vectorized_sum(col):
return sum(col) # 실제 엔진은 SIMD 로 배치 처리하지만, 여기선 개념만 시연
country_rle = run_length_encode(column_store["country"])
total_amount = vectorized_sum(column_store["amount"])
print("row store bytes touched for SUM(amount):", row_store_bytes_touched)
print("column store bytes touched for SUM(amount):", column_store_bytes_touched)
print("country column RLE:", country_rle) # [['KR', 2], ['US', 3]]
print("vectorized SUM(amount):", total_amount) # 485
docs/code/algorithms/algorithms-76.py
Exercise
Load the same event data into Postgres and DuckDB, then compare the scanned data volume and runtime of a large-scale aggregation query to confirm the effect of column pruning.
Practical Connection
Verex's analytics backend — indexing chain event logs to compute trading volume, open interest, and per-user P&L — is a textbook OLAP workload, so it's natural to separate the storage used for mirroring on-chain state from the storage used for analytics.
Where it lands in Jayverse
- Verex: pick a specific column-store — DuckDB or a managed OLAP service — for the volume, open-interest and P&L rollups. Keep the row-store mirror only for per-order lookups; don't let one Postgres instance serve both shapes as usage grows.
- gitboard: move dashboard metric queries off the operational database once they start scanning large event tables. Point those read paths at the same column-store rather than tuning indexes on the row-store mirror.
- Number: plan for the same OLAP shape in the reading pipeline. Historical market or price-data backtests are column-pruning-friendly workloads too; move past ad hoc scripts to a column-store before it grows large.
Key expressions
| Expression | 뜻 · 쓰이는 자리 |
|---|---|
| lay out | 배열하다, 배치하다 · 데이터가 저장되는 방식을 말할 때. "lays out values of the same column contiguously" |
| drop sharply | 급격히 줄어들다 · 수치가 크게 감소할 때. "and I/O drops sharply." |
| operate directly on | ~에 직접 연산을 수행하다 · 데이터를 별도 변환 없이 그대로 처리할 때. "operate directly on compressed data." |
| in batches of | ~단위로 묶어서, 배치로 · 한 번에 여러 개씩 처리할 때. "processes data in batches of thousands of values" |
| cache locality | 캐시 지역성 · 메모리 접근 패턴이 캐시 효율을 높일 때. "exploiting cache locality and SIMD." |
| the tradeoff is that | 대가(단점)는 ~라는 것이다 · 장점과 함께 따라오는 단점을 소개할 때. "The tradeoff is that single-row lookups and frequent updates suffer" |
| mismatched | 서로 맞지 않는, 어긋난 · 두 요소가 궁합이 안 맞을 때. "workload shape and storage layout are mismatched" |
| OLAP | 온라인 분석 처리(Online Analytical Processing) · 대규모 집계·분석 쿼리에 최적화된 워크로드 유형, 컬럼 스토어가 적합. "Column Stores and Vectorized Execution (OLAP)" |
| OLTP | 온라인 트랜잭션 처리(Online Transaction Processing) · 단건 조회·빈번한 업데이트에 최적화된 워크로드, 로우 스토어가 적합. "so OLTP paths still belong on row stores" |
| DuckDB | 임베디드형 OLAP 데이터베이스(제품명) · 컬럼 스토어 실습 비교 대상으로 쓰이는 오픈소스 분석 DB. "Load the same event data into Postgres and DuckDB" |
| RLE | 런렝스 인코딩(Run-Length Encoding) · 같은 값이 연속될 때 압축 효율이 좋은 압축 기법, 컬럼 스토어에서 활용. "compression schemes like RLE, dictionary encoding, delta encoding" |
| SIMD | 단일 명령 다중 데이터(Single Instruction, Multiple Data) · 하나의 명령으로 여러 값을 동시에 처리하는 병렬화 기법. "exploiting cache locality and SIMD" |
If you study this on a given day, add a note link and a ✅ to this line in the source curriculum (docs/knowledge/dev-100-curriculum.md) and this spot will lead straight to the note body. You can also write directly on this page — but regenerating overwrites it, so it's safer to keep anything you want to save as markdown under docs/algorithms/.