skills.rest
htafolla
htafollaCOMMUNITY
Explore all skills by this author →
GitHub

database-engineer

Design database schemas, optimize queries with EXPLAIN ANALYZE, and plan safe migrations.

Updated Sep 9, 2025
Search Tags:#performance#query optimization#indexing#database migrations#schema design#PostgreSQL#SQL safety
One-click install
npx skills add https://github.com/htafolla/chrono-warp-drive --skill database-engineer-htafolla
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-engineer
Source: https://github.com/htafolla/chrono-warp-drive/tree/main/.opencode/skills/database-engineer
Command: npx skills add https://github.com/htafolla/chrono-warp-drive --skill database-engineer-htafolla

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

It solves the problem of slow or unreliable database design by guiding you to build correct schemas, efficient queries, and safe migrations that reduce downtime and production risk.

Core Features & Use Cases

  • Schema Design for correctness and performance: Applies normalization discipline (3NF minimum) with intentional denormalization for read-heavy workloads, using appropriate data types and standard audit/soft-delete columns.
  • Query Optimization that eliminates bottlenecks: Uses EXPLAIN ANALYZE, covering indexes, explicit column selection, and parameterized queries, with cursor-based pagination to avoid OFFSET inefficiency.
  • Migration Strategy for zero/low downtime: Enforces transactional wraps, additive/gradual column changes, concurrent index creation, batch backfills, and deprecation windows before removals.

Use case: You’re launching a new feature that adds audit fields and soft deletes to existing entities, then must keep key read queries under strict latency targets while migrating without downtime.

Quick Start

Ask: Create a PostgreSQL schema for a multi-tenant billing system, propose indexes for the top 5 queries, and outline a zero-downtime migration plan adding deleted_at and audit columns.

Frequently Asked Questions about database-engineer

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a zero-downtime database migration for adding audit columns?▼

You can achieve zero-downtime database migrations by using transactional wraps, additive column changes, concurrent index creation, batch backfills, and deprecation windows before removing old structures.

How do I optimize slow PostgreSQL queries using EXPLAIN ANALYZE?▼

You optimize PostgreSQL queries by running EXPLAIN ANALYZE to identify bottlenecks, then applying covering indexes, explicit column selection, parameterized queries, and cursor-based pagination to avoid OFFSET inefficiency.

What is the best way to design a database schema for read-heavy workloads?▼

The best way to design a database schema for read-heavy workloads applies 3NF normalization as a baseline with intentional denormalization, appropriate data types, and standard audit or soft-delete columns to improve query performance.

Does this database schema design approach work for both OLTP and OLAP systems?▼

Yes, the database schema design approach applies to both OLTP and OLAP workloads across SQL and NoSQL systems, supporting correct schemas, efficient queries, and safe migrations for varied data access patterns.

Why should I use cursor-based pagination instead of OFFSET in SQL queries?▼

You should use cursor-based pagination instead of OFFSET in SQL queries because OFFSET causes increasing scan times on large datasets, whereas cursor-based pagination maintains consistent query performance.

What are the limitations of concurrent index creation during database migrations?▼

Concurrent index creation during database migrations takes longer to complete and requires additional server resources, but it prevents blocking write operations and significantly reduces production downtime risks.

Related Skills

Explore More Software Engineering Skills→
sskim91sskim91
1

sql-optimization-patterns

Analyze SQL query execution plans and apply indexing strategies to reduce latency.

Community
Intermediate
scanadyscanady

tech-database-optimizer

Analyze PostgreSQL and MySQL EXPLAIN ANALYZE outputs to diagnose database bottlenecks.

Community
Advanced
yuhle-qugyuhle-qug

database-expert

Analyze and optimize database performance across PostgreSQL, MySQL, MongoDB, and SQLite.

Community
Advanced
HieubkavHieubkav

sql-optimization-patterns

Analyze SQL query plans with EXPLAIN and implement indexing strategies.

Community
Advanced