Star schema modeling for analytics-friendly queries, B-Tree vs LSM-Tree storage engines explained precisely, when to choose each, and why OLTP and OLAP systems are usually physically separated.
Published September 23, 2026
Design a storage layer for analytical queries ("total revenue by region by month," "average order value by customer segment") — large aggregate queries scanning significant data volume — WITHOUT running those queries against the live transactional (OLTP) database and degrading its performance for actual customer-facing traffic.
Functional: support large aggregate/analytical queries efficiently; ingest data from the transactional system (typically via Batch Processing System's ETL pipeline, or CDC per Event-Driven Architecture Patterns); model data in a way that's natural for analytical questions. Non-functional: analytical query load must NOT impact the transactional database's performance; the storage layer must handle both large scan-heavy reads AND a steady stream of incoming writes efficiently.
Fact table (orders_fact): Dimension tables:
order_id, customer_id (FK), dim_customer: customer_id, name, region, segment
product_id (FK), date_id (FK), dim_product: product_id, name, category
quantity, revenue dim_date: date_id, day, month, quarter, year
A fact table holds the actual MEASUREMENTS (revenue, quantity — the numbers you're aggregating) plus foreign keys to dimension tables, which hold the DESCRIPTIVE attributes you slice and group by (customer region, product category, calendar month). This structure is deliberately optimized for the query SHAPE analytics actually needs — "revenue by region by month" is a straightforward JOIN of the fact table against dim_customer and dim_date, filtered and grouped — rather than the highly-normalized, transaction-optimized schema a live OLTP database typically uses, which is optimized for a completely different access pattern (fast individual record lookups/updates, not broad aggregation).
B-Tree (most OLTP databases — PostgreSQL, MySQL default):
Each write finds and updates the EXACT page on disk holding that record
-> fast point lookups and range queries, but writes get expensive under
heavy load (random disk I/O to find and update the right page)
LSM-Tree (Cassandra, RocksDB, many write-heavy/OLAP-adjacent systems):
Writes go to an in-memory structure (MEMTABLE) first, flushed sequentially
to disk as immutable sorted files (SSTABLES) once the memtable fills
-> writes are FAST (sequential disk I/O, no need to find/update an exact
existing page), but READS may need to check MULTIPLE SSTables (since a
key's latest value could be in any of them) — periodic COMPACTION
merges older SSTables together to bound how many files a read must check
This is a genuinely fundamental storage-engine trade-off, not an implementation detail: B-Tree structures optimize for READ-heavy or balanced workloads needing consistently low-latency point lookups (exactly what an OLTP database serving live application traffic needs); LSM-Tree structures optimize for WRITE-heavy ingestion (exactly what a data warehouse continuously absorbing ETL loads, or a time-series database per Monitoring & Alerting System, needs) at some read-latency cost, mitigated by compaction.
This is the direct architectural conclusion from everything above: the transactional (OLTP) database and the analytical (OLAP/warehouse) storage layer have GENUINELY DIFFERENT optimal designs (schema shape, storage engine choice) for their respective workloads — running analytical queries directly against the OLTP database means either accepting a schema/storage-engine compromise that serves NEITHER workload well, or accepting that heavy analytical scans degrade the live transactional system's performance. Physically separating them (ETL/CDC continuously feeding data FROM the OLTP system INTO a purpose-built warehouse) lets each system be optimized for its own actual workload.
This OLTP/OLAP separation is precisely CQRS (Command Query Responsibility Segregation) at a large scale: the transactional database handles COMMANDS (writes — placing an order), the warehouse handles QUERIES (reads — analytical aggregation) — two separate, independently-optimized stores, kept in sync via an explicit data pipeline (ETL or CDC) rather than one store trying to serve both very different access patterns well simultaneously.
Q: How stale can the warehouse's data reasonably be relative to the live OLTP system? A: Depends entirely on the ETL/CDC pipeline's own latency (Batch Processing System's batch-latency vs Real-Time Analytics Dashboard's stream-processing freshness) — a nightly batch ETL means the warehouse can be up to 24 hours stale; a CDC-based pipeline can bring that down to minutes or less, a genuine trade-off between freshness and pipeline complexity that should be driven by actual business need for analytical freshness.
Q: Does a star schema's denormalization (dimension attributes duplicated across many fact rows) cause data-integrity problems? A: It CAN, if a dimension attribute changes (a customer moves regions) and historical fact rows need to reflect either the OLD or NEW region depending on the analysis — this is the well-known 'slowly changing dimension' problem in data warehousing, with established patterns (versioning dimension records, tracking effective date ranges) for handling it deliberately rather than accidentally.
Q: Why would compaction in an LSM-Tree ever be a genuine operational concern? A: Compaction itself consumes real I/O and CPU resources while running, and if write volume outpaces compaction's ability to keep up, the number of SSTables a read must check grows unboundedly, degrading read latency over time — this is a real, monitorable (Metrics & Monitoring) operational health signal for any LSM-Tree-backed system, not a purely theoretical concern.
Q: Could a single database engine support BOTH B-Tree and LSM-Tree-style storage for different tables? A: Some modern databases do offer pluggable storage engines per table for exactly this reason — but it's more common in practice to use genuinely different, purpose-built SYSTEMS (a B-Tree-based OLTP database alongside a separate LSM-Tree-based or columnar OLAP warehouse) rather than one system trying to excel at both storage models simultaneously.