• What is the Database?
• Why Data is not easy subject?
• ACID: The Standard for Reliability
• The Persistence Spectrum: Beyond "Disk vs RAM"
• Data Structures: How it’s Organized
• The Query Lifecycle: How Data is Fetched
• Indexing: The "Library Card Catalog"
• Storage Models: Rows vs. Columns
• Row-Oriented (OLTP)
• Column-Oriented (OLAP)
• Connection Pooling
• Summary

What is the Database?

Database — is just a server, if your server can read data and save data. Then other computers can connect to your computer and request the data or write the data.

Persistence is very important quality of database. If issue or outage occurs, data should be still there. If data is saved in Disk, the data must be always there. In contrast, if data is saved in Memory, data may be lost. But reading data from memory is faster.

Disk → Persistence ← Memory

Why Data is not easy subject?

Tradeoffs, because there are a lot of things databases can offer:

• Database have some structure in the way it stores the data.
• Availability - uptime of the system. If database goes down and database is the most critical part of the system. Then we should use distributed database.
• How do you store the data across multiple machines? Do you replicate data or split up the data into parts? Then it could lead us to consistency issue. Consistency is about — will we get up-to-date data or stale data.

ACID: The Standard for Reliability

When we say "persistence is important," architects usually define that through ACID properties. This is the contract a database makes with the developer to ensure that even if the power goes out mid-operation, the data remains valid.

• Atomicity: The "All or Nothing" rule. If you are transferring money, the withdrawal and the deposit must both happen, or neither happens.
• Consistency: The database moves from one valid state to another, following all predefined rules (like "Account balance cannot be negative").
• Isolation: Transactions happening at the same time shouldn't interfere with each other. It should feel like they are running one after another.
• Durability: Once the system tells you the write was successful, that data is permanently stored (usually via the Write-Ahead Log we discussed).

The Persistence Spectrum: Beyond "Disk vs RAM"

• WAL (Write-Ahead Logging): Most "Disk" databases actually write to an append-only log first. This ensures that even if the system crashes before the data is neatly organized in the main storage, it can be recovered.
• SSD vs. HDD: We no longer just say "Disk." Modern designs optimize for IOPS (Input/Output Operations Per Second). NVMe drives have narrowed the gap between memory and storage, changing how we design indexes.
• In-Memory with Snapshots: Systems like Redis provide the speed of RAM but periodically "checkpoint" or snapshot data to the disk, giving you a hybrid of speed and safety.

Data Structures: How it’s Organized

• B-Trees: Used by most Relational DBs (PostgreSQL, MySQL). Optimized for heavy reads and range queries.
• LSM Trees (Log-Structured Merge-Trees): Used by NoSQL DBs (Cassandra, RocksDB). Optimized for high-velocity writes.

The Query Lifecycle: How Data is Fetched

A database isn't just a passive box; it’s a high-performance engine. When a "request" comes in, it goes through several stages:

1. The Transport: The server accepts the connection (TCP/TLS).
2. The Parser: It breaks down your query (SQL or otherwise) to understand what you want.
3. The Optimizer: This is the "brain." It looks at the available Indexes and decides the fastest path to the data.
4. The Execution Engine: It actually pulls the bits from the Disk or Memory.

Indexing: The "Library Card Catalog"

If a database has to scan every single row to find one user, it will be slow. To solve this, we use Indexes.

• Primary Index: Usually a B-Tree structure that points directly to the physical location of the data.
• Secondary Index: Additional "shortcuts" (e.g., indexing by email instead of ID).
• Trade-off: Indexes make Reads lightning fast, but they make Writes slower because every time you add data, you also have to update the index.

Storage Models: Rows vs. Columns

How you arrange data on the disk changes what your server is "good" at.

Row-Oriented (OLTP)

Data for one record is stored together (e.g., ID, Name, Email).

• Best for: Online Transaction Processing. When you need to look up a specific user or update a single profile.
• Examples: PostgreSQL, MySQL.

Column-Oriented (OLAP)

All values for a specific column are stored together (e.g., all Ages are together, all Prices are together).

• Best for: Online Analytical Processing. When you want to calculate the "Average Price of 1,000,000 items." The CPU doesn't have to skip over Names and Emails to find the Prices.
• Examples: ClickHouse, Snowflake, BigQuery.

Connection Pooling

Opening a new connection to a database is "expensive" (it takes time and memory). Architects use a Connection Pool—a cache of pre-opened connections that the application can "borrow" and "return," preventing the database server from being overwhelmed by too many simultaneous handshake requests.

Summary