Database Administrator interview questions

A database parses and validates SQL, transforms it into an internal query tree, chooses an execution plan, and runs that plan.

• Parsing checks syntax and resolves referenced objects, columns, types, and permissions.
• Rewriting may expand views or apply engine rules before planning.
• The optimizer compares legal access paths and join orders using statistics and a cost model.
• The executor runs the chosen operators and returns rows while respecting transaction visibility.

EXPLAIN shows the optimizer's estimated plan, while EXPLAIN ANALYZE executes the statement and adds measured runtime information.

• Estimated rows and costs come from statistics and the engine's cost model.
• Actual rows, loops, and timing reveal how execution differed from estimates.
• Because ANALYZE runs the statement, write queries can change data unless wrapped and rolled back appropriately.
• PostgreSQL options such as BUFFERS expose cache and I/O activity in addition to timing.

These access paths trade startup, random access, and bulk page reading according to estimated selectivity.

• A sequential scan reads table pages and can be efficient when much of the table is needed.
• An index scan follows matching index entries to table rows and suits selective predicates.
• PostgreSQL bitmap scans collect matching row locations first, then visit table pages in a more grouped order.
• The optimizer chooses from estimated page cost, row count, ordering needs, and cache assumptions.

The three join algorithms combine inputs through repeated lookup, hashing, or ordered traversal.

• Nested loop scans the inner input for each outer row and performs well when the outer side is small and inner lookups are indexed.
• Hash join builds a hash table from one input and probes it with the other, commonly for equality joins.
• Merge join advances through two inputs sorted on compatible join keys and can handle large ordered sets efficiently.
• Join condition, input size, sort order, memory, and estimates determine which algorithm is eligible and attractive.

Cardinality estimates predict row counts, while cost estimates compare expected resource work in engine-specific units.

• Costs usually combine modeled page access, CPU processing, sorting, and operator startup.
• They are relative planning values and are not direct milliseconds.
• A row-count error early in the plan can lead to a poor join algorithm or join order later.
• Actual execution metrics are compared with estimates to assess whether statistics and assumptions represent the data.

A planner uses summarized data distribution and table size information to estimate predicate and join cardinality.

• Row and page counts estimate relation size.
• Distinct-value counts and NULL fractions estimate equality and missing-value selectivity.
• Histograms approximate range distributions, while most-common-value lists represent skew.
• Extended or multicolumn statistics can describe correlation and dependencies that independent column statistics miss.

ANALYZE samples table data and updates planner statistics so estimates reflect the current distribution.

• PostgreSQL autovacuum workers can trigger automatic analyze after enough table changes.
• MySQL maintains persistent or sampled statistics with engine-specific refresh behavior.
• The sampling target balances collection cost, catalog size, and precision for skewed data.
• Statistics describe values, not cached query plans forever, so prepared-plan behavior remains a separate concern.

Selectivity is the fraction of rows expected to satisfy a predicate, and it determines how much data each access path must process.

• A unique equality predicate is highly selective because it returns at most one row.
• A Boolean column with evenly split values is usually not selective by itself.
• Skew means a predicate on one common value can behave very differently from one on a rare value.
• The planner estimates selectivity from statistics and combines it with I/O, CPU, and ordering costs.

A sargable predicate lets the optimizer map a search condition to an ordered or otherwise searchable index operation.

• Direct comparison of an indexed column to a compatible value is commonly sargable.
• Wrapping the column in a function can prevent ordinary index use unless a matching expression index exists.
• Implicit type conversion on the indexed side can also block an efficient lookup.
• Rewriting preserves query semantics first, because an index-friendly expression is useless if it changes the result.

The optimizer normally reorders and combines safe predicates according to its plan rather than following their textual order.

• SQL describes the required result, not an imperative sequence of row tests.
• Predicate pushdown can move filters closer to scans or below joins when semantics permit.
• Volatile functions, outer joins, and error-producing expressions can constrain legal rewrites.
• Performance should be evaluated from the execution plan, not inferred from the condition order in the query text.

