Master Database Engineering & Design

A database index is a separate data structure that stores a sorted subset of table column values with pointers to full rows, enabling fast data retrieval without full table scans.

Database table partitioning divides a large table into smaller physical segments (partitions) within a single database instance, improving query performance through partition pruning and enabling efficient data archival.

SQL query optimization is the process of analyzing and rewriting SQL queries, adjusting index strategies, and tuning database configuration to minimize query execution time and resource consumption.

A database transaction is a sequence of SQL operations executed as a single atomic unit — all operations succeed together or all are rolled back, leaving the database in a consistent state.

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee database transactions are processed reliably even in the presence of errors, power failures, and concurrent access.

Database normalization is the process of organizing relational database tables to minimize data redundancy and prevent update, insert, and delete anomalies by applying a series of formal rules called normal forms.

Database replication is the process of continuously copying data from one database server (primary/master) to one or more replica servers, enabling read scaling, high availability, and geographic distribution.

Database sharding is a horizontal scaling technique that distributes data across multiple independent database instances (shards), with each shard holding a non-overlapping subset of the total dataset.