Database Internals 01 Introduction and Overview

07 Nov, 2026

Every database solve some problem, every problem cannot be solved by single database.

• Hot fetching
• Cold storage
• Key Value
• Time series data
• Store large Blobs
• Document Oriented
• Graph

Some major grouping

• OLAP
• OLTP
• HTAP

Common DBMS Architecture

1. Transport

• Communicate client queries to database server
• Also used by DB nodes for inter communication

2. Query processor

3. Query Parser

• Parses, interprets, and validates
• Access control check #how

4. Query Optimizer

• Removes impossible and redundant parts of the query #how
• Find most effective way of executing the query based on criterias
  • Indexes
  • Ordering
  • Access Method
• These are based on
  • Internal statistics - index cardinality, estimated intersection sizes
  • Data placement - which nodes hold the data, cost of transferring it
• Cost estimation

5. Execution Engine

• Queries generally follows an execution/query plan - sequence of operations to complete the query end to end
• Execution plans #what
• Collects results from:
  • Local execution - Queries inside the current node
  • Remote execution - This node reading from or writing to other nodes in the cluster, including replication #what
  • Single DN node has Query processor, execution engine and storage engine

6. Storage Engine

• Local execution happens by this
• Transaction Manager
  • Maintains the consistency of data
  • Keep the data sane
  • Data cannot leave in a logically inconsistent state
• Lock Manager
  • Handles integrity
  • Concurrent operations should not update each other's data
• Access Methods
  • Data structures which access and organize the data in the disk
  • B-Trees
  • LSM Trees
• Buffer Manager
  • Act as cache as it managed cached pages in memory
• Recovery Manager
  • Handles the Durability of the system
  • Restoring data to state before failure

In-memory vs Disk Based DBMS

In-memory

• Main persistence storage is RAM
• Simple to program as OS handles memory management
• Not durable as RAM are volatile memory

Disk Based

• Main persistence is disk
• Harder to implement as we have to manage these manually:
  • Data references
  • Serialisation formats
  • Freed memory
  • Fragmentation
• Durable with many techniques like WAL

Durability in Memory Databases

• Durability only comes from disk
• We can have backup logs in disk
• We can do checkpointing - till a time we take a snapshot of whole DB and save it in disk and after that rely on WAL

Gains of Memory Databases

• Not everything has to be handled carefully. It borrows every power the OS will have
  • Can use larger pool of data structures
  • Performance optimisations
  • Works entirely with pointers
  • No special handeling of variable sizes

Column vs Row Orientated DBMS

This division is mostly based on the read path or the access pattern.

Row Orientated

• Data are stored and partitioned row wise
• The content of data fetched with all its fields and properties. These are written together in the disk as well. Basically point queries.
• OLTP aligns well with this pattern
• Read path is ensured by a unique key
• It also improves Spatial Locality
• id,name,dob,pho

Column Orientated

• Data is stored and partitioned column wise.
• All the data of same columns are written together
  • Also help with compression as we know they all will have same datatype
  • Allow effective queries like aggregations as they can be read in single pass
• Access pattern is that we only need a specific column - analytical works, trends, etc
• OLAP aligns with this one
• One way of storing it introduces duplicates, as every column needs to hold the key of its row:
  • Symbol: 1:INR, 2:USD, 3:EUR, 4:INR
  • Date: 1:01-12-2026, 2:01-12-2026, 3:01-12-2026, 4:02-12-2026
  • Rate: 1:0, 2:99, 3:150, 4:0
• We also need to store some metadata to reconstruct the whole data if needed

Wide Column Store

• Conventionally different from both. Here the data is represented as hierarchies of Maps. The hierarchy is based on the access keys and at the very low level the data is stored in row format
• If we defined sequence of keys then the data retrieval is the fastest.
• Row keys are sorted lexicographically, which is why choosing the key order matters
• Each cell is versioned by timestamp
  • Bigtable’s model is (row, column, timestamp) → value.
• Data representation looks like:

