t1k:web:backend:databases

Field	Value
Module	`backend`
Version	`1.7.0`
Effort	`medium`
Tools	—

Keywords: aggregation, database, migration, MongoDB, PostgreSQL, query, schema, SQL

How to invoke

/t1k:web:backend:databases
[query or schema task]

Databases Skill

Unified guide for working with MongoDB (document-oriented) and PostgreSQL (relational) databases. Choose the right database for your use case and master both systems.

When to Use This Skill

Use when:

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
Managing database users and permissions
Analyzing slow queries and performance issues
Administering production database deployments

Database Design

db-design.md - Activate when user requests: Database/table design for transactional (OLTP), analytics (OLAP), create or extend schema, design fact/dimension tables, analyze/review CSV/JSON/SQL files to create tables, or need advice on data storage structure.

MongoDB References

mongodb-crud.md - CRUD operations, query operators, atomic updates
mongodb-aggregation.md - Aggregation pipeline, stages, operators, patterns
mongodb-indexing.md - Index types, compound indexes, performance optimization
mongodb-atlas.md - Atlas cloud setup, clusters, monitoring, search

PostgreSQL References

postgresql-queries.md - SELECT, JOINs, subqueries, CTEs, window functions
postgresql-psql-cli.md - psql commands, meta-commands, scripting
postgresql-performance.md - EXPLAIN, query optimization, vacuum, indexes
postgresql-administration.md - User management, backups, replication, maintenance

Python Utilities

Database utility scripts in scripts/:

db_migrate.py - Generate and apply migrations for both databases (MongoDB and PostgreSQL)
db_backup.py - Backup and restore MongoDB and PostgreSQL
db_performance_check.py - Analyze slow queries and recommend indexes

# Generate migration
python scripts/db_migrate.py --db mongodb --generate "add_user_index"

# Run backup
python scripts/db_backup.py --db postgres --output /backups/

# Check performance
python scripts/db_performance_check.py --db mongodb --threshold 100ms

Best Practices

MongoDB:

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

PostgreSQL:

Normalize schema to 3NF, denormalize 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 (pgBouncer) for web apps

Resources

MongoDB: https://www.mongodb.com/docs/
PostgreSQL: https://www.postgresql.org/docs/
MongoDB University: https://learn.mongodb.com/
PostgreSQL Tutorial: https://www.postgresqltutorial.com/

Gotchas

Connection pools per-instance NOT per-process — serverless functions instantiate a pool per cold start; without pg-bouncer upstream you’ll exhaust connections at concurrency 100+.
ORM-generated migrations can drop columns silently — review every migration diff before merge; auto-merge is a prod-incident waiting to happen.
Postgres advisory locks vs row locks — bulk jobs should use advisory; per-record updates use FOR UPDATE SKIP LOCKED.
Indexes added without CONCURRENTLY block writes for the duration of CREATE INDEX on prod tables.