Database storage engines are one of the most important concepts to know when developing a relational database, they determine how well the tables in your database can relate data between each other. This article provides an introduction to what they are.
A database storage engine is one of the software components of a DBMS that handles how data is stored, retrieved, updated and managed in the database. It essentially does all the CRUD operations that occurs in the database.
Database storage engines exists in two types
- Transactional
- Non transactional
- Transactional Storage Engines
These are storage engines that support transactions in a database. A transaction is an operation in a database that must either be totally completed or failed, what this means is when an SQL operation starts running, it has to complete its work fully or not complete its work at all in case any error occured. It is possible that a transaction fails because there are certain factors that could stop the transaction from completing and if this where the case, the storage engine would roll the database back to it's previous state before the operation occured. Transactional storage engines follow the ACID standard and support foreign key constraints which is very important for relational databases. An example of a popular transactional storage engine is INNODB.
- Non Transactional Storage Engines
You can simply put it as the opposite of transactional storage engines. Non transactional storage engines don't fully support transactions which makes them read data faster than transactional storage engines. With a non transactional storage engine, you can't have relationships between tables because they do not support foreign key constraints. In the case of a non transactional storage engine, if any error occurs during an SQL operation, you would have to manually roll back the changes yourself as the storage engine does not do this on it's own. In terms of speed, non transactional storage engines are fast in running SQL operations. An example of a common non transactional storage engine is MyISAM.
If you are going to be relating data a lot in your database or you just want consistency, it is much better to go for a transactional storage engine. In the case where you need to store and retrieve data for quick access and there is no relationship with that data, using a non transactional storage engine would be a better option.
CONCLUSION
Database storage engines have a lot of importance and use cases, let me know in the comments any point i might have missed out, thanks for reading!.
Top comments (0)