Database Concepts - kdwivedi1985/system-design GitHub Wiki

Atomicity- Treats a transaction as single unit of work. Commit all or none. e.g Place order will involve set of transactions create Order Header, Order line items, payment record, shipping address in separate table. Successful transaction will create records in all tables and failure of any one of those will revert respective transaction from all tables.
Consistency- Standardization and uniformity across all the records. Follows predefined set of rules and constraints. e.g. schema, pk, foreign key, unique key and not null constraint etc.
Isolation- Each concurrent transactions is executed independently and maintains consistence snapshot of data. It handles the race condition.
Durability- The permanent storage after commit.

Idempotent- Performing one operation multiple times results in same result. e.g. If a client sends the same "create order" request multiple times due to network retries, an idempotent system will recognize the repeated request (via the unique key) and create only one order, ignoring subsequent duplicates. Unique keys/idempotency tokens (like UUIDs) are often used to ensure idempotency.

2PC is Two-Phase Commit in distributed system used to ensure that a transaction across multiple databases or systems either commits completely or rolls back entirely. It ensures data consistency and ACID compliance across distributed systems.
The drawbacks of 2PC is potential blocking issues, performance overhead, and complexity in handling failures.

Optimistic locking assumes that multiple transactions can complete without affecting each other. It allows transactions to proceed without locking the data resources they affect.
Before committing, each transaction verifies that no other transaction has modified the data since it was last read. If a conflict is detected, the transaction is rolled back or retry (re-enforce read/update).
It provides better performance.

Pessimistic locking aims to prevent conflicts by acquiring locks before performing operations, allowing only one transaction to access and modify a resource. A lock is acquired on the resource (e.g., a row in a table) before the transaction starts, and the lock is held until the transaction is completed.

OLTP is used for processing real-time transactions and updates. e.g. Relational databases, used for order, payment processing etc.

OLAP is used for complex data analytics and reporting. It is read-heavy and batch oriented. Uses Relational Db, multiple dimensional(such as time, geography, and product categories etc.)Db or Hybrid. e.g. Snowflake, Redshift etc. mainly used for Business Intelligence and Data warehousing.

CAP theorem helps selecting and designing distributed databases. At a time only one of the two properties are guaranteed. The tradeoff between partition is relevant during network partitions or failures.
If network partition happens choose between Availability and Consistency.

It is an extension to CAP theorem, considers extra element : Latency.
In case of network partitioning, distributed systems must choose between Consistency and Availability. (Same as CAP)
If there is no partitioning, choose between Latency and Consistency.

Vertical Partitioning : Column based split. e.g. In the user table if user_id and passwords are frequently accessed. Create a separate partition for that.Vertical partitioning means splitting a table into multiple tables by columns.
Table Partitioning: Table Partitioning is also referred as Horizontal Partitioning in single database.
- Use when dealing with huge tables and optimize write/read performance. e.g. manage large volumes of time-series, geo, or customer-specific data.
  - e.g. A sales table partitioned by sale_date, so January rows go in one table, February rows in another.
Horizontal Partitioning / Sharding: Horizontal Partitioning is also called Sharding.
A shard is a subset of your data stored in a separate database instance or server.

Range-Based Sharding:
- Data is divided based on a range of values (like IDs, names or dates). Each shard holds a specific range.
  - Pros: Simple and predictable. Efficient for range-based queries.
  - Cons: Can cause hot spots if data isn't evenly distributed
Hash-Based Sharding:
- A hash function (usually on a key like user_id) determines the shard. This is the most commonly used strategy in large-scale systems
  - Pros: Uniform data distribution, avoids hot shards
  - Cons: Harder to query across shards, re-sharding is expensive if number of nodes changes
Directory-Based Sharding:
- Uses a central directory or metadata database to map each record to its respective shard. Gives full control over where each piece of data lives.
  - Pros: Highly flexible and customizable
  - Cons: Directory can become a bottleneck or a single point of failure. if the directory is down your whole system will go down. This risk may be with other strategies too but comparatively less as this table maybe really large.
    - Every read/write needs a lookup in the shard map (directory).
Geo-Based Sharding:
- Data is shared based on the user's geographical location (e.g., continent or country). Commonly used in globally distributed systems.
  - Pros: Reduces latency by keeping data closer to the user
  - Cons: Uneven distribution if regions differ in size

Can we have shards for complete DB or just growing tables and how?

We can have completely separate shards for all data but always better to go with sharding for large growing tables instead of complete schema, that will overkill the resources.
Use sharding when you are dealing with read/write heavy systems

In general, Multi-tenant is an architectural style in cloud applications.
A tenant is typically a customer or client in a Software-as-a-Service (SaaS) application. Each tenant could be an organization, company, or user group.
Multi-tenant architecture means your application is designed to serve multiple tenants from a shared environment — shared codebase, shared infrastructure, and possibly shared databases. e.g. salesforce

Types:

Shared Database, Shared Schema: All tenant share the same db and same schema distinguished by tenant_id
- users (id, tenant_id, name, email)
Shared Database, separate schema: One DB, each tenant has its own schema
- tenant1.users, tenant2.users
Separate Database per tenant: Each tenant has its own database instance
- tenant1_db, tenant2_db
Use sharding when you are dealing with thousand of tanenats.
Use multi-tenancy when you need logical separation
Use multi-tenant + sharding when you have thousands of tenant

What is GIS (Global secondary index)?

A global secondary index is an index that enables fast queries on non-partition key attributes. e.g. other columns of the table.
It covers all records across partitions or shards, and is independent of the table’s primary partition key.
Used mainly in distributed databases (like YugabyteDB, DynamoDB, Citus, or CockroachDB), enables fast queries on non-partition key attributes.
e.g. A time-series app: Partitions data by created_at (monthly). Uses a GSI on user_id to quickly find all events for a given user across months.