databases

Query optimization in PostgreSQL involves analyzing execution plans with EXPLAIN, adding appropriate indexes on frequently filtered columns, and restructuring joins. This skill provides concrete examples for diagnosing slow queries and applying indexing strategies to reduce execution time.

MongoDB migrations require planning schema compatibility, using tools like mongodump/mongorestore or MongoDB Atlas for data transfer, and validating data integrity post-migration. This skill covers migration patterns, Python automation scripts, and production considerations for both self-hosted and cloud deployments.

Automated backups involve configuring backup scripts, scheduling with cron or equivalent tools, and storing snapshots securely. This skill provides runnable backup scripts and CLI commands for both PostgreSQL and MongoDB to ensure data recovery capability.

Aggregation pipelines handle complex multi-stage data transformations, filtering, grouping, and calculations that simple queries cannot express. This skill covers pipeline construction, stage ordering, and performance patterns for MongoDB analytical workloads.

PostgreSQL and MongoDB serve different use cases—relational structure versus flexible schemas—and can coexist in polyglot architectures. This skill addresses schema design, query construction, and administration patterns for each, enabling informed technology selection per workflow.

Indexing accelerates query execution by reducing full-table scans; strategy depends on query patterns, data distribution, and update frequency. This skill provides indexing guidance, performance analysis commands, and configuration examples for PostgreSQL and MongoDB at scale.