Database Performance

What is an Index in a Database?

A database index is a data structure that speeds up read queries by letting the database find rows without scanning the entire table, at the cost of extra stor…

What is a Stored Procedure in SQL?

A stored procedure is a precompiled, named block of SQL statements saved inside the database that can be executed repeatedly with a single call, optionally acc…

What is a Materialized View?

A materialized view is a database object that stores the physical result of a query on disk, unlike a regular view which recomputes its result every time it is…

What is a Query Execution Plan?

A query execution plan is the step-by-step, low-level operation tree the database engine builds to actually run a SQL statement — which access paths, join orde…

What Does EXPLAIN ANALYZE Do?

EXPLAIN ANALYZE actually runs the query and returns the real execution plan annotated with true measured timings and row counts for every operator, unlike plai…

Cost-Based vs Rule-Based Query Optimization: What is the Difference?

A rule-based optimizer picks an execution plan by applying a fixed priority ranking of access methods regardless of the actual data, while a cost-based optimiz…

Index Scan vs Sequential Scan: When Does the Optimizer Choose Each?

A sequential scan reads every row of a table in physical order to find matches, while an index scan uses an index structure to jump directly to only the rows t…

How Does the Nested Loop Join Algorithm Work?

A nested loop join works by iterating over every row of an outer table and, for each one, scanning or probing an inner table to find matching rows, making it t…

What is the VACUUM Process in PostgreSQL?

VACUUM is a PostgreSQL maintenance operation that reclaims storage occupied by dead tuples — rows left behind by UPDATE and DELETE under PostgreSQL's MVCC mode…

What Causes Table Bloat and How Do You Fix It?

Table bloat is the accumulation of dead or wasted space inside a table's data pages — usually because updates and deletes leave dead tuples faster than autovac…

What is Index Bloat and When Should You Reindex?

Index bloat is wasted space inside a B-tree index caused by deleted or updated entries whose slots are not fully compacted back, and the fix is to run REINDEX…

What is a Reliable Methodology for Tuning a Slow SQL Query?

A reliable query-tuning methodology starts with measuring, not guessing: capture the query's actual execution plan, identify the single most expensive operatio…

How Do You Analyze a Slow Query Log to Find Optimization Targets?

Analyzing a slow query log means aggregating logged queries by their normalized pattern and ranking them by total cumulative time, not by the single slowest in…

What is the N+1 Query Problem and How Do You Fix It?

The N+1 query problem is a performance anti-pattern where code runs one query to fetch a list of N parent records, then runs one additional query per parent to…

ORM Lazy Loading vs Eager Loading: What is the Difference?

Lazy loading fetches an object's related data only at the moment it is actually accessed in code, issuing a separate query on demand, while eager loading fetch…

What Query Batching Strategies Reduce Database Round Trips?

Query batching combines what would be many individual database round trips into fewer, larger operations — such as a single multi-row INSERT, a batched IN clau…

How Do You Tune a Database Connection Pool?

A connection pool is tuned by sizing it to the database's actual concurrency capacity rather than the application's thread count, then adjusting timeouts and e…

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 the Cache-Aside (Lazy-Loading) Caching Pattern?

Cache-aside is a caching pattern where the application code itself checks the cache first, and on a miss reads from the database, populates the cache with that…

Write-Through vs Write-Back Cache: What is the Difference?

A write-through cache writes to the cache and the underlying database synchronously as one operation before confirming success, while a write-back (write-behin…

What are the Main Cache Invalidation Strategies?

Cache invalidation removes or refreshes stale cached data using three common strategies: TTL-based expiration (entries auto-expire after a fixed time), explici…

Redis as a Cache vs a Primary Data Store: What Changes?

Using Redis as a cache means treating it as disposable, rebuildable-from-source data that the application never needs to survive as its only copy, whereas usin…

What are the Trade-offs of Caching Database Query Results?

Caching query results trades a risk of serving stale data and added system complexity for dramatically lower read latency and reduced load on the database, and…

What is Lock Escalation in Databases?

Lock escalation is when a database engine converts many fine-grained row-level or page-level locks held by a single transaction into one coarser table-level lo…