Difference between SQL and NoSQL
A SQL database stores a record as rows across tables mapped by foreign keys, and a NoSQL database stores it as one blob of JSON.
In SQL a person's address sits in a separate table, and address ID 23 maps to the row for Munich, Germany. In NoSQL the address is an object nested inside the person's object.
The advantages of NoSQL:
- Insertions and retrievals need the whole blob, so the relevant data is kept together and no join is needed.
- The schema is flexible. A new attribute such as salary is added straight away, where SQL adds a column under locks on the table.
- Horizontal partitioning is built in, with a focus on availability.
- It is built for aggregations.
The disadvantages:
- Not many updates are supported.
- ACID is not guaranteed, so financial systems do not use it for transactions.
- Relations are not implicit: there is no foreign key constraint.
- Joins are hard and manual.
NoSQL fits data that is a block, with many writes and few updates.
Sharding
Sharding is horizontal partitioning: one attribute of the data is the key that breaks the data into pieces, and each database server gets one chunk.
The range of user IDs is cut like a pizza, so that each slice is served by one database server. Tinder shards on location, so a query for the users in one city reads a single shard.
The problems, and what answers each:
- Joins across shards. The query goes to two shards and joins the data across the network, which is extremely expensive.
- The shards are inflexible in number. Hierarchical sharding breaks a shard with too much data into smaller pieces, and a manager for the shard maps each request to the right piece.
- A shard fails. In a master-slave architecture writes go to the master and reads are distributed across the slaves. If the master fails, the slaves choose a master among themselves.
An index on a shard can be on a different attribute, such as age within New York.