Articles tagged with #Databases
A curated list of engineering series, deep dives, and notes related to #Databases.
Building a Custom Transactional Storage Engine in Java: The Database Capstone
Build a complete, working transactional storage engine in Java with slotted memory pages, WAL crash recovery, LRU buffer pool management, and 2PL locking.
Advanced SQL Performance Tuning: Window Functions, Recursive CTEs, and Partitioning
Master advanced SQL optimization: Window function framing, recursive CTE tree traversals, and table partitioning with partition pruning.
Operating Production Databases: High Availability Replication & Connection Pooling
Learn how to operate production databases: MySQL binary logging formats (RBR/SBR), GTIDs, semi-synchronous replication, and HikariCP connection pooling.
MySQL vs PostgreSQL MVCC: Heap Tuple Versions vs Undo Log Segments
Compare MySQL InnoDB and PostgreSQL MVCC implementations: Undo log version chains vs in-page heap tuples, HOT optimizations, and VACUUM engines.
Multi-Region Distributed Databases: Active-Active vs Active-Passive Cross-Data-Center Replication
Master multi-region distributed databases. Compare Active-Passive and Active-Active cross-datacenter replication models and conflict resolution.
Declarative Transaction Management: How @Transactional Works Under the Hood
Understand Spring's @Transactional internal mechanics: TransactionInterceptor proxying, PlatformTransactionManager, propagation levels, and silent rollback failures.
Hibernate ORM and Spring Data JPA: Entity Management and N+1 Query Traps
Dissect Hibernate ORM and Spring Data JPA internals. Master the Persistence Context, entity states, lazy loading proxies, and solutions for N+1 queries.
Data Access Primitives: From Plain JDBC to Spring JdbcTemplate
Trace data access evolution in Java from raw JDBC boilerplate and connection leaks to Spring JdbcTemplate and HikariCP connection pool tuning.
Composite & Covering Indexes: Maximizing Index-Only Scans and Avoiding Table Lookups
Master multi-column composite indexes in MySQL: leftmost prefix rules, range predicate traps, Index Condition Pushdown (ICP), and covering indexes.
Join Algorithms Under the Hood: Nested Loop, Hash Join & Sort-Merge Join Mechanics
Master relational join algorithms: Simple, Index, and Block Nested Loop Joins, Classic & Grace Hash Joins, and Sort-Merge Joins with big-O complexities.
The Volcano Execution Model & Cost-Based Optimizer: From SQL to EXPLAIN Plans
Explore the internal life cycle of a SQL query: parsing ASTs, algebraic rewrites, predicate pushdown, Volcano Iterator model mechanics, and Cost-Based Optimizer (CBO) statistics.
Stateful Workloads: StatefulSets, Stable Network Identities, and Ordered Scaling
Master Kubernetes StatefulSets: Ordinal indexing (pod-0, pod-1), Headless Service DNS identities, dedicated volumeClaimTemplates, and ordered scaling.
Multi-Version Concurrency Control (MVCC): How Non-Blocking Snapshot Reads Work
Learn how modern databases like MySQL InnoDB implement Multi-Version Concurrency Control (MVCC) to achieve non-blocking snapshot reads using DB_TRX_ID and roll pointers.
Two-Phase Locking (2PL) & Deadlock Resolution: Shared vs Exclusive Lock Mechanics
Understand Strict 2-Phase Locking (2PL), lock hierarchy (S, X, IS, IX), lock compatibility matrices, Wait-For Graph deadlock detection, and rollback cost strategies.
Database Concurrency Anomalies: Dirty Reads, Non-Repeatable Reads, Phantoms & Lost Updates
Explore database concurrency anomalies including dirty reads, non-repeatable reads, phantom reads, and lost updates, with interleaved transaction timelines.
The InnoDB Buffer Pool: Dirty Pages, LRU Eviction, and LSN Checkpointing
Learn how MySQL InnoDB Buffer Pool caches 16KB data pages, evicts cold pages via midpoint LRU algorithms, and flushes dirty pages asynchronously.
Write-Ahead Logging (WAL) & ARIES Crash Recovery: How Databases Guarantee Durability
Learn how Write-Ahead Logging (WAL) and the ARIES algorithm guarantee zero data loss during power outages and system crashes.
Database Sharding & Partitioning Strategies: Range, Hash, and Dynamic Rebalancing
Master database sharding strategies. Learn range partitioning, hash sharding, cross-shard query traps, and dynamic shard splitting.
Demystifying ACID: Transactions as an Isolation & Recovery Abstraction
Deconstruct ACID transaction guarantees in database engines. Learn how atomicity, consistency, isolation, and durability function under the hood.
The B+ Tree Deep Dive: Why Database Indexes Use Balanced Trees Instead of Hash Maps
Discover why database storage engines use B+ Trees for primary and secondary indexes. Learn how high fan-out page nodes enable range scans in 3 disk IOs.
Pages, Blocks, and Heap Files: How Database Storage Engines Layout Data on Disk
Explore how database engines organize table data on disk using 16KB Slotted Pages, slot offset arrays, tuple headers, and Record IDs.
Why Files Fail as Databases: Concurrent Access, Update Anomalies & Crash Recovery
Discover why storing application data inside flat CSV or JSON files leads to race conditions, lost updates, corrupted data on crash, and performance failure.
Mastering Database Internals from First Principles: Series Introduction & Learning Roadmap
Discover what you will learn in this 20-part series on Database Internals. Build a transactional storage engine and master B+ Trees and MVCC.