Databases

In real-world systems, applications constantly generate and rely on data such as user accounts, transactions, messages, and logs. Storing this data directly in application memory is not sufficient, because memory is temporary and disappears when the program stops or crashes.

Databases solve this by providing durable storage. Data is written to disk in a way that survives restarts and system failures. This is what allows systems like banking platforms, social networks, and e-commerce sites to maintain long-term state.

Beyond storage, databases provide structured ways to access data. Instead of manually scanning through files, developers can use queries to retrieve exactly what they need. For example, fetching a user by email or retrieving all orders from the past week.

Databases also enforce consistency rules, ensuring that data remains valid even when multiple operations occur simultaneously. Without this, systems would easily corrupt data under concurrent access.

At a high level, the database acts as the system of record. The application processes requests, but the database is responsible for storing and managing the data those requests depend on.

SQL databases follow the relational model, where data is organized into tables. Each table represents an entity (such as users or orders), and each row represents a record within that entity.

A key feature is the schema. Before storing data, you define the structure, including column types and constraints. This ensures that all data follows a consistent format, which reduces errors and maintains integrity.

Relationships between tables are central to SQL systems. For example, a user table can be linked to an orders table through a user_id, allowing the system to connect related data efficiently.

SQL databases also support powerful query capabilities. Using joins, filters, and aggregations, developers can retrieve complex datasets across multiple tables in a single query.

This structured approach provides strong consistency and reliability, which is why SQL databases like PostgreSQL and MySQL are widely used in systems where correctness and data integrity are critical.

Unlike SQL databases, NoSQL systems do not require a predefined schema. This means each record can have a different structure, making them useful when data is unstructured or frequently changing.

Different NoSQL databases optimize for different use cases. Document databases like MongoDB store JSON-like objects, key-value stores like Redis provide extremely fast lookups, wide-column databases like Cassandra handle massive distributed datasets, and graph databases model relationships directly.

This flexibility makes NoSQL systems easier to scale horizontally across many servers, which is critical for large-scale applications handling high traffic.

The tradeoff is that many NoSQL systems sacrifice strict consistency or complex relational queries in favor of performance and scalability. Choosing between SQL and NoSQL depends on the structure of the data and the requirements of the system.

When a query runs without an index, the database must check each row one by one to find matching results. This is called a full table scan, and it becomes extremely slow as the dataset grows.

An index acts like a lookup structure (similar to a book index). Instead of scanning everything, the database can jump directly to the location of the data. This significantly reduces query time, especially for large tables.

For example:

CREATE INDEX idx_users_email
ON users(email);

This creates an index on the email column, allowing queries like "find user by email" to execute much faster.

However, indexes are not free. Every time data is inserted, updated, or deleted, the index must also be updated. This makes writes slightly slower and increases storage usage.

Effective database design involves choosing the right columns to index—typically those frequently used in search conditions, filters, or joins.

In many systems, operations are not isolated. For example, transferring money between two accounts requires updating both balances. If one update succeeds and the other fails, the system becomes inconsistent.

Transactions solve this by grouping operations together:

BEGIN
UPDATE account A
UPDATE account B
COMMIT

If any step fails during execution, the database performs a rollback, undoing all previous changes in the transaction.

This guarantees that partial updates never occur. Either the entire operation completes successfully, or the system returns to its previous state.

Transactions are critical in systems where correctness matters, such as financial systems, inventory tracking, and booking systems. Without them, failures or concurrency issues would easily corrupt data.

ACID is a set of four properties that relational databases enforce to ensure correctness during transactions.

Atomicity means a transaction is indivisible. If any step fails, the entire transaction is rolled back, preventing partial updates.

Consistency ensures that after a transaction completes, the database adheres to all rules such as constraints, relationships, and data validity. Invalid states are never allowed.

Isolation ensures that multiple transactions running at the same time do not interfere with each other. Each transaction behaves as if it is running alone, even in a concurrent system.

Durability guarantees that once a transaction is committed, the data is permanently stored, even if the system crashes immediately after.

Together, these properties allow databases to handle failures, concurrency, and complex operations without corrupting data. They are foundational to building reliable systems where correctness matters.

When you send a query to a database, it does not blindly execute it step by step. Instead, the database first analyzes the query using a component called the query planner.

The planner evaluates multiple possible ways to execute the query and selects the most efficient one. This includes decisions like whether to use an index, how to join tables, and the order of operations.

For example, if a query searches for a user by email and an index exists on that column, the database will use the index rather than scanning every row. This can reduce execution time from seconds to milliseconds on large datasets.

The result of this process is an execution plan, which defines exactly how the query will run internally.

Query optimization becomes critical as data grows. Poorly optimized queries can cause slow responses, high CPU usage, and system bottlenecks, while well-optimized queries keep systems fast and scalable.

Replication is used to keep multiple copies of the same data on different database servers. The most common setup is a primary-replica model.

In this model, the primary database handles all write operations. Any changes made are then propagated to replica databases. These replicas maintain copies of the data and can serve read requests.

This setup provides several benefits. If the primary database fails, a replica can take over, improving system availability. It also allows systems to scale read traffic by distributing queries across multiple replicas instead of overloading a single database.

However, replication introduces complexity. There can be slight delays between when data is written to the primary and when it appears on replicas (replication lag). This means replicas may not always have the most up-to-date data.

Despite this tradeoff, replication is a core technique in building scalable and fault-tolerant backend systems.

As data grows, a single database server eventually becomes a bottleneck. Sharding solves this by splitting data into smaller pieces, with each shard stored on a different server.

For example:

Shard 1 → users 1–1M
Shard 2 → users 1M–2M
Shard 3 → users 2M–3M

Requests are routed to the correct shard based on the data being accessed. This allows systems to scale horizontally and handle much larger workloads.

The tradeoff is added complexity, especially when queries need data from multiple shards.