🗄️ Relational Databases (RDBMS)

Application code is transient, but data is permanent. A Programmer treats the database schema as the ultimate source of truth and understands that database bottlenecks are often the hardest to scale.

💎 ACID Transactions

A transaction is a sequence of operations performed as a single logical unit of work.

Atomicity: All operations succeed, or none do (All or nothing).
Consistency: The database transforms from one valid state to another, maintaining all constraints (Foreign keys, unique indexes).
Isolation: Transactions occurring concurrently do not interfere with each other.
Durability: Once a transaction is committed, it remains so, even in the event of a power loss or crash.

⚡ Indexing: The Key to Performance

Indexes are data structures (usually B-Trees or Hash Tables) that improve the speed of data retrieval at the cost of additional writes and storage.

Clustered Index: Defines the physical order of data in the table (usually the Primary Key).
Non-Clustered Index: A separate structure that points to the data rows.
Composite Index: An index on multiple columns. Crucial: The order of columns matters (Left-to-right rule).
Covering Index: A special case where the index itself contains all the data required for the query, avoiding a “Bookmark Lookup” to the main table.

🔒 Locking & Concurrency Control

To maintain Isolation, databases use locks to prevent multiple processes from modifying the same data simultaneously.

Lock Types:

Shared (S) Lock: Used for Read operations. Multiple transactions can hold a shared lock.
Exclusive (X) Lock: Used for Write operations. Only one transaction can hold this lock.

Common Issues:

Deadlocks: When Transaction A waits for B, and B waits for A. The engine must kill one to resolve the cycle.
Optimistic Locking: Assume no conflict. Check a version number before committing. Great for high-read/low-write systems.
Pessimistic Locking: Lock the record as soon as it’s read. Necessary for high-contention financial operations.

📏 Normalization vs. Denormalization

Normalization (1NF, 2NF, 3NF)

Goal: Eliminate data redundancy and protect data integrity.
When to use: In OLTP (Online Transactional Processing) systems where data accuracy is paramount.

Denormalization

Goal: Improve read performance by reducing the number of JOIN operations.
When to use: In OLAP (Online Analytical Processing) or high-scale read-heavy systems where “stale” or redundant data is an acceptable trade-off for speed.

⚖️ Database Decisions

Isolation Levels

As a Programmer, you must choose the right balance between Consistency and Performance:

Read Uncommitted: Fastest, but allows “Dirty Reads”.
Read Committed: Prevents dirty reads (Default for most DBs).
Repeatable Read: Prevents “Non-repeatable reads”.
Serializable: Highest isolation, but significantly reduces concurrency.

Connection Pooling

Never open a new DB connection for every request. Use a pool (like PgBouncer or HikariCP) to reuse connections, as the TCP handshake and authentication overhead are expensive.

SQL Server Connection Pooling is a technique used by client libraries (such as ADO.NET, ODBC, JDBC) to reuse existing database connections instead of creating and destroying a new connection every time an application talks to SQL Server.

The goal is to improve performance because opening a database connection is relatively expensive.

💡 Note: Don’t put business logic in Stored Procedures unless absolutely necessary for performance. It makes version control, testing, and horizontal scaling significantly harder. Keep the DB “dumb” (data storage/integrity) and the App “smart” (logic).