system-design-refresher

Databases

All SQL databases use tables for data storage and use SQL as a query language. They are great for transactions, complex queries and integrity.

The most common examples are PostgresQL, MySQL, SQLite, etc.

Atomicity: transactions are all or nothing
Consistency: after a transaction your database should be in a consistent state
Isolation: each transaction should be independent
Durability: once a transaction is commited the data is there to stay

These databases drop the Consistency property from the ACID. No schemas, good for unstructured data.

There are different types of NoSQL databases:

The most common examples are MongoDB, Cassandra, Redis, etc.

Can be Relational or NoSQL depending on their design and structure.

Vertical vs Horizontal scaling

Consists of distributing different portions (shards) of the dataset across multiple servers.

Some of the sharding strategies are:

Range-based Sharding: based on the range of a given key
Directory-based Sharding: Lookup service to direct traffic to the correct node
Geographical Sharding: Based on geographic location

Keeping copy of data in multiple servers for high availability.

Master-Slave vs Master-Master replication

Master-Slave replication: strategy where slave instances are only used for READ operations
Master-Master replication: all instances can do WRITE and READ operations

Performance improvement techniques

Some of the most common techniques to optimize performance are:

Caching: In-memory caching is usually used for frequently accessed data
Indexing: Creating an index for frequently accessed columns will significantly speed up retrieval times
Query Optimization: Optimizing queries for fast data access. i.e. minimizing JOINs and using tools to understand query performance.