---
title: "Databases"
description: "38 posts about Databases, summarised, each linking to the original."
---

# Databases
> 38 posts about Databases, summarised, each linking to the original.

## Articles

### [What Is a Transactional Database?](https://yomu.fyi/post/what-is-a-transactional-database.md)
- Company: [Databricks](https://yomu.fyi/company/databricks.md)
- Author: Databricks Staff
- Published: Apr 20, 2026
- Type: Explainer

Transactional databases handle large volumes of short, real-time read and write operations for systems such as orders, payments, account balances, and customer records. They support online transaction processing (OLTP) by treating related steps as one logical unit: successful work commits, while failures roll back the full transaction. ACID—atomicity, consistency, isolation, and durability—protects integrity under concurrency and failures, with row-oriented storage optimized for individual-record access, locking and isolation levels coordinating concurrent work, and write-ahead logging (WAL) supporting crash recovery. The source positions these databases as foundations for banking, e-commerce, healthcare, reservations, and telecommunications, while noting that row-oriented execution is less suitable for large analytical workloads and that horizontal scaling is more complex than in distributed NoSQL systems.


### [Migrating Etsy’s database sharding to Vitess](https://yomu.fyi/post/migrating-etsy-s-database-sharding-to-vitess.md)
- Company: [Etsy](https://yomu.fyi/company/etsy.md)
- Author: Ella Yarmo-Gray
- Published: Mar 19, 2026
- Type: Problem & solution

Etsy migrated its long-running MySQL shard management to Vitess vindexes after an architecture spanning about 1,000 tables and shards, 425 TB of data, and roughly 1.7 million requests per second. The legacy ORM used a single index database to map records to shards, creating a scaling bottleneck and single point of failure. To avoid moving existing data, engineers built custom SQLite lookup and hybrid vindexes, changed new shard assignments to match Vitess’s hash algorithm, and introduced routing incrementally table by table with traffic ramps, rollback, and query compatibility testing. After five years, about 2,500 pull requests, and 6,000 queries, the migration removed the index database dependency, made scaling operations take days rather than months, simplified developer access, and kept database performance largely unchanged without a mass data migration.


### [How we made Notion available offline](https://yomu.fyi/post/how-we-made-notion-available-offline.md)
- Company: [Notion](https://yomu.fyi/company/notion.md)
- Author: Raymond Xu
- Published: Dec 11, 2025
- Type: Explainer

Notion's Offline Mode required turning a best-effort SQLite cache into storage that could guarantee a page and all its dependent records were available without a network connection. The system uses a forest of offline page trees backed by offline\_page and offline\_action tables, allowing independent reasons such as explicit toggles, favorites, automatic downloads, and inheritance to coexist while preserving hierarchy. To keep content current, clients subscribe to push channels emitted by the page version snapshot system, then use lastDownloadedTimestamp and server lastUpdatedTime to fetch only newer pages after reconnecting. When pages or databases change, the client reconciles fresh page and database snapshots with offline\_action, adding or removing rows rather than rebuilding entire trees. This makes offline availability precise and eventually consistent, while supporting conflict resolution through the CRDT data model.


### [Highly concurrent in-memory counter in GoLang](https://yomu.fyi/post/highly-concurrent-in-memory-counter-in-golang.md)
- Company: [Grab](https://yomu.fyi/company/grab.md)
- Author: Naveen Kumar Jakuva Premkumar
- Published: Oct 6, 2025
- Type: Problem & solution

An overloaded database faced heavy traffic from campaign usage tracking, motivating an in-memory counter rather than an immediate SQL-to-NoSQL migration. Because exact counts were not required, increments could be cached in the application server and flushed periodically to reduce database queries and CPU use. The implementation uses Go’s Sync.Map, with LoadOrStore and CompareAndSwap for concurrent increments, plus LoadAndDelete to atomically remove values for asynchronous persistence; the source notes a fresh-map swap as an alternative locking optimization. On an Apple M4 Pro, the 2,000-key benchmark reported 53 ns/op and 1 billion operations per 60 seconds, versus 160 ns/op and 466 million operations for a Mutex-protected map, but Sync.Map used 88 B/op and four allocations. In production, update queries fell from 140 QPS to 45 QPS, master CPU from 35% to 18%, and replica CPU from 19% to 12%.


### [MongoDB.local NYC 2025：定义 AI 时代的理想数据库](https://yomu.fyi/post/mongodb-local-nyc-2025-ai-cn.md)
- Company: [MongoDB](https://yomu.fyi/company/mongodb.md)
- Published: Sep 18, 2025
- Type: Announcement

The page associated with the title “MongoDB.local NYC 2025: Defining the Ideal Database for the AI Era” is rendered as a MongoDB Resources page rather than an event article. Its metadata describes a collection of webinars, video presentations, white papers, datasheets, and other resources, with controls for content type and language. The visible resource content includes “MongoDB Search Fundamentals: Powering Modern Search Experiences,” which presents MongoDB Search as an integrated full-text search capability and covers lexical search, search indexes, autocomplete, fuzzy queries, scoring, filters, and facets. The provided text does not state details about the NYC event or establish a conclusion about an ideal database for AI.


### [Engineering stories behind the Medium Daily Digest Algorithm: Part 2](https://yomu.fyi/post/engineering-stories-behind-the-medium-daily-digest-algorithm-part-2.md)
- Company: [Medium](https://yomu.fyi/company/medium.md)
- Author: Raphael Montaud
- Published: Aug 28, 2025
- Type: Problem & solution

Medium’s Daily Digest filtering had become costly and difficult to maintain after a single Bloom Filter instance accumulated billions of items for read, muted, and presentation rules. The system could face up to 15,000 filter questions per feed and more than 15 million per second across thousands of feeds, while Bloom Filters could not delete or enumerate entries and introduced derived-data drift. The team replaced the muted-writer filter with a user-based database query that fetched all muted writers for the current user and cross-referenced them with the story shortlist, then split read filtering between early user-based queries and later pairwise lookups. The resulting implementation removed Bloom Filters entirely, was reported as ten times cheaper in their situation, made costs independent of shortlist size, supported reading-history clearing, and was easier to control and debug.


### [Navigating The AI Revolution: The Importance Of Adaptation](https://yomu.fyi/post/navigating-the-ai-revolution-the-importance-of-adaptation.md)
- Company: [MongoDB](https://yomu.fyi/company/mongodb.md)
- Published: Jun 4, 2025
- Type: Problem & solution

A TDWI survey conducted with MongoDB and Capgemini found that only 11% of companies have AI applications in production, despite widespread recognition of AI’s value. The report identifies data readiness challenges including managing diverse data types, ensuring accessibility, and providing sufficient compute power, with less than half of companies managing newer data formats and only 41% feeling they have enough compute. The post argues that AI applications are dynamic, continuously adapting and learning from user behavior, requiring a flexible data foundation unlike traditional static software. MongoDB positions its modern database solutions as essential, citing examples like Lombard Odier’s modernization with generative AI tooling and Victoria’s Secret’s AI-powered visual search using MongoDB Atlas Vector Search. The post concludes that organizations must modernize their tech stacks and execution to keep pace with AI-driven change, or risk being left behind.


### [MongoDB 8.0: Raising The Bar](https://yomu.fyi/post/mongodb-8-0-raising-the-bar.md)
- Company: [MongoDB](https://yomu.fyi/company/mongodb.md)
- Published: Oct 2, 2024
- Type: Announcement

MongoDB 8.0, the latest version of the document database, is now generally available, delivering significant performance and security improvements. The release features 36% better read throughput, 56% faster bulk writes, 20% faster concurrent writes during data replication, and 200% faster complex aggregations of time series data. MongoDB’s internal build system, which runs on MongoDB, saw query latencies drop by approximately 75% after upgrading. The company also reports that customer OCELL experienced queries running twice as fast. MongoDB 8.0 includes sharding enhancements for up to 50 times faster data distribution, quantized vectors that reduce memory usage by up to 96%, and expanded Queryable Encryption to support range queries. The release is available via MongoDB Atlas, MongoDB Enterprise Advanced, and MongoDB Community Edition.


### [How the SQLite Virtual Machine Works](https://yomu.fyi/post/how-the-sqlite-virtual-machine-works.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Sep 7, 2022
- Type: Explainer

SQLite processes SQL through a parse-optimize-execute pipeline that turns query text into executable virtual-machine instructions. It tokenizes characters, parses them into an AST such as a Select struct, and uses table statistics to choose between a full table scan and index-based access. The source describes sqlite\_stat1's coarse index and duplicate-value counts alongside sqlite\_stat4's distributed samples, then traces bytecode that opens the persons table, compares favorite\_color with "blue", and returns matching columns. Registers hold intermediate values, cursors iterate through tables, and sqlite3\_step() resumes execution after each returned row; the VM has 186 opcodes as of writing, while bind parameters allow prepared statements to skip repeated parsing and optimization.


### [How SQLite Scales Read Concurrency](https://yomu.fyi/post/how-sqlite-scales-read-concurrency.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Aug 24, 2022
- Type: Explainer

SQLite’s write-ahead log (WAL) is presented as the mechanism that overturns the common claim that SQLite is inherently single-user or unable to scale reads. Unlike the rollback journal, which blocks readers and writers from operating at the same time, WAL leaves original database pages in place and appends changed pages to a -wal file, allowing point-in-time snapshots to coexist with ongoing writes. SQLite tracks those entries in a shared-memory (SHM) index: 32KB blocks contain page-number lists and 8,192-slot hash maps, with slot positions calculated as (pgno \* 383)%8192 while transaction position determines visibility. The post concludes that WAL mode benefits most applications, while noting that rollback journaling can remain preferable for edge cases such as multi-database transactions.


### [How SQLite helps you do ACID](https://yomu.fyi/post/how-sqlite-helps-you-do-acid.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Aug 10, 2022
- Type: Explainer

SQLite’s rollback journal provides atomicity, isolation, and durability by preserving database state before a transaction changes the main file. Without journaling, an interrupted 4KB page write could corrupt the database; the journal instead records original pages, then permits recovery if commit steps fail. A journal header stores a nonce, the database’s pre-transaction page count, sector size, and page size, while each page record includes its page number, a copy of prior contents, and a 32-bit checksum. The mode allows concurrent readers but makes writers wait for readers and blocks new readers, using SHARED, RESERVED, and PENDING filesystem locks. Its simplicity brings a concurrency bottleneck; WAL and wal2 provide better support for concurrent readers, which the post says will be covered next.


### [SQLite Internals: Pages & B-trees](https://yomu.fyi/post/sqlite-internals-pages-b-trees.md)
- Company: [Fly.io](https://yomu.fyi/company/fly-io.md)
- Published: Jul 27, 2022
- Type: Explainer

SQLite stores table rows as encoded byte arrays, and understanding that representation explains how a simple database preserves compact records and supports efficient growth. Using a sandwich table as an example, the post decodes a row’s varints, type values, UTF-8 text, IEEE-754 floating-point value, and integer count, then examines 4KB pages, cell-pointer indexes, and page headers. SQLite’s b+tree places records in leaf pages and key-range indexes in interior pages; page splits add levels as the database grows, while searches traverse only one page per tree level. The examples estimate that a depth-four tree can hold about 500³ leaf pages, or roughly 476GB, while requiring four page reads for a lookup. The discussion concludes that integer storage is more space-efficient than floating-point storage and that internals such as record encoding and PRAGMAs become less opaque when understood.


### [Faster Pull Request Merges](https://yomu.fyi/post/faster-pull-request-merges.md)
- Company: [Ramp](https://yomu.fyi/company/ramp.md)
- Author: Neal Wu & Young Kim
- Published: Mar 22, 2022
- Type: Problem & solution

Ramp’s growing Python pytest suite took over 12 minutes, while pull requests could wait up to one to two hours between merge queueing and production. Previously, Kodiak kept pull requests in a merge queue, requiring each PR to be up-to-date with master and rerun the full suite after every merge. Removing that requirement exposed a failure mode: parallel branches could introduce Alembic migrations with multiple heads, making the database migration history invalid. Ramp instead added migrations/migration-hash.txt, a hash of migration filenames that creates merge conflicts between migration-changing PRs while leaving other PRs free to merge; the deploy pipeline still runs tests on master before deployment. This reduced average merge time from more than an hour to 12 minutes.


### [Five Common Data Stores and When to Use Them](https://yomu.fyi/post/five-common-data-stores-and-when-to-use-them.md)
- Company: [Shopify](https://yomu.fyi/company/shopify.md)
- Author: 2023-10-18
- Published: Oct 15, 2019
- Type: Explainer

The article compares five common data stores—relational databases, NoSQL databases, key-value stores, full-text search engines, and message queues—and connects each to appropriate design needs. Relational databases use schema-bound tables, primary and foreign keys, SQL, and ACID transactions for durable business-critical data, while NoSQL databases trade strict schemas for flexibility with document and wide-column models. In-memory stores such as Redis and Memcached provide fast access and configurable eviction for caching; Redis additionally supports richer data structures and disk persistence. Full-text search engines target word and substring searches, whereas Kafka uses a distributed append-only log and producer-consumer model to move data reliably between services. The article concludes that the right choice depends on whether data must be permanent, flexible, quickly accessed, searched, or transferred.


### [Building Resilient GraphQL APIs Using Idempotency](https://yomu.fyi/post/building-resilient-graphql-apis-using-idempotency.md)
- Company: [Shopify](https://yomu.fyi/company/shopify.md)
- Author: 2023-10-18
- Published: Aug 27, 2019
- Type: Problem & solution

Shopify’s Payment Service uses API-level idempotency to make GraphQL payment mutations resilient to timeouts, connection failures, and ambiguous outcomes that could otherwise leave merchants unpaid or double-charge buyers. The design places a UUID-based idempotency key directly in each mutation input, identifies requests by client plus key, and locks concurrent duplicates so retries receive a 409 response while the original call is processing. An IncomingRequest record stores request state and completed recovery points, allowing completed responses to be replayed and incomplete attempts to recover before continuing. Handlers divide work into no-side-effect, transactional local-side-effect, and remote-side-effect steps, each optionally providing run and recover functions. The approach adds database-write overhead and requires careful compatibility testing when recovery points change, but the team reports that its structured handlers are clear and maintainable.


### [Pagination with Relative Cursors](https://yomu.fyi/post/pagination-with-relative-cursors.md)
- Company: [Shopify](https://yomu.fyi/company/shopify.md)
- Author: 2023-10-18
- Published: Aug 12, 2019
- Type: Explainer

Incremental page-number pagination becomes increasingly expensive for large offsets, because the database scans and discards earlier records before returning a page. Shopify describes relative cursor pagination, which remembers the last record seen and continues from that point, using an indexed id to keep query time nearly constant as pages increase. In tests on a shop with 14 million products, a 100,000 offset took 2,221.60 ms, compared with 5.24 ms using the last id, while offsets of 1,000,000 and above consistently timed out. Sorting by another field requires retaining both its last value and a unique secondary key such as id, with a matching compound index to avoid skipped records and maintain performance. Shopify introduced Link-header URLs for next and previous pages and began migrating REST endpoints from page numbers, reporting early /admin/products.json usage as about 11 times faster, though adoption remained low and full results were pending.


### [A Pods Architecture To Allow Shopify To Scale](https://yomu.fyi/post/a-pods-architecture-to-allow-shopify-to-scale.md)
- Company: [Shopify](https://yomu.fyi/company/shopify.md)
- Author: Xavier Denis
- Published: Mar 2, 2018
- Type: Explainer

Shopify outgrew its ability to scale by buying larger database servers, so it introduced database sharding in 2015. Sharding improved performance and horizontal scalability but made actions dependent on every shard, increasing the risk of platform-wide unavailability. In 2016, Shopify reorganized its runtime around pods: each pod groups shops on isolated datastores, while shared workers, application servers, and load balancers are restricted to one pod at a time. Sorting Hat assigns each request to a pod and location, while every web request and delayed job is kept within one pod; paired data centers and Pod Mover support recovery without dropping requests or jobs. The design makes a pod the unit for scalability, resiliency, and disaster recovery, and simplifies evacuating an entire data center.


### [Handling Very Large Tables in Postgres Using Partitioning](https://yomu.fyi/post/handling-very-large-tables-in-postgres-using-partitioning.md)
- Company: [Heroku](https://yomu.fyi/company/heroku.md)
- Author: Rimas Silkaitis
- Published: Sep 13, 2016
- Type: Tutorial

Very large Postgres tables containing application events or log-like data can degrade query performance, slow index updates, and make vacuum disproportionately long. The article explains table partitioning through inheritance, using a master table, child partitions, CHECK constraints, and a trigger that routes inserts according to created\_at; for an events workload with 10 million daily inserts and short-lived data, it uses daily partitions. It then introduces pg\_partman, whose create\_parent command configures time-based partitioning and whose run\_maintenance function creates new partitions and removes old ones according to retention settings. Partitioning can improve performance and simplify deletion of expired data, but it should follow indexing and usage analysis, especially when queries cover a small time range and older data has little value.


[Newer posts](https://yomu.fyi/topic/databases.md)