Menu
Database comparison catalogs, RDBMS vs NoSQL selection criteria, CAP/ACID/BASE theory, OLTP vs OLAP, and technology-specific characteristics
| name | nw-database-technology-selection |
| description | Database comparison catalogs, RDBMS vs NoSQL selection criteria, CAP/ACID/BASE theory, OLTP vs OLAP, and technology-specific characteristics |
| user-invocable | false |
| disable-model-invocation | true |
Start with these questions:
Strengths: Full ACID, advanced cost-based optimizer, rich indexes (B-tree, Hash, GiST, GIN, BRIN), JSONB | Best for: complex queries, mixed OLTP/analytics, geospatial (PostGIS), JSON+relational hybrid | Scaling: read replicas, partitioning, PgBouncer, Citus for horizontal | Watch: write-heavy needs tuning, vertical scaling limits
Strengths: RAC clustering, Data Guard, Flashback, mature optimizer, partitioning | Best for: enterprise OLTP, mission-critical with vendor support, large-scale DW | Scaling: RAC horizontal, partitioning, Active Data Guard read replicas | Watch: licensing cost, vendor lock-in
Strengths: BI integration (SSRS/SSAS/SSIS), Always On AG, TDE built-in, columnstore indexes | Best for: Microsoft ecosystem, BI-heavy, hybrid OLTP/analytics | Scaling: Always On AG for HA, read-scale replicas, partitioning | Watch: Windows-centric, licensing model
Strengths: Simplicity, wide adoption, InnoDB ACID, good read performance, easy replication | Best for: web apps, read-heavy, simple transactional systems | Scaling: primary-replica, Group Replication, MySQL Router | Watch: less sophisticated optimizer than PostgreSQL, limited window functions in older versions
JSON-like documents, flexible schemas | Best for: CMS, catalogs, user profiles, rapid prototyping | Query: MongoDB aggregation pipeline, Couchbase N1QL | Indexing: compound (ESR rule: Equality-Sort-Range), text, geospatial | Trade-offs: flexible schema vs consistency enforcement, $lookup joins expensive
Simple key-value pairs, values can be complex structures | Best for: caching, sessions, leaderboards, shopping carts | Redis: in-memory sub-ms, FT.SEARCH/FT.AGGREGATE | DynamoDB: single-digit ms at any scale, Query on partition+sort key | Trade-offs: Redis limited by RAM, DynamoDB requires careful partition key design
Wide columns grouped into column families, partitioned by partition key | Best for: write-heavy, time-series, IoT, event logging, audit trails | Cassandra CQL: SQL-like, must include partition key, no joins | Linear horizontal scaling, SAI indexing 43% throughput gain over SASI | Trade-offs: query flexibility limited to partition key, query-first schema design, strong consistency causes up to 95% perf degradation
Nodes and edges with properties, index-free adjacency | Best for: social networks, recommendations, fraud detection, knowledge graphs | Neo4j Cypher (pattern matching), ArangoDB AQL (multi-model) | Relationship traversals far more efficient than recursive SQL CTEs | Trade-offs: not suited for aggregation-heavy analytics, scaling more complex
Atomicity: all-or-nothing | Consistency: valid state transitions | Isolation: concurrent transactions don't interfere (levels: READ UNCOMMITTED/COMMITTED, REPEATABLE READ, SERIALIZABLE) | Durability: committed data survives failures | Use when: financial transactions, inventory, order processing, data correctness non-negotiable
Basically Available | Soft state (may change without input) | Eventually consistent | Use when: availability > immediate consistency (social feeds, recommendations, activity streams)
During network partition, choose:
PACELC extension: even without partitions, latency vs consistency trade-off exists.
Many short atomic transactions (INSERT/UPDATE/DELETE) | Normalized 3NF | Simple queries, few rows, ms response | High write concurrency, ACID required | DBs: PostgreSQL, MySQL, Oracle, SQL Server
Complex analytical queries with aggregations | Denormalized star/snowflake | Complex SELECTs with JOINs, GROUP BY, window functions, seconds-minutes response | Read-heavy, fewer concurrent users | DBs: Snowflake, Redshift, BigQuery, Druid, ClickHouse
Combines OLTP+OLAP in single system | Examples: TiDB, CockroachDB, SingleStore, SQL Server with columnstore | Trade-off: convenience vs potential performance compromise for both workloads
Wave methodology knowledge for the buddy agent — what each wave does, its inputs and outputs, and how to route questions.
Bug fix workflow: root cause analysis → user review → regression test + fix via TDD
Orchestrates the full DELIVER wave end-to-end (roadmap > execute-all > finalize). Use when all prior waves are complete and the feature is ready for implementation.
Designs system architecture with C4 diagrams and technology selection. Routes to the right architect based on design scope (system, domain, application, or full stack). Two interaction modes: guide (collaborative Q&A) or propose (architect presents options with trade-offs).
Designs CI/CD pipelines, infrastructure, observability, and deployment strategy. Use when preparing platform readiness for a feature.
Conducts evidence-based product discovery through customer interviews and assumption testing. Use at project start to validate problem-solution fit.
