How I built a Relational Database from scratch in C++
My Learnings from building PRQLite
“What I cannot create, I do not understand” ~ Richard Feynman
I recently graduated from college and have A LOT of free time before joining as a full-time software engineer. I have always wanted to work on a fun project before joining somewhere, so I decided to build a relational database from scratch!
It sounded like a proper challenge to my dev skills (spoiler alert: it was), so in this blog, I will explain its architecture and hopefully provide a generic resource for anyone wanting to study database internals or build a database for themselves in C++ (I have also attached all the resources used at the end of the blog). Please feel free to star the repository.
Phase I: The Frontend (REPL and Parser)
To start off the project I decided to keep things simple and build the REPL (Read Evaluate Print Loop) and make a DB class with a connect method etc. I tried to keep it very similar to PostgreSQL.
After the basic REPL loop was implemented, It was time to move on to lexing and parsing. For this, I used a subset of SQL Grammar (see reference).
Since I had built a compiler for C/C++ like language in the past, I was familiar with concepts of Lexing and Parsing a great resource for this is the Dragon Book for Compilers. I will be skipping the lexing phase as it was pretty simple and straightforward (Since SQL Grammar is pretty small with a small token list, I didn’t use the normal DFA based RegEx approach [also didnt want to use external libraries for regex]).
Instead of a powerful LALR bottom-up parser I have decided to go with a top-down recursive descent parser.
Parsing Logic and Semantic Analysis
You must ensure the grammar you are working with doesn’t contain Left Recursion as recursive descent parsers can’t work with such grammars and will get stuck in infinite loops.
In recursive descent parsing, the parser walks down the tree recursively by matching and peeking at tokens. Your parser must support the following constructs/methods:
peek()Inspects the token stream without consumingmatch(TokenType t)verifies if the current token matches the expected type; consumes it if true.advance()consumes the token from the streamreset()clears the token stream
The parser builds an Abstract Syntax Tree (AST for short used in rest of the blog). The AST Node is defined as (overview):
class ASTNode{
public:
virtual void accept(db::parser::ASTVisitor& visitor)=0;
virtual ~ASTNode();
...
};ASTNodes are designed in a visitor pattern so that the Semantic Analyzer and the Executor Engine can easily the walk the tree without cluttering the node classes themselves with execution logic. By decoupling the data structure from the operations performed on it, the architecture stays clean. If I later decide to add a query optimizer, I just write a new Visitor — the AST doesn’t have to change at all.
Building the Tree :
SelectStatement* Parser::parseSelect() {
match(TokenType::SELECT);
auto columns = parseColumnDefs();
match(TokenType::FROM);
std::string table = parseIdentifier();
// ... routing down the tree
}The beauty of recursive descent parsing is that we have to simply write the grammar as C++ methods and simply consuming tokens as needed.
void SemanticAnalyzer::visit(db::parser::SelectStatement& node){
auto t_id = db::semantic::Catalog::getInstance().getTableId(node.tableName);
auto t_schema = db::semantic::Catalog::getInstance().getTableSchema(t_id); node.tableId = t_id;
const auto& t_cols = t_schema->columns;Semantic Analyzer works similarly; it walks the ASTNode Tree using Visitor Pattern. The job of semantic analyzer is to bind information into the nodes for the Executor Engine to run the query. We use a global singleton Catalogclass for semantic analysis and storing table schemas, table nos. etc.
Phase II: The Virtual Machine
Now comes the fun part of the project: the actual query executions and file storage systems. The Virtual Machine consists of the executor engine which works using a Volcano Iterator model (more ahead).
The above is an architecture diagram of how I implemented the volcano architecture in my query executor. Each operator is a child of an abstract executor, and we make a tree of these operators, only the leaf nodes have access to the table storage/schema using Read/Write Guards (more on that in next phase). The other operators can simply pull data from their children by calling next and then apply necessary modifications.
class AbstractExecutor{
protected:
AbstractExecutor* _child;
db::parser::ASTNode* node;
public:
virtual void init() = 0;
virtual std::optional<db::memory::Tuple> next() = 0;
virtual ~AbstractExecutor() = default;
virtual db::table::TableSchema getOutputSchema()const=0;
};class InsertOperator : public AbstractExecutor{
public:
InsertOperator(...);
std::optional<db::memory::Tuple> next() override;
...
private:
std::shared_ptr<db::memory::TableManager> table_manager;
...
};Reads and writes happen via Table Iterators The iterators function similar to C++ Iterators and run on table manager.
class TableManager{
private:
friend class TableIterator;
std::atomic&lt;page_id_t&gt; pageNos{0};
page_id_t metadata_page_id;
public:
TableManager();
TableIterator begin();
TableIterator end();
void createTuple(Tuple &amp;t);
TableManager(...);
page_id_t getMetadataPageId() const { return ...}
};The Table Iterators are designed smartly so they point to the next tuple on a page allowing smooth mechanism for reads and writes; they create a page when a page has no more space for tuples (architecture of page and buffer pool is discussed in next section).
Tuple is a fundamental memory unit in our system where raw bytes are packed in a buffer. For example, see the below diagram
+-------------------------------------------+
| db::memory::Tuple |
+-------------------------------------------+
| INT(4B) | BOOL(1B) | TEXT(255B, padded) |
+-------------------------------------------+
| 0x00 0x00 0x00 0x2A | 0x01 | "Alice" 0x00 |
+-------------------------------------------+
| 42 (int) | true | 'Alice' + zeros |
+-------------------------------------------+Let’s walk through an example to understand the executor engine:
SELECT name,id FROM friend WHERE name = 'garv' OR id = 5;For such a query we have the following execution tree:
ProjectionOperator → FilterOperator → SelectOperator
The SelectOperator does a sequential scan of the table row by row by using the table iterator to find the correct page number and slot for the tuple (logic in next section). Then it transmits tuples one by one to the filter operator which evaluates the WHERE clause and on the basis of that drops the tuple and requests for a new one or passes it up. Once it reaches the top projection operator deserializes the tuple and only keeps the required columns. This tuple is then handed to the thread which called DB::connect() method or printed to the CLI.
These executor objects are called similarly using the visitor pattern discussed above by an ExecutorEngine where the visit method of the engine corresponding to each tree root creates the corresponding abstract executor tree nodes according to the statement type.
class ExecutorEngine : public db::parser::ASTVisitor{
private:
volatile std::sig_atomic_t* interrupt;
public:
ExecutorEngine(volatile std::sig_atomic_t* i);
void execute(db::parser::ASTNode* root){
if(root){
root->accept(*this);}}
void visit(db::parser::InsertStatement& node) override;
void visit(db::parser::CreateStatement& node) override;
void visit(db::parser::DeleteStatement& node) override;
void visit(db::parser::SelectStatement& node) override;
void visit(db::parser::CreateIdxStatement& node) override;
};Phase III: The Backend (Storage Engine)
Storage in databases is done via a paging mechanism where we write to pages of size 4kb (or 8kb in case of PostgreSQL). This is done to minimize I/O as rows are grouped together and we can keep a pool of recently used pages in memory. This phase deals with everything from storing tuples in pages all the way to storing and loading those pages in/out of the disk.
Paging
Above is the diagram of the paging architecture I have used, in a slotted page the header of the page holds fixed-size slots which contain offset and size of the tuple stored in the page (as tuples can be of varying sizes imagine VARCHAR(n)). To insert a tuple, we first serialize it to bytes form and then check if freeSpace >= size(slot) + size(tuple) If yes, then we simply store the tuple else we must store it in another page/new page. The data in the page is filled from the higher address space (from 4096B to 0B direction) and slots are filled from lower address space (similar to how a heap and a stack might grow).
Buffer Pool and Page Tables
A Buffer Pool is a data structure in memory which stores a list of frames. Each frame contains page, pin count, dirty bit and a frame ID (used for replacement policies).
Get Garv Sethi’s stories in your inbox
Join Medium for free to get updates from this writer.
In Buffer Pool we keep frequently accessed pages in memory to avoid costly reads/writes to the disk. When a page is evicted from the memory if it has its dirty bit set, then we write back to the disk else the write is skipped. Buffer Pool also consists of a replacer policy abstract object which can be swapped out on demand currently my implementation uses a Random Eviction Policy.
A page table is just a thread-safe concurrent implementation of a HashMap which tells if a certain page is in memory or not and points to the frame in the pool and avoids a costly O(n) lookup in the pool.
class BufferPoolManager{
private:
Frame* bufferPool;
size_t poolSize = {M_FRAMES};
std::unique_ptr<PageTable> pageTable;
std::unique_ptr<DiskManager> diskManager;
std::list<frame_id_t> free_list_;
std::unique_ptr<Replacer> replacer;
BufferPoolManager();
BufferPoolManager(const BufferPoolManager&) = delete;
BufferPoolManager& operator=(const BufferPoolManager&) = delete;
BufferPoolManager(BufferPoolManager&&) = delete;
BufferPoolManager& operator=(BufferPoolManager&&) = delete;
Frame * fetchFrame(page_id_t page_id);
void unpinFrame(frame_id_t frame_id);
void pinFrame(frame_id_t frame_id);
friend class ReadPageGuard;
friend class WritePageGuard;
public:
ReadPageGuard fetchPage(page_id_t page_id);
WritePageGuard writePage(page_id_t page_id);
static BufferPoolManager& getInstance();
~BufferPoolManager();
Frame* allocateNewFrame(page_id_t new_page_id);
WritePageGuard newPage(page_id_t page_id);
void flushPagestoDisk();
};Read/Write Guards and Disk Manager
Read/Write Guards are a construct of my Database which are objects returned by buffer pool on any read/write request. They ensure by using a shared/unique mutex that all reads/writes happen safely without corrupting disk data.
Disk Manager is an OS independent implementation of an interface which reads/writes from a .db file on disk at certain distance from the start and since read/write guards ensure no two threads are trying to cause a reader/writer problem at the same spot in the file it can service requests concurrently without any locking or race conditions.
If you have implemented everything till here your Database should be able to do most basic operations like: SELECT/INSERT/DELETE/CREATE without any hiccups.
Phase IV: Filters, Indexes and ACIDity
This stage is what turns a normal database into a production ready DB. Indexes like B+ Trees/B-link Trees allow for fast lookups/insertions/deletions and range queries while filters like Bloom Filters allow us to skip key lookups which aren’t seen in the table. Transactions make a database ACID compliant let’s discuss these concepts and their implementations one by one.
Transactions : Achieving ACIDity
Implementing SELECT and INSERT is fun, but what happens if the power goes out halfway through a massive table update? Or if two users try to buy the last concert ticket at the exact same millisecond? Without transactions, your database will corrupt its own files.
To make the database production-ready, we need to enforce ACID properties (Atomicity, Consistency, Isolation, Durability). I tackled this by building three distinct but cooperating components: a Lock Manager, a Write-Ahead Log (WAL), and a Transaction Manager.
1. The Lock Manager
Instead of locking the entire database, the system locks specific rows using their RecordID.
class LockManager {
public:
void lockShared(transaction_id_t txn_id, const db::memory::RecordID& rid);
void lockExclusive(transaction_id_t txn_id, const db::memory::RecordID& rid);
void unlockAll(transaction_id_t txn_id);
// ...
};I am using std::mutex and std::condition_variable to block threads efficiently until the row they need becomes available.
2. The Write-Ahead Log
Before any change is written to the main database file, a lightweight LogRecord is appended to the WAL. Because the WAL is an append-only file, writing to it is incredibly fast (sequential I/O). Each log is tagged with a unique Log Sequence Number (lsn_t). If the system crashes, the database simply replays the WAL on reboot to restore the exact state prior to the crash, ensuring Durability.
3. The Transaction Manager
The TransactionManager is the singleton that ties the memory guards, the Lock Manager, and the WAL together. It provides the classic begin(), commit(), and rollback() interface.
- If a user calls
commit(), the manager flushes the WAL to disk, writes the dirty pages, and releases all locks. - If a user calls
rollback()(or if an error occurs), the manager executes theundo_actions_—a vector of C++ lambdas holding the exact inverse operations of what was just done in memory—instantly reverting the database to its previous state.
Filters
A bloom filter is a probabilistic data structure which solves set membership queries of the form: whether an element is possibly in a set or definitely not in it.
Instead of storing actual elements, a Bloom Filter maintains a bit array of size m. Each inserted item is hashed k times using independent hash functions. Each hash maps the item to one of the m bits, which are then set to 1.
When checking membership, the same k hashes are computed; if all corresponding bits are 1, the item might exist. If any bit is 0, the item definitely does not exist.
I will skip the implementation of the bloom filter in favor of the mathematical formulations of this data structure.
After inserting n elements, the probability that a bit remains 0/1 is:
When querying an element not in the set, the probability that all k bits are 1 (a false positive) is:
The optimal number of hash functions minimizing false positives is:
The Bloom Filter implementation in my design is lock free as we used a bit-array of std::atomic<uint64_t> to ensure atomic loads and stores.
Indexes
This blog is long enough as it is, if this gets enough reach I will make a separate blog on B+Trees (or any other topic in the blog in depth).
Conclusion
Building PRQLite has been one of my best hands-on learning experiences ever, I got to learn so much about database internals and various computer science concepts. What started as an attempt to understand how does a SQL query works turned into me implementing execution engines, parsers and buffer pools. The hardest part was clearly me migrating from an in-memory data store to persistence using paging.
If you’re interested in databases or systems programming, I highly recommend building a small database yourself. Even implementing a parser, storage engine, or transaction manager will deepen your understanding far beyond reading books/documentation alone.
The source code is open source and still lacks many production level features so feel free to contribute by raising a pull request ;P .
