database-internals-overview.js·4 scenarios
Database Internals Overview — concept page covering wire protocol, auth/RBAC, parser/planner/optimizer/executor, transaction manager + MVCC + locks, buffer pool / WAL / B-tree / LSM, disk + backup/PITR. 4 scenarios: query lifecycle, buffer pool hit vs miss, commit path with WAL fsync, B-tree vs LSM write path.
Любая СУБД — Postgres, MySQL, Cassandra, MongoDB, SQLite, RocksDB — внутри устроена по одной и той же layered architecture: wire protocol, parser, planner, optimizer, executor, transaction manager, buffer pool, WAL, storage engine, disk. Различаются деталями (B-tree vs LSM, MVCC vs locks, single-process vs multi-process), но архитектурные слои те же. Понимание этих слоёв — единственный способ перестать относиться к БД как к чёрному ящику и начать осознанно её настраивать. Без этого знания запрос внезапно начинает тормозить в 100 раз после INSERT в соседнюю таблицу, COMMIT блокируется на 5 секунд, реплика отстаёт на час, а cluster теряет данные после crash — и непонятно, что чинить.
«БД = transaction manager + query engine + storage engine + recovery system. SQL входит сверху, проходит парсинг и планирование, исполнитель тянет страницы из buffer pool (или диска при miss), MVCC решает что видно, WAL гарантирует durability через fsync, checkpointer асинхронно сбрасывает грязные страницы на диск.»
Главное правило: COMMIT блокируется на WAL fsync (~100µs), а не на data flush. Data pages летят на диск асинхронно через checkpointer. Это позволяет крутить 10K commit/s — но в обмен на recovery time после crash (нужно реплеить WAL от последнего checkpoint).
Полная архитектура Postgres-like БД, разбитая на 6 групп + клиент:
wire (TCP 5432, FE/BE, TLS), auth (pg_hba.conf, SCRAM/cert/peer, 1 RTT), rbac (роли, GRANT/REVOKE, RLS), session (backend process — 1 на коннект, ~10MB RSS, pgbouncer для пулинга).parser (SQL → AST, bison), rewriter (view expansion, RLS injection, CTE inlining), planner (enumerate plans, join orders), optimizer (cost-based, pg_statistic histograms, GEQO для big joins), plancache (prepared statements), executor (volcano iterator, seq/index/bitmap scan).txmgr (xid, BEGIN/COMMIT/ROLLBACK), mvcc (xmin/xmax, snapshot isolation), locks (lock manager, row/page/table, 2PL для serializable), vacuum (autovacuum, удаляет dead tuples, обновляет stats, предотвращает xid wraparound).bpool (shared_buffers, 8KB pages, clock-sweep eviction), pagecache (OS page cache, double buffering), btree (B-tree индексы), lsm (LSM-tree альтернатива), wal (16MB segments, append-only), walwriter (background flush каждые 200ms), bgwriter (постепенный flush dirty pages), checkpoint (fsync всех dirty pages, 5 min default).datafiles (base/, 1GB segments, heap + indexes), walfiles (pg_wal/, sequential write, ~100µs fsync), archive (S3/NFS для PITR), backup (pg_basebackup + WAL = PITR).ADR-001 «Понимать internals vs treat database as black box» висит на ноде planner.
В скрипте 4 сценария, покрывающих full query path и storage trade-offs.
Полный путь SELECT. SELECT * FROM orders WHERE user_id=42 AND created_at > now()-7d проходит через все слои: client → wire (FE/BE Query message) → auth (already-authenticated, session reused через pgbouncer) → parser (~50µs) → rewriter (view expansion, RLS injection security barrier) → planner (cost = page_reads × 1.0 + cpu_tuple × 0.01) → optimizer (pg_statistic histograms, selectivity 0.001 → выбираем index) → plancache (prepared statement, plan reused) → executor (volcano: IndexScan → Filter → Limit) → mvcc (xmin/xmax visibility per tuple) → btree (root → branch → leaf, 3-4 page reads) → bpool (pin & lock, ~99% cache hit) → 47 строк обратно. Это mental model для EXPLAIN ANALYZE: каждая строка плана — это узел executor, который дёргает storage через bpool.
Hot vs cold page — 100× разница в latency. Тот же запрос дважды:
pread(orders_pkey, 8KB) → NVMe ~100µs ИЛИ HDD ~10ms, evict LRU victim, fill buffer.Fix: shared_buffers = 25% RAM, pg_prewarm после restart, effective_cache_size для подсказки planner. Эта разница — основная причина «запрос после рестарта тормозит первые 5 минут».
Как durability покупается за fsync. BEGIN; UPDATE accounts SET balance=balance-100 WHERE id=7; COMMIT;. Executor → row-level FOR UPDATE lock, ROW EXCLUSIVE на heap; MVCC создаёт новую tuple (xmin=current_xid), старая помечается xmax; heap page dirty в bpool — НЕ пишется на диск сразу; XLogInsert redo record в WAL buffers; COMMIT → XLogFlush до текущей LSN → walwriter → write() + fdatasync() ~100µs NVMe / ~5ms slow disk → commit запись на диске → ack клиенту. Async data flush: checkpoint каждые 5 минут (или WAL > max_wal_size), pwrite + fsync всех dirty pages (I/O spike ~GB), advance redo pointer, recycle старых WAL segments. archive_command копирует WAL в S3 для PITR. Crash recovery: redo от last checkpoint LSN, undo незакоммиченных транзакций (ARIES).
Read-optimized vs write-optimized storage. Та же UPDATE в B-tree (Postgres) vs LSM (Cassandra).
Choice: B-tree = read-heavy + range scans; LSM = write-heavy + time-series + сжатие. Hybrids: InnoDB change buffer, TokuDB fractal trees, Postgres heap-only tuples — все смешивают идеи. Подробнее в [CONCEPT]b-tree-vs-lsm.
Context. Большинство разработчиков относятся к БД как к чёрному ящику. Работает на маленьких объёмах, катастрофически проваливается на масштабе. Симптомы: запрос внезапно занимает 30s после INSERT в смежную таблицу (статистика устарела, planner выбрал nested loop вместо hash join); UPDATE одной строки блокирует на 10 минут весь worker pool (long-running tx + autovacuum не может почистить); реплика отстаёт на час под нагрузкой (физическая репликация single-threaded); cluster теряет данные после crash (fsync не настроен или disk кеш игнорирует FUA).
Decision. Black-box допустим только до «PoC на ноутбуке». С момента >100 RPS / >10GB / >5 разработчиков пишут SQL — требуется минимальная грамотность:
Без этого DBA становится «magical figure», который раз в квартал героически чинит то, что разработчики ломают. Practical curriculum: этот overview → [CONCEPT]b-tree-vs-lsm → [CONCEPT]mvcc → [CONCEPT]wal-write-ahead-log → query-optimization → [CONCEPT]postgres-internals / [CONCEPT]mysql-internals.
idle_in_transaction_session_timeout, dashboard на pg_stat_activity.WHERE id > last_seen_id LIMIT 100).EXPLAIN ANALYZE до production. Запрос работает на 1000 строк за 5ms — летит на 10M строк за 30 секунд (seq scan + nested loop).pg_stat_activity / SHOW PROCESSLIST. Не знать, какие запросы сейчас выполняются — значит управлять БД вслепую.Не каждое приложение требует глубоких знаний internals:
Но как только: >100 RPS, >10GB данных, >5 разработчиков пишут SQL, нужен uptime SLA — все эти отговорки заканчиваются и internals становятся обязательными.
Концепты курса:
Книги (порядок чтения):
Курсы:
Online: