+-----------------------------------------------------------------------------------------+
|                  Relational Database Sharding Strategy Matrix                           |
+-----------------------------------------------------------------------------------------+

VITESS (The MySQL Cloud-Native Clustering Standard)
- Core Architecture: Proxy Middleware Layer (VTGate) + Tablet Sidecars (VTTablet).
- Routing Logic: VSchema (Virtual Schema) + Vindex (Hash/Lookup routing).
- Best for: Massive-scale MySQL fleets, Kubernetes-native deployments, transparent client routing.

CITUS (The Native PostgreSQL Distributed Extension)
- Core Architecture: Native PostgreSQL Extension (Coordinator Node + Worker Nodes).
- Routing Logic: Declarative Distribution Column (`create_distributed_table`).
- Best for: Multi-tenant B2B SaaS platforms where data naturally scopes by `tenant_id`.

APPLICATION-LEVEL SHARDING (Consistent Hashing / Routing Middleware)
- Core Architecture: Sharding logic implemented directly in Node.js/Go/Java application code.
- Routing Logic: Consistent Hashing Rings (e.g. Ketama / MurmurHash3) over virtual buckets.
- Best for: Simple, siloed datasets avoiding external database proxies.

+-----------------------------------------------------------------------------------------+
|                  Vitess Cloud-Native Sharding Topology                                  |
+-----------------------------------------------------------------------------------------+

[Application / Microservices (Standard MySQL Driver)]
                         |
                         v (Connects via standard port 3306)
+-----------------------------------------------------------------+
| VTGATE PROXY CLUSTER (Stateless Routers):                       |
| 1. Parses incoming SQL query.                                   |
| 2. Consults VSchema to determine sharding key (e.g. 'user_id'). |
| 3. Computes Vindex hash -> Routes directly to target shard!     |
+-----------------------+-----------------------------------------+
                        |
        +---------------+---------------+---------------+
        | (Range -80)                   | (Range 80-c0)                 | (Range c0-)
        v                               v                               v
+---------------+---------------+---------------+---------------+---------------+---------------+
| VTTablet Sidecar               | VTTablet Sidecar               | VTTablet Sidecar               |
| MySQL Shard 1 (Master/Replica)| MySQL Shard 2 (Master/Replica)| MySQL Shard 3 (Master/Replica)|
+-------------------------------+-------------------------------+-------------------------------+

{
  "sharded": true,
  "vindexes": {
    "hash_vindex": {
      "type": "hash"
    }
  },
  "tables": {
    "users": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash_vindex"
        }
      ]
    },
    "orders": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash_vindex"
        }
      ]
    }
  }
}

-- 1. Enable Citus extension on Coordinator Node
CREATE EXTENSION citus;

-- 2. Create Standard PostgreSQL Tables
CREATE TABLE tenants (
    id UUID PRIMARY KEY,
    company_name TEXT NOT NULL,
    plan_tier TEXT NOT NULL
);

CREATE TABLE tenant_invoices (
    id UUID,
    tenant_id UUID NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    invoice_date DATE NOT NULL,
    PRIMARY KEY (tenant_id, id)
);

-- 3. Transform into Distributed Tables Sharded by 'tenant_id' (or 'id')
SELECT create_reference_table('tenants'); -- Replicated across all workers!
SELECT create_distributed_table('tenant_invoices', 'tenant_id');

+-----------------------------------------------------------------------------------------+
|                  Citus Distributed Multi-Tenant Execution                               |
+-----------------------------------------------------------------------------------------+

[Query: SELECT * FROM tenant_invoices WHERE tenant_id = 'org_984' AND amount > 500]
                                         |
                                         v (Coordinator inspects 'tenant_id')
[Citus Coordinator routes query directly to Worker Node 3 containing Shard 102048!]
[Worker Node 3 executes query locally in 1.2ms with standard PostgreSQL B-Trees!]

// sharding/hashRingRouter.ts
import crypto from "crypto";
import { Pool } from "pg";

export class ShardManager {
  private shardPools: Map<number, Pool> = new Map();
  private numBuckets: number = 1024; // 1,024 Virtual Buckets

  constructor(shardConnectionStrings: string[]) {
    shardConnectionStrings.forEach((connStr, index) => {
      this.shardPools.set(index, new Pool({ connectionString: connStr }));
    });
  }

  // 1. Hash Sharding Key (e.g. organizationId) into a Virtual Bucket
  public getShardForTenant(tenantId: string): Pool {
    const hash = crypto.createHash("md5").update(tenantId).digest("hex");
    const bucket = parseInt(hash.substring(0, 8), 16) % this.numBuckets;

    // Map Virtual Bucket to Physical Database Node
    const physicalNodeIndex = bucket % this.shardPools.size;
    return this.shardPools.get(physicalNodeIndex)!;
  }
}

// Usage in API Route:
const db = shardManager.getShardForTenant(user.tenantId);
const result = await db.query("SELECT * FROM invoices WHERE tenant_id = $1", [user.tenantId]);

+-----------------------------------------------------------------------------------------+
|                  Solving Cross-Shard Transactions in 2026                               |
+-----------------------------------------------------------------------------------------+

1. TWO-PHASE COMMIT (2PC - High Latency)
   - Coordinator locks both shards -> Phase 1: Prepare -> Phase 2: Commit.
   - Slower (Takes 40ms to 80ms), but provides strict ACID consistency.

2. ASYNCHRONOUS SAGA PATTERN / TRANSACTIONAL OUTBOX (The Recommended Standard)
   - Shard 1 atomically decrements User A balance & writes event to local 'outbox' table.
   - Background worker (Debezium CDC / Kafka) reads event and credits User B on Shard 4!

       +-------------------------------------------------------------+
       |             Sustained Write Throughput (Transactions / Sec) |
       +-------------------------------------------------------------+
 Single High-End PostgreSQL Node      | ============ [22,000 tps] (CPU Maxed Out!)
 8-Shard Citus Distributed Cluster    | ==================================== [158,000 tps] (7.2x Scale!)
                                      +-------------------------------------+
                                      0      40k     80k     120k    160k