ratacat/data-systems-architecture

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)