Elasticsearch & Columnar OLAP

Two analytical read patterns

Pattern	Index structure	Optimized query
Full-text search	Inverted index (term → posting list)	"phrases matching fuzzy query"
OLAP aggregation	Columnar files	SUM(revenue) GROUP BY country

Different storage layouts; choosing wrong tool is a common system-design failure.

Elasticsearch / Lucene storage

Documents indexed into Lucene segments — immutable mini-indexes on disk.

Inverted index per field:

term "elasticsearch" → [doc1:pos3, doc7:pos1, ...] term "storage" → [doc1:pos4, doc3:pos9, ...]

Query = intersect posting lists. Analyzers tokenize/stem at index time — must match query analyzer.

Near-real-time

New docs go to in-memory buffer → refresh (default 1s) makes segment searchable → periodic merge compacts segments (like LSM compaction). Tuning refresh_interval trades ingest throughput vs search latency.

Sharding and replicas

Index split into shards (primary partitions). Replicas copy shards for read scale and HA — same leader-follower pattern as RDBMS replicas (see Replication & HA).

Columnar OLAP engines

ClickHouse, Redshift, BigQuery storage layer, DuckDB — store columns contiguously, often sorted by a key for compression.

Technique	Effect
Late materialization	Filter on narrow columns before fetching wide ones
Vectorized execution	Process batches of values per CPU instruction
Sort key / ORDER BY	Run-length encoding on sorted columns
Projection / materialized views	Pre-aggregated storage for hot queries

Immutable parts merged in background — LSM family again.

Search vs OLAP trade-off matrix

Workload	Elasticsearch	Columnar OLAP
Point lookup by ID	Good (with routing)	Good
Full-text relevance	Excellent	Poor (no inverted index)
Faceted search	Good	Good on indexed dims
Billion-row aggregation	Poor	Excellent
Sub-second ad-hoc SQL	Awkward (DSL)	Native

Hybrid patterns in production

• Postgres OLTP + Elasticsearch search index (CDC sync)
• Kafka + ClickHouse for metrics
• Snowflake external tables over Iceberg Parquet

Each copy is a storage denormalization with its own consistency lag.

System design assembly: search, feed & ranking

Interviewers rarely ask "how does Lucene work?" in isolation — they ask how search and feeds fit the larger system.

Three-store pattern (common)

Store	Role	Consistency
OLTP (Postgres)	Source of truth for posts, users, relationships	Strong per row
Search index (Elasticsearch)	Full-text, facets, autocomplete	Eventual (CDC lag)
Cache (Redis)	Hot timelines, denormalized post cards	TTL + event invalidation

Write path: commit to OLTP → publish event (Kafka) → indexer updates ES + cache invalidation worker. Read path for home feed: often cache or precomputed timeline (Microservices → BFF for fan-out on read vs write).

Ranking vs retrieval

• Retrieval — inverted index returns candidate set (keywords, filters)
• Ranking — score and reorder (ML model, engagement signals, freshness)

Storage topic stops at retrieval. In design rounds: "We'd retrieve top-N from ES, then rank in a dedicated service with feature store / batch features — ranker is stateless, features may lag minutes." Don't dive into model training unless asked.

CDC into search

Debezium / outbox → indexer consumer. At-least-once indexing requires idempotent upsert by document ID. Stale index until catch-up — product copy may say "search may take a minute."

When OLAP joins the picture

Engagement metrics (clicks, dwell time) land in columnar OLAP (ClickHouse, BigQuery) for ranking features and dashboards — not in the search index. Link Data Engineering for pipeline semantics if the loop goes DE-flavored.

Not covered here

Kibana query DSL, dashboard design, or Spark SQL syntax — storage layout only.

End of track

You've covered relational internals through analytical storage. Cross-cutting themes: immutability (WAL, SSTables, segments, Parquet), amplification (write/read/compaction), and matching layout to access pattern.