jgarrison929

Database Operations

開發程式設計v1.0.0
作者:jgarrison929更新於 2026-05-11database-operations@clawhub_jgarrison929/database-operations
後端開發指令碼工具#Database#PostgreSQL#SQL Query

據 AlphaMoat 統計,截至 2026-10,Database Operations 累計下載 19,851 次、獲 8 個星標,按累計下載量在本次參與排名的 10,914 個 AI 技能中排名第 1,419 位,在「開發程式設計」分類(1,069 個)中排名第 97 位。

列表預設按累計下載量排序,也可按星標排序;詳情頁排名按累計下載量計算。

累計下載
2萬
星標
8
全站下載量排名
#1,419
全站前 14% · 「開發程式設計」前 10%
平台累計下載星標評論
ClawHub ↗1.4萬7-
SkillHub ↗2萬8—

各平台的計數口徑不同,統計時間不一致。

這個技能是做什麼的

涵蓋執行計劃、索引、審計、分割槽、快取、連線池和監控。先測量再最佳化,遷移先加後刪並準備回滾;生產使用需驗證目標模式和鎖影響,示例不保證零停機。

資料釋出於 2026-10-27

可以用來做什麼

  • 準備資料庫效能與遷移方案

什麼時候用

  • 需要分析慢查詢或規劃模式變更時

示例提示詞

以下是根據技能文件生成的示例,不代表實際執行結果。

先分析執行計劃,再提出索引與遷移方案,註明鎖影響和回滾步驟。

月度資料趨勢

累計下載2萬
1萬1.5萬2.1萬2026-03 · 累計下載 1.1萬2026-05 · 累計下載 1.3萬2026-06 · 累計下載 1.3萬2026-07 · 累計下載 1.3萬2026-08 · 累計下載 1.4萬2026-09 · 累計下載 1.4萬2026-10 · 累計下載 2萬26-0326-0926-10
星標8
7882026-03 · 星標 72026-05 · 星標 72026-06 · 星標 72026-07 · 星標 82026-08 · 星標 82026-09 · 星標 72026-10 · 星標 826-0326-0926-10

2026-09 起統計方法調整:同名技能按作者分別統計,並覆蓋全部技能。圖中虛線為調整前的月份,與之後的資料不直接比較,也不計算跨越調整的增長。

月份累計下載環比星標
2026-102萬—8
2026-091.4萬—7
2026-081.4萬+3.1%8
2026-071.3萬+4.0%8
2026-061.3萬+2.8%7
2026-051.3萬—7
2026-031.1萬—7

SKILL.md 原文

以下是作者釋出的原始檔案,按純文字顯示,本站未做修改或稽核;其中的連結不可點選,命令請核實後再執行。抓取於 2026-10-23。

13 KB · ClawHub v1.0.0
---
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
展開全部(共 497 行)
  - 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開發程式設計

【全棧能力】程式設計大神.Skill:面向複雜真實專案的高階全棧程式設計助手。不止生成程式碼,而是從一句目標接手陌生程式碼庫,拆解需求、補齊實現、追到根因、完成重構、聯調前後端,並把結果推進到能執行、能驗證、能交付。覆蓋 Web、後端、指令碼、iOS、安卓、鴻蒙 ArkUI、微信小程式及其他技術棧,支援中英文工程、疑難 Bug、跨模組任務和長任務續做;用構建、日誌、真實入口、裝置或介面證據說明結果。僅問概念時直接回答,要求落地時持續推進到驗收或明確阻塞。

↓ 1125.9萬★ 82u_42bbb65f
程式設計專家Pro.Skill開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 626.9萬★ 32user_47d655f0
程式設計專家.Skill開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 600.7萬★ 378user_741dc82b
dev-expert開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 528.1萬★ 32user_814dbe54
dev-expert-pro開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 104.7萬★ -user_814dbe54
程式設計專家 Pro開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 98.2萬★ 1user_28275a78
程式設計專家.Skill開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 70.1萬★ 6user_613b33ea
Github開發程式設計

用 gh 命令列工具檢視 GitHub 上的 PR 檢查、工作流執行與 API 查詢的技能。

↓ 48萬★ 714Peter Steinberger

jgarrison929 的其他技能

資料月份:2026-10 · 下載/星標數聚合自公開技能註冊庫(ClawHub、SkillHub),按月更新。口徑說明見資料方法。