databases_skill

Overview

This skill helps you design, query, optimize, migrate, and administer MongoDB and PostgreSQL databases. It covers schema design, aggregation pipelines and SQL queries, index strategies, replication/sharding, backups, and production troubleshooting. Use it to choose the right database pattern and apply pragmatic best practices for reliability and performance.

How this skill works

The skill inspects your use case and suggests whether a document (MongoDB) or relational (PostgreSQL) model fits best, then provides concrete schema patterns, query examples, and index recommendations. It generates or reviews queries and aggregation pipelines, outlines migration steps, and recommends backup, replication, and monitoring configurations. For production issues it analyzes slow queries, suggests EXPLAIN/EXPLAIN ANALYZE guidance, and proposes index and schema changes.

When to use it

• Designing or reviewing database schemas and data models
• Writing or optimizing SQL queries and MongoDB aggregation pipelines
• Planning migrations, backups, replication, or sharding strategies
• Tuning indexes and diagnosing slow queries in production
• Setting up user permissions, security, and monitoring for databases

Best practices

• Choose MongoDB for flexible, document-centric data and high write throughput; choose PostgreSQL for strict consistency and complex relational queries
• Index frequently filtered and joined columns; use compound and partial indexes where appropriate
• Use EXPLAIN/EXPLAIN ANALYZE or MongoDB explain() to profile and iterate on slow queries
• Apply schema constraints and foreign keys in PostgreSQL; use embedded vs referenced documents in MongoDB based on access patterns
• Automate backups and test restores; enable authentication and TLS in production; use connection pooling for high-concurrency apps

Example use cases

• Designing a user profile service with JSONB in PostgreSQL or documents in MongoDB depending on flexibility needs
• Writing and optimizing a complex reporting SQL query using CTEs and window functions
• Building a MongoDB aggregation pipeline to roll up time-series events for realtime analytics
• Planning a migration path from a single-node DB to sharded MongoDB cluster or a high-availability PostgreSQL setup with replication
• Analyzing slow transactions in production and recommending index or query rewrites