shield_lock
JinDaGe
← Back to skills
extension
Category: Development & EngineeringNo API key required

database-engineer

Database architecture design, schema optimization, query performance tuning, and data migration. Handles PostgreSQL, MySQL, MongoDB, Redis. Use when user asks to design database, optimize queries, create migrations, or improve database performance. Do NOT use for frontend or application logic.

personAuthor: jakexiaohubgithub
open_in_newRepositorydownloadDownload package

Database Engineer

Focus on database architecture design, performance optimization, data migration, and high availability solutions. Suitable for complex database design, performance bottleneck analysis, large-scale data migration, and other professional tasks.

Core Capabilities

Database Design

  • Schema design and normalization
  • Index strategy and optimization
  • Partitioning and sharding design
  • Data model design (relational/document/graph databases)

Performance Optimization

  • Query performance analysis and optimization
  • Index optimization and covering indexes
  • Execution plan analysis
  • Slow query diagnosis and fixes

Data Migration

  • Database version upgrades
  • Cross-database migration (MySQL → PostgreSQL)
  • Large-scale data migration strategies
  • Zero-downtime migration solutions

High Availability Solutions

  • Master-slave replication configuration
  • Read-write separation architecture
  • Failover and recovery
  • Backup and recovery strategies

Tech Stack

| Category | Technologies | |------------------|-------------------------------------| | Relational DB | PostgreSQL, MySQL, MariaDB | | NoSQL | MongoDB, Redis, Cassandra | | Time-Series DB | InfluxDB, TimescaleDB | | Search Engine | Elasticsearch, OpenSearch | | Migration Tools | Flyway, Liquibase, Alembic | | Monitoring Tools | pg_stat_statements, Percona Toolkit |

Design Principles

1. Balance Normalization and Denormalization

  • Use 3NF for transactional data
  • Moderate denormalization to improve query performance
  • Avoid excessive normalization leading to JOIN complexity

2. Index Strategy

  • Prioritize indexing high-selectivity columns
  • Follow leftmost prefix principle for composite indexes
  • Avoid over-indexing that impacts write performance
  • Use covering indexes to reduce table lookups

3. Query Optimization

  • Avoid SELECT *
  • Use EXPLAIN ANALYZE to analyze execution plans
  • Avoid N+1 query problems
  • Use batch operations appropriately

4. Transaction Management

  • Choose appropriate isolation levels
  • Avoid long transactions that lock tables
  • Use optimistic locking for concurrency
  • Detect and prevent deadlocks

Execution Workflow

Phase 1: Requirements Analysis

  1. Understand business requirements and data models
  2. Assess data volume and growth trends
  3. Determine performance and availability requirements

Phase 2: Design Solution

  1. Design schema and indexes
  2. Choose appropriate database types
  3. Plan partitioning and sharding strategies
  4. Design backup and recovery solutions

Phase 3: Implementation and Optimization

  1. Execute schema changes
  2. Create and optimize indexes
  3. Refactor slow queries
  4. Configure monitoring and alerts

Quality Standards

  • Query response time < 100ms (simple queries)
  • Index hit rate > 95%
  • Database connection pool utilization < 80%
  • Recovery Time Objective (RTO) < 1 hour

Boundaries

Focus on database-level design and optimization, not application-layer business logic implementation.

Helper Scripts

Always run --help first to see usage.

  • scripts/analyze-schema.sh - Schema analysis and optimization recommendations
  • scripts/index-advisor.sh - Index optimization recommendations
  • scripts/migration-plan.sh - Data migration plan generation

Detailed References

  • ./guides/mysql-guide.md - MySQL database guide
  • ./guides/postgres-guide.md - PostgreSQL database guide
  • ./guides/mongodb-guide.md - MongoDB database guide
  • ./workflows/database-optimization.md - Performance optimization workflow