According to AlphaMoat, as of 2026-10, Database Operations has 19,851 downloads and 8 stars — ranked by downloads #1,419 among 10,914 AI skills in this ranking, and #97 of 1,069 in Development.
The list defaults to total downloads and can also be sorted by stars; detail-page rankings use total downloads.
| Platform | Downloads | Stars | Comments |
|---|---|---|---|
| ClawHub ↗ | 13.8K | 7 | - |
| SkillHub ↗ | 19.9K | 8 | — |
Platforms count differently and update at different times.
It covers plans, indexes, auditing, partitioning, caches, pools and monitoring. Measure before tuning and use additive migration steps with rollback plans. Production use requires checking the target schema and locking effects; examples do not guarantee zero downtime.
Profile published 2026-10-27
Generated from the skill’s documentation as examples; not results of actual runs.
The counting method changed in 2026-09: skills with the same name are now counted separately by author, and the full catalog is covered. Dashed lines show months before the change; they are not compared with later months, and no growth is computed across the change.
| Month | Downloads | MoM | Stars |
|---|---|---|---|
| 2026-10 | 19.9K | — | 8 |
| 2026-09 | 13.5K | — | 7 |
| 2026-08 | 13.8K | +3.1% | 8 |
| 2026-07 | 13.4K | +4.0% | 8 |
| 2026-06 | 12.9K | +2.8% | 7 |
| 2026-05 | 12.5K | — | 7 |
| 2026-03 | 10.8K | — | 7 |
The author’s original file, shown as plain text without edits or review by AlphaMoat. Links are not clickable; check commands before running them. Fetched 2026-10-23.
--- name: database-operations version: 1.0.0 description: Use when designing database schemas, writing migrations, optimizing SQL queries, fixing N+1 problems, creating indexes, setting up PostgreSQL, configuring EF Core, implementing caching, partitioning tables, or any database performance question. triggers: - database - schema - migration - SQL - query optimization
- index
- PostgreSQL
- Postgres
- N+1
- slow query
- EXPLAIN
- partitioning
- caching
- Redis
- connection pool
- EF Core migration
- database design
role: specialist
scope: implementation
output-format: code
---
# Database Operations
Comprehensive database design, migration, and optimization specialist. Adapted from buildwithclaude by Dave Poon (MIT).
## Role Definition
You are a database optimization expert specializing in PostgreSQL, query performance, schema design, and EF Core migrations. You measure first, optimize second, and always plan rollback procedures.
## Core Principles
1. **Measure first** — always use `EXPLAIN ANALYZE` before optimizing
2. **Index strategically** — based on query patterns, not every column
3. **Denormalize selectively** — only when justified by read patterns
4. **Cache expensive computations** — Redis/materialized views for hot paths
5. **Plan rollback** — every migration has a reverse migration
6. **Zero-downtime migrations** — additive changes first, destructive later
---
## Schema Design Patterns
### User Management
```sql
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending');
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
status user_status DEFAULT 'active',
email_verified BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMPTZ, -- Soft delete
CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT users_names_not_empty CHECK (LENGTH(TRIM(first_name)) > 0 AND LENGTH(TRIM(last_name)) > 0)
);
-- Strategic indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status) WHERE status != 'active';
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;
```
### Audit Trail
```sql
CREATE TYPE audit_operation AS ENUM ('INSERT', 'UPDATE', 'DELETE');
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(255) NOT NULL,
record_id BIGINT NOT NULL,
operation audit_operation NOT NULL,
old_values JSONB,
new_values JSONB,
changed_fields TEXT[],
user_id BIGINT REFERENCES users(id),
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at);
-- Trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, record_id, operation, old_values)
VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values)
VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, record_id, operation, new_values)
VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Apply to any table
CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
```
### Soft Delete Pattern
```sql
-- Query filter view
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
-- Soft delete function
CREATE OR REPLACE FUNCTION soft_delete(p_table TEXT, p_id BIGINT)
RETURNS VOID AS $$
BEGIN
EXECUTE format('UPDATE %I SET deleted_at = CURRENT_TIMESTAMP WHERE id = $1 AND deleted_at IS NULL', p_table)
USING p_id;
END;
$$ LANGUAGE plpgsql;
```
### Full-Text Search
```sql
ALTER TABLE products ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(sku, ''))
) STORED;
CREATE INDEX idx_products_search ON products USING gin(search_vector);
-- Query
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'laptop & gaming');
```
---
## Query Optimization
### Analyze Before Optimizing
```sql
-- Always start here
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;
```
### Indexing Strategy
```sql
-- Single column for exact lookups
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Composite for multi-column queries (order matters!)
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status, created_at);
-- Partial index for filtered queries
CREATE INDEX CONCURRENTLY idx_products_low_stock
ON products(inventory_quantity)
WHERE inventory_tracking = true AND inventory_quantity <= 5;
-- Covering index (includes extra columns to avoid table lookup)
CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders(user_id, status) INCLUDE (total, created_at);
-- GIN index for JSONB
CREATE INDEX CONCURRENTLY idx_products_attrs ON products USING gin(attributes);
-- Expression index
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users(lower(email));
```
### Find Unused Indexes
```sql
SELECT
schemaname, tablename, indexname,
idx_scan as scans,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
```
### Find Missing Indexes (Slow Queries)
```sql
-- Enable pg_stat_statements first
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
WHERE mean_exec_time > 100 -- ms
ORDER BY total_exec_time DESC
LIMIT 20;
```
### N+1 Query Detection
```sql
-- Look for repeated similar queries in pg_stat_statements
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE calls > 100 AND query LIKE '%WHERE%id = $1%'
ORDER BY calls DESC;
```
---
## Migration Patterns
### Safe Column Addition
```sql
-- +migrate Up
-- Always use CONCURRENTLY for indexes in production
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone) WHERE phone IS NOT NULL;
-- +migrate Down
DROP INDEX IF EXISTS idx_users_phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;
```
### Safe Column Rename (Zero-Downtime)
```sql
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
UPDATE users SET display_name = name;
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
-- Step 2: Deploy code that writes to both columns
-- Step 3: Deploy code that reads from new column
-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN name;
```
### Table Partitioning
```sql
-- Create partitioned table
CREATE TABLE orders (
id BIGSERIAL,
user_id BIGINT NOT NULL,
total DECIMAL(10,2),
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Auto-create partitions
CREATE OR REPLACE FUNCTION create_monthly_partition(p_table TEXT, p_date DATE)
RETURNS VOID AS $$
DECLARE
partition_name TEXT := p_table || '_' || to_char(p_date, 'YYYY_MM');
next_date DATE := p_date + INTERVAL '1 month';
BEGIN
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
partition_name, p_table, p_date, next_date
);
END;
$$ LANGUAGE plpgsql;
```
---
## EF Core Migrations (.NET)
### Create and Apply
```bash
# Add migration
dotnet ef migrations add AddPhoneToUsers -p src/Infrastructure -s src/Api
# Apply
dotnet ef database update -p src/Infrastructure -s src/Api
# Generate idempotent SQL script for production
dotnet ef migrations script -p src/Infrastructure -s src/Api -o migration.sql --idempotent
# Rollback
dotnet ef database update PreviousMigrationName -p src/Infrastructure -s src/Api
```
### EF Core Configuration Best Practices
```csharp
// Use AsNoTracking for read queries
var users = await _db.Users
.AsNoTracking()
.Where(u => u.Status == UserStatus.Active)
.Select(u => new UserDto { Id = u.Id, Name = u.Name })
.ToListAsync(ct);
// Avoid N+1 with Include
var orders = await _db.Orders
.Include(o => o.Items)
.ThenInclude(i => i.Product)
.Where(o => o.UserId == userId)
.ToListAsync(ct);
// Better: Projection
var orders = await _db.Orders
.Where(o => o.UserId == userId)
.Select(o => new OrderDto
{
Id = o.Id,
Total = o.Total,
Items = o.Items.Select(i => new OrderItemDto
{
ProductName = i.Product.Name,
Quantity = i.Quantity,
}).ToList(),
})
.ToListAsync(ct);
```
---
## Caching Strategy
### Redis Query Cache
```typescript
import Redis from 'ioredis'
const redis = new Redis(process.env.REDIS_URL)
async function cachedQuery<T>(
key: string,
queryFn: () => Promise<T>,
ttlSeconds: number = 300
): Promise<T> {
const cached = await redis.get(key)
if (cached) return JSON.parse(cached)
const result = await queryFn()
await redis.setex(key, ttlSeconds, JSON.stringify(result))
return result
}
// Usage
const products = await cachedQuery(
`products:category:${categoryId}:page:${page}`,
() => db.product.findMany({ where: { categoryId }, skip, take }),
300 // 5 minutes
)
// Invalidation
async function invalidateProductCache(categoryId: string) {
const keys = await redis.keys(`products:category:${categoryId}:*`)
if (keys.length) await redis.del(...keys)
}
```
### Materialized Views
```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', created_at) as month,
category_id,
COUNT(*) as order_count,
SUM(total) as revenue,
AVG(total) as avg_order_value
FROM orders
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY 1, 2;
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month, category_id);
-- Refresh (can be scheduled via pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```
---
## Connection Pool Configuration
### Node.js (pg)
```typescript
import { Pool } from 'pg'
const pool = new Pool({
max: 20, // Max connections
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 2000, // Fail fast if can't connect in 2s
maxUses: 7500, // Refresh connection after N uses
})
// Monitor pool health
setInterval(() => {
console.log({
total: pool.totalCount,
idle: pool.idleCount,
waiting: pool.waitingCount,
})
}, 60000)
```
---
## Monitoring Queries
### Active Connections
```sql
SELECT count(*), state
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;
```
### Long-Running Queries
```sql
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - query_start) > interval '5 minutes'
AND state = 'active';
```
### Table Sizes
```sql
SELECT
relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
```
### Table Bloat
```sql
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size,
n_dead_tup,
n_live_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup::numeric / n_live_tup, 2)
ELSE 0
END as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC;
```
---
## Anti-Patterns
1. ❌ `SELECT *` — always specify needed columns
2. ❌ Missing indexes on foreign keys — always index FK columns
3. ❌ `LIKE '%search%'` — use full-text search or trigram indexes instead
4. ❌ Large `IN` clauses — use `ANY(ARRAY[...])` or join a values list
5. ❌ No `LIMIT` on unbounded queries — always paginate
6. ❌ Creating indexes without `CONCURRENTLY` in production
7. ❌ Running migrations without testing rollback
8. ❌ Ignoring `EXPLAIN ANALYZE` output — always verify execution plans
9. ❌ Storing money as `FLOAT` — use `DECIMAL(10,2)` or integer cents
10. ❌ Missing `NOT NULL` constraints — be explicit about nullability
【全栈能力】编程大神.Skill:面向复杂真实项目的高阶全栈编程助手。不止生成代码,而是从一句目标接手陌生代码库,拆解需求、补齐实现、追到根因、完成重构、联调前后端,并把结果推进到能运行、能验证、能交付。覆盖 Web、后端、脚本、iOS、安卓、鸿蒙 ArkUI、微信小程序及其他技术栈,支持中英文工程、疑难 Bug、跨模块任务和长任务续做;用构建、日志、真实入口、设备或接口证据说明结果。仅问概念时直接回答,要求落地时持续推进到验收或明确阻塞。
编程专家 Pro:融合编程专家 2.0.3 工程专题与编程大神的鸿蒙 ArkTS/ArkUI、微信小程序和小游戏开发资料。用于项目实现、根因调试、审查、重构、测试、架构性能分析及多端开发验证。支持自然语言和 @专题标识,按任务读取包内知识;只问概念时直接回答。
编程专家.Skill P8级编程助手,25年实战经验技能Skill,全栈:Java/Php网站/软件项目、API设计、Bug诊断、代码生成、代码审查、重构、测试用例、性能基准、技术选型、文档生成、任务拆解、Spec驱动、Karpathy规范、CMS二次开发、前端设计、MySQL/Mariadb、项目知识图谱,以及工程纪律层(根因调试硬闭环、阶段导航、事故复盘/SLO、威胁建模/供应链安全、生产就绪/渐进式发布、AI编码治理、面向Agent代码可读性、LLM应用安全)。支持 @标识 显式调用跳过路由。
编程专家.Skill P8级编程助手,25年实战经验技能Skill,全栈:Java/Php网站/软件项目、API设计、Bug诊断、代码生成、代码审查、重构、测试用例、性能基准、技术选型、文档生成、任务拆解、Spec驱动、Karpathy规范、CMS二次开发、前端设计、MySQL/Mariadb、项目知识图谱,以及工程纪律层(根因调试硬闭环、阶段导航、事故复盘/SLO、威胁建模/供应链安全、生产就绪/渐进式发布、AI编码治理、面向Agent代码可读性、LLM应用安全)。支持 @标识 显式调用跳过路由。
工程专题、鸿蒙与微信小程序开发的集成派生版
编程专家 Pro:融合编程专家 2.0.3 工程专题与编程大神的鸿蒙 ArkTS/ArkUI、微信小程序和小游戏开发资料。用于项目实现、根因调试、审查、重构、测试、架构性能分析及多端开发验证。支持自然语言和 @专题标识,按任务读取包内知识;只问概念时直接回答。
适用场景:全栈编程助手。接到需求先读现有代码和约束,再动手:Java/PHP 开发、接口设计、缺陷定位、代码评审、重构、测试用例、性能排查、技术选型与文档产出,也做 CMS 二次开发、前端页面和 MySQL 调优。每次改动先给方案和影响范围,改完必须跑验证,交付附交接说明。适合要把代码真正跑通、而不是只要片段的场景。写完代码常要配套的造价、法律、工程文档,站内已有现成技能,用「查找技能」按需求找即可。
A skill for working with GitHub through the gh CLI: pull request checks, workflow runs, and API queries.
Security audits of code and architecture from an application security engineer's view.
Next.js Expert provides TypeScript implementation guidance for App Router applications.
Builds shippable interfaces with shadcn/ui and Tailwind.
Data as of 2026-10. Downloads and stars are aggregated monthly from public skill registries (ClawHub, SkillHub). See data methodology.