Database

Double-Entry Ledger: Immutable Schema & Concurrency Answer-first: A production-grade double-entry ledger enforces immutable, append-only transaction logs decoupled from balance state updates. By using fixed-size C-aligned memory structs or PostgreSQL check constraints and triggers, the schema guarantees strict debit-credit mathematical invariants, prevents hot-row lock contention, and eliminates double-spend risks in high-concurrency core banking architectures. Executive Summary & Quick Answer: Ultra-high-throughput ledger systems require specialized schema layouts like TigerBeetle’s 128-byte fixed structures or PostgreSQL partition tables decoupling balance accumulation from transaction insertion. Isolating transaction logging from balance state eliminates hot-row lock contention, enabling 10,000+ TPS. ...

Distributed SQL ACID Latency: TiDB, CockroachDB & Spanner Answer-first: Distributed SQL engines preserve multi-region ACID serializability by combining Raft/Paxos consensus with bounded clock synchronization protocols such as Spanner TrueTime, CockroachDB HLC, or TiDB Percolator TSO. Selecting optimal commit-wait delays and timestamp allocation strategies minimizes two-phase commit overhead, achieving low transaction latencies across cross-region core banking nodes. Executive Summary & Quick Answer: Distributed SQL engines (TiDB Percolator, CockroachDB HLC, Google Spanner TrueTime) balance multi-region ACID compliance with low-latency commitments. Understanding clock drift bounds (TrueTime 2-7ms wait) vs TSO central allocator overhead is critical for tuning financial transaction latency under cross-region replication. ...

Answer-First: Enforcing ACID isolation levels in core banking prevents lost updates and dirty reads during high-concurrency transfers. Using PostgreSQL REPEATABLE READ or pessimistic row locking (SELECT FOR UPDATE) combined with Go connection pooling guarantees transactional integrity. Spanner and CockroachDB provide linearizable distributed ACID transactions across microservices using Paxos consensus and Hybrid Logical Clocks. Prerequisite: Part 2: CASA & Lending Domain Logic on transaction parameters. The Core Problem: Concurrency Answer-First: High-concurrency banking transfers risking race conditions and lost updates require strict database lock isolation to protect ledger state. ...

Executive Summary & Quick Answer: PayPay migrated its database layer from AWS Aurora MySQL to TiDB Distributed SQL to overcome vertical scaling limitations. TiDB’s Raft-based auto-sharding and horizontal compute/storage separation deliver linear scaling under billion-row transaction tables. Answer-First: PayPay utilizes TiDB as its distributed SQL database to achieve horizontal scaling without manual database sharding. TiDB maintains standard MySQL protocol compatibility and strict ACID guarantees while dynamically splitting tables into regions that are distributed across a multi-node cluster. ...

The EAV schema is why most Magento migrations fail. It looks manageable from the outside: products stored across catalog_product_entity, catalog_product_entity_varchar, catalog_product_entity_int, catalog_product_entity_decimal, catalog_product_entity_datetime, and catalog_product_entity_text. Six tables, straightforward ETL job, done in a weekend. Then you discover that attribute_id = 75 means “product name” in your Magento instance and “color” in your staging instance. Every attribute ID is generated at install time and differs between environments. Any ETL script that hardcodes attribute IDs will produce corrupted data in production. ...

Answer-First: Spatial indexing serves as a high-performance pre-filtering layer that prevents heavy routing engines from collapsing under load. By using Uber H3 hexagonal cells and Redis GEO to narrow down 10,000 active drivers to the 50 closest candidates in RAM (<2ms), systems reduce routing engine CPU overhead by up to 95%. Prerequisite: Before reading this part, review Part 2: Zero to Hero Environment Setup. Part 3: Spatial Indexing — Uber H3, PostGIS & Redis GEO Executive Summary & Quick Answer: Spatial indexing serves as a high-performance pre-filtering layer that prevents heavy routing engines from collapsing under load. By using Uber H3 hexagonal cells and Redis GEO to narrow down 10,000 active drivers to the 50 closest candidates in RAM (<2ms), systems reduce routing engine CPU overhead by up to 95%. ...

MySQL Scalability Guide: Read Replicas, Sharding, and Distributed SQL Answer-First: Scaling MySQL requires matching the solution to the bottleneck: tune InnoDB buffer pool (70–80% RAM) and ProxySQL pooling for initial gains; add async read replicas for read-heavy workloads; apply Vitess or GORM application-level sharding for write-heavy data (>1TB); or migrate to distributed NewSQL (TiDB) when cross-shard queries and manual re-sharding become unsustainable. Tuning InnoDB buffer pool size for high read/write ratio workloads. Why standard read replication fails to solve write bottlenecks and when toshard. 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. ...

Vitess vs GORM Sharding: MySQL Write Scaling in Go Answer-First: Scaling MySQL write capacity in Go requires choosing between Vitess middleware proxy routing (VTGate) for transparent, zero-downtime cluster-wide resharding, or GORM application-level sharding for low-overhead Go-native table partitioning that requires explicit sharding keys in every query. Designing database sharding keys that prevent cross-shard joins. Configuring proxy routing layers like Vitess to scale MySQL queries horizontally. When your application reaches millions of users, a single database instance will inevitably become the biggest bottleneck in your entire architecture. To solve this, MySQL database scaling becomes mandatory. You must Scale DB for Microservices using Horizontal Scaling techniques. ...

Replace MySQL Sharding with TiDB: Distributed SQL Architecture Answer-First: Replacing manual MySQL database sharding with TiDB eliminates application-layer query routing and cross-shard JOIN limitations by using an auto-partitioning distributed SQL engine with Raft consensus storage (TiKV), stateless compute nodes, and native Percolator distributed ACID transactions. Migrating schemas to TiDB with zero downtime using DM-portal. How TiKV nodes scale independently of TiDB SQL computation nodes. 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. ...

Answer-First: Shopee scales its relational database layer past single-node MySQL limits by migrating to TiDB Distributed SQL. By separating stateless SQL compute (TiDB) from stateful key-value storage (TiKV) and columnar analytics (TiFlash), TiDB delivers transparent horizontal auto-sharding and ACID transactions without application-level sharding logic. Chapter 4: Database Scale - The Rise of TiDB and NewSQL ← Series hub | ← Prev | Next → Prerequisite: Read the previous article: Chapter 3: Traffic Shield - Peak Shaving with Kafka and Graceful Degradation. ...

← Series hub ← Prev • Next → Executive Summary & Quick Answer: 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. Prerequisite: Phase 4: Technology Overview This document is a deep-dive companion to Phase 4. It focuses on the internal mechanics that define the hard limits of peak performance systems: RPC protocol layouts, consensus log replication pipelines, storage engine compaction configurations, and distributed transactions. ...