PostgreSQL Cheatsheet: Indexes, JSONB, CTEs and Window Functions

# macOS — Homebrew
brew install postgresql@16
brew services start postgresql@16

# Linux — Debian/Ubuntu
sudo apt install postgresql-16
sudo systemctl enable --now postgresql

# Docker — disposable dev instance
docker run --name pg -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16

# Connect (URL form is the modern default)
psql "postgresql://user:pass@localhost:5432/mydb"
psql -h localhost -U postgres -d mydb       # flag form
psql                                         # local socket, current user

# Create role + db (run as superuser)
createuser -P app
createdb -O app shop

-- Rank orders per customer by amount (top 3 per customer)
SELECT *
FROM (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

-- Running total by day
SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_revenue;

-- Lead/lag: compare with previous row
SELECT
  day, revenue,
  LAG(revenue) OVER (ORDER BY day)  AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM daily_revenue;

-- Table with JSONB column + GIN index
CREATE TABLE events (
  id    BIGSERIAL PRIMARY KEY,
  ts    TIMESTAMPTZ NOT NULL DEFAULT now(),
  data  JSONB       NOT NULL
);
CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);

-- Insert
INSERT INTO events (data) VALUES
  ('{"type":"login","user":{"id":42,"plan":"pro"}}'),
  ('{"type":"click","user":{"id":7,"plan":"free"},"path":"/pricing"}');

-- Query: extract + filter (operators)
SELECT data->'user'->>'id'      AS user_id,    -- ->  keeps JSONB
       data->>'type'            AS event_type  -- ->> casts to text
FROM events
WHERE data @> '{"user":{"plan":"pro"}}';       -- contains

-- jsonb_path_query for nested traversal
SELECT jsonb_path_query(data, '$.user.id')
FROM events
WHERE data @? '$.user.plan ? (@ == "pro")';

-- Upsert: insert or update on conflict
INSERT INTO users (email, name, login_count)
VALUES ('a@x.com', 'Ana', 1)
ON CONFLICT (email) DO UPDATE
  SET name        = EXCLUDED.name,
      login_count = users.login_count + 1;

-- Conflict on partial unique index → name the constraint
ON CONFLICT ON CONSTRAINT users_email_active_key DO NOTHING;

-- RETURNING — get the row(s) back in one round-trip
INSERT INTO posts (slug, title)
VALUES ('hello', 'Hello')
ON CONFLICT (slug) DO UPDATE SET title = EXCLUDED.title
RETURNING id, (xmax = 0) AS inserted;       -- inserted vs updated

-- Bulk upsert from a VALUES list
INSERT INTO inventory (sku, qty)
VALUES ('a', 5), ('b', 2), ('c', 9)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

-- Plan only — fast, no execution
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Plan + real execution stats (ANALYZE actually runs the query)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- What to read in the output:
--   Seq Scan         → table scan; want Index/Bitmap on large tables
--   rows=...         → planner estimate
--   actual rows=...  → reality; big mismatch means stats are stale (ANALYZE)
--   Buffers: shared hit=… read=…  → cache-hot vs disk reads
--   Sort Method: external merge   → spilled to disk; raise work_mem

-- Refresh planner stats after big writes
VACUUM (ANALYZE) orders;

-- A tiny shop schema with the choices that age well
CREATE TABLE customer (
  id          BIGSERIAL PRIMARY KEY,
  email       CITEXT      UNIQUE NOT NULL,        -- case-insensitive
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE product (
  id     BIGSERIAL PRIMARY KEY,
  sku    TEXT    UNIQUE NOT NULL,
  price  NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE "order" (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT REFERENCES customer(id) ON DELETE RESTRICT,
  total        NUMERIC(12,2) NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX order_customer_placed_idx ON "order" (customer_id, placed_at DESC);

-- Top 5 spenders in the last 30 days
SELECT c.email, SUM(o.total) AS spend
FROM "order" o
JOIN customer c ON c.id = o.customer_id
WHERE o.placed_at >= now() - interval '30 days'
GROUP BY c.email
ORDER BY spend DESC
LIMIT 5;