Postgresdb

-- GOOD: identity PK, public uuid, FK with action, numeric money, timestamptz,
-- status via lookup FK, generated tsvector, CHECK constraints.
CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id    uuid NOT NULL DEFAULT gen_random_uuid(),  -- v4; see schema ref for v7
    user_id      bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    status       text NOT NULL REFERENCES order_statuses (code) ON UPDATE CASCADE,
    amount       numeric(19,4) NOT NULL CHECK (amount >= 0),
    currency     text NOT NULL CHECK (length(currency) = 3),
    note         text,
    search       tsvector GENERATED ALWAYS AS (to_tsvector('simple', coalesce(note, ''))) STORED,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (public_id)
);
CREATE INDEX ix_orders_user_id ON orders (user_id);     -- FKs are NOT auto-indexed
CREATE INDEX ix_orders_status  ON orders (status);

-- BAD: every column is a future migration or a bug.
CREATE TABLE orders (
    id          serial PRIMARY KEY,            -- sequence-ownership gotchas; use IDENTITY
    user_id     int REFERENCES users(id),      -- int overflows at 2.1B; no FK index
    status      varchar(20),                   -- length hack; no constraint on values
    amount      float,                         -- money drift
    created_at  timestamp DEFAULT now()        -- naive: loses the zone
);

-- Query: WHERE user_id = $1 AND created_at >= $2 ORDER BY created_at DESC
-- GOOD: equality (user_id) then range (created_at)
CREATE INDEX ix_orders_user_created ON orders (user_id, created_at DESC);

-- BAD: range-first index cannot satisfy the equality efficiently for this query
CREATE INDEX ix_orders_created_user ON orders (created_at, user_id);

-- Partial: index only the rows you query (smaller, hotter)
CREATE INDEX ix_orders_active ON orders (user_id, created_at DESC)
WHERE status <> 'cancelled';

-- Covering: INCLUDE non-key columns to enable an index-only scan
CREATE INDEX ix_orders_user_cover ON orders (user_id) INCLUDE (amount, created_at);

-- GOOD: keyset on a stable composite sort; uses ix_orders_user_created
SELECT id, amount, created_at
FROM orders
WHERE user_id = $1
  AND (created_at, id) < ($2, $3)   -- row-value comparator = last row of prev page
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- BAD: OFFSET scans and discards 100000 rows every page (O(n))
SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- GOOD: insert-or-update; EXCLUDED is the row that failed to insert
INSERT INTO inventory (sku, qty)
VALUES ($1, $2)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
WHERE inventory.qty + EXCLUDED.qty >= 0   -- guard
RETURNING id, qty;

-- DO NOTHING returns no row on conflict; wrap to always get the row:
WITH ins AS (
    INSERT INTO tags (name) VALUES ($1)
    ON CONFLICT (name) DO NOTHING
    RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM tags WHERE name = $1 LIMIT 1;

-- GOOD: contention-free job claim; concurrent workers never block each other
UPDATE jobs
SET status = 'processing', locked_at = now()
WHERE id = (
    SELECT id FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING id, payload;

-- BAD: application loops, one query per order (N+1)
--   for o in orders: SELECT * FROM order_items WHERE order_id = o.id
-- GOOD: one set-based query
SELECT o.id, json_agg(i.*) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = $1
GROUP BY o.id;

-- Top-N-per-group: JOIN LATERAL, not a window-filter scan
SELECT u.id, recent.*
FROM users u
JOIN LATERAL (
    SELECT id, amount, created_at FROM orders
    WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3
) recent ON true;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = $1 AND created_at >= $2;