Database Optimization

Clustered vs Non-Clustered Index: What is the Difference?

A clustered index determines the physical order in which table rows are stored on disk, while a non-clustered index is a separate structure that stores pointer…

How Does a B-Tree Index Work Internally?

A B-tree index is a balanced, sorted tree of fixed-size nodes where every leaf sits at the same depth, letting the database find any key in O(log n) disk reads…

Hash Index vs B-Tree Index: What is the Difference?

A hash index maps each key to a bucket via a hash function, giving O(1) average-case exact-match lookups but no support for range queries or ordering, while a…

What is a Covering Index in SQL?

A covering index is an index that includes every column a query needs — both the filter/sort columns and the selected columns — so the database engine can answ…

What is a Partial Index and When Should You Use One?

A partial index is an index built over only the subset of rows that satisfy a WHERE condition, rather than every row in the table, which keeps it smaller, fast…

What Are Index Selectivity and Cardinality?

Cardinality is the number of distinct values in a column, and selectivity is the ratio of distinct values to total rows — a high-selectivity column (close to 1…

How Do You Tune Autovacuum in PostgreSQL?

Tuning autovacuum means adjusting how aggressively and how frequently PostgreSQL's background autovacuum workers scan and clean each table, primarily by loweri…

How Do Prepared Statements Improve Database Performance?

A prepared statement is parsed, validated, and compiled into an execution plan once, and then executed repeatedly with different parameter values, so the datab…

What is a Global Secondary Index (GSI) in DynamoDB?

A Global Secondary Index (GSI) in DynamoDB is an alternate partition-and-sort-key view of a table that lets you query items efficiently by attributes other tha…