marslanalvi avatar

engineering-databases

SQL, Postgres, MySQL, Redis, ORMs, schema design and migrations

version
1.0.0
GitHub stars
5

Install

/plugin install engineering-databases@skills-stack

Run it inside Claude Code after adding the marketplace with /plugin marketplace add marslanalvi/SkillsStack.

Contents

Discovered from source

Skills 10

ClickHouse Analytics Patterns

ClickHouse-specific patterns for high-performance analytics and data engineering.

When to Activate

  • Designing ClickHouse table schemas (MergeTree engine selection)
  • Writing analytical queries (aggregations, window functions, joins)
  • Optimizing query performance (partition pruning, projections, materialized views)
  • Ingesting large volumes of data (batch inserts, Kafka integration)
  • Migrating from PostgreSQL/MySQL to ClickHouse for analytics
  • Implementing real-time dashboards or time-series analytics

Overview

ClickHouse is a column-oriented database management system (DBMS) for online analytical processing (OLAP). It's optimized for fast analytical queries on large datasets.

Key Features:

  • Column-oriented storage
  • Data compression
  • Parallel query execution
  • Distributed queries
  • Real-time analytics

Table Design Patterns

MergeTree Engine (Most Common)

CREATE TABLE markets_analytics (
    date Date,
    market_id String,
    market_name String,
    volume UInt64,
    trades UInt32,
    unique_traders UInt32,
    avg_trade_size Float64,
    created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, market_id)
SETTINGS index_granularity = 8192;

ReplacingMergeTree (Deduplication)

-- For data that may have duplicates (e.g., from multiple sources)
CREATE TABLE user_events (
    event_id String,
    user_id String,
    event_type String,
    timestamp DateTime,
    properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp)
PRIMARY KEY (user_id, event_id);

AggregatingMergeTree (Pre-aggregation)

-- For maintaining aggregated metrics
CREATE TABLE market_stats_hourly (
    hour DateTime,
    market_id String,
    total_volume AggregateFunction(sum, UInt64),
    total_trades AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, market_id);

-- Query aggregated data
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;

Query Optimization Patterns

Efficient Filtering

-- PASS: GOOD: Use indexed columns first
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
  AND market_id = 'market-123'
  AND volume > 1000
ORDER BY date DESC
LIMIT 100;

-- FAIL: BAD: Filter on non-indexed columns first
SELECT *
FROM markets_analytics
WHERE volume > 1000
  AND market_name LIKE '%election%'
  AND date >= '2025-01-01';

Aggregations

-- PASS: GOOD: Use ClickHouse-specific aggregation functions
SELECT
    toStartOfDay(created_at) AS day,
    market_id,
    sum(volume) AS total_volume,
    count() AS total_trades,
    uniq(trader_id) AS unique_traders,
    avg(trade_size) AS avg_size
FROM trades
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day, market_id
ORDER BY day DESC, total_volume DESC;

-- PASS: Use quantile for percentiles (more efficient than percentile)
SELECT
    quantile(0.50)(trade_size) AS median,
    quantile(0.95)(trade_size) AS p95,
    quantile(0.99)(trade_size) AS p99
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;

Window Functions

-- Calculate running totals
SELECT
    date,
    market_id,
    volume,
    sum(volume) OVER (
        PARTITION BY market_id
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_volume
FROM markets_analytics
WHERE date >= today() - INTERVAL 30 DAY
ORDER BY market_id, date;

Data Insertion Patterns

Bulk Insert (Recommended)

import { createClient } from '@clickhouse/client'

const clickhouse = createClient({
  url: process.env.CLICKHOUSE_URL ?? 'http://localhost:8123',
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD
})

// PASS: Batch insert (efficient)
async function bulkInsertTrades(trades: Trade[]) {
  await clickhouse.insert({
    table: 'trades',
    values: trades.map(trade => ({
      id: trade.id,
      market_id: trade.market_id,
      user_id: trade.user_id,
      amount: trade.amount,
      timestamp: trade.timestamp.toISOString()
    })),
    format: 'JSONEachRow'
  })
}

// FAIL: Individual inserts (slow)
async function insertTrade(trade: Trade) {
  // Don't do this in a loop!
  await clickhouse.insert({
    table: 'trades',
    values: [{
      id: trade.id,
      market_id: trade.market_id,
      user_id: trade.user_id,
      amount: trade.amount,
      timestamp: trade.timestamp.toISOString()
    }],
    format: 'JSONEachRow'
  })
}

Streaming Insert

// For continuous data ingestion
import { Readable } from 'node:stream'

async function streamInserts(dataSource: AsyncIterable<Record<string, unknown>>) {
  await clickhouse.insert({
    table: 'trades',
    values: Readable.from(dataSource, { objectMode: true }),
    format: 'JSONEachRow'
  })
}

Materialized Views

Real-time Aggregations

-- Create materialized view for hourly stats
CREATE MATERIALIZED VIEW market_stats_hourly_mv
TO market_stats_hourly
AS SELECT
    toStartOfHour(timestamp) AS hour,
    market_id,
    sumState(amount) AS total_volume,
    countState() AS total_trades,
    uniqState(user_id) AS unique_users
FROM trades
GROUP BY hour, market_id;

-- Query the materialized view
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, market_id;

Performance Monitoring

Query Performance

-- Check slow queries
SELECT
    query_id,
    user,
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_duration_ms > 1000
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

Table Statistics

-- Check table sizes
SELECT
    database,
    table,
    formatReadableSize(sum(bytes)) AS size,
    sum(rows) AS rows,
    max(modification_time) AS latest_modification
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;

Common Analytics Queries

Time Series Analysis

-- Daily active users
SELECT
    toDate(timestamp) AS date,
    uniq(user_id) AS daily_active_users
FROM events
WHERE timestamp >= today() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date;

-- Retention analysis
SELECT
    signup_date,
    countIf(days_since_signup = 0) AS day_0,
    countIf(days_since_signup = 1) AS day_1,
    countIf(days_since_signup = 7) AS day_7,
    countIf(days_since_signup = 30) AS day_30
FROM (
    SELECT
        user_id,
        min(toDate(timestamp)) AS signup_date,
        toDate(timestamp) AS activity_date,
        dateDiff('day', signup_date, activity_date) AS days_since_signup
    FROM events
    GROUP BY user_id, activity_date
)
GROUP BY signup_date
ORDER BY signup_date DESC;

Funnel Analysis

-- Conversion funnel
SELECT
    countIf(step = 'viewed_market') AS viewed,
    countIf(step = 'clicked_trade') AS clicked,
    countIf(step = 'completed_trade') AS completed,
    round(clicked / viewed * 100, 2) AS view_to_click_rate,
    round(completed / clicked * 100, 2) AS click_to_completion_rate
FROM (
    SELECT
        user_id,
        session_id,
        event_type AS step
    FROM events
    WHERE event_date = today()
)
GROUP BY session_id;

Cohort Analysis

-- User cohorts by signup month
SELECT
    toStartOfMonth(signup_date) AS cohort,
    toStartOfMonth(activity_date) AS month,
    dateDiff('month', cohort, month) AS months_since_signup,
    count(DISTINCT user_id) AS active_users
FROM (
    SELECT
        user_id,
        min(toDate(timestamp)) OVER (PARTITION BY user_id) AS signup_date,
        toDate(timestamp) AS activity_date
    FROM events
)
GROUP BY cohort, month, months_since_signup
ORDER BY cohort, months_since_signup;

Data Pipeline Patterns

ETL Pattern

// Extract, Transform, Load
async function etlPipeline() {
  // 1. Extract from source
  const rawData = await extractFromPostgres()

  // 2. Transform
  const transformed = rawData.map(row => ({
    date: new Date(row.created_at).toISOString().split('T')[0],
    market_id: row.market_slug,
    volume: parseFloat(row.total_volume),
    trades: parseInt(row.trade_count)
  }))

  // 3. Load to ClickHouse
  await bulkInsertToClickHouse(transformed)
}

// Run periodically
setInterval(etlPipeline, 60 * 60 * 1000)  // Every hour

Change Data Capture (CDC)

// Listen to PostgreSQL changes and sync to ClickHouse
import { Client } from 'pg'

const pgClient = new Client({ connectionString: process.env.DATABASE_URL })

pgClient.query('LISTEN market_updates')

pgClient.on('notification', async (msg) => {
  const update = JSON.parse(msg.payload)

  await clickhouse.insert({
    table: 'market_updates',
    values: [
      {
        market_id: update.id,
        event_type: update.operation,  // INSERT, UPDATE, DELETE
        timestamp: new Date(),
        data: JSON.stringify(update.new_data)
      }
    ],
    format: 'JSONEachRow'
  })
})

Best Practices

1. Partitioning Strategy

  • Partition by time (usually month or day)
  • Avoid too many partitions (performance impact)
  • Use DATE type for partition key

2. Ordering Key

  • Put most frequently filtered columns first
  • Consider cardinality (high cardinality first)
  • Order impacts compression

3. Data Types

  • Use smallest appropriate type (UInt32 vs UInt64)
  • Use LowCardinality for repeated strings
  • Use Enum for categorical data

4. Avoid

  • SELECT * (specify columns)
  • FINAL (merge data before query instead)
  • Too many JOINs (denormalize for analytics)
  • Small frequent inserts (batch instead)

5. Monitoring

  • Track query performance
  • Monitor disk usage
  • Check merge operations
  • Review slow query log

Remember: ClickHouse excels at analytical workloads. Design tables for your query patterns, batch inserts, and leverage materialized views for real-time aggregations.

Database Design

Learn to THINK, not copy SQL patterns.

🎯 Selective Reading Rule

Read ONLY files relevant to the request! Check the content map, find what you need.

FileDescriptionWhen to Read
database-selection.mdPostgreSQL vs Neon vs Turso vs SQLiteChoosing database
orm-selection.mdDrizzle vs Prisma vs KyselyChoosing ORM
schema-design.mdNormalization, PKs, relationshipsDesigning schema
indexing.mdIndex types, composite indexesPerformance tuning
optimization.mdN+1, EXPLAIN ANALYZEQuery optimization
migrations.mdSafe migrations, serverless DBsSchema changes

⚠️ Core Principle

  • ASK user for database preferences when unclear
  • Choose database/ORM based on CONTEXT
  • Don't default to PostgreSQL for everything

Decision Checklist

Before designing schema:

  • Asked user about database preference?
  • Chosen database for THIS context?
  • Considered deployment environment?
  • Planned index strategy?
  • Defined relationship types?

Anti-Patterns

❌ Default to PostgreSQL for simple apps (SQLite may suffice) ❌ Skip indexing ❌ Use SELECT * in production ❌ Store JSON when structured data is better ❌ Ignore N+1 queries

When to Use

Use when selecting a database model, reviewing schema relationships and indexes, or assessing a proposed schema with the bundled advisory tools.

Schema helper

scripts/schema_validator.py is a read-only heuristic inventory, not a Prisma or Drizzle parser. Missing schemas, unsupported formats and heuristic-only results return nonzero with passed: false and analysis_complete: false. Use the project-local ORM validator for syntax and type checks; review migration and constraint semantics separately.

Limitations

  • Use this skill only when the task clearly matches the scope described above.
  • Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
  • Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.

Database Migration

Master database schema and data migrations across ORMs (Sequelize, TypeORM, Prisma), including rollback strategies and zero-downtime deployments.

When to Use This Skill

  • Migrating between different ORMs
  • Performing schema transformations
  • Moving data between databases
  • Implementing rollback procedures
  • Zero-downtime deployments
  • Database version upgrades
  • Data model refactoring

ORM Migrations

Sequelize Migrations

// migrations/20231201-create-users.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      email: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable("users");
  },
};

