Intro

-- ===== The values =====
-- - ACID with a long track record
-- - Strict SQL conformance (no surprise type coercions)
-- - Rich type system: JSONB, arrays, range types, UUID, geo (PostGIS)
-- - Extensions everywhere (PostGIS, pgvector, pg_partman, TimescaleDB)
-- - Replication, logical decoding, partitioning all built in

-- ===== Hello, table =====
CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name  TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (email, name) VALUES ('a@x.io', 'Alex');

SELECT id, name FROM users WHERE email = 'a@x.io';

-- ===== Joins =====
CREATE TABLE orders (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total NUMERIC(12,2) NOT NULL
);

SELECT u.name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

-- ===== JSONB =====
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  data JSONB NOT NULL
);
SELECT data->>'kind' AS kind, COUNT(*)
FROM events
WHERE data @> '{"source":"web"}'
GROUP BY data->>'kind';

-- ===== Transactions =====
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- ===== Indexes =====
CREATE INDEX users_email_idx ON users (email);
CREATE INDEX events_data_kind_idx ON events ((data->>'kind'));
CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- ===== When Postgres wins =====
-- - Default choice for any new relational workload
-- - Mixed relational + JSON
-- - Geo (PostGIS), search (full-text + pgvector), time-series (TimescaleDB)
-- - When you need transactions and constraints to be enforced by the DB

-- ===== When Postgres hurts =====
-- - Extreme write throughput at scale (consider sharding or a dedicated TSDB)
-- - Very large analytical workloads (use a warehouse: Snowflake/BigQuery/ClickHouse)
-- - Strict mobile / embedded (SQLite)

-- ===== Patterns to internalise =====
-- - TIMESTAMPTZ everywhere (never TIMESTAMP without tz)
-- - UUID PKs for distributed-friendly IDs
-- - JSONB for sparse data; indexed via generated columns or expression indexes
-- - Pool connections (pgbouncer / PgCat) at scale

-- ===== Pitfalls =====
-- - DOUBLE PRECISION for money -> use NUMERIC
-- - Idle-in-transaction sessions holding locks -> set statement_timeout
-- - Forgetting indexes on FK columns
-- - VACUUM / autovacuum tuning ignored on hot write tables

-- Postgres: open-source, ACID, advanced types, extensible.
-- Used by GitLab, Instagram, Reddit, …