data-systems-architecture skill

---
name: data-systems-architecture
description: Use when designing databases for data-heavy applications, making schema decisions for performance, choosing between normalization and denormalization, selecting storage/indexing strategies, planning for scale, or evaluating OLTP vs OLAP trade-offs. Also use when encountering N+1 queries, ORM issues, or concurrency problems.
---

# Data Systems Architecture

## Overview

**Core principle:** Good data system architecture balances reliability (correct operation under faults), scalability (handling growth gracefully), and maintainability (enabling productive change over time). Every architectural decision involves trade-offs between these concerns.

This skill synthesizes knowledge from three foundational texts:
- *Designing Data-Intensive Applications* (Kleppmann) - distributed systems, storage engines, scaling
- *The Art of PostgreSQL* (Fontaine) - PostgreSQL-specific patterns, SQL as programming
- *PostgreSQL Query Optimization* (Dombrovskaya et al.) - execution plans, performance tuning

## When to Use

| Symptom | Start With |
|---------|------------|
| Designing a new database/schema | `01-foundational-principles.md` |
| Normalization vs denormalization decisions | `02-data-modeling.md` |
| Need to understand OLTP vs OLAP | `03-storage-engines.md` |
| Slow queries, index selection | `04-indexing.md` |
| Planning for growth, read replicas | `05-scaling-patterns.md` |
| Race conditions, deadlocks, isolation issues | `06-transactions-concurrency.md` |
| N+1 queries, ORM problems, application integration | `07-application-integration.md` |

## Navigation

### Reference Files (Load as needed)

```
01-foundational-principles.md    - Reliability/Scalability/Maintainability, load parameters
02-data-modeling.md              - Normalization, denormalization, schema design patterns
03-storage-engines.md            - B-trees, LSM-trees, OLTP vs OLAP, PostgreSQL internals
04-indexing.md                   - Index types, compound indexes, covering indexes, maintenance
05-scaling-patterns.md           - Replication, partitioning, sharding strategies
06-transactions-concurrency.md   - ACID, isolation levels, MVCC, locking patterns
07-application-integration.md    - ORM pitfalls, N+1, business logic placement, batch processing
```

## Quick Decision Framework

```
New system design?
├─ Yes → Read 01, then 02 for data model
└─ No → What's the problem?
         ├─ "Queries are slow" → Read 04 (indexing) + 03 (storage patterns)
         ├─ "Data is inconsistent" → Read 02 (modeling) + 06 (transactions)
         ├─ "Can't handle the load" → Read 05 (scaling) + 03 (OLTP vs OLAP)
         ├─ "App makes too many queries" → Read 07 (N+1, ORM patterns)
         └─ "Race conditions/deadlocks" → Read 06 (concurrency)
```

## Core Concepts (Quick Reference)

### The Three Pillars

| Concern | Definition | Key Question |
|---------|------------|--------------|
| **Reliability** | System works correctly under faults | What happens when things fail? |
| **Scalability** | Handles growth gracefully | What's 10x load look like? |
| **Maintainability** | Easy to operate and evolve | Can new engineers understand this? |

### Data Model Selection

| Model | Best For | Avoid When |
|-------|----------|------------|
| **Relational** | Many-to-many relationships, joins, consistency | Highly hierarchical data, constant schema changes |
| **Document** | Self-contained docs, tree structures | Need for joins, many-to-many |
| **Graph** | Highly connected data, recursive queries | Simple CRUD, no relationship traversal |

### OLTP vs OLAP

| Aspect | OLTP | OLAP |
|--------|------|------|
| **Query pattern** | Point lookups, few rows | Aggregates, many rows |
| **Optimization** | Index everything used in WHERE | Fewer indexes, full scans OK |
| **Storage** | Row-oriented | Consider column-oriented |

### Index Type Quick Reference

| Type | Use Case | PostgreSQL |
|------|----------|------------|
| **B-tree** | Equality, range, sorting | Default, most queries |
| **Hash** | Equality only | Faster for exact match |
| **GIN** | Arrays, JSONB, full-text | `@>`, `@@` operators |
| **GiST** | Geometric, range types | PostGIS, nearest-neighbor |
| **BRIN** | Large, naturally ordered tables | Time-series data |

### Isolation Levels

| Level | Prevents | PostgreSQL Default? |
|-------|----------|-------------------|
| **Read Committed** | Dirty reads | Yes |
| **Repeatable Read** | + Non-repeatable reads | No |
| **Serializable** | All anomalies | No (uses SSI) |

## Design Checklist

Before finalizing a data architecture:

- [ ] Identified load parameters (read/write ratio, data volume, latency requirements)
- [ ] Chose appropriate data model (relational/document/graph hybrid?)
- [ ] Normalized to 3NF first, denormalized only with measured justification
- [ ] Designed indexes for actual query patterns (not hypothetical)
- [ ] Considered 10x growth scenario
- [ ] Established isolation level requirements
- [ ] Defined where business logic lives (app vs DB vs both)
- [ ] Planned for operations (backups, monitoring, migrations)

## References

- Kleppmann, M. *Designing Data-Intensive Applications* (O'Reilly, 2017)
- Fontaine, D. *The Art of PostgreSQL* (2nd ed., 2020)
- Dombrovskaya, H., Novikov, B., Bailliekova, A. *PostgreSQL Query Optimization* (Apress, 2021)