// Run: npx sequelize-cli db:migrate
// Rollback: npx sequelize-cli db:migrate:undo

TypeORM Migrations

// migrations/1701234567-CreateUsers.ts
import { MigrationInterface, QueryRunner, Table } from "typeorm";

export class CreateUsers1701234567 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: "users",
        columns: [
          {
            name: "id",
            type: "int",
            isPrimary: true,
            isGenerated: true,
            generationStrategy: "increment",
          },
          {
            name: "email",
            type: "varchar",
            isUnique: true,
          },
          {
            name: "created_at",
            type: "timestamp",
            default: "CURRENT_TIMESTAMP",
          },
        ],
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable("users");
  }
}

// Run: npm run typeorm migration:run
// Rollback: npm run typeorm migration:revert

Prisma Migrations

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  createdAt DateTime @default(now())
}

// Generate migration: npx prisma migrate dev --name create_users
// Apply: npx prisma migrate deploy

Schema Transformations

Adding Columns with Defaults

// Safe migration: add column with default
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn("users", "status", {
      type: Sequelize.STRING,
      defaultValue: "active",
      allowNull: false,
    });
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn("users", "status");
  },
};

Renaming Columns (Zero Downtime)

// Step 1: Add new column
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn("users", "full_name", {
      type: Sequelize.STRING,
    });

    // Copy data from old column
    await queryInterface.sequelize.query("UPDATE users SET full_name = name");
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn("users", "full_name");
  },
};

// Step 2: Update application to use new column

// Step 3: Remove old column
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.removeColumn("users", "name");
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn("users", "name", {
      type: Sequelize.STRING,
    });
  },
};

Changing Column Types

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // For large tables, use multi-step approach

    // 1. Add new column
    await queryInterface.addColumn("users", "age_new", {
      type: Sequelize.INTEGER,
    });

    // 2. Copy and transform data
    await queryInterface.sequelize.query(`
      UPDATE users
      SET age_new = CAST(age AS INTEGER)
      WHERE age IS NOT NULL
    `);

    // 3. Drop old column
    await queryInterface.removeColumn("users", "age");

    // 4. Rename new column
    await queryInterface.renameColumn("users", "age_new", "age");
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.changeColumn("users", "age", {
      type: Sequelize.STRING,
    });
  },
};

Data Transformations

Complex Data Migration

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Get all records
    const [users] = await queryInterface.sequelize.query(
      "SELECT id, address_string FROM users",
    );

    // Transform each record
    for (const user of users) {
      const addressParts = user.address_string.split(",");

      await queryInterface.sequelize.query(
        `UPDATE users
         SET street = :street,
             city = :city,
             state = :state
         WHERE id = :id`,
        {
          replacements: {
            id: user.id,
            street: addressParts[0]?.trim(),
            city: addressParts[1]?.trim(),
            state: addressParts[2]?.trim(),
          },
        },
      );
    }

    // Drop old column
    await queryInterface.removeColumn("users", "address_string");
  },

  down: async (queryInterface, Sequelize) => {
    // Reconstruct original column
    await queryInterface.addColumn("users", "address_string", {
      type: Sequelize.STRING,
    });

    await queryInterface.sequelize.query(`
      UPDATE users
      SET address_string = CONCAT(street, ', ', city, ', ', state)
    `);

    await queryInterface.removeColumn("users", "street");
    await queryInterface.removeColumn("users", "city");
    await queryInterface.removeColumn("users", "state");
  },
};

Rollback Strategies

Transaction-Based Migrations

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      await queryInterface.addColumn(
        "users",
        "verified",
        { type: Sequelize.BOOLEAN, defaultValue: false },
        { transaction },
      );

      await queryInterface.sequelize.query(
        "UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL",
        { transaction },
      );

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn("users", "verified");
  },
};

