The Relational Model

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Modern PostgreSQL: Multiple paradigms in one database
 
-- 1. JSONB: Document storage with relational guarantees
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB,  -- Flexible schema per product type
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"cpu": "M2", "ram": 16, "storage": "512GB SSD", "ports": ["USB-C", "MagSafe"]}'),
('Running Shoe', '{"size": 10, "color": "blue", "waterproof": true}');
 
-- Query JSON with full SQL expressiveness
SELECT name, attributes->>'cpu' AS cpu, attributes->>'ram' AS ram
FROM products
WHERE attributes @> '{"ram": 16}'  -- Contains check
  AND (attributes->>'storage')::text LIKE '%SSD%';
 
-- 2. Full-Text Search
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || attributes::text);
CREATE INDEX products_search_idx ON products USING gin(search_vector);
 
SELECT name, ts_rank(search_vector, query) AS relevance
FROM products, to_tsquery('english', 'laptop | storage') AS query
WHERE search_vector @@ query
ORDER BY relevance DESC;
 
-- 3. Geospatial (requires PostGIS)
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOGRAPHY(POINT, 4326)
);
 
-- Find locations within 5km of a point
SELECT name, ST_Distance(coordinates, ST_MakePoint(-73.9857, 40.7484)::geography) AS distance_m
FROM locations
WHERE ST_DWithin(coordinates, ST_MakePoint(-73.9857, 40.7484)::geography, 5000);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

// Modern ORM usage with Prisma (TypeScript)
 
// prisma/schema.prisma - declarative schema
// model User {
//   id        Int      @id @default(autoincrement())
//   email     String   @unique
//   name      String?
//   posts     Post[]
//   createdAt DateTime @default(now())
// }
// 
// model Post {
//   id        Int      @id @default(autoincrement())
//   title     String
//   content   String?
//   published Boolean  @default(false)
//   author    User     @relation(fields: [authorId], references: [id])
//   authorId  Int
// }
 
import { PrismaClient } from '@prisma/client'
 
const prisma = new PrismaClient()
 
// Type-safe queries with auto-completion
async function getPublishedPosts(authorEmail: string) {
    const posts = await prisma.post.findMany({
        where: {
            published: true,
            author: {
                email: authorEmail  // Nested relation filtering
            }
        },
        include: {
            author: {
                select: {
                    name: true,
                    email: true
                }
            }
        },
        orderBy: {
            createdAt: 'desc'
        },
        take: 10  // Pagination
    })
    return posts  // Fully typed!
}
 
