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

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

Data Model Selection

OLTP vs OLAP

Index Type Quick Reference

Isolation Levels

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)