AlphaMoatによると、2026-10時点でDatabase Operationsのダウンロード数は19,851、スター数は8で、累計ダウンロード数で今回のランキング対象AIスキル10,914件中1,419位、開発では1,069件中97位です。
一覧の初期表示は累計ダウンロード数順で、スター数順にも並べ替えできます。詳細ページの順位は累計ダウンロード数で計算しています。
| プラットフォーム | ダウンロード数 | スター数 | コメント |
|---|---|---|---|
| ClawHub ↗ | 1.4万 | 7 | - |
| SkillHub ↗ | 2万 | 8 | — |
プラットフォームごとに集計方法と集計時点が異なります。
実行計画、索引、監査、分割、キャッシュ、接続プール、監視を扱います。測定後に調整し、追加から始める移行と復旧案を用意します。本番では対象構造とロックを確認し、無停止は保証しません。
資料公開日 2026-10-27
スキルのドキュメントをもとに生成した例であり、実際の実行結果ではありません。
2026-09 から集計方法を変更し、同名スキルを作者ごとに分けて集計し、全スキルを対象にしました。破線は変更前の月で、変更後のデータとは直接比較せず、変更をまたぐ増減も計算していません。
| 月 | ダウンロード数 | 前月比 | スター数 |
|---|---|---|---|
| 2026-10 | 2万 | — | 8 |
| 2026-09 | 1.4万 | — | 7 |
| 2026-08 | 1.4万 | +3.1% | 8 |
| 2026-07 | 1.3万 | +4.0% | 8 |
| 2026-06 | 1.3万 | +2.8% | 7 |
| 2026-05 | 1.3万 | — | 7 |
| 2026-03 | 1.1万 | — | 7 |
作者が公開した元のファイルをテキストのまま表示しています。AlphaMoat による編集や審査はしていません。リンクはクリックできません。コマンドは内容を確認してから実行してください。取得日 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 调优。每次改动先给方案和影响范围,改完必须跑验证,交付附交接说明。适合要把代码真正跑通、而不是只要片段的场景。写完代码常要配套的造价、法律、工程文档,站内已有现成技能,用「查找技能」按需求找即可。
gh コマンドラインで GitHub の PR チェック、ワークフロー実行、API 照会を扱うスキルです。
データ月:2026-10 · ダウンロード数とスター数は公開スキルレジストリ(ClawHub、SkillHub)から月単位で集計しています。詳しくはデータの方法論をご覧ください。