Checkpoint-Based Rollback

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Create backup table
    await queryInterface.sequelize.query(
      "CREATE TABLE users_backup AS SELECT * FROM users",
    );

    try {
      // Perform migration
      await queryInterface.addColumn("users", "new_field", {
        type: Sequelize.STRING,
      });

      // Verify migration
      const [result] = await queryInterface.sequelize.query(
        "SELECT COUNT(*) as count FROM users WHERE new_field IS NULL",
      );

      if (result[0].count > 0) {
        throw new Error("Migration verification failed");
      }

      // Drop backup
      await queryInterface.dropTable("users_backup");
    } catch (error) {
      // Restore from backup
      await queryInterface.sequelize.query("DROP TABLE users");
      await queryInterface.sequelize.query(
        "CREATE TABLE users AS SELECT * FROM users_backup",
      );
      await queryInterface.dropTable("users_backup");
      throw error;
    }
  },
};

Additional patterns and templates

More detailed templates and worked examples live in references/details.md. Read that file for the full pattern library.

Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Activate

  • Creating or altering database tables
  • Adding/removing columns or indexes
  • Running data migrations (backfill, transform)
  • Planning zero-downtime schema changes
  • Setting up migration tooling for a new project

Core Principles

  1. Every change is a migration β€” never alter production databases manually
  2. Migrations are forward-only in production β€” rollbacks use new forward migrations
  3. Schema and data migrations are separate β€” never mix DDL and DML in one migration
  4. Test migrations against production-sized data β€” a migration that works on 100 rows may lock on 10M
  5. Migrations are immutable once deployed β€” never edit a migration that has run in production

Migration Safety Checklist

Before applying any migration:

  • Migration has both UP and DOWN (or is explicitly marked irreversible)
  • No full table locks on large tables (use concurrent operations)
  • New columns have defaults or are nullable (never add NOT NULL without default)
  • Indexes created concurrently (not inline with CREATE TABLE for existing tables)
  • Data backfill is a separate migration from schema change
  • Tested against a copy of production data
  • Rollback plan documented

PostgreSQL Patterns

Adding a Column Safely

-- GOOD: Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default on existing table (requires full rewrite)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row

Adding an Index Without Downtime

-- BAD: Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Note: CONCURRENTLY cannot run inside a transaction block
-- Most migration tools need special handling for this

Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

-- Step 1: Add new column (migration 001)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill data (migration 002, data migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Update application code to read/write both columns
-- Deploy application changes

-- Step 4: Stop writing to old column, drop it (migration 003)
ALTER TABLE users DROP COLUMN username;

Removing a Column Safely

-- Step 1: Remove all application references to the column
-- Step 2: Deploy application without the column reference
-- Step 3: Drop column in next migration
ALTER TABLE orders DROP COLUMN legacy_status;

-- For Django: use SeparateDatabaseAndState to remove from model
-- without generating DROP COLUMN (then drop in next migration)

Large Data Migrations

-- BAD: Updates all rows in one transaction (locks table)
UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch update with progress
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Prisma (TypeScript/Node.js)

Workflow

# Create migration from schema changes
npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Generate client after schema changes
npx prisma generate

Schema Example

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  avatarUrl String?  @map("avatar_url")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  orders    Order[]

  @@map("users")
  @@index([email])
}

Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills):

# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index
-- migrations/20240115_add_email_index/migration.sql
-- Prisma cannot generate CONCURRENTLY, so we write it manually
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

Drizzle (TypeScript/Node.js)

Workflow

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only, no migration file)
npx drizzle-kit push

Schema Example

import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Kysely (TypeScript/Node.js)

Workflow (kysely-ctl)

# Initialize config file (kysely.config.ts)
kysely init

# Create a new migration file
kysely migrate make add_user_avatar

# Apply all pending migrations
kysely migrate latest

# Rollback last migration
kysely migrate down

# Show migration status
kysely migrate list

Migration File

// migrations/2024_01_15_001_create_user_profile.ts
import { type Kysely, sql } from 'kysely'

// IMPORTANT: Always use Kysely<any>, not your typed DB interface.
// Migrations are frozen in time and must not depend on current schema types.
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('user_profile')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('avatar_url', 'text')
    .addColumn('created_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull()
    )
    .execute()

  await db.schema
    .createIndex('idx_user_profile_avatar')
    .on('user_profile')
    .column('avatar_url')
    .execute()
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('user_profile').execute()
}

Programmatic Migrator

import { Migrator, FileMigrationProvider } from 'kysely'
import { promises as fs } from 'fs'
import * as path from 'path'
// ESM only β€” CJS can use __dirname directly
import { fileURLToPath } from 'url'
const migrationFolder = path.join(
  path.dirname(fileURLToPath(import.meta.url)),
  './migrations',
)

// `db` is your Kysely<any> database instance
const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder,
  }),
  // WARNING: Only enable in development. Disables timestamp-ordering
  // validation, which can cause schema drift between environments.
  // allowUnorderedMigrations: true,
})

const { error, results } = await migrator.migrateToLatest()

results?.forEach((it) => {
  if (it.status === 'Success') {
    console.log(`migration "${it.migrationName}" executed successfully`)
  } else if (it.status === 'Error') {
    console.error(`failed to execute migration "${it.migrationName}"`)
  }
})

if (error) {
  console.error('migration failed', error)
  process.exit(1)
}

Django (Python)

Workflow

# Generate migration from model changes
python manage.py makemigrations

# Apply migrations
python manage.py migrate

# Show migration status
python manage.py showmigrations

# Generate empty migration for custom SQL
python manage.py makemigrations --empty app_name -n description

Data Migration

from django.db import migrations

def backfill_display_names(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    batch_size = 5000
    users = User.objects.filter(display_name="")
    while users.exists():
        batch = list(users[:batch_size])
        for user in batch:
            user.display_name = user.username
        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor):
    pass  # Data migration, no reverse needed

class Migration(migrations.Migration):
    dependencies = [("accounts", "0015_add_display_name")]

    operations = [
        migrations.RunPython(backfill_display_names, reverse_backfill),
    ]

SeparateDatabaseAndState

Remove a column from the Django model without dropping it from the database immediately:

class Migration(migrations.Migration):
    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.RemoveField(model_name="user", name="legacy_field"),
            ],
            database_operations=[],  # Don't touch the DB yet
        ),
    ]

golang-migrate (Go)

Workflow

# Create migration pair
migrate create -ext sql -dir migrations -seq add_user_avatar

# Apply all pending migrations
migrate -path migrations -database "$DATABASE_URL" up

# Rollback last migration
migrate -path migrations -database "$DATABASE_URL" down 1

# Force version (fix dirty state)
migrate -path migrations -database "$DATABASE_URL" force VERSION

Migration Files

-- migrations/000003_add_user_avatar.up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;

-- migrations/000003_add_user_avatar.down.sql
DROP INDEX IF EXISTS idx_users_avatar;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

Zero-Downtime Migration Strategy

For critical production changes, follow the expand-contract pattern:

Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Deploy: app writes to BOTH old and new
  - Backfill existing data

Phase 2: MIGRATE
  - Deploy: app reads from NEW, writes to BOTH
  - Verify data consistency

Phase 3: CONTRACT
  - Deploy: app only uses NEW
  - Drop old column/table in separate migration

Timeline Example

Day 1: Migration adds new_status column (nullable)
Day 1: Deploy app v2 β€” writes to both status and new_status
Day 2: Run backfill migration for existing rows
Day 3: Deploy app v3 β€” reads from new_status only
Day 7: Migration drops old status column

Anti-Patterns

Anti-PatternWhy It FailsBetter Approach
Manual SQL in productionNo audit trail, unrepeatableAlways use migration files
Editing deployed migrationsCauses drift between environmentsCreate new migration instead
NOT NULL without defaultLocks table, rewrites all rowsAdd nullable, backfill, then add constraint
Inline index on large tableBlocks writes during buildCREATE INDEX CONCURRENTLY
Schema + data in one migrationHard to rollback, long transactionsSeparate migrations
Dropping column before removing codeApplication errors on missing columnRemove code first, drop column next deploy

MySQL Patterns

Use this skill when working on MySQL or MariaDB schema design, migrations, slow-query investigation, queue-style transactions, connection pools, or production database configuration. Prefer exact version checks before applying a feature-specific pattern because MySQL and MariaDB have diverged in several SQL details.

