databases_skill

Overview

This skill provides practical MongoDB and PostgreSQL database administration guidance for designing schemas, optimizing queries, and operating production deployments. It combines document-store and relational best practices to help you choose the right model, implement indexes, run migrations, and maintain backups. The skill targets developers and DBAs who need concrete, actionable steps for performance and reliability.

How this skill works

The skill inspects schema design, query patterns, and index usage across MongoDB and PostgreSQL and recommends optimizations such as new indexes, query rewrites, or schema changes. It helps plan and execute migrations, set up replication/sharding, and defines backup and restore procedures. It also surfaces common diagnostics (EXPLAIN plans, slow query traces) and prescribes targeted fixes like index creation or connection pooling.

When to use it

• Designing or reviewing a new database schema
• Writing or optimizing complex SQL or aggregation pipeline queries
• Creating indexes to improve query performance
• Planning or performing data migrations between schemas or systems
• Setting up replication, sharding, backups, or disaster recovery
• Troubleshooting slow queries and production performance issues

Best practices

• Choose MongoDB for flexible, document-centric workloads and PostgreSQL for strong consistency and complex relational queries
• Design schemas to match access patterns: embed for 1-to-few, reference for many-to-many
• Always analyze queries with EXPLAIN/EXPLAIN ANALYZE or MongoDB explain and act on hotspots
• Index selectively: favor compound indexes that match query filters and sort order
• Use connection pooling (pgBouncer) and appropriate client settings to avoid connection storms
• Implement regular backups, test restores, and automate maintenance (VACUUM/ANALYZE for Postgres; backup snapshots for MongoDB)

Example use cases

• Build a content catalog with MongoDB documents and aggregation pipelines for search and filtering
• Implement financial transactions and analytics in PostgreSQL using ACID transactions and window functions
• Migrate an app from a relational schema to a hybrid model with JSONB for semi-structured fields
• Optimize a slow API by adding a compound index and rewriting a costly join into a targeted query
• Set up MongoDB sharding for a high-write IoT ingestion pipeline with Atlas monitoring