Question 1

When does a database index hurt performance?

Accepted Answer

Indexes speed up reads by maintaining an ordered structure alongside the table, and that structure has to be kept correct on every write. Every insert, update and delete must update every affected index, so a table with eight indexes does substantially more work per write than one with two.

That cost is the main way indexes hurt. On a write-heavy table it can dominate, and adding an index to fix one slow report can measurably slow the ingestion path that matters more.

Several other effects are worth knowing.

- Storage. Indexes can collectively exceed the size of the table itself, which affects backup times, restore times and how much of the working set fits in memory.
- Redundancy. A composite index on two columns already serves queries filtering on the first alone, so a separate single-column index on that first column is pure overhead. This is a common form of accumulated waste.
- Planner confusion. More candidate indexes means more plans to consider, and with imperfect statistics the planner can choose a worse one. An index that is used but should not be is harder to diagnose than one that is ignored.
- Low selectivity. Indexing a column with very few distinct values, such as a boolean or a status with three states, rarely helps, because matching rows are a large fraction of the table and a sequential scan is cheaper.

The practical discipline is to add indexes in response to measured slow queries rather than in anticipation, and to review them periodically. Most databases expose usage statistics per index, and an index with zero scans since the last restart is costing writes for nothing.

Question 2

Why is my PostgreSQL query still slow after adding an index?

Accepted Answer

An index existing and an index being used are different things. The planner chooses a strategy based on its cost estimates, and it will ignore an index whenever it believes a sequential scan will be

Question 3

When should you use an array rather than a linked list?

Accepted Answer

Almost always — and the textbook answer, which favours linked lists for insertion and deletion, is misleading on modern hardware because it ignores how memory actually behaves. The theoretical

Question 4

What techniques can I use to tune the performance of MongoDB?

Accepted Answer

MongoDB performance tuning techniques include indexing, sharding, schema design, connection pooling, query optimization, and hardware optimization. Each method enhances database efficiency and responsiveness.

Question 5

What is a hash table, and why is lookup constant time?

Accepted Answer

A structure that turns a key into an array index by computing a number from it, so finding an item takes roughly the same time whether the table holds ten entries or ten million. The mechanism. A

Question 6

What are sharding and partitioning?

Accepted Answer

Both split a large dataset into pieces. Partitioning divides a table within one database; sharding distributes data across separate database instances. The distinction matters because one is an

Question 7

What is Big O notation actually telling you?

Accepted Answer

Big O describes how an algorithm's cost grows as the input grows. It is a statement about the shape of the growth curve, not about speed in any absolute sense. What it deliberately ignores: Constant

Related Questions