Optimizing schemas for read-heavy vs write-heavy workloads

Different Workloads Need Different Schemas

A transactional (OLTP) schema is optimized for fast single-row reads and writes. An analytical (OLAP) schema is optimized for scanning millions of rows and aggregating. Most systems start with OLTP and add a separate OLAP layer when reporting slows down production queries.

OLTP Optimizations

• Normalize to 3NF — minimize writes per transaction.
• Index selectively on high-cardinality columns used in WHERE clauses.
• Keep rows narrow — fewer columns means more rows per disk page.

OLAP Optimizations

-- Denormalized fact table for analytics (star schema)
CREATE TABLE fact_orders (
  order_id        BIGINT,
  customer_id     BIGINT,
  customer_name   VARCHAR(100),  -- denormalized
  customer_country CHAR(2),      -- denormalized
  product_id      BIGINT,
  product_name    VARCHAR(100),  -- denormalized
  category_name   VARCHAR(100),  -- denormalized
  quantity        INT,
  revenue         DECIMAL(12,2),
  order_date      DATE
);

Materialized Views

Pre-compute expensive aggregates and store them as a table. Refresh on a schedule or on-demand. Available natively in PostgreSQL; emulated with scheduled jobs in MySQL.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT DATE(order_date) AS day, SUM(revenue) AS total
  FROM fact_orders GROUP BY day;

REFRESH MATERIALIZED VIEW daily_revenue;