Back to Details

databases

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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.
MongoDB（面向文档型）与PostgreSQL（关系型）数据库的统一使用指南。根据你的使用场景选择合适的数据库，并精通这两种系统。

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
适用于以下场景：
  • 设计数据库模式与数据模型
  • 编写查询语句（SQL或MongoDB查询语言）
  • 构建聚合管道或复杂关联查询
  • 优化索引与查询性能
  • 实施数据库迁移
  • 设置复制、分片或集群
  • 配置备份与灾难恢复
  • 管理数据库用户与权限
  • 分析慢查询与性能问题
  • 管理生产环境数据库部署

Database Selection Guide

数据库选择指南

Choose MongoDB When:

选择MongoDB的场景：

  • Schema flexibility: frequent structure changes, heterogeneous data
  • Document-centric: natural JSON/BSON data model
  • Horizontal scaling: need to shard across multiple servers
  • High write throughput: IoT, logging, real-time analytics
  • Nested/hierarchical data: embedded documents preferred
  • Rapid prototyping: schema evolution without migrations
Best for: Content management, catalogs, IoT time series, real-time analytics, mobile apps, user profiles
  • 模式灵活性：数据结构频繁变更、异构数据
  • 文档中心型：天然适配JSON/BSON数据模型
  • 水平扩展：需要跨多台服务器分片
  • 高写入吞吐量：物联网、日志、实时分析
  • 嵌套/层级数据：优先使用嵌入式文档
  • 快速原型开发：无需迁移即可实现模式演进
最佳适用场景： 内容管理、目录系统、物联网时间序列数据、实时分析、移动应用、用户画像

Choose PostgreSQL When:

选择PostgreSQL的场景：

  • Strong consistency: ACID transactions critical
  • Complex relationships: many-to-many joins, referential integrity
  • SQL requirement: team expertise, reporting tools, BI systems
  • Data integrity: strict schema validation, constraints
  • Mature ecosystem: extensive tooling, extensions
  • Complex queries: window functions, CTEs, analytical workloads
Best for: Financial systems, e-commerce transactions, ERP, CRM, data warehousing, analytics
  • 强一致性：ACID事务至关重要
  • 复杂关系：多对多关联、引用完整性
  • SQL需求：团队具备SQL经验、报表工具、BI系统
  • 数据完整性：严格的模式验证、约束
  • 成熟生态系统：丰富的工具与扩展
  • 复杂查询：窗口函数、CTE、分析型工作负载
最佳适用场景： 金融系统、电商交易、ERP、CRM、数据仓库、分析系统

Both Support:

两者均支持：

  • JSON/JSONB storage and querying
  • Full-text search capabilities
  • Geospatial queries and indexing
  • Replication and high availability
  • ACID transactions (MongoDB 4.0+)
  • Strong security features
  • JSON/JSONB存储与查询
  • 全文搜索功能
  • 地理空间查询与索引
  • 复制与高可用
  • ACID事务（MongoDB 4.0+）
  • 强大的安全特性

Quick Start

快速开始

MongoDB Setup

MongoDB 安装配置

bash
undefined
bash
undefined

Atlas (Cloud) - Recommended

Atlas（云服务）- 推荐

1. Sign up at mongodb.com/atlas

1. 在mongodb.com/atlas注册账号

2. Create M0 free cluster

2. 创建M0免费集群

3. Get connection string

3. 获取连接字符串

Connection

连接

mongodb+srv://user:pass@cluster.mongodb.net/db
mongodb+srv://user:pass@cluster.mongodb.net/db

Shell

命令行客户端

mongosh "mongodb+srv://cluster.mongodb.net/mydb"
mongosh "mongodb+srv://cluster.mongodb.net/mydb"

Basic operations

基础操作

db.users.insertOne({ name: "Alice", age: 30 }) db.users.find({ age: { $gte: 18 } }) db.users.updateOne({ name: "Alice" }, { $set: { age: 31 } }) db.users.deleteOne({ name: "Alice" })
undefined
db.users.insertOne({ name: "Alice", age: 30 }) db.users.find({ age: { $gte: 18 } }) db.users.updateOne({ name: "Alice" }, { $set: { age: 31 } }) db.users.deleteOne({ name: "Alice" })
undefined

PostgreSQL Setup

PostgreSQL 安装配置

bash
undefined
bash
undefined

Ubuntu/Debian

Ubuntu/Debian系统

sudo apt-get install postgresql postgresql-contrib
sudo apt-get install postgresql postgresql-contrib

Start service

启动服务

sudo systemctl start postgresql
sudo systemctl start postgresql

Connect

连接数据库

psql -U postgres -d mydb
psql -U postgres -d mydb

Basic operations

基础操作

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, age INT); INSERT INTO users (name, age) VALUES ('Alice', 30); SELECT * FROM users WHERE age >= 18; UPDATE users SET age = 31 WHERE name = 'Alice'; DELETE FROM users WHERE name = 'Alice';
undefined
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, age INT); INSERT INTO users (name, age) VALUES ('Alice', 30); SELECT * FROM users WHERE age >= 18; UPDATE users SET age = 31 WHERE name = 'Alice'; DELETE FROM users WHERE name = 'Alice';
undefined

Common Operations

常见操作

Create/Insert

创建/插入

javascript
// MongoDB
db.users.insertOne({ name: "Bob", email: "bob@example.com" })
db.users.insertMany([{ name: "Alice" }, { name: "Charlie" }])
sql
-- PostgreSQL
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Alice', NULL), ('Charlie', NULL);
javascript
// MongoDB
db.users.insertOne({ name: "Bob", email: "bob@example.com" })
db.users.insertMany([{ name: "Alice" }, { name: "Charlie" }])
sql
-- PostgreSQL
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Alice', NULL), ('Charlie', NULL);