A covering index contains every column needed to satisfy a query, allowing the engine to avoid or reduce table-row lookups.

• Search and join columns form the searchable index key.
• Additional output columns may be included as non-key payload where the engine supports INCLUDE.
• PostgreSQL index-only scans still depend on visibility information to know whether heap access can be skipped.
• Wider indexes consume more storage and write bandwidth, so coverage should target stable, frequent query shapes.

Column order should follow equality constraints, range boundaries, join keys, and required ordering in the queries the index is meant to serve.

• Leading equality columns narrow the searchable prefix before a later range column.
• Once an unrestricted range is encountered, later columns may filter but often cannot narrow the same index scan efficiently.
• ORDER BY can benefit when its sequence and direction align with the usable index prefix.
• Cardinality alone is not a universal rule because the full predicate and sort shape determine usefulness.

A partial index stores entries only for rows satisfying a fixed predicate, reducing index size and maintenance for targeted queries.

• An index WHERE status = 'open' can serve queries that select the same open-row subset.
• The planner must be able to prove that the query predicate implies the index predicate.
• Partial unique indexes can enforce uniqueness only within a subset, such as active records.
• They are unsuitable when queries need most excluded rows or when the subset definition changes constantly.

An expression index stores the result of a deterministic expression so matching query expressions can be searched directly.

• An index on lower(email) can support case-normalized equality lookups written with the same semantics.
• The query expression must match the indexed expression closely enough for the planner to recognize it.
• Expression indexes can also enforce uniqueness on a normalized representation.
• Computing and maintaining the expression adds cost to writes and increases schema coupling to query form.

The operator and data access pattern determine the index type rather than the column type alone.

• B-tree supports equality, ranges, ordering, and prefix scans for sortable values.
• Hash focuses on equality and offers fewer access patterns than B-tree.
• GIN is an inverted index suited to multivalued content such as arrays, full text, and many JSONB operators.
• GiST supports extensible search strategies such as geometric, range, nearest-neighbor, and some text operations.

An index is most attractive when it avoids enough table work to offset traversal and row-fetch cost.

• A predicate returning a tiny fraction of rows often benefits from an index.
• A common value returning most rows may be cheaper to read sequentially.
• Clustering, cache state, row width, and index-only coverage can change the crossover point.
• Skew-aware statistics matter because average distinct counts can misrepresent popular and rare values.

An index can supply matching keys or useful ordering when its leading columns align with the operation.

• An index on a foreign key can make repeated inner lookups in a nested-loop join efficient.
• Ordered index access can avoid a separate sort for a compatible ORDER BY.
• GROUP BY may exploit ordered input for grouped aggregation, though a hash aggregate can still be cheaper.
• One index rarely serves every filter, join, and order permutation, so query families need explicit prioritization.

Each index consumes storage and adds work to data modification, logging, caching, and maintenance.

• INSERT must add entries to every applicable index.
• UPDATE may change indexed keys or invalidate versions, while DELETE leaves cleanup work according to the engine.
• Random page changes, page splits, and accumulated dead space can increase write amplification.
• Redundant indexes waste resources and may overlap without serving a distinct constraint or query pattern.

Multiversion concurrency control lets transactions read consistent row versions while other transactions modify the same logical data.

• Readers use a snapshot to decide which versions are visible.
• Writers create or reference new versions instead of overwriting a version visible to every reader immediately.
• This reduces reader-writer blocking but does not eliminate writer conflicts or all locks.
• Old versions require cleanup or reuse after no relevant transaction can see them.

A transaction snapshot defines which committed and in-progress changes are visible to a statement or transaction.

• It records a visibility boundary derived from transaction identifiers or engine-specific version metadata.
• Read Committed commonly acquires a fresh snapshot for each statement.
• Repeatable Read commonly holds one transaction-level view, subject to engine semantics.
• A long-lived snapshot can keep old row versions from becoming reclaimable.