Introduction to Materialize

-- Create a view that's automatically maintained
CREATE MATERIALIZED VIEW revenue_by_region AS
SELECT 
  region,
  SUM(order_total) AS total_revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY region;

-- Results are always fresh, reads are instant
SELECT * FROM revenue_by_region;

┌─────────────────────────────────────────────────────────────┐
│                     External Systems                         │
│  PostgreSQL  │  MySQL  │  Kafka  │  Webhooks  │  More...    │
└──────────────────────────┬──────────────────────────────────┘
                           │
                           ▼
                   ┌───────────────┐
                   │    Sources    │  ◄─── Read data from external systems
                   └───────┬───────┘
                           │
                           ▼
                   ┌───────────────┐
                   │  Views/MVs    │  ◄─── Transform data with SQL
                   │   + Indexes   │       Incrementally maintained
                   └───────┬───────┘
                           │
                           ▼
                   ┌───────────────┐
                   │   Queries     │  ◄─── Fast reads (computationally free)
                   │     &         │       via PostgreSQL protocol
                   │ Subscriptions │
                   └───────┬───────┘
                           │
                           ▼
                   ┌───────────────┐
                   │     Sinks     │  ◄─── Push results downstream
                   └───────────────┘

-- Create a source from sample data
CREATE SOURCE tpch
  FROM LOAD GENERATOR TPCH (SCALE FACTOR 1)
  FOR ALL TABLES;

-- Define a reusable view
CREATE VIEW revenue (supplier_no, total_revenue) AS
  SELECT
    l_suppkey,
    SUM(l_extendedprice * (1 - l_discount))
  FROM lineitem
  WHERE
    l_shipdate >= DATE '1996-01-01'
    AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' month
  GROUP BY l_suppkey;

-- Create a materialized view that's incrementally maintained
CREATE MATERIALIZED VIEW tpch_q15 AS
  SELECT
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
  FROM supplier, revenue
  WHERE
    s_suppkey = supplier_no
    AND total_revenue = (
      SELECT max(total_revenue)
      FROM revenue
    )
  ORDER BY s_suppkey;

-- Create an index for fast point lookups
CREATE INDEX tpch_q15_idx ON tpch_q15 (s_suppkey);

-- Query results are instant and always up-to-date
SELECT * FROM tpch_q15;

-- Subscribe to see changes as they happen
SUBSCRIBE TO (
  SELECT * FROM revenue_by_region
);