database-operations — Skillopedia

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 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/material…

),\n CONSTRAINT users_names_not_empty CHECK (LENGTH(TRIM(first_name)) > 0 AND LENGTH(TRIM(last_name)) > 0)\n);\n\n-- Strategic indexes\nCREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_users_status ON users(status) WHERE status != 'active';\nCREATE INDEX idx_users_created_at ON users(created_at);\nCREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;\n```\n\n### Audit Trail\n\n```sql\nCREATE TYPE audit_operation AS ENUM ('INSERT', 'UPDATE', 'DELETE');\n\nCREATE TABLE audit_log (\n id BIGSERIAL PRIMARY KEY,\n table_name VARCHAR(255) NOT NULL,\n record_id BIGINT NOT NULL,\n operation audit_operation NOT NULL,\n old_values JSONB,\n new_values JSONB,\n changed_fields TEXT[],\n user_id BIGINT REFERENCES users(id),\n created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);\nCREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at);\n\n-- Trigger function\nCREATE OR REPLACE FUNCTION audit_trigger_function()\nRETURNS TRIGGER AS $\nBEGIN\n IF TG_OP = 'DELETE' THEN\n INSERT INTO audit_log (table_name, record_id, operation, old_values)\n VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));\n RETURN OLD;\n ELSIF TG_OP = 'UPDATE' THEN\n INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values)\n VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));\n RETURN NEW;\n ELSIF TG_OP = 'INSERT' THEN\n INSERT INTO audit_log (table_name, record_id, operation, new_values)\n VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));\n RETURN NEW;\n END IF;\nEND;\n$ LANGUAGE plpgsql;\n\n-- Apply to any table\nCREATE TRIGGER audit_users\nAFTER INSERT OR UPDATE OR DELETE ON users\nFOR EACH ROW EXECUTE FUNCTION audit_trigger_function();\n```\n\n### Soft Delete Pattern\n\n```sql\n-- Query filter view\nCREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;\n\n-- Soft delete function\nCREATE OR REPLACE FUNCTION soft_delete(p_table TEXT, p_id BIGINT)\nRETURNS VOID AS $\nBEGIN\n EXECUTE format('UPDATE %I SET deleted_at = CURRENT_TIMESTAMP WHERE id = $1 AND deleted_at IS NULL', p_table)\n USING p_id;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n### Full-Text Search\n\n```sql\nALTER TABLE products ADD COLUMN search_vector tsvector\n GENERATED ALWAYS AS (\n to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(sku, ''))\n ) STORED;\n\nCREATE INDEX idx_products_search ON products USING gin(search_vector);\n\n-- Query\nSELECT * FROM products\nWHERE search_vector @@ to_tsquery('english', 'laptop & gaming');\n```\n\n---\n\n## Query Optimization\n\n### Analyze Before Optimizing\n\n```sql\n-- Always start here\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT u.id, u.name, COUNT(o.id) as order_count\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id\nWHERE u.created_at > '2024-01-01'\nGROUP BY u.id, u.name\nORDER BY order_count DESC;\n```\n\n### Indexing Strategy\n\n```sql\n-- Single column for exact lookups\nCREATE INDEX CONCURRENTLY idx_users_email ON users(email);\n\n-- Composite for multi-column queries (order matters!)\nCREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status, created_at);\n\n-- Partial index for filtered queries\nCREATE INDEX CONCURRENTLY idx_products_low_stock\nON products(inventory_quantity)\nWHERE inventory_tracking = true AND inventory_quantity \u003c= 5;\n\n-- Covering index (includes extra columns to avoid table lookup)\nCREATE INDEX CONCURRENTLY idx_orders_covering\nON orders(user_id, status) INCLUDE (total, created_at);\n\n-- GIN index for JSONB\nCREATE INDEX CONCURRENTLY idx_products_attrs ON products USING gin(attributes);\n\n-- Expression index\nCREATE INDEX CONCURRENTLY idx_users_email_lower ON users(lower(email));\n```\n\n### Find Unused Indexes\n\n```sql\nSELECT\n schemaname, tablename, indexname,\n idx_scan as scans,\n pg_size_pretty(pg_relation_size(indexrelid)) as size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\nORDER BY pg_relation_size(indexrelid) DESC;\n```\n\n### Find Missing Indexes (Slow Queries)\n\n```sql\n-- Enable pg_stat_statements first\nSELECT query, calls, total_exec_time, mean_exec_time, rows\nFROM pg_stat_statements\nWHERE mean_exec_time > 100 -- ms\nORDER BY total_exec_time DESC\nLIMIT 20;\n```\n\n### N+1 Query Detection\n\n```sql\n-- Look for repeated similar queries in pg_stat_statements\nSELECT query, calls, mean_exec_time\nFROM pg_stat_statements\nWHERE calls > 100 AND query LIKE '%WHERE%id = $1%'\nORDER BY calls DESC;\n```\n\n---\n\n## Migration Patterns\n\n### Safe Column Addition\n\n```sql\n-- +migrate Up\n-- Always use CONCURRENTLY for indexes in production\nALTER TABLE users ADD COLUMN phone VARCHAR(20);\nCREATE INDEX CONCURRENTLY idx_users_phone ON users(phone) WHERE phone IS NOT NULL;\n\n-- +migrate Down\nDROP INDEX IF EXISTS idx_users_phone;\nALTER TABLE users DROP COLUMN IF EXISTS phone;\n```\n\n### Safe Column Rename (Zero-Downtime)\n\n```sql\n-- Step 1: Add new column\nALTER TABLE users ADD COLUMN display_name VARCHAR(100);\nUPDATE users SET display_name = name;\nALTER TABLE users ALTER COLUMN display_name SET NOT NULL;\n\n-- Step 2: Deploy code that writes to both columns\n-- Step 3: Deploy code that reads from new column\n-- Step 4: Drop old column\nALTER TABLE users DROP COLUMN name;\n```\n\n### Table Partitioning\n\n```sql\n-- Create partitioned table\nCREATE TABLE orders (\n id BIGSERIAL,\n user_id BIGINT NOT NULL,\n total DECIMAL(10,2),\n created_at TIMESTAMPTZ NOT NULL,\n PRIMARY KEY (id, created_at)\n) PARTITION BY RANGE (created_at);\n\n-- Monthly partitions\nCREATE TABLE orders_2024_01 PARTITION OF orders\n FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');\nCREATE TABLE orders_2024_02 PARTITION OF orders\n FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');\n\n-- Auto-create partitions\nCREATE OR REPLACE FUNCTION create_monthly_partition(p_table TEXT, p_date DATE)\nRETURNS VOID AS $\nDECLARE\n partition_name TEXT := p_table || '_' || to_char(p_date, 'YYYY_MM');\n next_date DATE := p_date + INTERVAL '1 month';\nBEGIN\n EXECUTE format(\n 'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',\n partition_name, p_table, p_date, next_date\n );\nEND;\n$ LANGUAGE plpgsql;\n```\n\n---\n\n## EF Core Migrations (.NET)\n\n### Create and Apply\n\n```bash\n# Add migration\ndotnet ef migrations add AddPhoneToUsers -p src/Infrastructure -s src/Api\n\n# Apply\ndotnet ef database update -p src/Infrastructure -s src/Api\n\n# Generate idempotent SQL script for production\ndotnet ef migrations script -p src/Infrastructure -s src/Api -o migration.sql --idempotent\n\n# Rollback\ndotnet ef database update PreviousMigrationName -p src/Infrastructure -s src/Api\n```\n\n### EF Core Configuration Best Practices\n\n```csharp\n// Use AsNoTracking for read queries\nvar users = await _db.Users\n .AsNoTracking()\n .Where(u => u.Status == UserStatus.Active)\n .Select(u => new UserDto { Id = u.Id, Name = u.Name })\n .ToListAsync(ct);\n\n// Avoid N+1 with Include\nvar orders = await _db.Orders\n .Include(o => o.Items)\n .ThenInclude(i => i.Product)\n .Where(o => o.UserId == userId)\n .ToListAsync(ct);\n\n// Better: Projection\nvar orders = await _db.Orders\n .Where(o => o.UserId == userId)\n .Select(o => new OrderDto\n {\n Id = o.Id,\n Total = o.Total,\n Items = o.Items.Select(i => new OrderItemDto\n {\n ProductName = i.Product.Name,\n Quantity = i.Quantity,\n }).ToList(),\n })\n .ToListAsync(ct);\n```\n\n---\n\n## Caching Strategy\n\n### Redis Query Cache\n\n```typescript\nimport Redis from 'ioredis'\n\nconst redis = new Redis(process.env.REDIS_URL)\n\nasync function cachedQuery\u003cT>(\n key: string,\n queryFn: () => Promise\u003cT>,\n ttlSeconds: number = 300\n): Promise\u003cT> {\n const cached = await redis.get(key)\n if (cached) return JSON.parse(cached)\n\n const result = await queryFn()\n await redis.setex(key, ttlSeconds, JSON.stringify(result))\n return result\n}\n\n// Usage\nconst products = await cachedQuery(\n `products:category:${categoryId}:page:${page}`,\n () => db.product.findMany({ where: { categoryId }, skip, take }),\n 300 // 5 minutes\n)\n\n// Invalidation\nasync function invalidateProductCache(categoryId: string) {\n const keys = await redis.keys(`products:category:${categoryId}:*`)\n if (keys.length) await redis.del(...keys)\n}\n```\n\n### Materialized Views\n\n```sql\nCREATE MATERIALIZED VIEW monthly_sales AS\nSELECT\n DATE_TRUNC('month', created_at) as month,\n category_id,\n COUNT(*) as order_count,\n SUM(total) as revenue,\n AVG(total) as avg_order_value\nFROM orders\nWHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)\nGROUP BY 1, 2;\n\nCREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month, category_id);\n\n-- Refresh (can be scheduled via pg_cron)\nREFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;\n```\n\n---\n\n## Connection Pool Configuration\n\n### Node.js (pg)\n\n```typescript\nimport { Pool } from 'pg'\n\nconst pool = new Pool({\n max: 20, // Max connections\n idleTimeoutMillis: 30000, // Close idle connections after 30s\n connectionTimeoutMillis: 2000, // Fail fast if can't connect in 2s\n maxUses: 7500, // Refresh connection after N uses\n})\n\n// Monitor pool health\nsetInterval(() => {\n console.log({\n total: pool.totalCount,\n idle: pool.idleCount,\n waiting: pool.waitingCount,\n })\n}, 60000)\n```\n\n---\n\n## Monitoring Queries\n\n### Active Connections\n\n```sql\nSELECT count(*), state\nFROM pg_stat_activity\nWHERE datname = current_database()\nGROUP BY state;\n```\n\n### Long-Running Queries\n\n```sql\nSELECT pid, now() - query_start AS duration, query, state\nFROM pg_stat_activity\nWHERE (now() - query_start) > interval '5 minutes'\nAND state = 'active';\n```\n\n### Table Sizes\n\n```sql\nSELECT\n relname AS table,\n pg_size_pretty(pg_total_relation_size(relid)) AS total_size,\n pg_size_pretty(pg_relation_size(relid)) AS data_size,\n pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size\nFROM pg_catalog.pg_statio_user_tables\nORDER BY pg_total_relation_size(relid) DESC\nLIMIT 20;\n```\n\n### Table Bloat\n\n```sql\nSELECT\n tablename,\n pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size,\n n_dead_tup,\n n_live_tup,\n CASE WHEN n_live_tup > 0\n THEN round(n_dead_tup::numeric / n_live_tup, 2)\n ELSE 0\n END as dead_ratio\nFROM pg_stat_user_tables\nWHERE n_dead_tup > 1000\nORDER BY dead_ratio DESC;\n```\n\n---\n\n## Anti-Patterns\n\n1. ❌ `SELECT *` — always specify needed columns\n2. ❌ Missing indexes on foreign keys — always index FK columns\n3. ❌ `LIKE '%search%'` — use full-text search or trigram indexes instead\n4. ❌ Large `IN` clauses — use `ANY(ARRAY[...])` or join a values list\n5. ❌ No `LIMIT` on unbounded queries — always paginate\n6. ❌ Creating indexes without `CONCURRENTLY` in production\n7. ❌ Running migrations without testing rollback\n8. ❌ Ignoring `EXPLAIN ANALYZE` output — always verify execution plans\n9. ❌ Storing money as `FLOAT` — use `DECIMAL(10,2)` or integer cents\n10. ❌ Missing `NOT NULL` constraints — be explicit about nullability\n---","attachment_filenames":["_meta.json",".clawhub/origin.json"],"attachments":[{"filename":"_meta.json","content":"{\n \"ownerId\": \"kn783g0k8dgj2wyz27c0e9he9180ce6k\",\n \"slug\": \"database-operations\",\n \"version\": \"1.0.0\",\n \"publishedAt\": 1770008191804\n}","content_type":"application/json; charset=utf-8","language":"json","size":138,"content_sha256":"edd4fefd765a3a824916336363639844f0a86bac2b039a6e570f8c52c8947531"},{"filename":".clawhub/origin.json","content":"{\n \"version\": 1,\n \"registry\": \"https://clawhub.ai\",\n \"slug\": \"database-operations\",\n \"installedVersion\": \"1.0.0\",\n \"installedAt\": 1770438892525\n}\n","content_type":"application/json; charset=utf-8","language":"json","size":151,"content_sha256":"99aa377fabc7bb8689293d3b4dfdd451031f146d964a95b773c60aef997a013b"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Database Operations","type":"text"}]},{"type":"paragraph","content":[{"text":"Comprehensive database design, migration, and optimization specialist. Adapted from buildwithclaude by Dave Poon (MIT).","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Role Definition","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Principles","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Measure first","type":"text","marks":[{"type":"strong"}]},{"text":" — always use ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" before optimizing","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index strategically","type":"text","marks":[{"type":"strong"}]},{"text":" — based on query patterns, not every column","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Denormalize selectively","type":"text","marks":[{"type":"strong"}]},{"text":" — only when justified by read patterns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Cache expensive computations","type":"text","marks":[{"type":"strong"}]},{"text":" — Redis/materialized views for hot paths","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Plan rollback","type":"text","marks":[{"type":"strong"}]},{"text":" — every migration has a reverse migration","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Zero-downtime migrations","type":"text","marks":[{"type":"strong"}]},{"text":" — additive changes first, destructive later","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Schema Design Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"User Management","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending');\n\nCREATE TABLE users (\n id BIGSERIAL PRIMARY KEY,\n email VARCHAR(255) UNIQUE NOT NULL,\n username VARCHAR(50) UNIQUE NOT NULL,\n password_hash VARCHAR(255) NOT NULL,\n first_name VARCHAR(100) NOT NULL,\n last_name VARCHAR(100) NOT NULL,\n status user_status DEFAULT 'active',\n email_verified BOOLEAN DEFAULT FALSE,\n created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,\n deleted_at TIMESTAMPTZ, -- Soft delete\n\n CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

),\n CONSTRAINT users_names_not_empty CHECK (LENGTH(TRIM(first_name)) > 0 AND LENGTH(TRIM(last_name)) > 0)\n);\n\n-- Strategic indexes\nCREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_users_status ON users(status) WHERE status != 'active';\nCREATE INDEX idx_users_created_at ON users(created_at);\nCREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Audit Trail","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE TYPE audit_operation AS ENUM ('INSERT', 'UPDATE', 'DELETE');\n\nCREATE TABLE audit_log (\n id BIGSERIAL PRIMARY KEY,\n table_name VARCHAR(255) NOT NULL,\n record_id BIGINT NOT NULL,\n operation audit_operation NOT NULL,\n old_values JSONB,\n new_values JSONB,\n changed_fields TEXT[],\n user_id BIGINT REFERENCES users(id),\n created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);\nCREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at);\n\n-- Trigger function\nCREATE OR REPLACE FUNCTION audit_trigger_function()\nRETURNS TRIGGER AS $\nBEGIN\n IF TG_OP = 'DELETE' THEN\n INSERT INTO audit_log (table_name, record_id, operation, old_values)\n VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));\n RETURN OLD;\n ELSIF TG_OP = 'UPDATE' THEN\n INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values)\n VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));\n RETURN NEW;\n ELSIF TG_OP = 'INSERT' THEN\n INSERT INTO audit_log (table_name, record_id, operation, new_values)\n VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));\n RETURN NEW;\n END IF;\nEND;\n$ LANGUAGE plpgsql;\n\n-- Apply to any table\nCREATE TRIGGER audit_users\nAFTER INSERT OR UPDATE OR DELETE ON users\nFOR EACH ROW EXECUTE FUNCTION audit_trigger_function();","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Soft Delete Pattern","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Query filter view\nCREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;\n\n-- Soft delete function\nCREATE OR REPLACE FUNCTION soft_delete(p_table TEXT, p_id BIGINT)\nRETURNS VOID AS $\nBEGIN\n EXECUTE format('UPDATE %I SET deleted_at = CURRENT_TIMESTAMP WHERE id = $1 AND deleted_at IS NULL', p_table)\n USING p_id;\nEND;\n$ LANGUAGE plpgsql;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Full-Text Search","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"ALTER TABLE products ADD COLUMN search_vector tsvector\n GENERATED ALWAYS AS (\n to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(sku, ''))\n ) STORED;\n\nCREATE INDEX idx_products_search ON products USING gin(search_vector);\n\n-- Query\nSELECT * FROM products\nWHERE search_vector @@ to_tsquery('english', 'laptop & gaming');","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Query Optimization","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Analyze Before Optimizing","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Always start here\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT u.id, u.name, COUNT(o.id) as order_count\nFROM users u\nLEFT JOIN orders o ON u.id = o.user_id\nWHERE u.created_at > '2024-01-01'\nGROUP BY u.id, u.name\nORDER BY order_count DESC;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Indexing Strategy","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Single column for exact lookups\nCREATE INDEX CONCURRENTLY idx_users_email ON users(email);\n\n-- Composite for multi-column queries (order matters!)\nCREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status, created_at);\n\n-- Partial index for filtered queries\nCREATE INDEX CONCURRENTLY idx_products_low_stock\nON products(inventory_quantity)\nWHERE inventory_tracking = true AND inventory_quantity \u003c= 5;\n\n-- Covering index (includes extra columns to avoid table lookup)\nCREATE INDEX CONCURRENTLY idx_orders_covering\nON orders(user_id, status) INCLUDE (total, created_at);\n\n-- GIN index for JSONB\nCREATE INDEX CONCURRENTLY idx_products_attrs ON products USING gin(attributes);\n\n-- Expression index\nCREATE INDEX CONCURRENTLY idx_users_email_lower ON users(lower(email));","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Find Unused Indexes","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT\n schemaname, tablename, indexname,\n idx_scan as scans,\n pg_size_pretty(pg_relation_size(indexrelid)) as size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\nORDER BY pg_relation_size(indexrelid) DESC;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Find Missing Indexes (Slow Queries)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Enable pg_stat_statements first\nSELECT query, calls, total_exec_time, mean_exec_time, rows\nFROM pg_stat_statements\nWHERE mean_exec_time > 100 -- ms\nORDER BY total_exec_time DESC\nLIMIT 20;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"N+1 Query Detection","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Look for repeated similar queries in pg_stat_statements\nSELECT query, calls, mean_exec_time\nFROM pg_stat_statements\nWHERE calls > 100 AND query LIKE '%WHERE%id = $1%'\nORDER BY calls DESC;","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Migration Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Safe Column Addition","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- +migrate Up\n-- Always use CONCURRENTLY for indexes in production\nALTER TABLE users ADD COLUMN phone VARCHAR(20);\nCREATE INDEX CONCURRENTLY idx_users_phone ON users(phone) WHERE phone IS NOT NULL;\n\n-- +migrate Down\nDROP INDEX IF EXISTS idx_users_phone;\nALTER TABLE users DROP COLUMN IF EXISTS phone;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Safe Column Rename (Zero-Downtime)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Step 1: Add new column\nALTER TABLE users ADD COLUMN display_name VARCHAR(100);\nUPDATE users SET display_name = name;\nALTER TABLE users ALTER COLUMN display_name SET NOT NULL;\n\n-- Step 2: Deploy code that writes to both columns\n-- Step 3: Deploy code that reads from new column\n-- Step 4: Drop old column\nALTER TABLE users DROP COLUMN name;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Table Partitioning","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Create partitioned table\nCREATE TABLE orders (\n id BIGSERIAL,\n user_id BIGINT NOT NULL,\n total DECIMAL(10,2),\n created_at TIMESTAMPTZ NOT NULL,\n PRIMARY KEY (id, created_at)\n) PARTITION BY RANGE (created_at);\n\n-- Monthly partitions\nCREATE TABLE orders_2024_01 PARTITION OF orders\n FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');\nCREATE TABLE orders_2024_02 PARTITION OF orders\n FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');\n\n-- Auto-create partitions\nCREATE OR REPLACE FUNCTION create_monthly_partition(p_table TEXT, p_date DATE)\nRETURNS VOID AS $\nDECLARE\n partition_name TEXT := p_table || '_' || to_char(p_date, 'YYYY_MM');\n next_date DATE := p_date + INTERVAL '1 month';\nBEGIN\n EXECUTE format(\n 'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',\n partition_name, p_table, p_date, next_date\n );\nEND;\n$ LANGUAGE plpgsql;","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"EF Core Migrations (.NET)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Create and Apply","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Add migration\ndotnet ef migrations add AddPhoneToUsers -p src/Infrastructure -s src/Api\n\n# Apply\ndotnet ef database update -p src/Infrastructure -s src/Api\n\n# Generate idempotent SQL script for production\ndotnet ef migrations script -p src/Infrastructure -s src/Api -o migration.sql --idempotent\n\n# Rollback\ndotnet ef database update PreviousMigrationName -p src/Infrastructure -s src/Api","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"EF Core Configuration Best Practices","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"csharp"},"content":[{"text":"// Use AsNoTracking for read queries\nvar users = await _db.Users\n .AsNoTracking()\n .Where(u => u.Status == UserStatus.Active)\n .Select(u => new UserDto { Id = u.Id, Name = u.Name })\n .ToListAsync(ct);\n\n// Avoid N+1 with Include\nvar orders = await _db.Orders\n .Include(o => o.Items)\n .ThenInclude(i => i.Product)\n .Where(o => o.UserId == userId)\n .ToListAsync(ct);\n\n// Better: Projection\nvar orders = await _db.Orders\n .Where(o => o.UserId == userId)\n .Select(o => new OrderDto\n {\n Id = o.Id,\n Total = o.Total,\n Items = o.Items.Select(i => new OrderItemDto\n {\n ProductName = i.Product.Name,\n Quantity = i.Quantity,\n }).ToList(),\n })\n .ToListAsync(ct);","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Caching Strategy","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Redis Query Cache","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"import Redis from 'ioredis'\n\nconst redis = new Redis(process.env.REDIS_URL)\n\nasync function cachedQuery\u003cT>(\n key: string,\n queryFn: () => Promise\u003cT>,\n ttlSeconds: number = 300\n): Promise\u003cT> {\n const cached = await redis.get(key)\n if (cached) return JSON.parse(cached)\n\n const result = await queryFn()\n await redis.setex(key, ttlSeconds, JSON.stringify(result))\n return result\n}\n\n// Usage\nconst products = await cachedQuery(\n `products:category:${categoryId}:page:${page}`,\n () => db.product.findMany({ where: { categoryId }, skip, take }),\n 300 // 5 minutes\n)\n\n// Invalidation\nasync function invalidateProductCache(categoryId: string) {\n const keys = await redis.keys(`products:category:${categoryId}:*`)\n if (keys.length) await redis.del(...keys)\n}","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Materialized Views","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE MATERIALIZED VIEW monthly_sales AS\nSELECT\n DATE_TRUNC('month', created_at) as month,\n category_id,\n COUNT(*) as order_count,\n SUM(total) as revenue,\n AVG(total) as avg_order_value\nFROM orders\nWHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)\nGROUP BY 1, 2;\n\nCREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month, category_id);\n\n-- Refresh (can be scheduled via pg_cron)\nREFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Connection Pool Configuration","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Node.js (pg)","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"import { Pool } from 'pg'\n\nconst pool = new Pool({\n max: 20, // Max connections\n idleTimeoutMillis: 30000, // Close idle connections after 30s\n connectionTimeoutMillis: 2000, // Fail fast if can't connect in 2s\n maxUses: 7500, // Refresh connection after N uses\n})\n\n// Monitor pool health\nsetInterval(() => {\n console.log({\n total: pool.totalCount,\n idle: pool.idleCount,\n waiting: pool.waitingCount,\n })\n}, 60000)","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Monitoring Queries","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Active Connections","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT count(*), state\nFROM pg_stat_activity\nWHERE datname = current_database()\nGROUP BY state;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Long-Running Queries","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT pid, now() - query_start AS duration, query, state\nFROM pg_stat_activity\nWHERE (now() - query_start) > interval '5 minutes'\nAND state = 'active';","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Table Sizes","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT\n relname AS table,\n pg_size_pretty(pg_total_relation_size(relid)) AS total_size,\n pg_size_pretty(pg_relation_size(relid)) AS data_size,\n pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size\nFROM pg_catalog.pg_statio_user_tables\nORDER BY pg_total_relation_size(relid) DESC\nLIMIT 20;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Table Bloat","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"SELECT\n tablename,\n pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size,\n n_dead_tup,\n n_live_tup,\n CASE WHEN n_live_tup > 0\n THEN round(n_dead_tup::numeric / n_live_tup, 2)\n ELSE 0\n END as dead_ratio\nFROM pg_stat_user_tables\nWHERE n_dead_tup > 1000\nORDER BY dead_ratio DESC;","type":"text"}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Anti-Patterns","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"SELECT *","type":"text","marks":[{"type":"code_inline"}]},{"text":" — always specify needed columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Missing indexes on foreign keys — always index FK columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"LIKE '%search%'","type":"text","marks":[{"type":"code_inline"}]},{"text":" — use full-text search or trigram indexes instead","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Large ","type":"text"},{"text":"IN","type":"text","marks":[{"type":"code_inline"}]},{"text":" clauses — use ","type":"text"},{"text":"ANY(ARRAY[...])","type":"text","marks":[{"type":"code_inline"}]},{"text":" or join a values list","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ No ","type":"text"},{"text":"LIMIT","type":"text","marks":[{"type":"code_inline"}]},{"text":" on unbounded queries — always paginate","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Creating indexes without ","type":"text"},{"text":"CONCURRENTLY","type":"text","marks":[{"type":"code_inline"}]},{"text":" in production","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Running migrations without testing rollback","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Ignoring ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" output — always verify execution plans","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Storing money as ","type":"text"},{"text":"FLOAT","type":"text","marks":[{"type":"code_inline"}]},{"text":" — use ","type":"text"},{"text":"DECIMAL(10,2)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or integer cents","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"❌ Missing ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" constraints — be explicit about nullability","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"database-operations","role":"specialist","scope":"implementation","author":"@skillopedia","source":{"stars":0,"repo_name":"openclaw-workspace","origin_url":"https://github.com/boomsystel-code/openclaw-workspace/blob/HEAD/skills/database-operations/SKILL.md","repo_owner":"boomsystel-code","body_sha256":"ed0020119d643cc5d885ac4a4c225b180a6deadb3f5f2324d76c2434149c1067","cluster_key":"f19cef0736b855e760b18f4c7aed8a378256fcf40da1c380cb37d2fdb5456c32","clean_bundle":{"format":"clean-skill-bundle-v1","source":"boomsystel-code/openclaw-workspace/skills/database-operations/SKILL.md","attachments":[{"id":"08fb8e82-8676-516f-a231-46cb0f9198bf","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/08fb8e82-8676-516f-a231-46cb0f9198bf/attachment.json","path":".clawhub/origin.json","size":151,"sha256":"99aa377fabc7bb8689293d3b4dfdd451031f146d964a95b773c60aef997a013b","contentType":"application/json; charset=utf-8"},{"id":"90d71496-5433-58cb-8e9a-f3d7b3bc70f5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/90d71496-5433-58cb-8e9a-f3d7b3bc70f5/attachment.json","path":"_meta.json","size":138,"sha256":"edd4fefd765a3a824916336363639844f0a86bac2b039a6e570f8c52c8947531","contentType":"application/json; charset=utf-8"}],"bundle_sha256":"c1db2e8343ed5f876d53893e86f83ae2fc7c9dc7754a817327381e29798b63f5","attachment_count":2,"text_attachments":2,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/database-operations/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","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"],"import_tag":"clean-skills-v1","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.","output-format":"code"}},"renderedAt":1782981021442}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.