Let us start with a few well known examples
SQL
MySQL, Microsoft SQL Server, Amazon Aurora, PostgreSQL
No-SQL
Elasticsearch, DynamoDB, AmazonElastiCache, mongoDB
What is SQL?
- SQL is an abbreviated version of “Structured Query Language”
- It is the core of RDBMS engines such as PostgreSQL, MySQL
- 4 key components of SQL:
- Structure
- Storage
- Scale
- Access
Structure
- Table
- Constraints
- Relations
Storage
- Concentrate (Typically, 1 Node)
Scale
- Vertical
- Better Machine
- Horizontal
- More machines
- Master => Replica(s)
Access
- Raw SQL
- Direct Database Connection
- Object Relational Mapper (ORM)
What is No-SQL?
- It is anything that is non-structural
- There are many implementations… Table, Document, Graph
- Built to scale with high performance, but queries are less flexible
Structure
- Tables, Documents (JSON), Graphs
- Generally rely on key-value store
Storage
- Hashing Input
Scale
- Horizontal – More Partitions
Access
- REST APIs
- CRUD (Create-Read-Update-Delete) in Vendor Specific Language
When to use what?
SQL
- When your access patterns aren’t defines (if you don’t know how your business use case will evolve & not sure if storing data in a particular pattern will allow your queries in an effective way later)
- When you want to perform flexible queries
- When you want to perform relational queries
- When you want to enforce field constraints
- When you want to use a well documented access language (SQL)
No-SQL
- When your access pattern is defined
- When your primary key is known
- When your data model fits (graphs)
- When you need high performance and low latency
How to pick - example scenarios
Scenario #1: Small project + Low scale, Unknown Access Patterns
=> SQL
Scenario #2: Large project + High scale + Relational Queries
=> SQL with read replicas; e.g., Amazon Aurora
Scenario #3: Medium / Large Project + High Scale + High Performance
=> No SQL – DynamoDB, mongoDB