{
  "DEL|BOM|2026-12-01|BUSINESS|2A0C0I": {
    "fare": {
      "0|290000|BA178": { "airline": "BA", "depart": "19:30" },
      "0|310000|DL1":   { "airline": "DL", "depart": "21:00" },
      "1|240000|IB6252": { "airline": "IB", "depart": "17:10" }
    },
    "meta": { "fetched_at": "2026-10-21T10:15:00Z", "provider": "TJ" }
  },
  "DEL|BBI|2026-12-01|ECONOMY|1A0C0I": {
    "fare": {
      "0|45000|DL1":    { "airline": "DL", "depart": "21:00" },
      "0|52000|BA178":  { "airline": "BA", "depart": "19:30" }
    }
  }
}

The storing happens with

• Row key - the partition key of the data - "DEL|BOM|2026-12-01|BUSINESS|2A0C0I"
• Column families - fare, meta
• Column Key - 0|290000|BA178
• The column families are stored separately
  • In HBase, each column family has its own files
  • In Bigtable, families are grouped into locality groups
  • Reading fare never touches the meta files

Column Family "fare"
row key          | column            | value
DEL|BBI|...      | 0|45000|DL1       | {...}
DEL|BBI|...      | 0|52000|BA178     | {...}
DEL|BOM|...      | 0|290000|BA178    | {...}

Column Family "meta"
row key          | column            | value
DEL|BOM|...      | fetched_at        | 2026-10-21T10:15:00Z
DEL|BOM|...      | provider          | TJ

Data files and Index Files

Quick access is the primary objective of any database. This makes how the data being organised very valuable. Using the file system of an OS is not efficient as there are many operations which maximised:

• read - records can be located in the smallest possible steps
• update - minimum number of changes on the disk
• storing - minimize storage overhead per stored data efficiency.

Data Files

DBMS store data records in files where each file usually represents a table know as data files.

• Index-organized Tables (IOT)
  • Stores and updated data in the indexes
  • Easy access as storage happens in index order
• Heap-organized Tables (heap files)
  • Append in write order - new insertions just append to the new lines
  • Update may or may not happen in-place.
  • Do not follow any order
  • So no file reorganization happens when new page appends
  • No order so need additional indexes for any access
• Hash-organized Tables (Hashed files)
  • Records are stored in buckets?
  • Hash value determines which bucket
  • Records in individual buckets can be sorted order or append order

Index Files

As the data grow, and for effective access index files are present which allow us to effectively locate data records without scanning the entire file.

• An index maps keys to record locations
• Smaller than the data file
• Keys may be unique or not
• Secondary index can have many entries per key.

Clustered vs Non-clustered

• Clustered - data records are stored in index key order (IOTs are clustered by definition)
• Non-clustered - index points into a separate data file whose order differs from the index
• Primary indexes are often clustered, but not always

Primary Index as an Indirection

When fetching by secondary indexes there are 2 ways to do it

• Referenced directly
  • Secondary index have the offset to access the said data
  • Fewer disk seeks
  • Cost is there to update the pointer info when data is updated or rellocated during any reasons
    • These updates are usually fine if the workload is not write-heavy with many secondary indexes
• Via primary Keys
  • Secondary index have pointer to primary key
    • A additional lookup increase
  • MySQL InnoDB does this
    • Secondary index lookup the primary index
    • Primary index then lookup actual record

Hybrid approach is always there, where secondary index will have both the data file offsets and primary keys. If the data offset is not valid then only do an extra lookup via primary index and then updating the offset stored in the secondary index.

Buffering, Immutability, and Ordering

All the storage structure have these 3 common variables

• Buffering
  • How much time and space you choose to keep the data in-memory before flushing it to disk
• Immutability
  • Choice of "do you want to change the existing data"
  • Some storage engines do not change the file content once they are written.
    • Append-only - new writes and updates are appended to the end of the file
    • Copy-on-write - a modified page is written to a new location; the old one is left untouched
• Ordering
  • This tell that the keys which are sorted are written in continuous segment on disk.
  • Useful if the data access pattern is not just the lokup but scan a range