Claude Code Plugins

Community-maintained marketplace

Feedback

data-systems-architecture

@ratacat/claude-skills
8
0

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 Skill

1Download skill
2Enable skills in Claude

Open claude.ai/settings/capabilities and find the "Skills" section

3Upload to Claude

Click "Upload skill" and select the downloaded ZIP file

Note: Please verify skill by going through its instructions before using it.

SKILL.md

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)