databases

Query optimization in PostgreSQL involves analyzing execution plans, adding strategic indexes on frequently filtered columns, and rewriting queries to reduce table scans. Use EXPLAIN ANALYZE to identify bottlenecks, then create indexes on WHERE clause and JOIN columns, and consider query restructuring for complex operations.

MongoDB schema design balances embedding documents for related data against referencing for scalability. Model based on access patterns, denormalize frequently accessed data, use arrays for one-to-many relationships, and normalize when data is updated independently across many documents.

Production replication creates failover replicas; PostgreSQL uses streaming replication or logical replication, MongoDB uses replica sets. Backups combine automated snapshots with point-in-time recovery. Test restore procedures regularly and maintain separate backup storage to ensure reliability and compliance.

Aggregation pipelines process and transform MongoDB documents through stages like $match, $group, $project, and $sort. They filter data, compute statistics, reshape documents, and perform complex analytics within the database, reducing data transfer and improving performance.

Add indexes on columns used in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY operations. Monitor query performance with EXPLAIN ANALYZE in PostgreSQL or explain() in MongoDB. Avoid over-indexing, which slows writes; balance read optimization against write overhead based on workload patterns.

Data migration requires mapping relational schemas to document models, exporting source data, transforming structure, and validating completeness. Use ETL tools, custom scripts, or database-native utilities. Plan for downtime or dual-write strategies, verify record counts and data integrity, and test rollback procedures.