PostgreSQL & SQL

Schema Design and Normalization

Creating Tables

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'editor', 'admin')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  slug VARCHAR(200) UNIQUE NOT NULL,
  published BOOLEAN DEFAULT false,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  author_id UUID REFERENCES users(id) ON DELETE SET NULL,
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Indexes

-- Speed up common queries
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_published ON posts(published, published_at DESC);
CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_users_email ON users(email);

-- Composite index for common filter patterns
CREATE INDEX idx_posts_author_published ON posts(author_id, published, published_at DESC);

-- Partial index (only index published posts)
CREATE INDEX idx_published_posts ON posts(published_at DESC) WHERE published = true;

-- Full-text search index
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || content));

Updated_at Trigger

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Querying with JOINs and CTEs

JOIN Types

-- Inner join: only matching rows
SELECT p.title, u.name AS author
FROM posts p
INNER JOIN users u ON p.author_id = u.id;

-- Left join: all posts, even without author
SELECT p.title, u.name AS author
FROM posts p
LEFT JOIN users u ON p.author_id = u.id;

-- Many-to-many: posts with their tags
SELECT p.title, ARRAY_AGG(t.name) AS tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

Common Table Expressions (CTEs)

-- Recursive CTE: comment threads with depth
WITH RECURSIVE comment_tree AS (
  -- Base case: top-level comments
  SELECT id, content, author_id, parent_id, 0 AS depth,
         ARRAY[id] AS path
  FROM comments
  WHERE parent_id IS NULL AND post_id = $1

  UNION ALL

  -- Recursive case: child comments
  SELECT c.id, c.content, c.author_id, c.parent_id, ct.depth + 1,
         ct.path || c.id
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT ct.*, u.name AS author_name
FROM comment_tree ct
JOIN users u ON ct.author_id = u.id
ORDER BY ct.path;

-- CTE for complex aggregation
WITH author_stats AS (
  SELECT
    author_id,
    COUNT(*) AS total_posts,
    COUNT(*) FILTER (WHERE published) AS published_posts,
    AVG(LENGTH(content)) AS avg_content_length
  FROM posts
  GROUP BY author_id
)
SELECT u.name, u.email, as.*
FROM author_stats as
JOIN users u ON as.author_id = u.id
ORDER BY as.total_posts DESC;

Window Functions

-- Rank posts by date within each author
SELECT
  title,
  author_id,
  created_at,
  ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS row_num,
  RANK() OVER (ORDER BY LENGTH(content) DESC) AS content_rank
FROM posts;

-- Running total of comments per post
SELECT
  post_id,
  created_at,
  COUNT(*) OVER (PARTITION BY post_id ORDER BY created_at) AS running_count
FROM comments;

-- Lag/Lead for comparing consecutive rows
SELECT
  title,
  created_at,
  LAG(title) OVER (ORDER BY created_at) AS prev_post,
  LEAD(title) OVER (ORDER BY created_at) AS next_post
FROM posts;

Transactions and Migrations

Transaction Example

BEGIN;

-- Transfer credits between users
UPDATE users SET credits = credits - 100 WHERE id = 'user-1' AND credits >= 100;
UPDATE users SET credits = credits + 100 WHERE id = 'user-2';

-- Check if the first update affected a row
-- (if not, the transfer would underflow)
DO $$
BEGIN
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Insufficient credits';
  END IF;
END $$;

-- Insert the transaction record
INSERT INTO transactions (from_user, to_user, amount, created_at)
VALUES ('user-1', 'user-2', 100, NOW());

COMMIT;

Node.js Transactions

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function transferCredits(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const { rows } = await client.query(
      'UPDATE users SET credits = credits - $1 WHERE id = $2 AND credits >= $1 RETURNING id',
      [amount, fromId]
    );
    if (rows.length === 0) throw new Error('Insufficient credits');

    await client.query(
      'UPDATE users SET credits = credits + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query(
      'INSERT INTO transactions (from_user, to_user, amount) VALUES ($1, $2, $3)',
      [fromId, toId, amount]
    );

    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Database Migrations

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/002_add_user_role.sql
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';

-- migrations/003_create_posts.sql
CREATE TABLE IF NOT EXISTS posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Using a migration tool like db-migrate or Knex:

// migrations/20260912_add_posts_table.js
exports.up = function(knex) {
  return knex.schema.createTable('posts', table => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('author_id').references('id').inTable('users').onDelete('CASCADE');
    table.string('title', 200).notNullable();
    table.text('content').notNullable();
    table.boolean('published').defaultTo(false);
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('posts');
};

Query Optimization

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT p.title, u.name, COUNT(c.id) AS comment_count
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published = true
GROUP BY p.id, p.title, u.name
ORDER BY p.created_at DESC
LIMIT 20;

-- Look for:
-- Seq Scan → needs index
-- Nested Loop → OK for small datasets
-- Hash Join → OK for larger datasets
-- Sort → consider index on sort column

Connection Pooling

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,            // max connections in pool
  min: 5,             // keep minimum connections alive
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// Always release connections back to pool
async function query(text, params) {
  const start = Date.now();
  const { rows } = await pool.query(text, params);
  const duration = Date.now() - start;
  if (duration > 100) console.warn(`Slow query (${duration}ms):`, text);
  return rows;
}

// Use pg's built-in pool
pool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
});

N+1 Query Prevention

-- BAD: N+1 queries (1 for users + N for each user's posts)
-- SELECT * FROM users;
-- SELECT * FROM posts WHERE author_id = $1;  (for each user)

-- GOOD: Single query with JOIN
SELECT u.*, json_agg(p.*) AS posts
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id;

-- GOOD: Batch load with IN clause
SELECT * FROM posts WHERE author_id = ANY($1::uuid[]);

Pagination Patterns

-- Offset-based (slow for large offsets)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000;

-- Cursor-based (fast, consistent)
SELECT * FROM posts
WHERE created_at < $1  -- cursor = last seen created_at
ORDER BY created_at DESC
LIMIT 20;