Database

Series Navigation: This is Part 1 of the Core Banking Systems Architecture Masterclass. Master Curriculum Hub | Next: Part 2 — Distributed SQL ACID Latency → | Pillar Hub: Banking Microservices Architecture Double-Entry Ledger: Immutable Schema & Concurrency Answer-first: A production-grade financial ledger decouples historical transaction journaling from balance derivation by enforcing an append-only immutable architecture. By enforcing the mathematical identity $\sum \text{Debits} \equiv \sum \text{Credits}$ at the schema level, minor integer units, and ring-buffer batching, core banking engines eliminate balance drift, floating-point rounding errors, and catastrophic row contention under 150,000+ TPS transaction throughput. ...

Series Navigation: This is Part 2 of the Core Banking Systems Architecture Masterclass. ← Previous: Part 1 — Double-Entry Ledger Schema | Master Curriculum Hub | Next: Part 3 — Event Sourcing & CQRS → | Pillar Hub: Go Microservices Guide Distributed SQL ACID Latency: TiDB, CockroachDB & Spanner Answer-first: Distributed SQL platforms achieve horizontal write scalability and multi-region fault tolerance by pairing Multi-Raft consensus with bounded distributed clock synchronization. However, speed-of-light propagation across geographic regions imposes unavoidable 15ms to 45ms round-trip consensus latencies. Core banking architectures mitigate these penalties through locality-aware range leasing, pipelined Percolator two-phase commits, and stale follower reads for high-throughput balance inquiries. ...

Answer-first: Production agentic memory systems solve context window saturation and retrieval dilution by deploying a three-tiered hierarchical architecture: L1 short-term working scratchpads in Redis, L2 semantic episodic vector stores in Qdrant with mathematical exponential time decay, and L3 temporal knowledge graphs in Neo4j, enabling autonomous agents to sustain coherent reasoning across long-horizon enterprise workflows while bounding token consumption. Prerequisite: Solid understanding of dense vector embeddings, cosine distance metrics, graph database traversal primitives (Cypher), and caching eviction algorithms (LRU, LFU, TTL) is recommended. ...

← Previous Chapter: Part 2 — Golang vs. PHP/Laravel | Series hub | Next Chapter: Part 4 — MariaDB vs. MySQL → Answer-first: For distributed write-heavy architectures (≥10,000 writes/s) on MySQL/InnoDB, Snowflake ID (64-bit) is optimal, eliminating the 50% secondary index multiplier tax while preserving B-tree locality. For PostgreSQL, client-generated keys, or coordinate-free distributed topologies, UUIDv7 (RFC 9562) delivers 98% sequential page packing without dedicated coordinator nodes, overcoming random UUIDv4 page thrashing and IOPS cliff failures. ...

← Previous Chapter: Part 2: L4/L7 Load Balancing & API Gateways | Series Hub: System Design Masterclass | Next Chapter: Part 4: Database Scaling, Sharding & Distributed SQL → Prerequisite: Read Part 2: L4/L7 Load Balancing, API Gateways & eBPF Routing to understand edge ingress distribution before designing the cache hierarchy. Answer-first: Production caching in Go couples in-memory L1 caches with distributed Redis or Valkey clusters to shield relational databases. Employing the XFetch probabilistic early expiration algorithm alongside Go Singleflight deduplication completely eliminates thundering herd stampedes, while scalable Bloom filters prevent cache penetration, maintaining sub-millisecond P99 response times under 200,000 requests per second. ...

Previous Chapter: Part 2 — Event-Driven Architecture & Kafka at Scale | Series Hub | Next Chapter: Part 4 — SRE Practices & Chaos Engineering Answer-First: Operating a national mobile payment network generating billions of financial records pushed traditional Amazon Aurora MySQL past its physical write thresholds due to single-master bottlenecks, cross-replica replication lag, and connection exhaustion during marketing surges. PayPay executed a landmark zero-downtime migration to TiDB and TiKV, a cloud-native NewSQL distributed database. By decoupling stateless SQL compute from Multi-Raft storage engines across 96MB continuous Regions, TiDB provides horizontal write scalability, strictly linearizable ACID consistency, and real-time HTAP analytics through TiFlash without impacting high-frequency payment ledgers. ...

