---
name: ck-databases
description: Design schemas and write queries for MongoDB and PostgreSQL. Use for database design, SQL/NoSQL queries, aggregation pipelines, indexes, migrations, replication, performance optimization, psql CLI operations.
---

# ck-databases

Unified guide for working with MongoDB (document-oriented) and PostgreSQL (relational) databases.

## When to Use

- Designing database schemas and data models
- Writing queries (SQL or MongoDB query language)
- Building aggregation pipelines or complex joins
- Optimizing indexes and query performance
- Implementing database migrations
- Setting up replication, sharding, or clustering
- Configuring backups and disaster recovery
- Analyzing slow queries and performance issues
- Administering production database deployments

## Don't Use When

- ORM/query-builder API work only — refer to the specific library's docs via `ck-docs-seeker`
- Infrastructure setup for managed database services — use `ck-devops`

## Database Selection Guide

| Need | Choose |
|------|--------|
| ACID transactions, complex joins | PostgreSQL |
| Flexible document schema | MongoDB |
| Caching, sessions, queues | Redis |
| Analytics/OLAP workloads | PostgreSQL with proper indexing |
| 1-to-few relationships | MongoDB embedded documents |
| 1-to-many or many-to-many | MongoDB references or PostgreSQL FK |

## MongoDB Best Practices

- Use embedded documents for 1-to-few relationships
- Reference documents for 1-to-many or many-to-many
- Index frequently queried and sorted fields
- Use aggregation pipeline for complex transformations
- Enable authentication and TLS in production
- Use Atlas for managed hosting

**Common patterns:**
- CRUD operations with query operators and atomic updates
- Aggregation pipeline stages: `$match`, `$group`, `$lookup`, `$project`, `$sort`
- Index types: single field, compound, text, geospatial, TTL
- Atlas search for full-text capabilities

## PostgreSQL Best Practices

- Normalize schema to 3NF; denormalize selectively for performance
- Use foreign keys for referential integrity
- Index foreign keys and frequently filtered columns
- Use `EXPLAIN ANALYZE` to optimize queries
- Regular `VACUUM` and `ANALYZE` maintenance
- Connection pooling via pgBouncer for web apps

**Key features:**
- `SELECT`, JOINs, subqueries, CTEs, window functions
- `psql` meta-commands for administration
- `EXPLAIN`, query optimization, vacuum, index maintenance
- User management, backups, replication, point-in-time recovery

## Python Utility Scripts

Available in the skill's `scripts/` directory:

```bash
# Generate and apply migrations
python scripts/db_migrate.py --db mongodb --generate "add_user_index"
python scripts/db_migrate.py --db postgres --apply

# Backup and restore
python scripts/db_backup.py --db postgres --output /backups/

# Performance analysis
python scripts/db_performance_check.py --db mongodb --threshold 100ms
```

## Performance Optimization Checklist

- [ ] Indexes on frequently queried fields
- [ ] Compound indexes ordered by selectivity
- [ ] `EXPLAIN ANALYZE` run on slow queries
- [ ] Connection pooling configured
- [ ] Query result caching with Redis where appropriate
- [ ] Pagination instead of full table scans
- [ ] Read replicas for read-heavy workloads
- [ ] Partitioning for very large tables (PostgreSQL)

## Security Checklist

- [ ] Authentication enabled (no anonymous access)
- [ ] TLS/SSL for connections in production
- [ ] Parameterized queries everywhere (never string concatenation)
- [ ] Least-privilege database users
- [ ] Regular backups tested for restore
- [ ] Audit logging enabled for sensitive operations

## Resources

- MongoDB: https://www.mongodb.com/docs/
- PostgreSQL: https://www.postgresql.org/docs/