Activation

  • Designing MySQL or MariaDB tables, indexes, and constraints
  • Reviewing migrations before they run on large production tables
  • Debugging slow queries, lock waits, deadlocks, or connection exhaustion
  • Adding keyset pagination, upserts, full-text search, JSON columns, or queues
  • Configuring application connection pools, read replicas, TLS, or slow logs

Version Check

Start by identifying the engine and version:

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Keep MySQL and MariaDB guidance separate when syntax differs:

  • MySQL documents row aliases as the replacement for VALUES(col) in ON DUPLICATE KEY UPDATE; VALUES(col) is deprecated there.
  • MariaDB documents VALUES(col) as the supported way to reference inserted values in ON DUPLICATE KEY UPDATE; use it for cross-engine compatibility.
  • SKIP LOCKED is appropriate for queue-like work only. It skips locked rows and can return an inconsistent view, so do not use it for general accounting or integrity-sensitive reads.

Schema Defaults

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Default choices:

Use CasePreferAvoid
Surrogate primary keysBIGINT UNSIGNED AUTO_INCREMENTINT for tables that can grow beyond 2B rows
UUID lookup keysBINARY(16) with conversion helpersVARCHAR(36) primary keys on hot tables
Money and exact quantitiesDECIMAL(p, s)FLOAT or DOUBLE
User-facing textutf8mb4 tables and indexesMySQL utf8 / utf8mb3 defaults
Application timestampsDATETIME with UTC managed by the appAssuming DATETIME stores time zone metadata
Soft deletesdeleted_at DATETIME NULL plus scoped indexesFiltering soft-deleted rows without an index
Extensible status valueslookup table or constrained VARCHARENUM when values change often

Indexing

Composite index order usually follows equality predicates first, then range or sort columns:

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

Use EXPLAIN before adding or changing an index:

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Signals to investigate:

FieldRisk Signal
typeALL on a large table
keyNULL when a selective predicate exists
rowsVery high row estimate for an interactive path
ExtraUsing temporary, Using filesort, or broad Using where

Avoid adding indexes blindly. Each index increases write cost, migration time, backup size, and buffer-pool pressure.

Query Patterns

Upsert

Cross-engine-compatible form:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

MySQL row-alias form:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

Use the row-alias form only after confirming the target is MySQL. Use VALUES(col) for MariaDB or mixed MySQL/MariaDB fleets.

Keyset Pagination

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Back it with an index that matches the cursor:

CREATE INDEX idx_products_created_id ON products (created_at, id);

Do not use deep OFFSET pagination on large tables; it makes the server scan and discard rows before returning the page.

JSON Fields

Use JSON columns for extension data, not for fields that need heavy relational filtering or constraints.

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

For frequently queried JSON paths, expose a generated column and index that column. Keep foreign keys, ownership, tenancy, and lifecycle fields relational.

Full-Text Search

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

Use external search when you need typo tolerance, complex ranking, cross-table facets, or language-specific analysis beyond built-in full-text behavior.

Transactions

Keep transactions short and lock rows in a consistent order:

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

Deadlock and lock-wait checklist:

  • Lock rows in a deterministic order across code paths.
  • Do external API calls before opening the transaction, not inside it.
  • Add indexes for predicates used in UPDATE, DELETE, and locking reads.
  • On deadlock, roll back and retry the whole transaction with a bounded retry budget.
  • Capture SHOW ENGINE INNODB STATUS\G soon after a deadlock; it is overwritten by later events.

Queue-style worker claim:

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

Use SKIP LOCKED only for queue-like workloads where skipping a locked row is acceptable. It is not a replacement for normal transactional consistency.

Connection Pools

SQLAlchemy example:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:secret@db.internal/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Node.js mysql2 example:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

Keep application pool recycling below the server wait_timeout. If the server uses wait_timeout = 300, a pool_recycle around 240 seconds is coherent; pool_pre_ping still helps recover from network and failover events.

Diagnostics

Useful first-pass commands:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Enable the slow log in a controlled environment:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Use EXPLAIN ANALYZE only when it is safe to execute the query. It runs the statement and can be expensive on production-sized data.

Replication

Read replicas can lag. Do not route read-your-own-write paths, checkout flows, permission checks, or idempotency-key reads to a replica immediately after a write.

-- MySQL legacy terminology, still common in existing fleets
SHOW SLAVE STATUS\G;

-- Newer terminology where supported
SHOW REPLICA STATUS\G;

Check the engine/version before standardizing on one command. Monitor replica SQL thread health, IO thread health, and lag, not just whether the TCP connection is alive.

Security

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

Security review points:

  • Do not grant ALL PRIVILEGES or *.* to application users.
  • Require TLS for application users when traffic crosses hosts or networks.
  • Store credentials in the platform secret manager, not in examples, scripts, or repository files.
  • Separate migration/admin users from runtime application users.
  • Audit public network exposure and bind addresses before tuning performance.

Configuration

Example starting point for a dedicated database host:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

Treat configuration values as a prompt for review, not a universal preset. Size memory, connections, log retention, and durability settings from workload, hardware, backup policy, and recovery objectives.

Anti-Patterns

Anti-PatternRiskBetter Pattern
SELECT * in hot pathsOver-fetching and brittle clientsSelect explicit columns
Deep OFFSET paginationLinear scans and slow pagesKeyset pagination
No index on foreign-key joinsSlow joins and lock-heavy deletesIndex FK columns intentionally
Long transactionsLock waits and large undo historyCommit small units of work
Direct DML against mysql.userGrant-table corruption riskUse CREATE USER, ALTER USER, DROP USER
Application user with admin grantsHigh blast radiusLeast-privilege runtime user
Pool recycle above wait_timeoutStale pooled connectionsRecycle below timeout and pre-ping
Replica reads after writesStale user-facing statePin read-after-write flows to primary

Output Expectations

When this skill is used for review, return:

  1. Engine/version assumptions.
  2. Highest-risk correctness, lock, security, and migration issues.
  3. Exact SQL or code changes for the safe path.
  4. Validation plan: EXPLAIN, migration dry run, lock/deadlock check, and rollback criteria.
  5. Any MySQL/MariaDB syntax differences that affect the recommendation.

Related

  • Skill: postgres-patterns - PostgreSQL-specific schema and query patterns
  • Skill: database-migrations - migration planning and rollout safety
  • Skill: backend-patterns - API and service-layer patterns
  • Skill: security-review - secret handling, auth, and least privilege
  • Agent: database-reviewer - broader database review workflow

PostgreSQL Patterns

Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.

When to Activate

  • Writing SQL queries or migrations
  • Designing database schemas
  • Troubleshooting slow queries
  • Implementing Row Level Security
  • Setting up connection pooling

Quick Reference

Index Cheat Sheet

Query PatternIndex TypeExample
WHERE col = valueB-tree (default)CREATE INDEX idx ON t (col)
WHERE col > valueB-treeCREATE INDEX idx ON t (col)
WHERE a = x AND b > yCompositeCREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'GINCREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ queryGINCREATE INDEX idx ON t USING gin (col)
Time-series rangesBRINCREATE INDEX idx ON t USING brin (col)

Data Type Quick Reference

Use CaseCorrect TypeAvoid
IDsbigintint, random UUID
Stringstextvarchar(255)
Timestampstimestamptztimestamp
Moneynumeric(10,2)float
Flagsbooleanvarchar, int

Common Patterns

Composite Index Order:

-- Equality columns first, then range columns
CREATE INDEX idx ON orders (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'

Covering Index:

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Avoids table lookup for SELECT email, name, created_at

Partial Index:

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- Smaller index, only includes active users

RLS Policy (Optimized):

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- Wrap in SELECT!

UPSERT:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

Cursor Pagination:

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)

Queue Processing:

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Anti-Pattern Detection

-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Find slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Check table bloat
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Configuration Template

