How to choose the right database
- Relational (SQL-based) databases - such as MySQL, PostgreSQL, Oracle, and MS SQL Server - can be queried with SQL-like languages. They allow indexing and faster updating, but total querying time can be slow for large tables.
- Their simple structure matches most data types and they support atomic transactions but not OOP-based objects. They require vertical scaling (10-100TB), which requires downtime when adding resources to machines.
- NoSQL databases typically use JSON records with no common schema. There are four main types of NoSQL databases.
- Document-oriented databases make each document into a JSON and allow indexing by field (so all records must have that field); they support big data analytics using parallel computations (examples are MongoDB, CouchDB, and DocumentDB).
- Columnar databases store data by the column (querying by subsets of a column is fast), allowing better data compression. They are commonly used for data modeling and logging (an example is Cassandra).
- Key-value databases allow only key-based querying, which is fast, and each record has a ‘time to live’ (TTL) field deciding when it gets deleted. They are great for caching, but because of using RAM storage, are expensive (examples include Redis and Memcached).
- Graph databases use nodes to represent entities and edges to represent their relationship; they are good for knowledge graphs and social networks (examples include Neo4J or InfiniteGraph).
Full post here, 7 mins read