PostgreSQL

-- JSONB binary JSON with indexing
CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    data    JSONB NOT NULL,
    tags    TEXT[],           -- arrays are a native type
    created TIMESTAMPTZ DEFAULT NOW()
);

-- JSONB operators INSERT INTO events (data, tags) VALUES ('{"user_id": 1, "action": "login", "ip": "192.168.1.1"}', ARRAY['auth','security']);

SELECT data->>'action' AS action, data->'user_id' AS user_id FROM events WHERE data @> '{"action": "login"}' -- contains operator AND 'security' = ANY(tags);

-- GIN index for JSONB CREATE INDEX idx_data ON events USING GIN(data); CREATE INDEX idx_tags ON events USING GIN(tags);

-- Full-text search CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', title || ' ' || body)); SELECT title FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & performance'); ```

-- Window functions (more powerful than MySQL)
SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC),
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary),
    NTILE(4) OVER (ORDER BY salary) AS quartile,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS salary_vs_prev
FROM employees;

-- Recursive CTEs (hierarchical data) WITH RECURSIVE org_chart AS ( SELECT id, name, manager_id, 0 AS level, name AS path FROM employees WHERE manager_id IS NULL -- root

UNION ALL

SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' → ' || e.name FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT level, path FROM org_chart ORDER BY path; ```

-- Custom ENUM type
CREATE TYPE order_status AS ENUM ('pending','confirmed','shipped','delivered','cancelled');
CREATE TABLE orders (id SERIAL PRIMARY KEY, status order_status DEFAULT 'pending');

-- Composite types CREATE TYPE address AS (street TEXT, city TEXT, country TEXT, postal_code VARCHAR(10)); CREATE TABLE users (id SERIAL PRIMARY KEY, home_address address); INSERT INTO users (home_address) VALUES (ROW('123 Main St', 'Toronto', 'CA', 'M5V 2L7')); SELECT (home_address).city FROM users;

-- Domains reusable constraints CREATE DOMAIN email_address AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+.[^@]+$'); CREATE TABLE contacts (id SERIAL PRIMARY KEY, email email_address NOT NULL);

-- Extensions CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- UUID generation CREATE EXTENSION IF NOT EXISTS pgcrypto; -- cryptographic functions CREATE EXTENSION IF NOT EXISTS vector; -- pgvector for AI embeddings

SELECT uuid_generate_v4(); SELECT crypt('password', gen_salt('bf', 10)); -- bcrypt ```

-- Index types
CREATE INDEX idx_btree ON users(email);           -- default, B-tree
CREATE INDEX idx_hash ON sessions(token) USING HASH;   -- equality only, faster
CREATE INDEX idx_gin ON articles USING GIN(to_tsvector('english', body));  -- full-text
CREATE INDEX idx_gist ON locations USING GIST(coords);  -- geometric/spatial
CREATE INDEX idx_brin ON events USING BRIN(created_at);  -- huge tables, sequential data

-- Partial index (only index subset) CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- Covering index (include extra columns to avoid table lookup) CREATE INDEX idx_covering ON orders(user_id) INCLUDE (status, total, created_at);

-- EXPLAIN ANALYZE EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123;

-- pg_stat_statements for slow query analysis SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ```

-- Table partitioning huge tables by range
CREATE TABLE events (
    id         BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    data       JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE events_2024_q2 PARTITION OF events FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Partition by hash for even distribution CREATE TABLE users PARTITION BY HASH (id); CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);

-- Connection pooling with PgBouncer -- Max connections in PostgreSQL is a precious resource (~100-500) -- PgBouncer multiplexes thousands of application connections to ~50 DB connections -- Transaction pooling mode: most efficient

-- VACUUM & AUTOVACUUM VACUUM ANALYZE users; -- reclaim space + update statistics VACUUM FULL users; -- aggressive locks table, rewrites SELECT * FROM pg_stat_user_tables WHERE n_dead_tup > 10000; ```

Streaming Replication Setup ```sql -- postgresql.conf (primary) -- wal_level = replica -- max_wal_senders = 5 -- wal_keep_size = 1GB

-- Create replication user CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'strong_pass';

-- pg_hba.conf: allow replication connection -- host replication replicator replica_ip/32 scram-sha-256

-- On replica: -- pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/data -P -Xs -R ```

High Availability Options
• Patroni + etcd/consul: automatic failover and leader election
• Citus: horizontal sharding for PostgreSQL at massive scale
• pgpool-II: connection pooling + load balancing + automatic failover
• Timescale: time-series extension built on PostgreSQL

pgvector for AI Applications ``sql CREATE EXTENSION vector; CREATE TABLE embeddings (id SERIAL PRIMARY KEY, content TEXT, embedding vector(1536)); CREATE INDEX ON embeddings USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); SELECT content FROM embeddings ORDER BY embedding <=> '[0.1, 0.2, ...]' LIMIT 10; ``

-- ── JSONB Operations ──────────────────────────────────
CREATE TABLE events (
    id      BIGSERIAL PRIMARY KEY,
    data    JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert INSERT INTO events (data) VALUES ('{"type":"purchase","user_id":42,"total":99.99,"items":["abc","def"]}');

-- Query JSON fields SELECT data->>'type' AS event_type, -- text data->'user_id' AS user_id_json, -- json (data->>'total')::float AS total_float FROM events WHERE data->>'type' = 'purchase' AND (data->>'user_id')::int = 42;

-- Array contains SELECT * FROM events WHERE data->'items' ? 'abc';

-- Nested access SELECT data #>> '{metadata,campaign,id}' AS campaign_id FROM events;

-- JSONB index (GIN generalized inverted index) CREATE INDEX idx_events_data ON events USING GIN(data); -- Supports: ?, @>, <@ operators efficiently

-- ── Full-Text Search ────────────────────────────────── ALTER TABLE articles ADD COLUMN ts_vector TSVECTOR GENERATED ALWAYS AS ( to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '') ) ) STORED;

CREATE INDEX idx_articles_fts ON articles USING GIN(ts_vector);

-- Search SELECT title, ts_rank(ts_vector, query) AS rank FROM articles, to_tsquery('english', 'machine & learning & python') AS query WHERE ts_vector @@ query ORDER BY rank DESC LIMIT 10;

-- Highlight matching terms SELECT ts_headline('english', body, to_tsquery('machine & learning'), 'StartSel=, StopSel=, MaxFragments=3' ) FROM articles WHERE ts_vector @@ to_tsquery('machine & learning'); ```