Indexes are data structures — typically B-trees — that speed up lookups at the cost of additional storage and slower writes. A clustered index determines the physical order of data in the table, so a table can have at most one; non-clustered indexes are separate structures containing pointers back to the data rows, and a table may have many. A covering index includes every column needed by a query, allowing the database to satisfy the request without touching the underlying table at all. The trade-off appears on writes: every INSERT, UPDATE, and DELETE must maintain the indexes alongside the data, so heavily indexed tables pay a price on write-heavy workloads.
Keys enforce the structural integrity of your data. A primary key uniquely identifies each row in a table, cannot be NULL, and there can be only one per table. A unique key also enforces uniqueness but allows NULLs (typically one per column in many engines) and you can declare several per table. A foreign key links a column to the primary key (or unique key) of another table, enforcing referential integrity by rejecting values that have no matching parent. When uniqueness requires more than one column, a composite key spans two or more columns and treats their combination as the identifier. Views and materialized views offer different read-side trade-offs: a view is a stored query re-executed each time it is referenced, while a materialized view stores the result physically and must be refreshed on a schedule or on demand, trading freshness for query speed.
Understanding query performance requires reading query plans. The execution plan is the sequence of steps the database engine uses to run a query, including join order, index usage, and aggregation strategy. In PostgreSQL, EXPLAIN shows the planned steps and EXPLAIN ANALYZE actually executes the query and reports real timings and row counts. A sequential scan reads every row of the table, used when no suitable index exists or when the table is small enough that scanning is faster; an index seek locates qualifying rows directly via the B-tree and is efficient for selective predicates, while an index scan walks an entire index range — still smaller than the table but not as targeted as a seek.
Data modification statements also have important distinctions. UPDATE modifies existing rows and DELETE removes them; DELETE can use WHERE, fires triggers, and is fully logged. TRUNCATE drops all rows quickly, cannot use WHERE, cannot be rolled back in some engines, and resets identity counters. UPSERT inserts a new row or, if a row with the same key already exists, updates the existing one — PostgreSQL uses INSERT ... ON CONFLICT DO NOTHING or DO UPDATE, MySQL uses INSERT IGNORE or ON DUPLICATE KEY UPDATE. Transactions group statements so they either all succeed (COMMIT) or all fail (ROLLBACK), and reliable transaction systems guarantee the ACID properties: Atomicity, Consistency, Isolation, and Durability. The four standard isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE — trade concurrency against anomalies such as dirty reads (seeing uncommitted data), non-repeatable reads (the same row returning different values within one transaction), and phantom reads (range queries returning different row sets). When two transactions each hold a lock the other needs, a deadlock occurs; the database detects the cycle and aborts one transaction so the other can proceed.