We will discuss different types of engines, their use cases, pros and cons, and much more.
Before jumping into the world of database engines, let’s first understand, What is a database engine?
To put it simply, it is just software that performs CRUD operations and takes care of disk storage. Some database engines are simple and only take care of storage and CRUD while complex engines can provide features like ACID support and transactions.
They are also known as Storage engines. Database Systems build features on top of these database engines.
Now that we know what a database engine is let’s start our discussion
MyISAM
Written in C, MyISAM was the first database engine for MySQL.
ISAM stands for Indexed Sequential Access Method. In this engine every index that we create points to a row. It uses B-tree for indexing.
Since every index points to a row, read operations are fast. Even the insert operations are fast because we are appending the new record to the end of the file and we always know where the end of the file is. However, the main issue is with the update and delete operations. When we update/delete existing rows, we change the row size, this changes the offset and all the index pointers need to change. This is expensive.
It provides no Transaction support.
Database crashes corrupt tables and has to be manually repaired.
It only provides table-level locking. If you want isolation you just have to lock the entire table.
InnoDB
It replaced MyISAM as the default database engine of MySQL and MariaDB.
In this engine, the B+ tree is used for indexing. Each index points to a primary key and each primary key points to a row. If there is no primary key then the engine makes one for you.
It supports transactions and therefore implements ACID features.
It also allows row-level locking so you don’t need to lock your entire table. This makes query processing faster.
It also introduces a lot of new features like foreign keys, tablespaces, and spatial operations.
SQLite
It was designed by Dwayne Richard Hipp and written in C language
Image credit: https://commons.wikimedia.org/wiki/File:SQLite370.svg
It is one of the most widely used embedded databases and is used in web browsers, mobile applications, operating systems, etc.
It used B-Tree for indexing.
It allows all ACID features
It allows table-level locking. It does not need row-level locking because it is usually used locally and there is only a single user.
It allows concurrent reads and writes.
LevelDB
It was designed by two Google engineers Jeffery Dean and Sanjay Ghemawat and written in C++ language. It was inspired by Google BigTable.
It is not a SQL database. It does not have a relational database model and does not support SQL queries.
It used Log-Structured Merge Trees for indexing. It works great for high insert volume. (unlike B tree that may need to rebalance itself after insert which makes insert operations slow)
It does not allow transactions.
It is single-threaded
There are levels of files and as the file grows large, levels are merged.
They also support Write Ahead Logs to ensure durability.
RocksDB
It is a fork of Google’s LevelDB and is further developed by Facebook. It is used as MyRocks for MySQL, MariaDB, and Percona. It is also used by MongoRocks for MongoDB.
Similarities with LevelDB
New features added to RocksDB
It allows transactions.
It also used multi-threading to improve performance.
Other features include Geospatial indexing, Bloom filters, merge operators, etc.
