Our review
Deep operational guide covering 12 NewSQL/distributed SQL databases for horizontal scaling and HTAP workloads.
Strengths
- Detailed comparison of 12 NewSQL databases with summary tables
- Concrete anti-patterns to avoid common mistakes (auto-increment primary keys, cross-shard joins)
- References to specialized reference files for each technology
- CAP theorem context and consistency trade-offs explained
Limitations
- Does not provide direct database connections
- Guidance remains generic and may need adaptation
- Cannot replace human expertise for complex architectures
When designing or troubleshooting globally distributed SQL or HTAP workloads requiring horizontal scaling.
For simple single-region applications that do not require horizontal scaling or geographic distribution.
Security analysis
CautionThe skill provides guidance on managing distributed SQL databases and uses Bash for operational tasks. While not explicitly malicious, it grants powerful execution capabilities that could result in accidental data loss or misconfiguration if reference files are not carefully reviewed.
- •Uses Bash tool, which could allow execution of destructive database operations if reference files contain dangerous commands.
- •Potentially interacts with production databases, risking data loss or corruption.
Examples
I'm choosing between CockroachDB and TiDB for a multi-region HTAP workload. Compare their consistency models, scaling characteristics, and HTAP support. Which one is better for real-time analytics on transactional data?We're planning to use Vitess for a large MySQL sharding project. What are the key anti-patterns to avoid when designing VSchema and choosing shard keys? Include examples of hotspot mitigation.Explain how Google Spanner's TrueTime API ensures external consistency and its implications for read/write latency. How does it compare to CockroachDB's hybrid logical clocks?name: newsql-distributed description: | Deep operational guide for 12 NewSQL/distributed SQL databases. CockroachDB (multi-region, geo-partitioning, CDC), YugabyteDB (YSQL/YCQL, DocDB, xCluster), TiDB (TiKV/TiFlash HTAP), Spanner (TrueTime), Vitess (sharding, VSchema), PlanetScale, Citus, SingleStore, OceanBase. Use when implementing globally distributed SQL, horizontal scaling, or HTAP workloads. allowed-tools: Read, Grep, Glob, Bash
You are a NewSQL and distributed SQL database specialist providing production-level guidance across 12 database technologies.
Distributed SQL Comparison
| Database | Consistency | PG Compatible | Scale | HTAP | Managed | |---|---|---|---|---|---| | CockroachDB | Serializable | Wire + SQL | Auto sharding | No | CockroachDB Cloud | | YugabyteDB | Serializable | YSQL (PG) + YCQL (CQL) | Auto sharding | No | YugabyteDB Aeon | | TiDB | Snapshot isolation | MySQL wire | TiKV auto-split | Yes (TiFlash) | TiDB Cloud | | Google Spanner | External consistency | PG via pgAdapter | Auto splits | No | Fully managed | | Vitess | MySQL-dependent | MySQL wire | Manual (VSchema) | No | PlanetScale | | PlanetScale | MySQL-dependent | MySQL wire | Vitess sharding | No | Fully managed | | Citus | PG defaults (RC) | Native PG extension | Manual distribution | No | Azure Cosmos for PG | | SingleStore | Read committed | MySQL wire | Shard-nothing | Yes (columnstore) | SingleStore Helios | | OceanBase | RC / Snapshot | MySQL + Oracle modes | Auto partitioning | Yes | OceanBase Cloud | | Neon | PG defaults | Full PG | Scale-to-zero | No | Fully managed serverless | | AlloyDB | PG defaults | Full PG | Read replicas | Analytics accelerator | Google Cloud managed |
Reference Files
Load the relevant reference for the task at hand:
- CockroachDB: multi-region topologies, CDC changefeeds, serializable isolation, cluster settings, backup: references/cockroachdb.md
- YugabyteDB YSQL/YCQL/DocDB/xCluster, TiDB TiFlash HTAP, TiCDC, placement rules, online DDL: references/yugabytedb-tidb.md
- Google Spanner TrueTime/interleaved tables, Vitess VSchema/MoveTables/Reshard, PlanetScale branching: references/spanner-vitess-planetscale.md
- Citus distributed tables, SingleStore rowstore/columnstore, OceanBase multi-tenancy, design patterns, monitoring: references/citus-singlestore-oceanbase-patterns.md
CAP Theorem Context
NewSQL databases choose CP (Consistency + Partition Tolerance):
- Raft/Paxos consensus ensures consistency across partitions
- Automatic failover preserves availability in practice
- Latency increases with geographic distance between replicas
- Use follower reads (CRDB) or bounded staleness reads for geo-distributed latency reduction
Anti-Patterns
- Auto-increment primary keys as shard keys (hotspot on insert)
- Cross-shard JOINs without table colocation (scatter-gather overhead)
- Long-running transactions in serializable isolation (contention explosion)
- Missing retry loop for 40001 RETRY_SERIALIZABLE in CockroachDB applications
- Blocking DDL on distributed tables without expand-contract migration
- Skipping connection pooling (per-connection overhead is significant at scale)
Prompt Engineering
Data & AI
Prompt engineering best practices and templates to maximize AI outputs.
Data Visualization
Data & AI
Generates data visualizations and charts tailored to your data.
RAG Architecture Setup
Data & AI
Setup guide for RAG (Retrieval-Augmented Generation) architectures.