Database storage engines are one of the most important concepts to know when developing a relational database management system (RDBMS), they determine how well the tables in your database can relate data between each other.

A database storage engine is one of the software components of a DBMS that is used to handle how data is stored, retrieved, updated and managed from the database. It is essentially in charge of managing all the CRUD operations that are happening in the database. Storage engines exists in two types which are

• Transactional storage engines
• Non transactional storage engines

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 i mean by this is when an SQL operation starts running, it has to complete its work fully or not complete its work at all if any error occured during the process. It is possible that a transaction can fail because certain factors can occur that would stop the transaction from completing and if this where the case, the storage engine would be able to roll back any previous changes the database had before the operation occured. Also transactional databases follow the ACID (Atomicity, Consistency, Isolation, Durability) standard and support foreign key constraints. An example of a transactional storage engine is INNODB.

NON TRANSACTIONAL STORAGE ENGINES

You can simply put it as the opposite of transactional storage engines. They do not fully support transactions and in turn violate one of the properties of ACID. For non-transactional storage engines, if anything where to happen to your database during the SQL operation, you would have to manually roll back the changes yourself as the storage engine would not help you in doing this. One advantage though of a non transactional storage engine over a transactional storage engine is the fact that they process operations much faster than a transactional storage engine. An example of a non transactional storage engine is MyISAM.

MY PERSONAL OPINION

If you know you need something that can scale has your app grows and the data in your database are going to relate with each other, then it is a more safer option to go for a transactional storage engine but if you are just storing some sort of information in your table that has no relationship with other tables, then a non transactional storage engine would be better option.

Hope this was helpful to you and If i missed out anything, let me know in the comments.

If you liked this, you can follow me on Twitter and Linkedin.