database

Database Skills

Overview

Comprehensive database knowledge for designing, querying, optimizing, and managing data storage systems effectively across relational and non-relational paradigms.

Database Landscape

┌─────────────────────────────────────────────────────────┐
│                    Relational (SQL)                      │
│  PostgreSQL │ MySQL │ SQL Server │ Oracle │ SQLite      │
├─────────────────────────────────────────────────────────┤
│                    Document (NoSQL)                      │
│  MongoDB │ CouchDB │ Firestore │ RethinkDB              │
├─────────────────────────────────────────────────────────┤
│                    Key-Value                             │
│  Redis │ DynamoDB │ Memcached │ etcd │ Riak            │
├─────────────────────────────────────────────────────────┤
│                    Wide-Column                           │
│  Cassandra │ HBase │ ScyllaDB │ BigTable               │
├─────────────────────────────────────────────────────────┤
│                    Graph                                 │
│  Neo4j │ Amazon Neptune │ ArangoDB │ JanusGraph        │
├─────────────────────────────────────────────────────────┤
│                    Time-Series                           │
│  InfluxDB │ TimescaleDB │ Prometheus │ QuestDB         │
├─────────────────────────────────────────────────────────┤
│                    Search                                │
│  Elasticsearch │ OpenSearch │ Meilisearch │ Typesense  │
└─────────────────────────────────────────────────────────┘

Skill Categories

Fundamentals

Core database concepts every developer should know:

• Database types and their trade-offs
• ACID properties and transactions
• CAP theorem implications
• Normalization forms (1NF through BCNF)

SQL

Structured Query Language mastery:

• SQL fundamentals (CRUD operations)
• Advanced query techniques
• Joins explained with diagrams
• Window functions for analytics
• CTEs and subqueries

PostgreSQL

Deep dive into the world's most advanced open-source database:

• PostgreSQL-specific features
• Index types (B-tree, GIN, GiST, BRIN)
• JSON/JSONB operations
• Full-text search capabilities

NoSQL

Non-relational database patterns:

• MongoDB document modeling
• Redis data structures and patterns
• DynamoDB single-table design
• When to use NoSQL vs SQL

Design

Data modeling and schema design:

• Schema design principles
• Entity-relationship modeling
• Relationship types and implementation
• Strategic denormalization

Migrations

Safe database evolution:

• Migration strategies
• Zero-downtime migrations
• Data migration patterns

Optimization

Performance tuning techniques:

• Query optimization
• Indexing strategies
• EXPLAIN ANALYZE interpretation
• Connection pooling

Operations

Database administration:

• Backup and recovery
• Replication strategies
• Sharding patterns
• Monitoring and alerting

Decision Matrix: Choosing the Right Database

Use Case	Recommended	Alternative	Rationale
General purpose	PostgreSQL	MySQL	Versatile, ACID, JSON support
Simple web app	MySQL	SQLite	Wide hosting support
High-speed caching	Redis	Memcached	Data structures, persistence
Flexible documents	MongoDB	CouchDB	Schema-less, horizontal scale
Analytics/OLAP	ClickHouse	BigQuery	Column-oriented, fast aggregations
Complex relationships	Neo4j	ArangoDB	Native graph queries
Time-series data	TimescaleDB	InfluxDB	Time-based partitioning
Full-text search	Elasticsearch	Meilisearch	Inverted index, relevance
Global distribution	CockroachDB	Spanner	Geo-partitioning
Embedded/Edge	SQLite	DuckDB	Zero configuration

Database Selection Flowchart

Start: What's your primary need?
│
├─> Structured data with relationships?
│   ├─> Complex queries needed? → PostgreSQL
│   ├─> Simple CRUD, wide hosting? → MySQL
│   └─> Embedded/serverless? → SQLite
│
├─> Flexible schema/documents?
│   ├─> Horizontal scaling? → MongoDB
│   └─> Real-time sync? → Firestore
│
├─> High-speed caching?
│   ├─> Data structures needed? → Redis
│   └─> Simple key-value? → Memcached
│
├─> Analytics/reporting?
│   ├─> Real-time analytics? → ClickHouse
│   └─> Time-series data? → TimescaleDB
│
├─> Graph relationships?
│   └─> → Neo4j or Amazon Neptune
│
└─> Full-text search?
    └─> → Elasticsearch or Meilisearch