-- Connection limits (adjust for RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Timeouts
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Security defaults
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Related

  • Agent: database-reviewer - Full database review workflow
  • Skill: clickhouse-io - ClickHouse analytics patterns
  • Skill: backend-patterns - API and backend patterns

Based on Supabase Agent Skills (credit: Supabase team) (MIT License)

PostgreSQL Table Design

When to Use

  • Designing a new PostgreSQL schema, or reviewing one before it ships.
  • Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.
  • Deciding whether and how to partition a large table, or how to store semi-structured data.
  • Planning a schema change on a live database without downtime.

The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing, and worked DDL examples are in references/details.md; open it when a section below points there.

Core Rules

  • Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
  • Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic.
  • Add NOT NULL everywhere it is semantically required; use DEFAULTs for common values.
  • Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts, and join keys.
  • Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integers; DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).

PostgreSQL Gotchas

  • Identifiers: unquoted β†’ lowercased. Avoid quoted/mixed-case names; use snake_case.
  • Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE NULLS NOT DISTINCT (...) (PG15+) to restrict to one NULL.
  • FK indexes: PostgreSQL does not auto-index FK columns. Add them.
  • No silent coercions: length/precision overflows error out (no truncation). Inserting 999 into NUMERIC(2,0) fails, unlike databases that silently truncate or round.
  • Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...).
  • Heap storage: no clustered PK by default; CLUSTER is a one-off reorganization, not maintained on later inserts.
  • MVCC: updates/deletes leave dead tuples; vacuum handles themβ€”design to avoid hot wide-row churn.

Data Types

  • IDs: BIGINT GENERATED ALWAYS AS IDENTITY; UUID for distributed or opaque IDs, generated with uuidv7() (PG18+) or gen_random_uuid().
  • Numbers: BIGINT unless storage is critical; DOUBLE PRECISION over REAL; NUMERIC(p,s) for money and exact decimals.
  • Strings: TEXT, with CHECK (LENGTH(col) <= n) when a limit is needed; BYTEA for binary. Case-insensitive lookups: expression index on LOWER(col), or CITEXT when a constraint must be case-insensitive.
  • Time: TIMESTAMPTZ, DATE, INTERVAL. now() is transaction start; clock_timestamp() is wall clock.
  • Booleans: BOOLEAN NOT NULL unless tri-state is required.
  • Enums: CREATE TYPE ... AS ENUM only for small, stable sets; evolving business values get TEXT + CHECK or a lookup table.
  • JSONB over JSON, indexed with GIN, for optional/semi-structured attributes only.
  • Arrays, ranges, network, geometric, full-text, domain, composite, and vector types, plus TOAST storage and collation control: see references/details.md.

Types to avoid

AvoidUse instead
timestamp (without time zone)timestamptz
char(n), varchar(n)text (+ CHECK on length if needed)
moneynumeric
timetztimestamptz
timestamptz(0) or any precisiontimestamptz
serialgenerated always as identity

Constraints

  • PK: implicit UNIQUE + NOT NULL; creates a B-tree index.
  • FK: specify ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Index the referencing column. Use DEFERRABLE INITIALLY DEFERRED for circular dependencies checked at commit.
  • UNIQUE: creates a B-tree index; allows multiple NULLs unless NULLS NOT DISTINCT (PG15+). Prefer NULLS NOT DISTINCT unless duplicate NULLs are wanted.
  • CHECK: row-local; NULL passes (three-valued logic). Combine with NOT NULL: price NUMERIC NOT NULL CHECK (price > 0).
  • EXCLUDE: prevents overlaps with operators, e.g. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) stops double-booking. Needs a GiST-capable type.

Indexing

  • B-tree: default for equality/range (=, <, >, BETWEEN, ORDER BY).
  • Composite: leftmost-prefix rule (WHERE a = ? AND b > ? uses (a,b); WHERE b = ? does not). Most selective columns first.
  • Covering: CREATE INDEX ON tbl (id) INCLUDE (name, email) for index-only scans.
  • Partial: hot subsets, CREATE INDEX ON tbl (user_id) WHERE status = 'active'.
  • Expression: CREATE INDEX ON tbl (LOWER(email)); the query must use the same expression.
  • GIN: JSONB containment/existence, arrays, full-text search. GiST: ranges, geometry, exclusion constraints.
  • BRIN: large, naturally ordered data (time-series) at minimal storage cost; effective when disk order correlates with the indexed column.

Partitioning

  • Use for large tables (>100M rows) whose queries consistently filter on the partition key, or where maintenance (pruning, bulk replacement) follows a key.
  • RANGE for time-series (PARTITION BY RANGE (created_at); TimescaleDB automates it with retention and compression), LIST for discrete values, HASH for even distribution without a natural key.
  • Constraint exclusion: the planner prunes partitions through their CHECK constraints; declarative partitioning (PG10+) creates them for you.
  • Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
  • Limitations: no global UNIQUE constraintsβ€”include the partition key in PK/UNIQUE. FKs from partitioned tables need PG11+, FKs referencing a partitioned table need PG12+; on older versions, use triggers.

Examples

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);
CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);
-- JSONB attributes with a generated, indexable scalar
CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);

Going deeper

references/details.md holds the material this file only names:

  • The full data-type catalog: TOAST storage, collations, arrays, ranges, network, geometric, text search, domains, composites, vectors.
  • Table types (TEMPORARY, UNLOGGED) and row-level security.
  • Constraint and index notes, and partitioning DDL for RANGE, LIST, and HASH.
  • Workload patterns: update-heavy, insert-heavy, upsert design, safe schema evolution.
  • Generated columns and extensions (pg_trgm, citext, timescaledb, postgis, pgvector, and more).
  • JSONB indexing strategies, including jsonb_path_ops and extracted B-tree columns.

Prisma Patterns

Production patterns and non-obvious traps for Prisma ORM in TypeScript backends.

Check your version before applying patterns. The Prisma API surface has evolved across major releases:

npx prisma --version

Notable API differences across versions:

  • relationJoins can load relations via JOIN rather than separate queries, but may cause row explosion on large 1:N relations or deep include β€” benchmark both approaches
  • omit field modifier and prisma.$extends Client Extensions API were added
  • Newer installs: the package may be named prisma instead of @prisma/client; PrismaClient may require a driver adapter (e.g. @prisma/adapter-pg); datasource.url may live in prisma.config.ts instead of schema.prisma
  • CLI commands (migrate dev, migrate deploy, generate) are unchanged across versions

When to Activate

  • Designing or modifying Prisma schema models and relations
  • Writing queries, transactions, or pagination logic
  • Using updateMany, deleteMany, or any bulk operation
  • Running or planning database migrations
  • Deploying to serverless environments (Vercel, Lambda, Cloudflare Workers)
  • Implementing soft delete or multi-tenant row filtering

Core Concepts

ID Strategy

StrategyUse WhenAvoid When
@default(cuid())Default choice β€” URL-safe, sortable, no collisionsSequential IDs needed for external systems
@default(uuid())Interoperability with non-Prisma systems requiredHigh-write tables (random UUIDs fragment B-tree indexes)
@default(autoincrement())Internal join tables, audit logsPublic-facing IDs (exposes record count)

Schema Defaults