// Transactions for complex operations
async function createUserWithPost(userData: { email: string, name: string }, postData: { title: string }) {
    return prisma.$transaction(async (tx) => {
        const user = await tx.user.create({
            data: userData
        })
        const post = await tx.post.create({
            data: {
                ...postData,
                authorId: user.id
            }
        })
        return { user, post }
    })
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

// Using read replicas in application code
 
import { Pool } from 'pg';
 
// Separate pools for read and write
const writePool = new Pool({
    host: 'db-primary.example.com',
    max: 20
});
 
const readPool = new Pool({
    host: 'db-replica.example.com',  // Could be load-balanced across replicas
    max: 50  // More connections for reads
});
 
// Route queries appropriately
async function getUser(id: number) {
    // Reads go to replica (eventual consistency acceptable)
    const result = await readPool.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
}
 
async function updateUser(id: number, data: Partial<User>) {
    // Writes MUST go to primary
    await writePool.query(
        'UPDATE users SET name = $1, email = $2 WHERE id = $3',
        [data.name, data.email, id]
    );
}
 
// Read-after-write: use primary when consistency matters
async function updateAndReturn(id: number, data: Partial<User>) {
    await writePool.query('UPDATE users SET name = $1 WHERE id = $2', [data.name, id]);
    // Read from PRIMARY to see our own write
    const result = await writePool.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

// Connecting to cloud databases
 
// AWS RDS/Aurora with connection pooling (using RDS Proxy)
import { Pool } from 'pg';
 
const pool = new Pool({
    host: 'my-db-proxy.proxy-xxxxx.us-east-1.rds.amazonaws.com',
    port: 5432,
    database: 'myapp',
    user: 'app_user',
    password: process.env.DB_PASSWORD,
    ssl: { rejectUnauthorized: true },  // Always use SSL in cloud
    max: 50,  // Proxy handles actual connection pooling
    idleTimeoutMillis: 30000,
});
 
// Neon serverless PostgreSQL (connection pooling built-in)
import { neon } from '@neondatabase/serverless';
 
const sql = neon(process.env.DATABASE_URL!);
 
// Serverless function usage
export async function handler(event: any) {
    // Connection established per request (pooled by Neon)
    const users = await sql`SELECT * FROM users WHERE active = true`;
    return { statusCode: 200, body: JSON.stringify(users) };
}
 
// PlanetScale (MySQL, using planetscale.js)
import { connect } from '@planetscale/database';
 
const conn = connect({
    host: process.env.DATABASE_HOST,
    username: process.env.DATABASE_USERNAME,
    password: process.env.DATABASE_PASSWORD,
});
 
const results = await conn.execute('SELECT * FROM products WHERE category = ?', ['electronics']);

┌─────────────────────────────────────────────────────────────────┐
│                        Application Layer                         │
└───────────────────────────────┬─────────────────────────────────┘
                                │
        ┌───────────────────────┼───────────────────────┐
        │                       │                       │
        ▼                       ▼                       ▼
┌───────────────┐    ┌───────────────────┐    ┌───────────────┐
│   PostgreSQL  │    │      Redis        │    │ Elasticsearch │
│  Source of    │◄───│   Cache Layer     │    │    Search     │
│    Truth      │    │  (derived data)   │    │   (derived)   │
└───────┬───────┘    └───────────────────┘    └───────────────┘
        │                                             ▲
        │            ┌───────────────────┐           │
        └───────────►│   CDC Pipeline    │───────────┘
                     │  (Debezium/etc)   │
                     └───────────────────┘
 
Data Flows:
• Writes → PostgreSQL (source of truth)
• PostgreSQL → CDC → Elasticsearch (search sync)  
• PostgreSQL → Cache invalidation → Redis
• Reads → Redis (cached) or PostgreSQL (miss) or Elasticsearch (search)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

-- Common Table Expressions (CTEs) for query organization
WITH active_users AS (
    SELECT id, email, created_at
    FROM users
    WHERE last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT u.id, u.email, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
    FROM active_users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.email
)
SELECT email, order_count, total_spent,
       CASE 
           WHEN total_spent > 1000 THEN 'VIP'
           WHEN total_spent > 100 THEN 'Regular'
           ELSE 'New'
       END AS customer_tier
FROM user_orders
ORDER BY total_spent DESC;
 
-- Recursive CTE: Organizational hierarchy
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 AS level, ARRAY[name] AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees under managers
    SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT id, name, level, array_to_string(path, ' → ') AS reporting_chain
FROM org_tree
ORDER BY path;
 
-- Window Functions: Analytics without GROUP BY
SELECT 
    department,
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- LATERAL joins: correlated subqueries made easy
-- Get each user's 3 most recent orders
SELECT u.id, u.email, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT o.id AS order_id, o.total, o.created_at
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 3
) AS recent_orders;
 
-- FILTER for conditional aggregates
SELECT 
    department,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE salary > 100000) AS high_earners,
    AVG(salary) FILTER (WHERE hire_date > '2020-01-01') AS new_hire_avg,
    SUM(bonus) FILTER (WHERE performance_rating = 'A') AS a_rated_bonus_total
FROM employees
GROUP BY department;
 
-- JSON aggregation: build JSON in SQL
SELECT 
    d.name AS department,
    json_agg(json_build_object(
        'id', e.id,
        'name', e.name,
        'salary', e.salary
    ) ORDER BY e.salary DESC) AS employees
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;
 
-- UPSERT: Insert or update
INSERT INTO metrics (date, page, views, unique_visitors)
VALUES ('2024-01-15', '/home', 1000, 450)
ON CONFLICT (date, page) 
DO UPDATE SET 
    views = metrics.views + EXCLUDED.views,
    unique_visitors = GREATEST(metrics.unique_visitors, EXCLUDED.unique_visitors);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

-- PostgreSQL observability queries
 
-- Slow queries (requires pg_stat_statements extension)
SELECT 
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    total_exec_time::numeric(10,2) AS total_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
 
-- Active queries with wait events
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
 
-- Table bloat and vacuum status
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    n_dead_tup::float / NULLIF(n_live_tup, 0) AS dead_ratio,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- Index usage statistics
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC  -- Unused indexes at top
LIMIT 20;
 
-- Connection statistics
SELECT 
    state,
    COUNT(*) as count,
    MAX(now() - query_start) AS max_query_duration
FROM pg_stat_activity
GROUP BY state;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Create separate roles for different access patterns
 
-- Read-only role for reporting
CREATE ROLE app_readonly NOLOGIN;
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;
 
-- Application role with limited write access
CREATE ROLE app_readwrite NOLOGIN;
GRANT CONNECT ON DATABASE myapp TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
-- NOTE: No TRUNCATE, DROP, or schema modification rights
 
-- Create login users inheriting from roles
CREATE USER reporting_user WITH PASSWORD 'secure_password_1' IN ROLE app_readonly;
CREATE USER api_user WITH PASSWORD 'secure_password_2' IN ROLE app_readwrite;
 
-- Row-Level Security for multi-tenant isolation
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::int);
 
-- Application sets tenant context
SET app.current_tenant = '42';
SELECT * FROM orders;  -- Only sees tenant 42's orders