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: Facing hard single-writer throughput limits on Amazon Aurora MySQL during promotional peaks, PayPay migrated its core financial ledger to TiDB Distributed SQL. By leveraging Multi-Raft consensus across TiKV storage nodes, AUTO_RANDOM primary keys to eliminate hot-region bottlenecks, and Percolator-based distributed transactions, TiDB delivers linear write scaling, zero-downtime online DDLs, and sub-15ms P99 ledger settlement. ...

← 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: In-depth understanding of relational database engines, transaction isolation anomalies, concurrency control mechanisms, and distributed locking. ACID Transactions & Isolation Levels in Core Banking Answer-first: Ensuring ACID database guarantees in high-throughput core banking ledgers requires leveraging PostgreSQL Serializable Snapshot Isolation, row-level pessimistic locking via explicit SELECT FOR UPDATE statements, distributed Redis Redlocks, and deterministic lock ordering protocols to completely eliminate balance race conditions, phantom reads, and deadlocks during concurrent inter-bank financial fund transfers. ...

Previous Chapter: Chapter 3 — Traffic Shield & Peak Shaving | Series Hub | Next Chapter: Chapter 5 — Full-Stack Observability Answer-first: Shopee eliminated relational database bottlenecks by migrating mission-critical checkout clusters from sharded MySQL to TiDB NewSQL distributed storage. Decoupling stateless SQL compute from Multi-Raft consensus storage across 96MB TiKV regions enables elastic scaling, automated split-merge rebalancing, and Google Percolator distributed transactions, guaranteeing sub-twenty-millisecond p99 write latency and zero data loss across availability zones. ...

← 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. Prerequisite: Basic knowledge of MySQL replication, sharding concepts, and Go database connectivity. ...