Agent skill
data-systems-architecture
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.
Install this agent skill to your Project
npx add-skill https://github.com/ratacat/claude-skills/tree/main/skills/data-systems-architecture
SKILL.md
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)
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
ratacat/claude-skills
brave-search
Use when user asks to search the web, look something up online, find current/recent/latest information, or needs cited answers. Triggers on "search", "look up", "find out about", "what is the current/latest", image searches, news lookups. NOT for searching code/files—only for web/internet searches.
ratacat/claude-skills
bug-reproduction-validator
Use this agent when you receive a bug report or issue description and need to verify whether the reported behavior is actually a bug. This agent will attempt to reproduce the issue systematically, validate the steps to reproduce, and confirm whether the behavior deviates from expected functionality. <example>\nContext: The user has reported a potential bug in the application.\nuser: "Users are reporting that the email processing fails when there are special characters in the subject line"\nassistant: "I'll use the bug-reproduction-validator agent to verify if this is an actual bug by attempting to reproduce it"\n<commentary>\nSince there's a bug report about email processing with special characters, use the bug-reproduction-validator agent to systematically reproduce and validate the issue.\n</commentary>\n</example>\n<example>\nContext: An issue has been raised about unexpected behavior.\nuser: "There's a report that the brief summary isn't including all emails from today"\nassistant: "Let me launch the b...
ratacat/claude-skills
agent-native-audit
Run comprehensive agent-native architecture review with scored principles
ratacat/claude-skills
brainstorming
This skill should be used before implementing features, building components, or making changes. It guides exploring user intent, approaches, and design decisions before planning. Triggers on "let's brainstorm", "help me think through", "what should we build", "explore approaches", ambiguous feature requests, or when the user's request has multiple valid interpretations that need clarification.
ratacat/claude-skills
performance-oracle
Use this agent when you need to analyze code for performance issues, optimize algorithms, identify bottlenecks, or ensure scalability. This includes reviewing database queries, memory usage, caching strategies, and overall system performance. The agent should be invoked after implementing features or when performance concerns arise.\n\n<example>\nContext: The user has just implemented a new feature that processes user data.\nuser: "I've implemented the user analytics feature. Can you check if it will scale?"\nassistant: "I'll use the performance-oracle agent to analyze the scalability and performance characteristics of your implementation."\n<commentary>\nSince the user is concerned about scalability, use the Task tool to launch the performance-oracle agent to analyze the code for performance issues.\n</commentary>\n</example>\n\n<example>\nContext: The user is experiencing slow API responses.\nuser: "The API endpoint for fetching reports is taking over 2 seconds to respond"\nassistant: "Let me invoke the...
ratacat/claude-skills
triage
Triage and categorize findings for the CLI todo system
Didn't find tool you were looking for?