model User {
  id        String    @id @default(cuid())
  email     String    @unique  // @unique already creates an index β€” no @@index needed
  name      String
  role      Role      @default(USER)
  posts     Post[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  deletedAt DateTime?

  @@index([createdAt])
  @@index([deletedAt, createdAt]) // composite for soft-delete + sort queries
}
  • Add @@index on every foreign key and column used in WHERE or ORDER BY.
  • Declare deletedAt DateTime? upfront when soft delete is a foreseeable requirement β€” adding it later requires a migration on a live table.
  • updatedAt @updatedAt is set automatically by Prisma on update and upsert only (see Anti-Patterns for bulk update trap).

include vs select

includeselect
ReturnsAll scalar fields + specified relationsOnly specified fields
Use whenYou need most fields plus a relationHot paths, large tables, avoiding over-fetch
PerformanceMay over-fetch on wide tablesMinimal payload, faster on large datasets
Prisma 5 noteUses JOIN by default (relationJoins)Same
// include β€” all columns + relation
const user = await prisma.user.findUnique({
  where: { id },
  include: { posts: { select: { id: true, title: true } } },
});

// select β€” explicit allowlist
const user = await prisma.user.findUnique({
  where: { id },
  select: { id: true, email: true, name: true },
});

Never return raw Prisma entities from API responses β€” map to response DTOs to control exposed fields:

// BAD: leaks passwordHash, deletedAt, internal fields
return await prisma.user.findUniqueOrThrow({ where: { id } });

// GOOD: explicit DTO mapping
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
return { id: user.id, name: user.name, email: user.email };

Transaction Form Selection

SituationUse
Independent operations, no inter-dependencyArray form
Later step depends on earlier resultInteractive form
External calls (email, HTTP) involvedOutside transaction entirely
// Array form β€” batched in one round trip
const [user, post] = await prisma.$transaction([
  prisma.user.update({ where: { id }, data: { name } }),
  prisma.post.create({ data: { title, authorId: id } }),
]);

// Interactive form β€” use tx client only, never the outer prisma client
const post = await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id } });
  if (user.role !== 'ADMIN') throw new Error('Forbidden');
  return tx.post.create({ data: { title, authorId: user.id } });
});

PrismaClient Singleton

Each PrismaClient instance opens its own connection pool. Instantiate once.

// lib/prisma.ts

// Option A β€” adapter-based initialization (required by newer Prisma installs)
import { PrismaClient } from '@prisma/client'; // or the generated client path for your setup
import { PrismaPg } from '@prisma/adapter-pg';

function createPrismaClient() {
  const adapter = new PrismaPg({
    connectionString: process.env.DATABASE_URL!,
  });
  return new PrismaClient({
    adapter,
    log: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'],
  });
}

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma = globalForPrisma.prisma ?? createPrismaClient();

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Option B β€” direct initialization (older installs, no adapter needed)
// import { PrismaClient } from '@prisma/client';
// export const prisma = globalForPrisma.prisma ?? new PrismaClient({ ... });

Use Option A if your Prisma install requires an adapter argument in the PrismaClient constructor. Use Option B if new PrismaClient() works without arguments. Let the compiler tell you which is correct.

The globalThis pattern prevents duplicate instances during hot reload (Next.js, nodemon, ts-node-dev).

N+1 Problem

Loading relations inside a loop issues one query per row.

// BAD: N+1 β€” one extra query per user
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: single query
const users = await prisma.user.findMany({ include: { posts: true } });

With Prisma 5+ relationJoins, the include form uses a single JOIN. On large 1:N sets this may increase result set size β€” benchmark both approaches if the relation can return many rows per parent.

Code Examples

Cursor Pagination (preferred for feeds and large datasets)

async function getPosts(cursor?: string, limit = 20) {
  const items = await prisma.post.findMany({
    where: { published: true },
    orderBy: [
      { createdAt: 'desc' },
      { id: 'desc' }, // secondary sort prevents unstable pagination on duplicate timestamps
    ],
    take: limit + 1,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
  });

  const hasNextPage = items.length > limit;
  if (hasNextPage) items.pop();

  return { items, nextCursor: hasNextPage ? items[items.length - 1].id : null };
}

Fetch limit + 1 and pop β€” canonical way to detect hasNextPage without an extra count query. Always include a unique field (e.g. id) as a secondary orderBy to prevent unstable pagination when multiple rows share the same timestamp. Use offset pagination only when users need to jump to arbitrary pages (admin tables).

Soft Delete

// Always filter explicitly β€” do not rely on middleware (hides behavior, hard to debug)
const activeUsers = await prisma.user.findMany({ where: { deletedAt: null } });

await prisma.user.update({ where: { id }, data: { deletedAt: new Date() } });
await prisma.user.update({ where: { id }, data: { deletedAt: null } }); // restore

Error Handling

import { Prisma } from '@prisma/client'; // or the generated client path for your setup

try {
  await prisma.user.create({ data: { email } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') throw new ConflictError('Email already exists');
    if (e.code === 'P2025') throw new NotFoundError('Record not found');
    if (e.code === 'P2003') throw new BadRequestError('Referenced record does not exist');
  }
  throw e;
}

Common codes: P2002 unique violation Β· P2025 not found Β· P2003 foreign key violation.

Catch at the service boundary and translate to domain errors. Never expose raw Prisma messages to API consumers.

Connection Pool β€” Serverless

Embed connection params directly in DATABASE_URL β€” string concatenation breaks if the URL already has query parameters (e.g. ?schema=public):

# .env β€” preferred: embed params in the URL
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=1&pool_timeout=20"

# With an external pooler (PgBouncer, Supabase pooler)
DATABASE_URL="postgresql://user:pass@host/db?pgbouncer=true&connection_limit=1"
// Vercel, AWS Lambda, and similar serverless runtimes:
// cap pool to 1 per instance; connection_limit and pool_timeout controlled via DATABASE_URL

// Adapter-based setup (if your Prisma install requires an adapter):
import { PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
});

// Direct setup (if your Prisma install does not require an adapter):
// const prisma = new PrismaClient();

Anti-Patterns

updateMany returns a count, not records

// BAD: result is { count: 2 } β€” users[0] is undefined
const users = await prisma.user.updateMany({ where: { role: 'GUEST' }, data: { role: 'USER' } });

// GOOD: capture IDs first, then update, then fetch only the affected rows
const targets = await prisma.user.findMany({
  where: { role: 'GUEST' },
  select: { id: true },
});
const ids = targets.map((u) => u.id);
await prisma.user.updateMany({ where: { id: { in: ids } }, data: { role: 'USER' } });
const updated = await prisma.user.findMany({ where: { id: { in: ids } } });

Same applies to deleteMany β€” returns { count: n }, never the deleted rows.

$transaction interactive form times out after 5 seconds

// BAD: external call inside transaction exceeds 5s default β†’ "Transaction already closed"
await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUniqueOrThrow({ where: { id } });
  await sendWelcomeEmail(user.email); // external call
  await tx.user.update({ where: { id }, data: { emailSent: true } });
});

// GOOD: external calls outside the transaction
const user = await prisma.user.findUniqueOrThrow({ where: { id } });
await sendWelcomeEmail(user.email);
await prisma.user.update({ where: { id }, data: { emailSent: true } });

// Only raise timeout when bulk processing genuinely needs it
await prisma.$transaction(async (tx) => { ... }, { timeout: 30_000 });

migrate dev can reset the database

migrate dev detects schema drift and may prompt to reset the DB, dropping all data.

# NEVER on shared dev, staging, or production
npx prisma migrate dev --name add_column

# Safe everywhere except local solo dev
npx prisma migrate deploy

# Check drift without applying
npx prisma migrate diff \
  --from-migrations ./prisma/migrations \
  --to-schema-datamodel ./prisma/schema.prisma \
  --shadow-database-url "$SHADOW_DATABASE_URL"

Manually editing a migration file breaks future deploys

Prisma checksums every migration file. Editing after apply causes P3006 checksum mismatch on every environment where the original already ran. Create a new migration instead.

Breaking schema changes require multi-step migration

Adding NOT NULL to an existing column or renaming a column in one migration will lock the table or drop data. Use expand-and-contract:

# Step 1: create migration locally, then deploy
npx prisma migrate dev --name add_new_column   # local only
npx prisma migrate deploy                       # staging / production
// Step 2: backfill data (run in a script or migration job, not in the shell)
await prisma.user.updateMany({ data: { newColumn: derivedValue } });
# Step 3: create the NOT NULL constraint migration locally, then deploy
npx prisma migrate dev --name make_new_column_required  # local only
npx prisma migrate deploy                               # staging / production

@updatedAt does not fire on updateMany

@updatedAt is set automatically only on update and upsert. Bulk writes leave it stale.

// BAD: updatedAt stays at its old value
await prisma.post.updateMany({ where: { authorId }, data: { published: true } });

// GOOD
await prisma.post.updateMany({
  where: { authorId },
  data: { published: true, updatedAt: new Date() },
});

Soft delete + findUniqueOrThrow leaks deleted records

findUniqueOrThrow throws P2025 only when the row does not exist in the DB. Soft-deleted rows still exist and are returned without error.

findUniqueOrThrow requires a unique constraint field in where β€” adding deletedAt: null alongside id breaks the type because { id, deletedAt } is not a compound unique constraint. Use findFirstOrThrow instead.

// BAD: returns soft-deleted user
const user = await prisma.user.findUniqueOrThrow({ where: { id } });

// BAD: Prisma type error β€” { id, deletedAt } is not a unique constraint
const user = await prisma.user.findUniqueOrThrow({ where: { id, deletedAt: null } });

// GOOD: findFirstOrThrow supports arbitrary where conditions
const user = await prisma.user.findFirstOrThrow({ where: { id, deletedAt: null } });

deleteMany without where deletes every row

// BAD: silently wipes the table
await prisma.post.deleteMany();

// GOOD
await prisma.post.deleteMany({ where: { authorId: userId } });

Best Practices

RuleReason
migrate deploy in CI/CD, migrate dev only locallymigrate dev can reset the DB on drift
Map entities to response DTOsPrevents leaking internal fields
Catch PrismaClientKnownRequestError at service boundaryTranslate to domain errors
Prefer *OrThrow methods over manual null checksThrows P2025 automatically; use findFirstOrThrow when filtering non-unique fields
connection_limit=1 + external pooler in serverlessPrevents connection exhaustion
Always provide where on deleteManyPrevents accidental table wipe
Set updatedAt: new Date() manually in updateMany@updatedAt skips bulk writes

Related Skills

  • nestjs-patterns β€” NestJS service layer that integrates Prisma
  • postgres-patterns β€” PostgreSQL-level indexing and connection tuning
  • database-migrations β€” multi-step migration planning for production
  • backend-patterns β€” general API and service layer design

Redis Patterns

Quick reference for Redis best practices across common backend use cases.

How It Works

Redis is an in-memory data structure store that supports strings, hashes, lists, sets, sorted sets, streams, and more. Individual Redis commands are atomic on a single instance; multi-step workflows require Lua scripts, MULTI/EXEC transactions, or explicit synchronization to stay atomic. Data is optionally persisted via RDB snapshots or AOF logs. Clients communicate over TCP using the RESP protocol; connection pools are essential to avoid per-request handshake overhead.

When to Activate

  • Adding caching to an application
  • Implementing rate limiting or throttling
  • Building distributed locks or coordination
  • Setting up session or token storage
  • Using Pub/Sub or Redis Streams for messaging
  • Configuring Redis in production (pooling, eviction, clustering)

Data Structure Cheat Sheet

Use CaseStructureExample Key
Simple cacheStringproduct:123
User sessionHashsession:abc
LeaderboardSorted Setscores:weekly
Unique visitorsSetvisitors:2024-01-01
Activity feedListfeed:user:456
Event streamStreamevents:orders
Counters / rate limitsString (INCR)ratelimit:user:123
Bloom filter / HLLHyperLogLoghll:pageviews

Core Patterns

Cache-Aside (Lazy Loading)

import redis
import json

r = redis.Redis(host='localhost', port=6379, decode_responses=True)

def get_product(product_id: int):
    cache_key = f"product:{product_id}"
    cached = r.get(cache_key)

    if cached:
        return json.loads(cached)

    product = db.query("SELECT * FROM products WHERE id = %s", product_id)
    r.setex(cache_key, 3600, json.dumps(product))  # TTL: 1 hour
    return product

Write-Through Cache

def update_product(product_id: int, data: dict):
    # Write to DB first
    db.execute("UPDATE products SET ... WHERE id = %s", product_id)

    # Immediately update cache
    cache_key = f"product:{product_id}"
    r.setex(cache_key, 3600, json.dumps(data))

Cache Invalidation

# Tag-based invalidation β€” group related keys under a set
def cache_product(product_id: int, category_id: int, data: dict):
    key = f"product:{product_id}"
    tag = f"tag:category:{category_id}"
    pipe = r.pipeline(transaction=True)
    pipe.setex(key, 3600, json.dumps(data))
    pipe.sadd(tag, key)
    pipe.expire(tag, 3600)
    pipe.execute()

def invalidate_category(category_id: int):
    tag = f"tag:category:{category_id}"
    keys = r.smembers(tag)
    if keys:
        r.delete(*keys)
    r.delete(tag)

Session Storage

import time
import uuid

def create_session(user_id: int, ttl: int = 86400) -> str:
    session_id = str(uuid.uuid4())
    key = f"session:{session_id}"
    pipe = r.pipeline(transaction=True)
    pipe.hset(key, mapping={
        "user_id": user_id,
        "created_at": int(time.time()),
    })
    pipe.expire(key, ttl)
    pipe.execute()
    return session_id

def get_session(session_id: str) -> dict | None:
    data = r.hgetall(f"session:{session_id}")
    return data if data else None

def delete_session(session_id: str):
    r.delete(f"session:{session_id}")

Rate Limiting

Fixed Window (Simple)

def is_rate_limited(user_id: int, limit: int = 100, window: int = 60) -> bool:
    key = f"ratelimit:{user_id}:{int(time.time()) // window}"
    pipe = r.pipeline(transaction=True)
    pipe.incr(key)
    pipe.expire(key, window)
    count, _ = pipe.execute()
    return count > limit

Sliding Window (Lua β€” Atomic)

-- sliding_window.lua
local key = KEYS[1]
local now = tonumber(ARGV[1])
local window = tonumber(ARGV[2])
local limit = tonumber(ARGV[3])

redis.call('ZREMRANGEBYSCORE', key, 0, now - window)
local count = redis.call('ZCARD', key)

if count < limit then
    -- Use unique member (now + sequence) to avoid collisions within the same millisecond
    local seq_key = key .. ':seq'
    local seq = redis.call('INCR', seq_key)
    redis.call('EXPIRE', seq_key, math.ceil(window / 1000))
    redis.call('ZADD', key, now, now .. '-' .. seq)
    redis.call('EXPIRE', key, math.ceil(window / 1000))
    return 1
end
return 0
sliding_window = r.register_script(open('sliding_window.lua').read())

def allow_request(user_id: int) -> bool:
    key = f"ratelimit:sliding:{user_id}"
    now = int(time.time() * 1000)
    return bool(sliding_window(keys=[key], args=[now, 60000, 100]))

Distributed Locks

Distributed Lock (Single Node β€” SET NX PX)

import uuid

def acquire_lock(resource: str, ttl_ms: int = 5000) -> str | None:
    lock_key = f"lock:{resource}"
    token = str(uuid.uuid4())
    acquired = r.set(lock_key, token, px=ttl_ms, nx=True)
    return token if acquired else None

def release_lock(resource: str, token: str) -> bool:
    release_script = """
    if redis.call('get', KEYS[1]) == ARGV[1] then
        return redis.call('del', KEYS[1])
    else
        return 0
    end
    """
    result = r.eval(release_script, 1, f"lock:{resource}", token)
    return bool(result)

# Usage
token = acquire_lock("order:payment:123")
if token:
    try:
        process_payment()
    finally:
        release_lock("order:payment:123", token)

For multi-node setups use the redlock-py library which implements the full Redlock algorithm.

Pub/Sub & Streams

Pub/Sub (Fire-and-Forget)

# Publisher
def publish_event(channel: str, payload: dict):
    r.publish(channel, json.dumps(payload))

# Subscriber (blocking β€” run in separate thread/process)
def subscribe_events(channel: str):
    pubsub = r.pubsub()
    pubsub.subscribe(channel)
    for message in pubsub.listen():
        if message['type'] == 'message':
            handle(json.loads(message['data']))

