data-systems-architecture_skill

Overview

This skill helps design and evaluate data systems for data-heavy applications, guiding schema choices, indexing, storage, and scaling decisions. It emphasizes trade-offs between reliability, scalability, and maintainability so you can make pragmatic choices for production systems. Use it to diagnose query performance, concurrency issues, and ORM integration problems.

How this skill works

The skill inspects workload characteristics (read/write ratio, query shapes, growth expectations) and maps them to concrete architecture patterns: relational vs document vs graph models, OLTP vs OLAP storage choices, and appropriate index types. It provides prescriptive guidance on normalization vs denormalization, transaction isolation and locking, replication/partitioning strategies, and remedies for common application-layer problems like N+1 queries and ORM inefficiencies.

When to use it

• Designing a new database/schema for a data-heavy app
• Choosing between normalization and denormalization for performance
• Deciding OLTP vs OLAP and suitable storage/indexing engines
• Diagnosing slow queries and selecting or redesigning indexes
• Planning scaling: replication, partitioning, sharding, and 10x growth
• Resolving concurrency issues, deadlocks, or ORM N+1 query problems

Best practices

• Start by measuring: capture query patterns, latencies, and growth projections before changing schema
• Normalize to 3NF first; denormalize selectively with measurable performance gains and clear maintenance plans
• Design indexes for real, high-impact queries and monitor their maintenance costs
• Choose storage and index types based on access patterns (row vs column, B-tree vs GIN/BRIN)
• Define isolation level requirements explicitly and prefer optimistic patterns where possible to reduce contention
• Plan for operations: backups, monitoring, migration paths, and runbooks before launch

Example use cases

• Designing a primary schema for a transactional service with many joins and strict consistency
• Refactoring slow report queries by choosing columnar storage or materialized views for OLAP workloads
• Resolving an N+1 problem by introducing batch loading and targeted joins or query builders in the ORM layer
• Choosing partitioning and replica strategies to handle a 10x increase in write throughput
• Tuning PostgreSQL: selecting B-tree/GIN/BRIN indexes and adjusting vacuum/maintenance for large time-series data