
engineering-databases
SQL, Postgres, MySQL, Redis, ORMs, schema design and migrations
- version
- 1.0.0
- GitHub stars
- 5
Install
/plugin install engineering-databases@skills-stackRun it inside Claude Code after adding the marketplace with /plugin marketplace add marslanalvi/SkillsStack.
Contents
Discovered from sourceSkills 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.
| File | Description | When to Read |
|---|---|---|
database-selection.md | PostgreSQL vs Neon vs Turso vs SQLite | Choosing database |
orm-selection.md | Drizzle vs Prisma vs Kysely | Choosing ORM |
schema-design.md | Normalization, PKs, relationships | Designing schema |
indexing.md | Index types, composite indexes | Performance tuning |
optimization.md | N+1, EXPLAIN ANALYZE | Query optimization |
migrations.md | Safe migrations, serverless DBs | Schema 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
- Every change is a migration β never alter production databases manually
- Migrations are forward-only in production β rollbacks use new forward migrations
- Schema and data migrations are separate β never mix DDL and DML in one migration
- Test migrations against production-sized data β a migration that works on 100 rows may lock on 10M
- 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-Pattern | Why It Fails | Better Approach |
|---|---|---|
| Manual SQL in production | No audit trail, unrepeatable | Always use migration files |
| Editing deployed migrations | Causes drift between environments | Create new migration instead |
| NOT NULL without default | Locks table, rewrites all rows | Add nullable, backfill, then add constraint |
| Inline index on large table | Blocks writes during build | CREATE INDEX CONCURRENTLY |
| Schema + data in one migration | Hard to rollback, long transactions | Separate migrations |
| Dropping column before removing code | Application errors on missing column | Remove 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)inON DUPLICATE KEY UPDATE;VALUES(col)is deprecated there. - MariaDB documents
VALUES(col)as the supported way to reference inserted values inON DUPLICATE KEY UPDATE; use it for cross-engine compatibility. SKIP LOCKEDis 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 Case | Prefer | Avoid |
|---|---|---|
| Surrogate primary keys | BIGINT UNSIGNED AUTO_INCREMENT | INT for tables that can grow beyond 2B rows |
| UUID lookup keys | BINARY(16) with conversion helpers | VARCHAR(36) primary keys on hot tables |
| Money and exact quantities | DECIMAL(p, s) | FLOAT or DOUBLE |
| User-facing text | utf8mb4 tables and indexes | MySQL utf8 / utf8mb3 defaults |
| Application timestamps | DATETIME with UTC managed by the app | Assuming DATETIME stores time zone metadata |
| Soft deletes | deleted_at DATETIME NULL plus scoped indexes | Filtering soft-deleted rows without an index |
| Extensible status values | lookup table or constrained VARCHAR | ENUM 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:
| Field | Risk Signal |
|---|---|
type | ALL on a large table |
key | NULL when a selective predicate exists |
rows | Very high row estimate for an interactive path |
Extra | Using 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\Gsoon 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 PRIVILEGESor*.*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-Pattern | Risk | Better Pattern |
|---|---|---|
SELECT * in hot paths | Over-fetching and brittle clients | Select explicit columns |
Deep OFFSET pagination | Linear scans and slow pages | Keyset pagination |
| No index on foreign-key joins | Slow joins and lock-heavy deletes | Index FK columns intentionally |
| Long transactions | Lock waits and large undo history | Commit small units of work |
Direct DML against mysql.user | Grant-table corruption risk | Use CREATE USER, ALTER USER, DROP USER |
| Application user with admin grants | High blast radius | Least-privilege runtime user |
Pool recycle above wait_timeout | Stale pooled connections | Recycle below timeout and pre-ping |
| Replica reads after writes | Stale user-facing state | Pin read-after-write flows to primary |
Output Expectations
When this skill is used for review, return:
- Engine/version assumptions.
- Highest-risk correctness, lock, security, and migration issues.
- Exact SQL or code changes for the safe path.
- Validation plan:
EXPLAIN, migration dry run, lock/deadlock check, and rollback criteria. - 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 Pattern | Index Type | Example |
|---|---|---|
WHERE col = value | B-tree (default) | CREATE INDEX idx ON t (col) |
WHERE col > value | B-tree | CREATE INDEX idx ON t (col) |
WHERE a = x AND b > y | Composite | CREATE INDEX idx ON t (a, b) |
WHERE jsonb @> '{}' | GIN | CREATE INDEX idx ON t USING gin (col) |
WHERE tsv @@ query | GIN | CREATE INDEX idx ON t USING gin (col) |
| Time-series ranges | BRIN | CREATE INDEX idx ON t USING brin (col) |
Data Type Quick Reference
| Use Case | Correct Type | Avoid |
|---|---|---|
| IDs | bigint | int, random UUID |
| Strings | text | varchar(255) |
| Timestamps | timestamptz | timestamp |
| Money | numeric(10,2) | float |
| Flags | boolean | varchar, 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; useUUIDonly 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
NUMERICfor 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;
CLUSTERis 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;UUIDfor distributed or opaque IDs, generated withuuidv7()(PG18+) orgen_random_uuid(). - Numbers:
BIGINTunless storage is critical;DOUBLE PRECISIONoverREAL;NUMERIC(p,s)for money and exact decimals. - Strings:
TEXT, withCHECK (LENGTH(col) <= n)when a limit is needed;BYTEAfor binary. Case-insensitive lookups: expression index onLOWER(col), orCITEXTwhen a constraint must be case-insensitive. - Time:
TIMESTAMPTZ,DATE,INTERVAL.now()is transaction start;clock_timestamp()is wall clock. - Booleans:
BOOLEAN NOT NULLunless tri-state is required. - Enums:
CREATE TYPE ... AS ENUMonly for small, stable sets; evolving business values getTEXT+CHECKor 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
| Avoid | Use instead |
|---|---|
timestamp (without time zone) | timestamptz |
char(n), varchar(n) | text (+ CHECK on length if needed) |
money | numeric |
timetz | timestamptz |
timestamptz(0) or any precision | timestamptz |
serial | generated 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. UseDEFERRABLE INITIALLY DEFERREDfor circular dependencies checked at commit. - UNIQUE: creates a B-tree index; allows multiple NULLs unless
NULLS NOT DISTINCT(PG15+). PreferNULLS NOT DISTINCTunless 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
CHECKconstraints; 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_opsand 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 --versionNotable API differences across versions:
relationJoinscan load relations via JOIN rather than separate queries, but may cause row explosion on large 1:N relations or deepincludeβ benchmark both approachesomitfield modifier andprisma.$extendsClient Extensions API were added- Newer installs: the package may be named
prismainstead of@prisma/client;PrismaClientmay require a driver adapter (e.g.@prisma/adapter-pg);datasource.urlmay live inprisma.config.tsinstead ofschema.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
| Strategy | Use When | Avoid When |
|---|---|---|
@default(cuid()) | Default choice β URL-safe, sortable, no collisions | Sequential IDs needed for external systems |
@default(uuid()) | Interoperability with non-Prisma systems required | High-write tables (random UUIDs fragment B-tree indexes) |
@default(autoincrement()) | Internal join tables, audit logs | Public-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
@@indexon every foreign key and column used inWHEREorORDER BY. - Declare
deletedAt DateTime?upfront when soft delete is a foreseeable requirement β adding it later requires a migration on a live table. updatedAt @updatedAtis set automatically by Prisma onupdateandupsertonly (see Anti-Patterns for bulk update trap).
include vs select
include | select | |
|---|---|---|
| Returns | All scalar fields + specified relations | Only specified fields |
| Use when | You need most fields plus a relation | Hot paths, large tables, avoiding over-fetch |
| Performance | May over-fetch on wide tables | Minimal payload, faster on large datasets |
| Prisma 5 note | Uses 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
| Situation | Use |
|---|---|
| Independent operations, no inter-dependency | Array form |
| Later step depends on earlier result | Interactive form |
| External calls (email, HTTP) involved | Outside 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
| Rule | Reason |
|---|---|
migrate deploy in CI/CD, migrate dev only locally | migrate dev can reset the DB on drift |
| Map entities to response DTOs | Prevents leaking internal fields |
Catch PrismaClientKnownRequestError at service boundary | Translate to domain errors |
Prefer *OrThrow methods over manual null checks | Throws P2025 automatically; use findFirstOrThrow when filtering non-unique fields |
connection_limit=1 + external pooler in serverless | Prevents connection exhaustion |
Always provide where on deleteMany | Prevents accidental table wipe |
Set updatedAt: new Date() manually in updateMany | @updatedAt skips bulk writes |
Related Skills
nestjs-patternsβ NestJS service layer that integrates Prismapostgres-patternsβ PostgreSQL-level indexing and connection tuningdatabase-migrationsβ multi-step migration planning for productionbackend-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 Case | Structure | Example Key |
|---|---|---|
| Simple cache | String | product:123 |
| User session | Hash | session:abc |
| Leaderboard | Sorted Set | scores:weekly |
| Unique visitors | Set | visitors:2024-01-01 |
| Activity feed | List | feed:user:456 |
| Event stream | Stream | events:orders |
| Counters / rate limits | String (INCR) | ratelimit:user:123 |
| Bloom filter / HLL | HyperLogLog | hll: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-pylibrary 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 Type | Suggested TTL |
|---|---|
| User session | 24h (86400) |
| API response cache | 5β15 min |
| Rate limit window | Match window size |
| Short-lived tokens | 5β10 min |
| Leaderboard | 1hβ24h |
| Static/reference data | 1hβ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
| Policy | Behavior | Best For |
|---|---|---|
noeviction | Error on write when full | Queues / critical data |
allkeys-lru | Evict least recently used | General cache |
volatile-lru | LRU only among keys with TTL | Mixed data store |
allkeys-lfu | Evict least frequently used | Skewed access patterns |
volatile-ttl | Evict soonest-to-expire | Prioritize long-lived data |
Set via redis.conf: maxmemory-policy allkeys-lru
Anti-Patterns
| Anti-Pattern | Problem | Fix |
|---|---|---|
| Keys with no TTL | Memory grows unbounded | Always set TTL |
KEYS * in production | Blocks the server (O(N)) | Use SCAN cursor |
| Storing large blobs (>100KB) | Slow serialization, memory pressure | Store reference + fetch from object store |
| Single Redis for everything | No isolation between cache & queue | Use separate DBs or instances |
| Ignoring connection pool limits | Connection exhaustion under load | Size pool to workload |
| Not handling cache miss stampede | Thundering herd on cold start | Use locks or probabilistic early expiry |
FLUSHALL without thought | Wipes entire instance | Scope 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_lockfrom 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
| Pattern | When to Use |
|---|---|
| Cache-aside | Read-heavy, tolerate slight staleness |
| Write-through | Strong consistency required |
| Distributed lock | Prevent concurrent access to a resource |
| Sliding window rate limit | Accurate per-user throttling |
| Redis Streams | Durable event queue with consumer groups |
| Pub/Sub | Broadcast with no delivery guarantees needed |
| Sorted Set leaderboard | Ranked scoring, pagination |
| HyperLogLog | Approximate 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
- Index Selectively: Too many indexes slow down writes
- Monitor Query Performance: Use slow query logs
- Keep Statistics Updated: Run ANALYZE regularly
- Use Appropriate Data Types: Smaller types = better performance
- Normalize Thoughtfully: Balance normalization vs performance
- Cache Frequently Accessed Data: Use application-level caching
- Connection Pooling: Reuse database connections
- 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- engineering-debuggingv1.0.0
- engineering-dev-workflowv1.0.0
- engineering-frontendv1.0.0
- engineering-game-developmentv1.0.0
- engineering-git-githubv1.0.0
- engineering-javascript-typescriptv1.0.0
- engineering-jvmv1.0.0
- engineering-mobilev1.0.0
- engineering-other-languagesv1.0.0
- engineering-pythonv1.0.0
- engineering-systems-languagesv1.0.0
- engineering-testingv1.0.0
- marketing-content-socialv1.0.0
- marketing-growth-adsv1.0.0
- marketing-seov1.0.0
- productivity-automation-personalv1.0.0
- productivity-documents-officev1.0.0
- productivity-thinking-planningv1.0.0
- productivity-writing-communicationv1.0.0
- security-appsec-reviewv1.0.0
- security-blockchain-web3v1.0.0
- security-compliance-privacyv1.0.0
- security-fuzzingv1.0.0
- security-offensive-securityv1.0.0