Engine Architecture & Storage Internals

A column block holds one data type with similar, often repeating values, so it compresses far better than a row block that interleaves types. Run-length encoding stores value plus repeat count; dictionary encoding replaces values with small codes into a value table. Beyond disk savings, scans read fewer bytes and engines can filter and aggregate on encoded data without decompressing.

It uses an external merge sort: fill memory, sort that chunk, write it out as a sorted run, repeat until input is exhausted, then merge the runs by repeatedly taking the smallest head value across them. Each pass reads and writes the whole dataset, and more memory means fewer, larger runs and usually a single merge pass.

INFORMATION_SCHEMA is a standard, portable, read-only view layer with the same column names across engines, but only standard concepts. The native catalog is the real underlying dictionary: engine-specific, far richer, usually faster. Use the standard for portable tooling, native for depth and performance.

Join the engine's native catalog: object tables for names and ownership, size functions or storage columns for bytes, index-usage counters for scans since reset, and statistics catalogs for row estimates, distinct values and the last analyze time. The standard views cannot answer any of these.

They refresh optimizer statistics and reclaim space from dead row versions, on thresholds and under a throttle. Falling behind shows up as table and index bloat, growing history/undo, degrading scans, sudden bad plans from stale statistics, and maintenance that never finishes on the hottest tables.

Hit ratio is a ratio of page accesses, not of work. A query doing a hundred times more page accesses than needed can keep the ratio high while burning CPU and time. Measure absolute logical reads per query, physical reads, and latency - not the ratio.

The scan reads pages used once and lets them displace the hot working set - cache pollution. The OLTP workload then misses on pages it used to hit until the pool refills. Engines defend with midpoint insertion, small ring buffers for bulk reads, and marking scan pages for immediate reuse.

Dirty pages accumulate between checkpoints and are then flushed in a burst of random writes that saturates the device, so every query queues behind it. Smooth it by spreading the flush over most of the interval, letting a background writer trickle pages out continuously, and reducing dirty-page accumulation.

Session pooling binds a server connection to a client for its whole session - safe, but the multiplexing ratio is poor. Transaction pooling returns the server connection after each commit or rollback, allowing far more clients per server connection, but anything session-scoped breaks: session settings, temporary tables, session-level prepared statements, advisory locks, and cursors held across transactions.

A compensation log record (CLR) logs each reversal as it is performed and carries a pointer to the next record still to be undone. That makes rollback restartable and never repeated: after a second crash, recovery reads the CLRs, sees which reversals are done, and resumes from the pointer. CLRs are redo-only and are never themselves undone.

Redo repeats history so that after it, the pages match the exact state at the moment of the crash. Undo then operates on a state it understands, using the same rollback path used at runtime. Selective redo would create a state that never existed, where logged before-images and page-internal structures no longer line up.

For a new row the engine consults a free-space map, a compact per-page summary of remaining space, to pick a page without scanning the file. If a rewritten row no longer fits, the engine first compacts the page; if that is still not enough it places the row on another page, either updating index entries or leaving a forwarding pointer behind, which costs an extra read on later lookups.

A heap appends rows wherever there is space, so inserts are cheap and every lookup costs an index probe plus a fetch into the heap. An index-organized table keeps rows sorted inside the primary-key structure, so primary-key lookups and ranges need no extra fetch, but secondary lookups go through the primary key and inserts in random key order cause page splits and reordering.

It partitions both inputs by a hash of the join key into batches written to temporary files, then joins one pair of matching partitions at a time in memory. If one key value dominates, its partition stays too big to fit, so partitioning recurses without helping and that batch degrades badly.

DDL updates catalog rows under a lock on the object. Because every backend caches catalog entries and compiled plans, the change must be broadcast as an invalidation; other sessions block on the lock or on reaching a safe point, then rebuild their cached definitions and replan. Long-running readers delay the whole thing.

Bloat is space allocated to dead versions and half-empty pages rather than live data. Symptoms: files far larger than live data, scans reading mostly garbage, cache polluted, plans degrading. Ordinary cleanup frees space for reuse inside the object; only a rewrite or dropping a partition returns it to the operating system.

Commit latency is floored by the storage device's flush latency, since the commit record must be durable before acknowledgement. Group commit batches many concurrent commits into one flush, so throughput scales with concurrency even though single-commit latency does not improve.

An LSN is a monotonically increasing position in the log. Each page stores the LSN of the last change applied to it, so the engine can enforce log-before-data, skip redo of changes a page already has, and address a point in the log stream.

Log segments are recycled only once nothing still needs them. Something is holding a required position: an unfinished checkpoint, a long-running transaction, a lagging or disconnected replica, a failing archive process, or a stalled backup. Find the oldest required log position and its owner.

Size it from the working set, not a fixed percentage, and leave room for per-connection memory, sort/hash workspace and the OS. Double buffering is the same page being cached twice - once by the engine, once by the kernel - wasting RAM; direct I/O or a much larger pool avoids it.

Start from the recovery-time objective, since the interval bounds how much log replay a crash costs, then check the steady-state price: shorter intervals mean more repeated page writes and more full-page-image log volume. Choose the longest interval that still meets the recovery target.

Options: keep one row store and accept the scan cost; offload analytics to a replica; copy data into a columnar analytical system; or use a hybrid engine that keeps a columnar representation alongside the row data, updated from the transaction stream. Trade freshness, storage and operational cost against isolation of the two workloads.

Reduce versions produced and make removal a metadata operation. Narrow the churned row, cut updates per item, partition by time or state so completed work is dropped as a partition rather than deleted, tune cleanup to be far more aggressive on that table, and keep transactions short so the horizon never stalls.

Every schema multiplies catalog rows for tables, columns, indexes and constraints. At tens of thousands of tenants the dictionary itself becomes a hot, large, contended structure: slower planning, slower introspection, heavier backup and migrations that must run N times. Shared-schema scales the catalog; schema-per-tenant buys isolation.