Menu
Starting new projects and choosing database technology
Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Basé sur la classification professionnelle SOC
| name | database-database-selection |
| description | Starting new projects and choosing database technology |
Scope: Choosing the right database, SQL vs NoSQL, database comparison Lines: ~280 Last Updated: 2025-10-18
Activate this skill when:
SQL (Relational):
NoSQL (Non-Relational):
Type: Relational (SQL) Best For: General-purpose applications, complex queries, data integrity
Strengths:
Weaknesses:
Use Cases:
Example:
-- Complex query with JOINs, aggregations, CTEs
WITH top_customers AS (
SELECT user_id, SUM(total) as spent
FROM orders
WHERE created_at > NOW() - INTERVAL '1 year'
GROUP BY user_id
ORDER BY spent DESC
LIMIT 100
)
SELECT u.email, tc.spent, COUNT(o.id) as order_count
FROM top_customers tc
JOIN users u ON tc.user_id = u.id
JOIN orders o ON o.user_id = u.id
GROUP BY u.email, tc.spent;
Type: Relational (SQL) Best For: Web applications, read-heavy workloads, simple transactions
Strengths:
Weaknesses:
Use Cases:
When to choose over PostgreSQL:
Type: Document (NoSQL) Best For: Flexible schemas, hierarchical data, rapid development
Strengths:
Weaknesses:
Use Cases:
Example:
// Embedded document (no JOIN needed)
{
"_id": ObjectId("..."),
"user": "alice",
"cart": {
"items": [
{ "product": "Widget", "price": 29.99, "qty": 2 },
{ "product": "Gadget", "price": 49.99, "qty": 1 }
],
"total": 109.97
}
}
Type: In-memory key-value (NoSQL) Best For: Caching, sessions, real-time analytics, queues
Strengths:
Weaknesses:
Use Cases:
Example:
# Cache user session
SET session:abc123 '{"user_id": 42, "email": "alice@example.com"}' EX 3600
# Rate limiting
INCR ratelimit:user:42
EXPIRE ratelimit:user:42 60
# Leaderboard
ZADD leaderboard 9500 "alice"
ZREVRANGE leaderboard 0 9 WITHSCORES
Type: Key-value/Document (NoSQL, AWS) Best For: Serverless apps, high-scale key-value, event-driven
Strengths:
Weaknesses:
Use Cases:
When to choose:
Type: Relational (SQL, embedded) Best For: Local storage, embedded apps, development
Strengths:
Weaknesses:
Use Cases:
When to choose:
Atomicity: Transaction succeeds completely or fails completely Consistency: Data moves from one valid state to another Isolation: Concurrent transactions don't interfere Durability: Committed data is permanent
Example (PostgreSQL):
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both succeed or both fail
Best for:
Basically Available: System remains operational Soft state: State may change over time (eventual consistency) Eventual consistency: System will become consistent eventually
Example (MongoDB):
// Write may return before replication completes
db.users.updateOne(
{ _id: ObjectId("...") },
{ $inc: { post_count: 1 } }
)
// Other replicas may see stale data temporarily
Best for:
Guarantee: All reads return most recent write
Databases: PostgreSQL, MySQL (default), MongoDB (with read concern "linearizable")
Use case: Banking, inventory systems
Tradeoff: Higher latency, lower availability
Guarantee: Reads will eventually reflect writes (may be stale temporarily)
Databases: DynamoDB (default), Cassandra, MongoDB (with read concern "local")
Use case: Social feeds, product catalogs, analytics
Tradeoff: Lower latency, higher availability, potential stale reads
Guarantee: Reads respect causality (if A caused B, all see A before B)
Databases: MongoDB (with causal consistency sessions)
Use case: Chat applications, collaborative editing
Tradeoff: Balance between strong and eventual
Characteristics:
Databases: MySQL (with read replicas), Elasticsearch, ClickHouse
Patterns:
-- Denormalized for reads (PostgreSQL)
CREATE MATERIALIZED VIEW user_stats AS
SELECT
user_id,
COUNT(DISTINCT order_id) as order_count,
SUM(total) as lifetime_value
FROM orders
GROUP BY user_id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW user_stats;
Use cases:
Characteristics:
Databases: Cassandra, ClickHouse (for inserts), Kafka (event streaming)
Patterns:
// Append-only event log (MongoDB)
db.events.insertOne({
event_type: "page_view",
user_id: 42,
page: "/products/123",
timestamp: new Date()
})
// No updates, only inserts
Use cases:
Approach: Increase resources (CPU, RAM, disk) on single server
Best for: SQL databases, simple setups
Limits: Hardware limits (expensive at high end)
Databases: PostgreSQL, MySQL, SQLite
Approach: Add more servers, distribute data
Strategies:
Write → Primary
Reads → Replicas (multiple)
Databases: PostgreSQL, MySQL, MongoDB
Use case: Read-heavy workloads (10:1 read/write ratio)
Data partitioned across multiple servers by key
Users A-M → Shard 1
Users N-Z → Shard 2
Databases: MongoDB (built-in), PostgreSQL (Citus extension), Cassandra
Use case: Massive datasets, write-heavy workloads
Complexity: Application-level sharding logic, cross-shard queries expensive
What's the bottleneck?
│
├─ Reads → Add read replicas
│ └─ Still slow? → Add caching (Redis)
│
├─ Writes → Vertical scaling first
│ └─ Hit limits? → Horizontal sharding
│
└─ Data size → Sharding or partitioning
Requirements: Transactions, inventory, complex queries
Primary: PostgreSQL
Secondary: Redis
Example architecture:
PostgreSQL: Orders, products, users, inventory
Redis: Sessions, cart cache, product cache
S3/CloudFlare: Product images, static assets
Requirements: High write throughput, time-series data
Primary: ClickHouse or TimescaleDB (PostgreSQL extension)
Secondary: Redis
Alternative: MongoDB (with time-series collections)
Requirements: Flexible schema, high scale, denormalized data
Primary: MongoDB
Secondary: Redis
Tertiary: PostgreSQL
Requirements: Flexible content types, full-text search
Primary: PostgreSQL
Alternative: MongoDB + Elasticsearch
Requirements: Offline-first, sync, embedded database
Primary: SQLite (local)
Backend: PostgreSQL or MongoDB
Sync: Conflict resolution (last-write-wins or CRDTs)
Requirements: Ultra-low latency, strong consistency
Primary: In-memory database (Redis or custom)
Backup: PostgreSQL or TimescaleDB
Start: What's your primary requirement?
│
├─ Strong consistency + ACID?
│ ├─ Yes → SQL (PostgreSQL or MySQL)
│ │ └─ Complex queries? → PostgreSQL
│ │ └─ Read-heavy? → MySQL
│ └─ No → Continue
│
├─ Flexible schema?
│ ├─ Yes → MongoDB
│ └─ No → SQL
│
├─ Key-value access patterns?
│ ├─ Yes + In-memory → Redis
│ ├─ Yes + Persistent → DynamoDB or PostgreSQL
│ └─ No → Continue
│
├─ Time-series data?
│ ├─ Yes → TimescaleDB, ClickHouse, or InfluxDB
│ └─ No → Continue
│
├─ Full-text search?
│ ├─ Primary feature → Elasticsearch
│ ├─ Secondary → PostgreSQL (built-in) or MongoDB
│ └─ No → Continue
│
├─ Graph relationships?
│ ├─ Yes → Neo4j or PostgreSQL (recursive CTEs)
│ └─ No → Continue
│
├─ Embedded/Local?
│ └─ Yes → SQLite
│
└─ Default → PostgreSQL (most versatile)
Polyglot persistence: Using different databases for different parts of the system.
Example architecture:
PostgreSQL: Core business data (users, orders, products)
MongoDB: User-generated content (posts, comments, reviews)
Redis: Caching, sessions, rate limiting
Elasticsearch: Full-text search
S3: File storage (images, videos)
def get_user(user_id):
# Try cache first
cached = redis.get(f"user:{user_id}")
if cached:
return json.loads(cached)
# Cache miss, query database
user = db.query("SELECT * FROM users WHERE id = %s", [user_id])
# Cache result
redis.setex(f"user:{user_id}", 3600, json.dumps(user))
return user
# Write to PostgreSQL
db.execute("INSERT INTO products (name, description) VALUES (%s, %s)", [name, desc])
# Async sync to Elasticsearch
elasticsearch.index(
index="products",
document={"name": name, "description": desc}
)
# Search via Elasticsearch
results = elasticsearch.search(
index="products",
query={"match": {"description": "laptop"}}
)
# Write events to MongoDB (append-only log)
events.insert_one({
"event_type": "order_placed",
"order_id": 123,
"user_id": 42,
"timestamp": datetime.now()
})
# Aggregate to PostgreSQL (current state)
db.execute("UPDATE orders SET status = 'placed' WHERE id = 123")
Difficulty: Low to Medium
Challenges:
Tools: pgloader, AWS DMS
Difficulty: High
Challenges:
Strategy:
Example:
-- Before (PostgreSQL)
SELECT u.name, p.title, p.content
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.id = 42;
// After (MongoDB, embedded)
{
"_id": ObjectId("..."),
"title": "My Post",
"content": "...",
"author": {
"name": "Alice" // Embedded
}
}
Difficulty: Medium
Challenges:
Strategy:
| Database | Type | Best For | Scaling | Consistency |
|---|---|---|---|---|
| PostgreSQL | SQL | General-purpose, complex queries | Vertical + Read replicas | Strong |
| MySQL | SQL | Web apps, read-heavy | Vertical + Read replicas | Strong |
| MongoDB | Document | Flexible schema, hierarchical data | Horizontal (sharding) | Tunable |
| Redis | Key-value | Caching, sessions, real-time | Vertical + Clustering | Eventual |
| DynamoDB | Key-value | Serverless, AWS apps | Horizontal (auto) | Tunable |
| SQLite | SQL | Embedded, local apps | Single-user | Strong |
| Elasticsearch | Search | Full-text search | Horizontal | Eventual |
| Cassandra | Wide-column | High writes, time-series | Horizontal | Tunable |
| ClickHouse | Columnar | Analytics, OLAP | Horizontal | Eventual |
postgres-schema-design.md - Designing PostgreSQL schemasmongodb-document-design.md - Designing MongoDB documentsredis-data-structures.md - Using Redis effectivelydatabase-connection-pooling.md - Optimizing database connectionspostgres-query-optimization.md - Optimizing PostgreSQL queriesLast Updated: 2025-10-18 Format Version: 1.0 (Atomic)
Index of Build Systems Skills
Coordination patterns for distributed dataflow systems including barriers, epochs, and distributed snapshots
Windowing, sessionization, time-series aggregation, and late data handling for streaming systems
Comprehensive guide to GNU Debugger (GDB) for debugging C/C++/Rust programs. Covers breakpoints, stack traces, variable inspection, TUI mode, .gdbinit customization, Python scripting, remote debugging, and core file analysis.
Paxos consensus algorithm including Basic Paxos, Multi-Paxos, roles, phases, and practical implementations
Gossip protocols for disseminating information, failure detection, and eventual consistency in large-scale distributed systems