tomevault-io Database Design
Learn to THINK, not copy SQL patterns.
🎯 Selective Reading Rule
Read ONLY files relevant to the request! Check the content map, find what you need.
| File | Description | When to Read |
|---|---|---|
database-selection.md |
PostgreSQL vs Neon vs Turso vs SQLite | Choosing database |
orm-selection.md |
Drizzle vs Prisma vs Kysely | Choosing ORM |
schema-design.md |
Normalization, PKs, relationships | Designing schema |
indexing.md |
Index types, composite indexes | Performance tuning |
optimization.md |
N+1, EXPLAIN ANALYZE | Query optimization |
migrations.md |
Safe migrations, serverless DBs | Schema changes |
⚡ Indexing Strategy (Cheatsheet)
Default to B-Tree. Know when to switch.
| Index Type | Use Case | Example |
|---|---|---|
| B-Tree | Equality (=), Range (<, >), Sorting (ORDER BY) |
WHERE age > 21 |
| Hash | Exact equality ONLY (Faster than B-Tree, but limited) | WHERE uuid = '...' |
| GIN | JSONB, Full Text Search, Arrays | WHERE data @> '{"tag": "urgent"}' |
| GiST | Geo-spatial, Nearest Neighbor | WHERE location <@ box |
Composite Index Rule: Order matters! (last_name, first_name) helps WHERE last_name='Bond', but DOES NOT help WHERE first_name='James'. (Leftmost Prefix Rule)
⚖️ Scaling: Partitioning vs Sharding
| Strategy | What is it? | Complexity | When to use? |
|---|---|---|---|
| Partitioning | Splitting one table into chunks on the SAME server. | Medium | Table > 100GB. Need to delete old data fast (DROP PARTITION). |
| Sharding | Splitting data across DIFFERENT servers. | Extreme | Write QPS > Single Node limit. Massive scale (Petabytes). |
🔌 Connection Pooling (Serverless)
Serverless Apps + Postgres = Disaster. Lambda scales to 1,000 instances -> 1,000 DB connections -> DB Crashes.
Solution: Use PgBouncer (or AWS RDS Proxy / Supabase Pooler).
- Function connects to Proxy (TCP).
- Proxy holds small pool of persistent connections to DB.
- Transactional Mode: Connection matches to DB only for duration of transaction. Best for serverless.
⚠️ Core Principle
- ASK user for database preferences when unclear
- Choose database/ORM based on CONTEXT
- Don't default to PostgreSQL for everything
Decision Checklist
Before designing schema:
- Asked user about database preference?
- Chosen database for THIS context?
- Considered deployment environment?
- Planned index strategy?
- Defined relationship types?
Anti-Patterns
❌ Default to PostgreSQL for simple apps (SQLite may suffice) ❌ Skip indexing ❌ Use SELECT * in production ❌ Store JSON when structured data is better ❌ Ignore N+1 queries
Converted and distributed by TomeVault — claim your Tome and manage your conversions.