Quick Reference

ACID Properties

• Atomicity: All or nothing transactions
• Consistency: Valid state transitions only
• Isolation: Concurrent transaction separation
• Durability: Committed data persists

CAP Theorem

Pick two of three (in partition scenario):

• Consistency: Every read gets latest write
• Availability: Every request gets response
• Partition tolerance: System works despite network splits

Normal Forms Quick Guide

• 1NF: Atomic values, no repeating groups
• 2NF: 1NF + no partial dependencies
• 3NF: 2NF + no transitive dependencies
• BCNF: 3NF + every determinant is a candidate key

Common Patterns

Read-Heavy Workloads

┌─────────────┐     ┌─────────────┐
│   Primary   │────▶│   Replica   │◀── Reads
│  (Writes)   │     │   (Reads)   │
└─────────────┘     └─────────────┘
                    ┌─────────────┐
               ────▶│   Replica   │◀── Reads
                    │   (Reads)   │
                    └─────────────┘

Caching Strategy

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│    App      │────▶│   Cache     │────▶│  Database   │
│             │     │   (Redis)   │     │   (PGSQL)   │
└─────────────┘     └─────────────┘     └─────────────┘
       │                   │
       └───────────────────┘
         Cache miss: query DB, populate cache

Event Sourcing

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Events    │────▶│   Event     │────▶│  Projected  │
│   (append)  │     │   Store     │     │   Views     │
└─────────────┘     └─────────────┘     └─────────────┘

Files in This Skill

database/
├── _index.md                    # This file
├── fundamentals/
│   ├── database-types.md        # Database paradigms comparison
│   ├── acid-properties.md       # Transaction guarantees
│   ├── cap-theorem.md           # Distributed system trade-offs
│   └── normalization.md         # Data normalization forms
├── sql/
│   ├── sql-fundamentals.md      # Basic SQL operations
│   ├── advanced-queries.md      # Complex query patterns
│   ├── joins-explained.md       # Join types with diagrams
│   ├── window-functions.md      # Analytics functions
│   └── cte-subqueries.md        # CTEs and subqueries
├── postgresql/
│   ├── postgres-features.md     # PostgreSQL capabilities
│   ├── indexes.md               # Index types and usage
│   ├── json-operations.md       # JSON/JSONB handling
│   └── full-text-search.md      # FTS configuration
├── nosql/
│   ├── mongodb-basics.md        # MongoDB fundamentals
│   ├── redis-patterns.md        # Redis data patterns
│   ├── dynamodb-modeling.md     # DynamoDB design
│   └── when-to-use.md           # NoSQL vs SQL decision
├── design/
│   ├── schema-design.md         # Schema principles
│   ├── data-modeling.md         # ER modeling
│   ├── relationships.md         # Relationship types
│   └── denormalization.md       # Strategic denorm
├── migrations/
│   ├── migration-strategies.md  # Migration approaches
│   ├── zero-downtime.md         # Online migrations
│   └── data-migration.md        # Data movement
├── optimization/
│   ├── query-optimization.md    # Query tuning
│   ├── indexing-strategies.md   # Index design
│   ├── explain-analyze.md       # Query plans
│   └── connection-pooling.md    # Pool management
└── operations/
    ├── backup-recovery.md       # Backup strategies
    ├── replication.md           # Replication setup
    ├── sharding.md              # Horizontal scaling
    └── monitoring.md            # DB observability

Related Skills

• Backend Development: Database integration patterns
• API Design: Data access layer design
• Security: Database security, encryption
• DevOps: Database deployment, automation