Comparison Questions - Part 2

A B-Tree index keeps keys in a sorted balanced tree structure, supporting range queries, sorting, and inequality operators (<, >, <=, >=). A Hash index relies on a hash table mapping, which executes point lookups extremely fast (= operators) but cannot support range queries or partial index scans.

A Physical View describes the actual storage layout, file locations, indexing structures, and hardware organization of data on disk blocks. A Logical View describes how data is structurally presented to application users, abstracting away disk layout using conceptual tables and virtual representations.

Two-Phase Locking (2PL) is a concurrency control protocol used within a single database instance to ensure serializability by expanding and contracting lock phases. Two-Phase Commit (2PC) is an atomic distribution protocol ensuring that a transaction commits or aborts across multiple physical nodes in a cluster.

Strong Consistency guarantees that any read operation immediately following a write returns the newly updated value across all nodes. Eventual Consistency acknowledges updates immediately on one node, while background replication asynchronously synchronizes other replicas, allowing temporary stale reads.

Row-Oriented storage groups all column values of a single record together sequentially on disk, optimizing write-heavy transactional operations (OLTP). Column-Oriented storage packs all values of the same column across all records together, minimizing disk I/O during aggregate analytical operations (OLAP).

Optimistic Concurrency Control checks for modifications right before committing a transaction without acquiring pre-emptive locks, assuming conflicts are rare. Pessimistic locking explicitly locks resources at data access time to block concurrent updates, assuming structural data conflicts are highly frequent.

A Deadlock occurs when two or more active transactions are stuck in a total standstill because each holds a resource the other needs to proceed. A Live Lock occurs when transactions continuously change their state or repeatedly retry operations in response to each other without making any forward progress.

Sharding is a horizontal architecture that distributes different subsets of a database table across entirely separate physical server nodes. Partitioning splits a large table into distinct, smaller logical chunks (like by date ranges) managed within a single database instance.

Vertical Scaling (Scaling Up) involves adding more processing resources (RAM, CPU, SSD capacity) to a single database server. Horizontal Scaling (Scaling Out) involves connecting multiple independent database servers together into a distributed system to share the processing load.