← Previous Chapter: Part 3 — Primary Key Showdown: UUIDv7 vs. Snowflake | Series Hub | Next Chapter: Part 5 — Sharded MySQL vs. TiDB NewSQL → Part 4: MariaDB vs. MySQL: Storage Engines & Thread Pool Showdown Answer-first: MariaDB is no longer a drop-in replacement for MySQL. MySQL 8.4/9.0 dominates Cloud-Native ecosystems (AWS Aurora) with InnoDB tuning, binary JSONB O(1) updates, and Vector AI. Conversely, MariaDB 11.x excels on Bare-Metal/Kubernetes via native ThreadPool (50k+ conns), Galera 4 zero-lag multi-master, and MyRocks LSM storage compressing disk by 70%. ...

← Previous Chapter: Part 3: Caching Strategies & Redis/Valkey | Series Hub: System Design Masterclass | Next Chapter: Part 5: Asynchronous Messaging, Kafka KRaft & Event-Driven Systems → Prerequisite: Read Part 3: Caching Strategies, Redis/Valkey & Stampede Prevention to understand how memory caching shields databases before scaling storage horizontally. Answer-first: Scaling relational databases beyond vertical hardware limits requires horizontal sharding by consistent tenant keys, managing read-replica replication lag with GTID session tracking, and migrating toward Multi-Raft distributed SQL engines. Deploying Vitess VTGate or CockroachDB eliminates the single-node storage bottleneck while preserving ACID guarantees and sub-20ms P99 commit latencies across distributed clusters. ...

← Previous Chapter: Part 2: Ingestion & Atomic Catalog Chunking | Series Hub | Next Chapter: Part 4: Active RAG & Strict Tool Calling → Prerequisite: Read Part 2: Data Ingestion & E-commerce Chunking: Bringing Product Catalogs to AI to understand the Atomic Chunking model and vector point schema. Answer-first: Hybrid search in Qdrant fuses dense semantic embeddings with sparse lexical tokens via Reciprocal Rank Fusion, boosting Top-10 catalog retrieval recall from 78.2% to 96.8%. Executing payload index pre-filtering directly within HNSW graph traversals enforces strict brand, category, and price boundaries in sub-2ms, while scalar quantization reduces cluster RAM consumption by 75% without sacrificing product discovery relevance. ...

Prerequisite: Read Part 1: Double-Entry Bookkeeping and Part 2: CIF, CASA & Lending Domain Modeling. ACID Transactions & Isolation Levels in Core Banking Answer-first: Enforcing ACID transactions in core banking guarantees that concurrent balance transfers execute without lost updates, dirty reads, or phantom balance anomalies. By implementing deterministic row-level locking (SELECT ... FOR UPDATE ordered by account ID) under PostgreSQL READ COMMITTED or REPEATABLE READ isolation, banking engines prevent concurrency deadlocks, eliminate double-spending race conditions, and sustain sub-40ms P99 database write latencies under peak transactional loads. ...

Previous Chapter: Chapter 3 — Traffic Shield & Peak Shaving | Series Hub | Next Chapter: Chapter 5 — Full-Stack Observability Answer-First: Traditional MySQL sharding collapses under hyper-scale e-commerce growth due to manual resharding overhead, cross-shard joins, and high 2-Phase Commit (2PC) latency penalties. Shopee transitioned its massive order and inventory backbones to TiDB and TiKV, a cloud-native NewSQL distributed database. By decoupling stateless SQL compute (TiDB) from distributed transactional storage (TiKV) coordinated via Placement Driver (PD) and Multi-Raft consensus across 96MB continuous key Regions, TiDB delivers horizontal elastic scalability, zero-downtime auto-rebalancing, and real-time HTAP analytics without impacting write-heavy OLTP workloads. ...

