Cheat SheetsSystem DesignData Management

Data Management — Cheat Sheet

System Design · 7 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org
Data Management
System Design7 topicsQuick revision reference
1

Database Sharding

Sharding partitions data across multiple database instances based on a shard key. It enables horizontal scaling of the data tier but introduces complexity in queries, joins, and rebalancing.

  • ✓Sharding splits data across multiple DB instances based on a shard key — enabling horizontal data scaling.
  • ✓Hash-based sharding distributes evenly; range-based enables efficient range queries; directory-based is most flexible.
  • ✓Choose shard keys that keep related data together to minimise cross-shard queries.
  • ✓Cross-shard joins and transactions are expensive — prefer denormalisation and eventual consistency.
  • ✓Use consistent hashing to minimise data movement when adding shards.
  • ✓Tools like Vitess, Citus, and ProxySQL simplify sharding for MySQL and PostgreSQL.
Conceptual + Java — sharding strategies
// 1. Range-based sharding
// user_id 1–1,000,000      → Shard A
// user_id 1,000,001–2,000,000 → Shard B
// Pros: range queries within a shard are fast
// Cons: hotspots if users are not uniformly distributed

// 2. Hash-based sharding
// shard = hash(user_id) % num_shards
// Pros: even distribution
// Cons: range queries require scatter-gather across all shards
//       adding a shard requires rehashing (use consistent hashing!)

// 3. Directory-based sharding
// Lookup table: { user_id: shard_id }
// Pros: maximum flexibility, easy rebalancing
// Cons: lookup service is a single point of failure and bottleneck

// Shard routing in application code
public class ShardRouter {
    private final int numShards;

    public DataSource getShardFor(String userId) {
        int shard = Math.abs(userId.hashCode()) % numShards;
        return shardDataSources.get(shard);
    }
}
2

Database Replication

Database replication copies data from a primary (master) to one or more replicas (slaves). It improves read throughput, provides fault tolerance, and enables geographic distribution.

  • ✓Single-leader replication: all writes to primary, reads from replicas — simplest and most common.
  • ✓Async replication is fast but risks data loss; sync replication is safe but slower.
  • ✓Replication lag means reads from replicas may return stale data — design for eventual consistency.
  • ✓Automatic failover promotes a replica to primary — monitor replication lag to pick the best candidate.
  • ✓Multi-leader enables multi-region writes but requires conflict resolution (LWW, CRDTs, app-level).
Architecture + Java — read/write splitting
// Single-leader (master-slave) replication
//
// Clients
//   │ writes          │ reads
//   ▼                 ▼
// ┌─────────┐    ┌──────────┐
// │ Primary  │───→│ Replica 1│  (async replication)
// │ (Master) │───→│ Replica 2│
// │          │───→│ Replica 3│
// └─────────┘    └──────────┘
//
// MySQL binlog replication
// PostgreSQL WAL streaming replication

// Spring Boot read/write splitting
@Configuration
public class DataSourceConfig {

    @Bean @Primary
    public DataSource writeDataSource() {
        return createDataSource("jdbc:mysql://primary:3306/app");
    }

    @Bean
    public DataSource readDataSource() {
        return createDataSource("jdbc:mysql://replica:3306/app");
    }
}

// @Transactional(readOnly = true) → routes to read replica
// @Transactional                  → routes to primary
3

SQL vs NoSQL

SQL databases (relational) provide ACID transactions, structured schemas, and powerful joins. NoSQL databases (document, key-value, wide-column, graph) offer flexible schemas, horizontal scalability, and optimised access patterns at the cost of some consistency guarantees.

  • ✓SQL: ACID transactions, strong schema, complex joins — best for structured data with relationships.
  • ✓NoSQL: four types — document, key-value, wide-column, graph — each optimised for specific access patterns.
  • ✓NoSQL scales horizontally more easily; SQL requires sharding which adds complexity.
  • ✓Most large systems use polyglot persistence — the right database for each use case.
  • ✓Start with SQL unless you have a specific reason to choose NoSQL (scale, schema flexibility, access pattern).
SQL — schema, joins, ACID transactions
// SQL strengths: ACID, joins, schema enforcement
// PostgreSQL / MySQL

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    total      DECIMAL(10, 2) NOT NULL,
    status     VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Complex join + aggregation
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.id
HAVING SUM(o.total) > 1000
ORDER BY lifetime_value DESC;

-- ACID transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both or neither
4

Consistent Hashing

Consistent hashing maps both keys and nodes onto a virtual ring so that adding or removing a node only moves a small fraction of keys. It is the backbone of distributed caches, databases, and load balancers.

  • ✓Consistent hashing maps keys and nodes to a ring — only ~1/N keys move when a node is added or removed.
  • ✓Virtual nodes solve uneven distribution by giving each physical node multiple ring positions.
  • ✓Used in Cassandra (token ring), DynamoDB (partitions), Redis Cluster (hash slots), and CDNs.
  • ✓It eliminates the catastrophic key remapping of hash(key) % N when N changes.
  • ✓Combined with replication, it provides both scalability and fault tolerance.
