MongoDB
- Key value store (unlike relational DBMS). It’s a document store.
- Distributed
- Open Source
- Schema-free
- NoSQL
- No foreign Keys, No triggers
- Support both indexing (Primary, Secondary)
- No transaction concept (Not ACID)
- Master-Slave replication
- In memory capabilities, to define some structures to be held in-memory only
MySQL
- RDBMS (Relational Database Management Systems)
- Not Distributed
- Open Source as well as paid
- Schema based
- Support SQL
- Support triggers and foreign keys
- Support Indexing (Primary, Secondary)
- Master-Master replication & Master-Slave replication
- Support Transactions (ACID compliant)
- In memory capabilities, to define some structures to be held in-memory only
Redis
- In memory data structure store, used as db, cache
- Key-value store (document store)
- Schema free
- NoSQL
- Support Indexing (Primary, Secondary)
- No foreign Keys, No triggers
- Master-Master replication & Master-Slave replication
- Sharding
- No MapReduce
- Optimistic locking, atomic execution of transactions
- In memory capabilities, to define some structures to be held in-memory only
PSQL
- RDBMS (Relational Database Management Systems)
- Not Distributed
- Open Source
- Schema based
- Support SQL
- Support triggers and foreign keys
- Support Indexing (Primary, Secondary)
- Master-Slave replication
- Support Transactions (ACID compliant)
- No in-memory capabilities
Note: Amazon AWS Redshift is a columnar PSQL database.
While a relational database is optimised for storing rows of data, generally for transactional applications (OLTP – Online Transaction Processing), a columnar database is optimised for fast retrieval of columnar data, typically useful in analytical applications (OLAP – Online Analytical Processing).
A columnar database stores the data of an entire column in consecutive memory blocks, while a relational database stores the data of an entire row of a table in consecutive memory blocks.
Column-oriented storage drastically reduces the overall disk I/O requirements and reduces the amount of data you need to load from disk for an analytical query. This is because an analytical query, usually require to fetch data of few (required) columns of a large table.