Redis Streams (Durable Queue)

# Producer
def emit(stream: str, event: dict):
    r.xadd(stream, event, maxlen=10000)  # Cap stream length

# Consumer group β€” guarantees at-least-once delivery
try:
    r.xgroup_create('events:orders', 'processor', id='0', mkstream=True)
except Exception:
    pass  # Group already exists

def consume(stream: str, group: str, consumer: str):
    while True:
        messages = r.xreadgroup(group, consumer, {stream: '>'}, count=10, block=2000)
        for _, entries in (messages or []):
            for msg_id, data in entries:
                process(data)
                r.xack(stream, group, msg_id)

Prefer Streams over Pub/Sub when you need delivery guarantees, consumer groups, or replay.

Key Design

Naming Conventions

# Pattern: resource:id:field
user:123:profile
order:456:status
cache:product:789

# Pattern: namespace:resource:id
myapp:session:abc123
myapp:ratelimit:user:123

# Pattern: resource:date (time-bound keys)
stats:pageviews:2024-01-01

TTL Strategy

Data TypeSuggested TTL
User session24h (86400)
API response cache5–15 min
Rate limit windowMatch window size
Short-lived tokens5–10 min
Leaderboard1h–24h
Static/reference data1h–1 week

Always set a TTL. Keys without TTL accumulate indefinitely and cause memory pressure.

Connection Management

Connection Pooling

from redis import ConnectionPool, Redis

pool = ConnectionPool(
    host='localhost',
    port=6379,
    db=0,
    max_connections=20,
    decode_responses=True,
    socket_connect_timeout=2,
    socket_timeout=2,
)

r = Redis(connection_pool=pool)

Cluster Mode

from redis.cluster import RedisCluster

r = RedisCluster(
    startup_nodes=[{"host": "redis-1", "port": 6379}],
    decode_responses=True,
    skip_full_coverage_check=True,
)

Sentinel (High Availability)

from redis.sentinel import Sentinel

sentinel = Sentinel(
    [('sentinel-1', 26379), ('sentinel-2', 26379)],
    socket_timeout=0.5,
)
master = sentinel.master_for('mymaster', decode_responses=True)
replica = sentinel.slave_for('mymaster', decode_responses=True)

Eviction Policies

PolicyBehaviorBest For
noevictionError on write when fullQueues / critical data
allkeys-lruEvict least recently usedGeneral cache
volatile-lruLRU only among keys with TTLMixed data store
allkeys-lfuEvict least frequently usedSkewed access patterns
volatile-ttlEvict soonest-to-expirePrioritize long-lived data

Set via redis.conf: maxmemory-policy allkeys-lru

Anti-Patterns

Anti-PatternProblemFix
Keys with no TTLMemory grows unboundedAlways set TTL
KEYS * in productionBlocks the server (O(N))Use SCAN cursor
Storing large blobs (>100KB)Slow serialization, memory pressureStore reference + fetch from object store
Single Redis for everythingNo isolation between cache & queueUse separate DBs or instances
Ignoring connection pool limitsConnection exhaustion under loadSize pool to workload
Not handling cache miss stampedeThundering herd on cold startUse locks or probabilistic early expiry
FLUSHALL without thoughtWipes entire instanceScope deletes by key pattern

Cache Miss Stampede Prevention

import threading

_locks: dict[str, threading.Lock] = {}
_locks_mutex = threading.Lock()

def get_with_lock(key: str, fetch_fn, ttl: int = 300):
    cached = r.get(key)
    if cached:
        return json.loads(cached)

    with _locks_mutex:
        if key not in _locks:
            _locks[key] = threading.Lock()
        lock = _locks[key]
    with lock:
        cached = r.get(key)  # Re-check after acquiring lock
        if cached:
            return json.loads(cached)
        value = fetch_fn()
        r.setex(key, ttl, json.dumps(value))
        return value

Note: for multi-process deployments, replace the in-process lock with acquire_lock/release_lock from the Distributed Locks section above.

Examples

Add caching to a Django/Flask API endpoint: Use cache-aside with setex and a 5-minute TTL on the response. Key on the request parameters.

Rate-limit an API by user: Use fixed-window with pipeline(transaction=True) for low-traffic endpoints; use sliding-window Lua for accurate per-user throttling.

Coordinate a background job across workers: Use acquire_lock with a TTL that exceeds the expected job duration. Always release in a finally block.

Fan-out notifications to multiple subscribers: Use Pub/Sub for fire-and-forget. Switch to Streams if you need guaranteed delivery or replay for late consumers.

Quick Reference

PatternWhen to Use
Cache-asideRead-heavy, tolerate slight staleness
Write-throughStrong consistency required
Distributed lockPrevent concurrent access to a resource
Sliding window rate limitAccurate per-user throttling
Redis StreamsDurable event queue with consumer groups
Pub/SubBroadcast with no delivery guarantees needed
Sorted Set leaderboardRanked scoring, pagination
HyperLogLogApproximate unique count at low memory

Related

  • Skill: postgres-patterns β€” relational data patterns
  • Skill: backend-patterns β€” API and service layer patterns
  • Skill: database-migrations β€” schema versioning
  • Skill: django-patterns β€” Django cache framework integration
  • Agent: database-reviewer β€” full database review workflow

SQL Optimization Patterns

Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.

When to Use This Skill

  • Debugging slow-running queries
  • Designing performant database schemas
  • Optimizing application response times
  • Reducing database load and costs
  • Improving scalability for growing datasets
  • Analyzing EXPLAIN query plans
  • Implementing efficient indexes
  • Resolving N+1 query problems

Core Concepts

1. Query Execution Plans (EXPLAIN)

Understanding EXPLAIN output is fundamental to optimization.

PostgreSQL EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Key Metrics to Watch:

  • Seq Scan: Full table scan (usually slow for large tables)
  • Index Scan: Using index (good)
  • Index Only Scan: Using index without touching table (best)
  • Nested Loop: Join method (okay for small datasets)
  • Hash Join: Join method (good for larger datasets)
  • Merge Join: Join method (good for sorted data)
  • Cost: Estimated query cost (lower is better)
  • Rows: Estimated rows returned
  • Actual Time: Real execution time

2. Index Strategies

Indexes are the most powerful optimization tool.

Index Types:

  • B-Tree: Default, good for equality and range queries
  • Hash: Only for equality (=) comparisons
  • GIN: Full-text search, array queries, JSONB
  • GiST: Geometric data, full-text search
  • BRIN: Block Range INdex for very large tables with correlation
-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

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

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Query Optimization Patterns

Avoid SELECT *:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

Use WHERE Clause Efficiently:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Or store normalized data
SELECT * FROM users WHERE email = 'user@example.com';

Optimize JOINs:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Detailed patterns and worked examples

Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.

Best Practices

  1. Index Selectively: Too many indexes slow down writes
  2. Monitor Query Performance: Use slow query logs
  3. Keep Statistics Updated: Run ANALYZE regularly
  4. Use Appropriate Data Types: Smaller types = better performance
  5. Normalize Thoughtfully: Balance normalization vs performance
  6. Cache Frequently Accessed Data: Use application-level caching
  7. Connection Pooling: Reuse database connections
  8. Regular Maintenance: VACUUM, ANALYZE, rebuild indexes
-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Reclaim space (locks table)

-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Common Pitfalls

  • Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
  • Unused Indexes: Waste space and slow writes
  • Missing Indexes: Slow queries, full table scans
  • Implicit Type Conversion: Prevents index usage
  • OR Conditions: Can't use indexes efficiently
  • LIKE with Leading Wildcard: LIKE '%abc' can't use index
  • Function in WHERE: Prevents index usage unless functional index exists

Monitoring Queries

-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Sponsors

Details

Marketplace
marslanalvi/SkillsStack
Source
plugins/engineering/databases
Category
Engineering
Keywords
engineering, databases

More from marslanalvi/SkillsStack

All 45