Read/Query

读取/查询

javascript
// MongoDB
db.users.find({ age: { $gte: 18 } })
db.users.findOne({ email: "bob@example.com" })
sql
-- PostgreSQL
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE email = 'bob@example.com' LIMIT 1;
javascript
// MongoDB
db.users.find({ age: { $gte: 18 } })
db.users.findOne({ email: "bob@example.com" })
sql
-- PostgreSQL
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE email = 'bob@example.com' LIMIT 1;

Update

更新

javascript
// MongoDB
db.users.updateOne({ name: "Bob" }, { $set: { age: 25 } })
db.users.updateMany({ status: "pending" }, { $set: { status: "active" } })
sql
-- PostgreSQL
UPDATE users SET age = 25 WHERE name = 'Bob';
UPDATE users SET status = 'active' WHERE status = 'pending';
javascript
// MongoDB
db.users.updateOne({ name: "Bob" }, { $set: { age: 25 } })
db.users.updateMany({ status: "pending" }, { $set: { status: "active" } })
sql
-- PostgreSQL
UPDATE users SET age = 25 WHERE name = 'Bob';
UPDATE users SET status = 'active' WHERE status = 'pending';

Delete

删除

javascript
// MongoDB
db.users.deleteOne({ name: "Bob" })
db.users.deleteMany({ status: "deleted" })
sql
-- PostgreSQL
DELETE FROM users WHERE name = 'Bob';
DELETE FROM users WHERE status = 'deleted';
javascript
// MongoDB
db.users.deleteOne({ name: "Bob" })
db.users.deleteMany({ status: "deleted" })
sql
-- PostgreSQL
DELETE FROM users WHERE name = 'Bob';
DELETE FROM users WHERE status = 'deleted';

Indexing

索引

javascript
// MongoDB
db.users.createIndex({ email: 1 })
db.users.createIndex({ status: 1, createdAt: -1 })
sql
-- PostgreSQL
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);
javascript
// MongoDB
db.users.createIndex({ email: 1 })
db.users.createIndex({ status: 1, createdAt: -1 })
sql
-- PostgreSQL
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

Reference Navigation

参考文档导航

MongoDB References

MongoDB 参考文档

  • 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
  • mongodb-crud.md - CRUD操作、查询运算符、原子更新
  • mongodb-aggregation.md - 聚合管道、阶段、运算符、模式
  • mongodb-indexing.md - 索引类型、复合索引、性能优化
  • mongodb-atlas.md - Atlas云服务配置、集群、监控、搜索

PostgreSQL References

PostgreSQL 参考文档

  • 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
  • postgresql-queries.md - SELECT、关联查询、子查询、CTE、窗口函数
  • postgresql-psql-cli.md - psql命令、元命令、脚本
  • postgresql-performance.md - EXPLAIN、查询优化、VACUUM、索引
  • postgresql-administration.md - 用户管理、备份、复制、维护

Python Utilities

Python 工具

Database utility scripts in 
scripts/
:
  • db_migrate.py - Generate and apply migrations for both databases
  • db_backup.py - Backup and restore MongoDB and PostgreSQL
  • db_performance_check.py - Analyze slow queries and recommend indexes
bash
undefined
scripts/
目录下的数据库工具脚本：
  • db_migrate.py - 为两种数据库生成并执行迁移
  • db_backup.py - MongoDB与PostgreSQL的备份与恢复
  • db_performance_check.py - 分析慢查询并推荐索引
bash
undefined

Generate migration

生成迁移脚本

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

Run backup

执行备份

python scripts/db_backup.py --db postgres --output /backups/
python scripts/db_backup.py --db postgres --output /backups/

Check performance

检查性能

python scripts/db_performance_check.py --db mongodb --threshold 100ms
undefined
python scripts/db_performance_check.py --db mongodb --threshold 100ms
undefined

Key Differences Summary

核心差异总结

FeatureMongoDBPostgreSQL
Data ModelDocument (JSON/BSON)Relational (Tables/Rows)
SchemaFlexible, dynamicStrict, predefined
Query LanguageMongoDB Query LanguageSQL
Joins$lookup (limited)Native, optimized
TransactionsMulti-document (4.0+)Native ACID
ScalingHorizontal (sharding)Vertical (primary), Horizontal (extensions)
IndexesSingle, compound, text, geo, etcB-tree, hash, GiST, GIN, etc
特性MongoDBPostgreSQL
数据模型文档型（JSON/BSON）关系型（表/行）
模式灵活、动态严格、预定义
查询语言MongoDB查询语言SQL
关联查询$lookup（有限支持）原生、优化完善
事务多文档事务（4.0+）原生ACID
扩展方式水平扩展（分片）垂直扩展（主节点）、水平扩展（扩展插件）
索引单字段、复合、文本、地理空间等B-tree、哈希、GiST、GIN等

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
MongoDB：
  • 1对少关系使用嵌入式文档
  • 1对多或多对多关系使用引用文档
  • 为频繁查询的字段创建索引
  • 使用聚合管道处理复杂转换
  • 生产环境启用认证与TLS
  • 使用Atlas托管服务
PostgreSQL：
  • 模式规范化至3NF，为性能需求反规范化
  • 使用外键保证引用完整性
  • 为外键与频繁过滤的字段创建索引
  • 使用EXPLAIN ANALYZE优化查询
  • 定期执行VACUUM与ANALYZE维护
  • Web应用使用连接池（pgBouncer）

Resources

资源