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:
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)
1---2name: data-systems-architecture3description: 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.4---56# Data Systems Architecture78## Overview910**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.1112This skill synthesizes knowledge from three foundational texts:13- *Designing Data-Intensive Applications* (Kleppmann) - distributed systems, storage engines, scaling14- *The Art of PostgreSQL* (Fontaine) - PostgreSQL-specific patterns, SQL as programming15- *PostgreSQL Query Optimization* (Dombrovskaya et al.) - execution plans, performance tuning1617## When to Use1819| Symptom | Start With |20|---------|------------|21| Designing a new database/schema | `01-foundational-principles.md` |22| Normalization vs denormalization decisions | `02-data-modeling.md` |23| Need to understand OLTP vs OLAP | `03-storage-engines.md` |24| Slow queries, index selection | `04-indexing.md` |25| Planning for growth, read replicas | `05-scaling-patterns.md` |26| Race conditions, deadlocks, isolation issues | `06-transactions-concurrency.md` |27| N+1 queries, ORM problems, application integration | `07-application-integration.md` |2829## Navigation3031### Reference Files (Load as needed)3233```3401-foundational-principles.md - Reliability/Scalability/Maintainability, load parameters3502-data-modeling.md - Normalization, denormalization, schema design patterns3603-storage-engines.md - B-trees, LSM-trees, OLTP vs OLAP, PostgreSQL internals3704-indexing.md - Index types, compound indexes, covering indexes, maintenance3805-scaling-patterns.md - Replication, partitioning, sharding strategies3906-transactions-concurrency.md - ACID, isolation levels, MVCC, locking patterns4007-application-integration.md - ORM pitfalls, N+1, business logic placement, batch processing41```4243## Quick Decision Framework4445```46New system design?47├─ Yes → Read 01, then 02 for data model48└─ No → What's the problem?49 ├─ "Queries are slow" → Read 04 (indexing) + 03 (storage patterns)50 ├─ "Data is inconsistent" → Read 02 (modeling) + 06 (transactions)51 ├─ "Can't handle the load" → Read 05 (scaling) + 03 (OLTP vs OLAP)52 ├─ "App makes too many queries" → Read 07 (N+1, ORM patterns)53 └─ "Race conditions/deadlocks" → Read 06 (concurrency)54```5556## Core Concepts (Quick Reference)5758### The Three Pillars5960| Concern | Definition | Key Question |61|---------|------------|--------------|62| **Reliability** | System works correctly under faults | What happens when things fail? |63| **Scalability** | Handles growth gracefully | What's 10x load look like? |64| **Maintainability** | Easy to operate and evolve | Can new engineers understand this? |6566### Data Model Selection6768| Model | Best For | Avoid When |69|-------|----------|------------|70| **Relational** | Many-to-many relationships, joins, consistency | Highly hierarchical data, constant schema changes |71| **Document** | Self-contained docs, tree structures | Need for joins, many-to-many |72| **Graph** | Highly connected data, recursive queries | Simple CRUD, no relationship traversal |7374### OLTP vs OLAP7576| Aspect | OLTP | OLAP |77|--------|------|------|78| **Query pattern** | Point lookups, few rows | Aggregates, many rows |79| **Optimization** | Index everything used in WHERE | Fewer indexes, full scans OK |80| **Storage** | Row-oriented | Consider column-oriented |8182### Index Type Quick Reference8384| Type | Use Case | PostgreSQL |85|------|----------|------------|86| **B-tree** | Equality, range, sorting | Default, most queries |87| **Hash** | Equality only | Faster for exact match |88| **GIN** | Arrays, JSONB, full-text | `@>`, `@@` operators |89| **GiST** | Geometric, range types | PostGIS, nearest-neighbor |90| **BRIN** | Large, naturally ordered tables | Time-series data |9192### Isolation Levels9394| Level | Prevents | PostgreSQL Default? |95|-------|----------|-------------------|96| **Read Committed** | Dirty reads | Yes |97| **Repeatable Read** | + Non-repeatable reads | No |98| **Serializable** | All anomalies | No (uses SSI) |99100## Design Checklist101102Before finalizing a data architecture:103104- [ ] Identified load parameters (read/write ratio, data volume, latency requirements)105- [ ] Chose appropriate data model (relational/document/graph hybrid?)106- [ ] Normalized to 3NF first, denormalized only with measured justification107- [ ] Designed indexes for actual query patterns (not hypothetical)108- [ ] Considered 10x growth scenario109- [ ] Established isolation level requirements110- [ ] Defined where business logic lives (app vs DB vs both)111- [ ] Planned for operations (backups, monitoring, migrations)112113## References114115- Kleppmann, M. *Designing Data-Intensive Applications* (O'Reilly, 2017)116- Fontaine, D. *The Art of PostgreSQL* (2nd ed., 2020)117- Dombrovskaya, H., Novikov, B., Bailliekova, A. *PostgreSQL Query Optimization* (Apress, 2021)
levalencia