Conceptual — hash ring visualisation
// Consistent hash ring
//
//         0 / 2^32
//          │
//    Node C ●──────── ● Node A
//          │         /
//          │  key1 ●   ← maps to Node A (first CW)
//          │       /
//    key3 ●──────●
//          │     Node B
//          │
//
// Adding Node D between B and C:
//   Only keys between B and D move to D
//   All other keys stay put
//
// Without consistent hashing:
//   hash(key) % 3 → hash(key) % 4 = ~75% of keys remapped!
// With consistent hashing:
//   Only ~1/N keys remapped (where N = number of nodes)
5

Database Indexing

An index is a data structure (usually B-Tree or hash) that speeds up reads by avoiding full table scans. Proper indexing is the single biggest performance lever for database-backed systems.

  • ✓B-Tree indexes support equality, range, sorting — the default choice for most queries.
  • ✓Composite indexes follow the leftmost prefix rule — column order in the index matters.
  • ✓Covering indexes include all queried columns, enabling index-only scans (fastest reads).
  • ✓Every index slows writes — balance read performance against write overhead.
  • ✓Always use EXPLAIN ANALYZE to verify queries are using indexes as expected.
SQL — B-Tree vs Hash indexes
// B-Tree index — most common, supports ranges
CREATE INDEX idx_orders_date ON orders (created_at);

SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY created_at DESC;
-- Uses B-Tree index for range scan + ordering

// Hash index — O(1) equality only (PostgreSQL)
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

SELECT * FROM sessions WHERE token = 'abc123';
-- Hash index: direct lookup, no range support

// B-Tree internals
//         [50]              ← root
//        /    \
//    [20,30]  [70,80]       ← internal nodes
//    / | \    / | \
//  [..] [..] [..] [..]     ← leaf nodes (sorted data pointers)
// O(log n) lookups — 4 levels can index ~1 billion rows
6

ACID vs BASE

ACID (Atomicity, Consistency, Isolation, Durability) guarantees strict transactional correctness in relational databases. BASE (Basically Available, Soft state, Eventually consistent) trades strict consistency for availability and scalability in distributed systems.

  • ✓ACID: strict transactional guarantees — Atomicity, Consistency, Isolation, Durability.
  • ✓BASE: relaxed consistency for scalability — Basically Available, Soft state, Eventually consistent.
  • ✓ACID is expensive in distributed systems (2PC, coordination overhead); BASE scales naturally.
  • ✓Use ACID for financial/critical data; BASE for social feeds, analytics, and high-scale reads.
  • ✓Most microservice architectures use ACID within a service and BASE between services.
SQL — ACID properties demonstrated
// ACID transaction example — bank transfer
BEGIN;
  -- Atomicity: both succeed or both roll back
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;

  -- Consistency: CHECK constraint prevents negative balance
  -- ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

  -- Isolation: other transactions see either the old or new state, not partial
  -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  -- Durability: once COMMIT returns, data survives power failure
COMMIT;

// Isolation levels (weakest → strongest):
// READ UNCOMMITTED → dirty reads possible
// READ COMMITTED   → no dirty reads (PostgreSQL default)
// REPEATABLE READ  → no non-repeatable reads (MySQL InnoDB default)
// SERIALIZABLE     → full isolation (slowest)
7

Data Partitioning Strategies

Data partitioning divides a dataset into smaller subsets for parallel processing and storage. Horizontal partitioning splits rows; vertical partitioning splits columns. The choice of partition key and strategy determines query performance and scalability.

  • ✓Horizontal partitioning splits rows; vertical partitioning splits columns; functional partitioning splits by domain.
  • ✓The partition key must distribute data evenly and match query access patterns.
  • ✓Hot partitions are the #1 problem — use composite keys, salting, or write-sharding to avoid them.
  • ✓Time-based partitioning is great for logs and time-series — old partitions can be archived or dropped.
  • ✓Microservices naturally use functional partitioning — each service owns its database.
Conceptual + SQL — partitioning types
// Horizontal partitioning (sharding)
// Partition by user_id range or hash
//
// Partition 1: user_id 1–1M       (all columns)
// Partition 2: user_id 1M+1–2M    (all columns)
// Partition 3: user_id 2M+1–3M    (all columns)

// Vertical partitioning
// Split wide table into narrow, focused tables
//
// users_core:    id, name, email, created_at    (hot, queried often)
// users_profile: id, bio, avatar_url, settings  (cold, queried rarely)
// users_audit:   id, last_login, login_count    (analytics)

// PostgreSQL declarative partitioning
CREATE TABLE orders (
    id         BIGSERIAL,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total      DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
Learn this free with Aria, your AI tutor → AiCanCode.org/learn/system-design