← Previous Chapter: Part 4 — MariaDB vs. MySQL | Series Hub | Next Chapter: Part 6 — Apache Kafka vs. NATS JetStream → Part 5: Sharded MySQL (Vitess) vs. TiDB NewSQL: Distributed ACID, Scale-Out Limits & Latency Penalties Answer-first: Sharded MySQL (Vitess) delivers unmatched sub-2ms write latency and isolated failure blast radius for clean single-shard workloads (tenant_id/user_id). Conversely, TiDB NewSQL is the definitive architecture for unpartitionable relational schemas and cross-shard queries via zero-touch 96MB Region auto-splits, trading off an 8–15ms write latency floor due to Google Percolator 2PC and Raft consensus hops. ...

🏛️ Anchor Pillar Hub #8: Alipay Double 11 Architecture (544K TPS) | 🗺️ Sitewide Engineering Reading Map ← Series hub ← Prev • Next → Answer-first: Alipay’s Double 11 technology deep dive reveals high-performance internals: binary Bolt RPC protocol multiplexing over single TCP streams, RocketMQ 2PC transactional messaging for async decoupling, OceanBase LSM-tree compaction tuning, and multi-zone Paxos quorum consensus to achieve 544,000 TPS payment processing. Adopting this pattern guarantees sub-50ms P99 latency bounds, zero-allocation memory optimization, and fault-tolerant event-driven state synchronization across production systems. ...

MySQL Scalability & Sharding: Vitess vs TiDB (10k+ TPS) Answer-first: Scaling MySQL requires a phased architectural progression: optimizing InnoDB buffer pools (100–500 TPS), implementing ProxySQL read/write splitting (500–3,000 TPS), and migrating to horizontal sharding or TiDB Distributed SQL (3,000–10,000+ TPS). TiDB serves as the premier MySQL sharding alternative, eliminating manual application-level partitioning through stateless SQL compute nodes and Raft-replicated distributed TiKV storage. MySQL scalability is the ability to increase database throughput — reads per second, writes per second, or data volume — without rewriting your application. The critical distinction: read scaling (adding replicas) and write scaling (sharding or distributed SQL) require completely different architectural approaches. Choosing the wrong path creates technical debt that takes months to unwind. ...

Answer-first: Scaling MySQL writes beyond the 12,000 TPS single-primary InnoDB fsync ceiling mandates choosing between middleware clustering (Vitess) or application-layer routing (GORM Sharding). Vitess provides transparent SQL scatter-gather and zero-downtime VReplication resharding at the cost of operational proxy overhead, whereas GORM Sharding achieves zero-proxy microsecond execution bounds at the expense of rigid schema partitioning. When an engineering organization scales beyond millions of active transactions, a monolithic relational database instance inevitably becomes the single biggest systemic bottleneck in the entire software architecture. While read traffic can be scaled horizontally almost indefinitely by attaching read replicas behind a load-balancing proxy like ProxySQL, write traffic hits an unyielding physical ceiling on a single MySQL Primary instance. ...

MySQL Sharding Alternatives: Vitess vs TiDB Guide Answer-first: TiDB is the leading open-source MySQL sharding alternative, replacing fragile application-level sharding logic (Vitess, GORM Sharding) with an auto-partitioned Distributed SQL architecture. By distributing 96MB Raft Regions across TiKV storage nodes and utilizing the Percolator distributed transaction protocol, TiDB delivers horizontal write scaling, cross-node ACID transactions, and zero-downtime online DDL while maintaining 100% MySQL wire compatibility. Scaling a relational database is one of the most demanding challenges in system design. As applications grow from thousands to millions of active users, the database ceases to be a simple storage engine and becomes the primary bottleneck of the entire system architecture. In this technical guide, we explore the architectural progression of scaling MySQL—beginning with replication topologies, stepping through the complexities and operational hazards of manual database sharding (including proxy middleware like Vitess), and evaluating NewSQL alternatives, specifically the distributed architecture of TiDB. ...