Skip to content

t1k:web:backend:databases

FieldValue
Modulebackend
Version1.13.12
Effortmedium
Tools—

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

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

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

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
  • Storing growth-driven config (allow-lists, tenants, flags) as rows, not env or JSON: see t1k:web:devops:deploy § “Config Is Data, Not Deploy Config”
  • Managing database users and permissions
  • Analyzing slow queries and performance issues
  • Administering production database deployments
  • 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-crud.md (references/mongodb-crud.md) - CRUD operations, query operators, atomic updates
  • mongodb-aggregation.md (references/mongodb-aggregation.md) - Aggregation pipeline, stages, operators, patterns
  • mongodb-indexing.md (references/mongodb-indexing.md) - Index types, compound indexes, performance optimization
  • mongodb-atlas.md (references/mongodb-atlas.md) - Atlas cloud setup, clusters, monitoring, search
  • postgresql-queries.md (references/postgresql-queries.md) - SELECT, JOINs, subqueries, CTEs, window functions
  • postgresql-psql-cli.md (references/postgresql-psql-cli.md) - psql commands, meta-commands, scripting
  • postgresql-performance.md (references/postgresql-performance.md) - EXPLAIN, query optimization, vacuum, indexes
  • postgresql-administration.md (references/postgresql-administration.md) - User management, backups, replication, maintenance

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
Terminal window
# Generate migration
python scripts/db_migrate.py --db mongodb generate add_user_index
# Run backup
python scripts/db_backup.py --db postgres --backup-dir /backups/ backup --uri "$DATABASE_URL"
# Check performance (threshold in ms)
python scripts/db_performance_check.py --db mongodb --uri "$MONGODB_URI" --threshold 100

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
  • prisma migrate diff RESETS whatever --shadow-database-url points at — the --from-migrations/--to-migrations forms require a shadow DB and drop/recreate every table in it to replay migrations. Passing a live DATABASE_URL there destroys production data. For a read-only drift check use --from-url "$DATABASE_URL" --to-schema-datamodel prisma/schema.prisma, which needs no shadow DB; if one is genuinely required, CREATE DATABASE shadow_check and point at that.