All 74 articles, sorted alphabetically
Make Latest Row Queries Repeatable When Timestamps Are Tied
Keeping the latest row per key with ROW_NUMBER ordered by a timestamp is nondeterministic when timestamps tie. A measured DuckDB run where 10 identical runs gave 9 different answers, why SQL allows it, and how to choose a tie-breaker that is both deterministic and correct.
Read article →Replication Slots That Fill the Disk: WAL Retention, max_slot_wal_keep_size, Lost Slots and the Idle Timeout
Why an abandoned PostgreSQL replication slot keeps every WAL file and can fill the disk or block vacuum, how to read pg_replication_slots, how max_slot_wal_keep_size and the PostgreSQL 18 idle_replication_slot_timeout bound the damage, and a safe runbook for dropping or recovering a slot.
Read article →Running Out of 32-Bit Keys in PostgreSQL: Measuring Headroom, Sequences Versus Columns, and an Online Migration to bigint
A table whose primary key is a 32-bit integer can run out of values at about 2.1 billion, and the failure is a hard stop on inserts. This shows how to measure how close you are, why the sequence and the column exhaust separately, and how to migrate to bigint without a long lock.
Read article →Upgrading to DuckDB 2.0: a One-Way Storage Format, Lambda and Division Changes, Triggers, and Exposing the Quack Server Safely
What DuckDB 2.0.0 LTS changes for teams that run it in production: database files that older versions cannot open, the lambda syntax and integer division changes, the new parser, triggers and their limits, the Quack client-server protocol, and an upgrade order for fleets with many DuckDB processes.
Read article →PostgreSQL HOT Updates: Why Some UPDATEs Write to Every Index, Fillfactor, the Indexed-Column Trap and Measuring the HOT Ratio
How heap-only tuple updates let PostgreSQL skip index maintenance, the two conditions they need, why one index on a frequently updated column turns every update into index writes, how fillfactor reserves room, and the statistics that show whether it works.
Read article →pgBackRest 2.60.0: Restores That Stop on Invalid Page Checksums, an Expire That Refuses, and How to Rehearse Both
pgBackRest 2.60.0, released on 18 October 2026, makes restore stop when a restored file has an invalid PostgreSQL page checksum and makes expire refuse when the oldest retained backup has checksum errors. What changed, why, and how to adjust restore runbooks and retention monitoring.
Read article →Online Schema Changes in MySQL: INSTANT and INPLACE DDL, the gh-ost Binlog Copy, Replica-Lag Throttling and the Cut-Over
How to change a large MySQL table without an outage: which ALTER operations are instant or in place in MySQL 8.4, the metadata lock each still needs, how gh-ost copies rows and tails the binary log without triggers, throttles on replica lag and cuts over safely.
Read article →DDL Behind a Lock Queue: Why One Slow Query Makes ALTER TABLE Stall Every Reader, and the lock_timeout Retry Loop That Fixes It
In PostgreSQL an ALTER TABLE that waits for ACCESS EXCLUSIVE makes every new query on the table wait behind it, and a connection pool drains in seconds. The mechanism, the lock level of each ALTER TABLE subform, a safe migration loop with lock_timeout and retries, and how to verify it.
Read article →PostgreSQL 19 RC 1: What Changes and How to Prepare Before the November GA
PostgreSQL 19 RC 1 was announced on 15 October 2026 with GA planned for 19 November. The incompatibilities that break upgrades or change behaviour, the features worth planning for, pre-upgrade checks you can run today, and a test plan.
Read article →ClickHouse, in depth: MergeTree parts and sparse indexes, sort-key design, the engine family, materialized views, replication with Keeper and the too-many-parts failure
How ClickHouse stores and queries data: MergeTree parts, granules, marks and the sparse primary index, choosing ORDER BY and PARTITION BY, Replacing, …
Read article →CockroachDB Architecture Deep Dive, in depth: ranges, Raft and leases, intents and parallel commits, clock uncertainty, retries and schema design
How CockroachDB works from SQL down to disk: key encoding, ranges and meta addressing, Raft per range and leader leases, write intents, transaction re…
Read article →Database Connection Pooling
A deep dive on database connection pooling: why connections are expensive, connection lifecycle and overhead, pooling modes (statement, transaction, s…
Read article →Database Indexing, in depth: choosing a strategy from access patterns, primary keys, tenants, pagination, time series and engine architecture
An indexing strategy for real workloads: how heap, clustered and LSM engines change index costs, choosing primary keys (sequential, UUIDv4, UUIDv7), t…
Read article →ACID Transactions, in depth: what each letter promises, the mechanism behind it, the settings that quietly weaken it and how to test it
ACID transactions explained from first principles: atomicity through logs and undo, consistency as constraints plus your invariants, isolation levels …
Read article →Bitmap index architecture
Deep-dive on bitmap indexes: one bitmap per distinct value, boolean AND/OR/NOT and popcount query evaluation, compression with RLE/Roaring, where they…
Read article →Bloom filter architecture in LSM engines
Deep-dive on Bloom filters in log-structured merge-tree databases: how a compact bit array and k hash functions answer 'definitely absent' o…
Read article →Buffer pool architecture
Deep-dive on database buffer pools: page tables and frames, pin counts and latches, clock sweep and midpoint LRU scan resistance, the WAL-before-data …
Read article →CDC via logical decoding
Deep-dive on Change Data Capture via logical decoding: WAL-based change streaming, logical decoding of physical records, replication slots and output …
Read article →Columnar Database Architecture in Depth: storage layout, encodings, pruning, vectorised execution and the write path
How a column-store database engine works inside: the IO arithmetic that motivates it, parts and granules, dictionary, run-length, delta and bit-packed…
Read article →Database Deadlock Detection: Wait-For Graphs
How databases detect deadlocks: the lock manager's wait-for graph, cycle detection, choosing a victim to abort, lock timeouts, wound-wait, and lo…
Read article →GIN inverted index architecture
Deep-dive on the Generalized Inverted Index (GIN): extracting many keys per composite value, mapping each key to a sorted posting list of row ids with…
Read article →Group commit architecture
Deep-dive on database group commit: why one fsync per transaction caps throughput at flush latency, how the commit queue and group leader let many com…
Read article →Hash Join Explained: Build and Probe, Grace and Hybrid Hash Join
How a hash join works: build on the smaller input, probe with the larger in O(N+M), spill when memory runs out, grace and hybrid partitioning, and ske…
Read article →Index-only scan architecture, in depth
How index-only scans really work: PostgreSQL's visibility map, heap fetches, INCLUDE columns, planner limits, the recent-data trap, InnoDB's…
Read article →B-tree Indexes, in depth: page layout, fan-out arithmetic, search and split paths, concurrency and keeping indexes healthy
How the B+tree behind almost every database index works: why pages and fan-out keep trees three or four levels deep, what lives on leaf and internal p…
Read article →Hash Indexes, in depth: bucket addressing, linear and extendible hashing, PostgreSQL internals, and when a hash index beats a B-tree
How hash indexes work from first principles: hashing keys to buckets, overflow chains, extendible and linear hashing with a runnable simulator, the on…
Read article →Database indexing architecture, in depth: how indexes attach to storage, what each one costs on write, and how to design composite and covering indexes
An engine-agnostic guide to how database indexes are wired into storage: B+tree fanout and height, heap plus TID versus clustered primary keys, double…
Read article →Database Isolation Levels, in depth: from the ANSI phenomena to dependency graphs, and how to test what your database really does
Isolation levels from their definitions: histories and serializability, the three ANSI SQL-92 phenomena and why they are ambiguous, the 1995 critique …
Read article →LSM Tree Explained: WAL, Memtable, SSTables and Compaction
How an LSM tree works: WAL plus memtable writes, flushes to immutable SSTables with bloom filters, L0 to Ln levels, compaction and amplification trade…
Read article →Materialized views -- precomputed query results
Deep-dive on database materialized views: the expensive-query need, precomputing and storing results, materialized vs regular view (stored vs recomput…
Read article →MVCC -- readers and writers that never block each other
Deep-dive on Multi-Version Concurrency Control (MVCC): the reader-writer contention problem, keeping multiple versions, consistent snapshots, no read …
Read article →OLTP vs OLAP Database Architectures in Depth
OLTP vs OLAP in depth: row stores, B+trees, WAL and MVCC versus columnar encoding, pruning and vectorised execution, a worked orders example, CDC pipe…
Read article →Database Partitioning Architecture, in depth: range, list, hash and composite schemes, partition pruning, partitioned indexes and the retention lifecycle
A first-principles guide to single-node declarative table partitioning in PostgreSQL and MySQL: when partitioning helps and when it hurts, choosing th…
Read article →Database query planner architecture, in depth: statistics, cost models, join search and why plans go wrong
How a relational query planner turns SQL into an execution plan, using PostgreSQL as the running example: parse and rewrite, access paths, the cost mo…
Read article →Database Replication Architecture in Depth: What Gets Shipped, When a Commit Is Acknowledged, and How Failover Loses Data
Database replication from first principles: statement, row and physical log shipping; asynchronous, semi-synchronous, synchronous and quorum commit wi…
Read article →Serializable isolation, in depth: how databases enforce it, what it costs, and how to run it in production
How serializable isolation is enforced and what it costs: two-phase locking, serializable snapshot isolation and optimistic validation compared by who…
Read article →Database sharding -- horizontal partitioning across nodes
Deep-dive on database sharding: the single-node limit, the critical shard key choice, sharding strategies (hash/range/directory), routing, hot shards …
Read article →Snapshot isolation architecture, in depth: how snapshots are built, the commit check, write skew, SSI and distributed timestamps
How snapshot isolation works inside a database: building a snapshot from transaction ids, the visibility rule, first-committer-wins and first-updater-…
Read article →Transaction isolation architecture, in depth: what each engine's levels really mean, reproducible anomalies and retry-safe code
A practical guide to transaction isolation: the anomalies that define each level, how locking, snapshot isolation and serializable snapshot isolation …
Read article →Transactional Database Architecture in Depth
A 2500-word walkthrough of transactional DB: MVCC, lock manager, WAL/redo, rollback, isolation levels, 2PC, vacuum, recovery.
Read article →Two-Phase Commit (2PC) Explained: Prepare, Commit, Blocking
How two-phase commit (2PC) makes distributed transactions atomic: prepare and commit phases, in-doubt locks, coordinator crash, presumed abort.
Read article →Vacuum and bloat architecture
Deep-dive on Postgres vacuum and bloat: why MVCC updates leave dead tuples, the cluster-wide visibility horizon and the idle transactions, replication…
Read article →Vector database architecture, in depth: index families, memory math, quantization with rescoring and measured recall
How a vector database works inside: write path and segments, flat, IVF, HNSW and SSD graph indexes, per-vector memory math, int8, PQ and binary quanti…
Read article →WAL architecture, in depth: the two logging rules, LSNs, checkpoints, ARIES-style recovery worked by hand, torn pages and fsync failure
How a write-ahead log makes a database durable and fast: the log-before-data and log-before-commit rules, record format and LSNs, steal and no-force, …
Read article →Write Amplification Explained: LSM Compaction and SSD Endurance
What write amplification is: WAL and memtable flushes, leveled vs tiered LSM compaction, SSD flash translation layer garbage collection and endurance.
Read article →DuckDB, in depth: the in-process engine, vectorised push-based pipelines, single-file storage, MVCC and out-of-core execution
How DuckDB works inside and what that means in operation: the in-process model, the query pipeline, 2,048-value vectors, morsel-driven parallel pipeli…
Read article →DuckDB Practical Uses, in depth: Parquet and CSV analysis, dataframes, lake queries, ETL, CI data tests, and where it stops fitting
Practical DuckDB from first principles: the in-process vectorized design, querying Parquet and CSV in place, zero-copy interop with pandas, Polars and…
Read article →Postgres Logical Replication Use Cases, in depth: upgrades, filtered feeds, consolidation, tenant moves, bidirectional sync and their limits
A use-case catalogue for PostgreSQL logical replication: how publications, slots and apply workers fit together, then a recipe per case (near-zero-dow…
Read article →Materialized Views in Postgres, in depth: plain vs concurrent refresh, locking, scheduling and incremental alternatives
How PostgreSQL materialized views really work: storage and populated state, what plain REFRESH and REFRESH CONCURRENTLY do internally and which locks …
Read article →MongoDB, in depth: inside the engine, from the WiredTiger cache and checkpoints to the query planner, the oplog, elections and sharded routing
How MongoDB works underneath the document API: the WiredTiger cache, MVCC snapshots, journal and checkpoints, how the query planner picks and caches p…
Read article →MongoDB: When It Actually Fits, in depth: the document as the unit of atomicity, the aggregate test, modelling, consistency settings and where it strains
A first-principles fit test for MongoDB: what the document model, replica sets and sharding actually guarantee, the aggregate-shaped access test, work…
Read article →MySQL InnoDB, in depth: one UPDATE through the clustered index, buffer pool, undo, redo, doublewrite and binlog, plus read views, next-key locking and crash recovery
How InnoDB executes and protects a single row change: the clustered index and hidden columns, the buffer pool, undo records and MVCC read views with t…
Read article →MySQL Replication, in depth: binlog formats, GTIDs, async versus semi-sync, parallel appliers, lag you can trust and safe failover
How MySQL replication works and how to run it: the binary log, row versus statement formats, file positions versus GTIDs, asynchronous and semi-synchr…
Read article →MySQL vs Postgres in 2026, in depth: storage, MVCC, locking, replication and the release lines that matter now
An engine-level comparison of MySQL and PostgreSQL in 2026: the current release lines, clustered InnoDB versus the Postgres heap, undo-log purge versu…
Read article →pgvector at 100M Scale, in depth: sizing, halfvec and binary quantization, index builds, filtered search, partitioning and replicas
How to run pgvector with a hundred million embeddings: storage and memory arithmetic, index dimension limits, halfvec and binary-quantized indexes wit…
Read article →PostgreSQL WAL, in depth: segments and LSNs, synchronous_commit, full-page writes, checkpoint sizing, archiving and a full pg_wal
How PostgreSQL's write-ahead log works in practice: how LSNs map to 16 MB segment files, what pg_waldump shows, the wal_level and synchronous_com…
Read article →Postgres Index Strategies, in depth: designing indexes from the workload, proving them with EXPLAIN and retiring them safely
A workload-first method for PostgreSQL indexing on one worked table: finding hot query shapes with pg_stat_statements, composite column order, partial…
Read article →PostgreSQL Indexes
The four PostgreSQL index types explained: B-tree for range queries and sorting, GIN for inverted indexes and full-text search, GIST for spatial and c…
Read article →Postgres Isolation Levels, in depth: per-statement snapshots, the UPDATE re-check, 40001 errors, SERIALIZABLE in practice and a coupon-cap worked example
What each PostgreSQL isolation level actually does: Read Committed's per-statement snapshots and wait-and-recheck updates, Repeatable Read's…
Read article →PostgreSQL Logical Replication + CDC
PostgreSQL logical replication: row-level selective replication across major versions, WAL-based change data capture, replication slots and decoding, …
Read article →PostgreSQL MVCC and VACUUM, in depth: tuple versions, the xmin horizon, autovacuum arithmetic and freezing
How PostgreSQL implements MVCC in the heap and how VACUUM cleans up after it: tuple headers and pageinspect, snapshot visibility, the xmin horizon and…
Read article →PostgreSQL Table Partitioning, in depth: declarative DDL, the lock each command takes, building indexes without downtime, migrating a live table and keeping the planner honest
An operational guide to PostgreSQL declarative partitioning: how a partitioned table routes rows, the DDL and bound rules, what ATTACH, DETACH and ind…
Read article →Redis Data Structures, in depth: choosing types, internal encodings, queues and streams, big keys and cluster limits
A practical guide to Redis data structures: strings, hashes, lists, sets, sorted sets, streams, bitmaps and HyperLogLog, the compact and full encoding…
Read article →Redis, in depth: the single command thread, keyspace and expiry, eviction, atomicity, replication, Sentinel, Cluster and latency diagnosis
How a Redis server actually runs: the event loop and io-threads, keyspace hashing and incremental rehash, lazy and active expiry, maxmemory and sample…
Read article →Redis Persistence Modes, in depth: RDB snapshots, AOF and fsync policies, multi-part AOF, fork costs, WAITAOF and choosing what you can afford to lose
How Redis persists an in-memory dataset: RDB snapshots and fork with copy-on-write, the append-only file and its three fsync policies, rewrites and th…
Read article →ScyllaDB vs Cassandra, in depth: shard-per-core against the JVM, tablets against vnodes, compaction, licensing and how to choose
An engineer's comparison of ScyllaDB and Apache Cassandra: the shared CQL and LSM foundation, the threading and memory models that make them beha…
Read article →Sharding Strategies Compared, in depth: range, hash, token ring, fixed slots, directory and entity sharding, and what each costs to reshard
A side-by-side comparison of database sharding strategies: the two-level key-to-shard and shard-to-node mapping, range sharding with automatic splits,…
Read article →SQL Query Optimization, in depth: finding the queries that matter, reading EXPLAIN ANALYZE, and a catalogue of fixes that actually work
A practical PostgreSQL-centred method for SQL query optimization: rank queries with pg_stat_statements, read EXPLAIN ANALYZE and BUFFERS output, fix n…
Read article →TimescaleDB, in depth: hypertables and chunks, chunk exclusion, sizing the chunk interval, the columnstore, continuous aggregate refresh and the job scheduler
How TimescaleDB works inside Postgres: hypertables and chunks, plan-time and run-time chunk exclusion, sizing the chunk interval with a worked example…
Read article →TimescaleDB vs InfluxDB, in depth: storage engines, schema, write and query paths, cardinality, compression, rollups and how to choose
A version-explicit comparison of TimescaleDB and InfluxDB for time-series workloads: hypertables and chunks versus TSM and the InfluxDB 3 Arrow, DataF…
Read article →TimescaleDB vs InfluxDB vs Prometheus, in depth: data models, storage engines, query languages and a sizing worked example
A first-principles comparison of TimescaleDB, InfluxDB 3 and Prometheus: how each models series, how its storage engine moves data from WAL to compres…
Read article →When to Pick Cassandra, in depth: the architecture behind the choice, where it fits, where it hurts and how to decide
A decision guide to Apache Cassandra: how its leaderless ring and LSM write path shape what is cheap and expensive, query-first modelling, the workloa…
Read article →When to Pick DynamoDB, in depth: access patterns first, where it fits, where it hurts and how to decide
A decision guide for Amazon DynamoDB: what you trade for serverless scale, an access-pattern inventory method, a worked multi-tenant order system with…
Read article →When to Pick Postgres, in depth: the architecture behind the default choice, where it fits, where it strains and how to decide
A decision guide for PostgreSQL grounded in its architecture: one write primary, MVCC with vacuum, process-per-connection and an extensible type and i…
Read article →