Three Tables, Deliberately Without Indexes

-- schema.sql — run with:  psql "$PG_URL" -f schema.sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

DROP TABLE IF EXISTS events, orders, users CASCADE;

CREATE TABLE users (
  id           bigserial PRIMARY KEY,
  email        text        NOT NULL,
  country      char(2)     NOT NULL,
  plan         text        NOT NULL,   -- 'free' | 'pro' | 'enterprise'
  created_at   timestamptz NOT NULL,
  last_seen_at timestamptz,
  prefs        jsonb       NOT NULL DEFAULT '{}'
);

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint      NOT NULL REFERENCES users(id),
  status      text        NOT NULL,   -- 'cart'|'paid'|'shipped'|'refunded'
  total_cents bigint      NOT NULL,
  currency    char(3)     NOT NULL,
  created_at  timestamptz NOT NULL,
  shipped_at  timestamptz
);

CREATE TABLE events (
  id         bigserial PRIMARY KEY,
  user_id    bigint      NOT NULL,
  kind       text        NOT NULL,    -- 'view'|'click'|'purchase'|...
  payload    jsonb       NOT NULL,
  created_at timestamptz NOT NULL
);

-- ══ WHAT IS DELIBERATELY MISSING ════════════════════════════════
--
-- No index on orders.user_id. None on orders.status, orders.created_at,
-- events.user_id, events.kind or users.email. Every one of those is a
-- reasonable index that a production schema would have, and every one
-- of them is left out on purpose.
--
-- You will create each of them in a later module, after reading a plan
-- that justifies it, and you will measure what it cost on insert.

\d orders
--  Indexes:
--      "orders_pkey" PRIMARY KEY, btree (id)
--
-- One index. That is the starting line.

-- ══ THE ONE THING THAT IS DECLARED ══════════════════════════════
-- orders.user_id REFERENCES users(id) is not decoration. A validated
-- foreign key tells the planner every order matches exactly one user,
-- which removes a whole class of join estimate error. Module 8 shows
-- the estimates with and without it.
--
-- Note it does NOT create an index on the referencing column —
-- PostgreSQL indexes the referenced side only. The missing index on
-- orders.user_id is the single most consequential omission here.

-- ── events has no foreign key, on purpose ───────────────────────
-- A high-volume append-only table often skips the constraint to avoid
-- the per-insert check and the lock on the parent row. That asymmetry
-- between orders and events is itself a lesson, and module 5 uses it.