Lucent Owl logo Lucent Owl

Database Diagrams

Last updated:

5 related guides
diagrams databases architecture-diagrams

Change Data Capture

Flow

Committed changes flowing from the transaction log to secondary stores.

Change data capture feeding secondary stores from the primary database's log The application writes to the primary database, PostgreSQL, which records each committed change in its transaction log. A CDC connector such as Debezium reads the log and publishes each change as an event to a stream such as a Kafka topic. A search index and a cache each consume the stream and update their copies. Only committed changes reach the log, so the copies can lag the primary but won't drift from it. Application [writes business data] writes Primary database [system of record: PostgreSQL] Transaction log committed changes reads CDC connector [Debezium] change to event Event stream [Kafka topic] ordered changes Search index [Elasticsearch] Cache [Redis] Only committed changes reach the log, so the copies can lag the primary but won't drift from it.

LSM Tree With Leveled Compaction

Flow

Writes buffered in memory, flushed to immutable files, merged level by level.

How an LSM tree handles writes A write is appended to the write-ahead log on disk and inserted into the memtable, a sorted structure in memory. When the memtable is full, it is flushed to disk as an immutable SSTable in level 0. Compaction merges SSTables from level 0 into level 1 and from level 1 into level 2, keeping the newest version of each key and dropping overwritten and deleted values. MEMORY DISK Write insert Memtable [sorted, in memory] append Write-ahead log [sequential append] flush when full Level 0 SSTable SSTable SSTable Level 1 SSTable SSTable SSTable SSTable Level 2 SSTable SSTable SSTable SSTable compaction merge, keep newest compaction drop old versions

Clustered Table vs Heap Table

Structure

A secondary-index lookup in a clustered table versus a heap table.

Looking up a row through a secondary index in a clustered table and in a heap table In a clustered table, as in InnoDB or SQL Server, a secondary index on email returns the row's primary key, 42. The engine then walks the primary-key B-tree, which is the table itself, down to the leaf page holding the full row. That is two tree walks. In a heap table, as in PostgreSQL, the index on email returns the row's physical location, page 17 slot 3, and the engine reads that heap page directly. That is one index walk and one page read. CLUSTERED TABLE: INNODB, SQL SERVER HEAP TABLE: POSTGRESQL Secondary index [on email] key 42 Primary-key B-tree [the table itself] Leaf: full row Two tree walks per secondary lookup Index [on email] page 17, slot 3 Heap page 17 [rows in no order] One index walk, then one page read

Row vs Column Layout

Structure

The same four orders stored row by row and column by column.

Four orders laid out in row-oriented pages and in column-oriented storage In row-oriented storage, each page holds whole rows: page 1 holds orders 1 and 2 with their id, name, total, and date together, and page 2 holds orders 3 and 4. Reading one order reads one page. In column-oriented storage, each column is stored separately: all ids together, all names together, all totals together, and all dates together. Summing the total column reads only the totals. ROW-ORIENTED COLUMN-ORIENTED Page 1 1 | Alice | 99.99 | 2026-01-15 2 | Bob | 45.50 | 2026-01-15 Page 2 3 | Carol | 200.00 | 2026-01-16 4 | Dan | 12.00 | 2026-01-16 Reading one order reads one page id 1 2 3 4 name Alice Bob Carol Dan total 99.99 45.50 200.00 12.00 date 01-15 01-15 01-16 01-16 SUM(total) reads only the total column

Lost Update Under Read Committed

C4 · Dynamic

Two transactions both read stock 1 and both sell the last item.

Two transactions losing an update under Read Committed Transaction 1 reads the product row and gets stock 1. Transaction 2 reads the same row and also gets stock 1. Transaction 1 writes stock 0 and commits. Transaction 2, still acting on its earlier read, writes stock 0 and commits. The row ends at stock 0, but two items were sold with one in stock. Transaction 1 products row 7 Transaction 2 SELECT stock stock = 1 SELECT stock stock = 1 UPDATE stock = 0; COMMIT sells the item stock: 0 UPDATE stock = 0; COMMIT still acting on its read of 1, sells the same item stock: 0 Two items sold, one in stock

Write Skew

C4 · Dynamic

Two transactions each take a different doctor off call, leaving nobody on call.

Two transactions producing write skew under snapshot isolation Transaction 1, for Alice, counts the doctors on call and gets 2. Transaction 2, for Bob, counts from its own snapshot and also gets 2. Transaction 1 sets Alice off call and commits. Transaction 2 sets Bob off call and commits. The two transactions updated different rows, so neither conflicts with the other, and the doctors table ends with nobody on call. Transaction 1 (Alice) doctors table Transaction 2 (Bob) count on call 2 count on call 2 Alice off call; COMMIT Alice: off Bob off call; COMMIT different row, so no conflict Bob: off Nobody on call

Replication Topologies

Structure

Where writes enter and how they spread in each replication topology.

Single-leader, multi-leader, and leaderless replication In single-leader replication, a client writes to the one leader, which ships its log to two followers. In multi-leader replication, clients write to leader A or leader B, each leader has its own follower, and the two leaders replicate to each other and must resolve conflicting writes. In leaderless replication, a client sends each write to all three replicas and the write succeeds once W of the N replicas acknowledge it; reads query R replicas. SINGLE-LEADER MULTI-LEADER LEADERLESS Client writes Leader log Follower Follower Client Client Leader A Leader B replicate both ways, resolve conflicts Follower Follower Client write to all N Replica Replica Replica A write succeeds after W of N acks; a read asks R replicas

Reading Your Own Write From a Lagging Follower

C4 · Dynamic

A user's write reaches the leader, but their next read hits a follower that hasn't caught up.

A read-your-writes violation caused by replication lag A user saves a new display name, and the write goes to the leader, which confirms it. The leader then ships the change to a follower, but the change is delayed. Before it arrives, the user reloads the page, the read is routed to the follower, and the follower returns the old name. The change reaches the follower only afterward. User Leader Follower save name = "Sam" OK replication, delayed reload profile name = "Alex" the user's own change is missing change arrives after the read

ELT Into a Lakehouse

Flow

Raw data loaded first, then refined in layers inside the analytical store.

An ELT pipeline loading raw data and refining it in layers Three sources, an application database read through change data capture, SaaS APIs, and an event stream, are extracted and loaded without transformation into the raw layer of the analytical store, often called bronze. Transformations run inside the store with SQL, producing a cleaned layer, silver, and then business-ready tables, gold. BI dashboards read the gold tables, and data science reads the silver and gold layers. SOURCES Application DB [via change data capture] SaaS APIs [CRM, billing] Event stream [clicks, telemetry] extract + load, no transform ANALYTICAL STORE Raw [bronze] Cleaned [silver] Business-ready [gold] transform inside the store with SQL (tools like dbt) BI dashboards [reads gold] Data science [silver and gold]

Found this useful? Share it:

Share on LinkedIn