Database Architect

What it does

The Database Architect skill assists in designing, optimizing, and troubleshooting relational and NoSQL databases. It guides users through a structured process, from understanding data requirements to leveraging advanced database features like those found in PostgreSQL 17. The skill focuses on schema design, indexing strategies, migration planning, query optimization, and utilizing new database capabilities for improved performance and scalability.

When to use it

• When designing a new database or application requiring data storage.
• To optimize the performance of existing slow-running queries.
• For planning and executing database migrations with minimal downtime.
• To leverage PostgreSQL 17 features like JSON enhancements, improved VACUUM memory management, and incremental backups.
• When needing to design a NoSQL database schema based on query patterns.

Key capabilities

• Requirements Gathering: Defines entities, relationships, access patterns, volume, and consistency needs.
• Schema Design (Relational): Normalization (3NF), primary/foreign key definition, denormalization considerations.
• Schema Design (NoSQL): Modeling for queries, embedding vs. referencing data, shard key selection.
• Indexing Strategy: Creating indexes based on query patterns and guidelines.
• Migration Planning: Versioned migrations with up/down scripts and best practices.
• Query Optimization: Analyzing execution plans using EXPLAIN ANALYZE, identifying table scans, optimizing joins, batch operations, connection pooling.
• PostgreSQL 17 Feature Utilization: Leveraging new features like JSON_TABLE(), improved VACUUM memory management, streaming I/O, and incremental backups.

Example prompts

• "Design a database schema for an e-commerce application with users, products, and orders."
• "How can I optimize this slow query: SELECT * FROM users WHERE email = '[email protected]';?"
• "What are the best practices for creating versioned migrations in PostgreSQL?"

Tips & gotchas

• The skill emphasizes a structured approach; follow the steps sequentially for optimal results.
• Denormalization should only be considered after proving performance needs through analysis.
• Be mindful of over-indexing, as it can negatively impact write performance.