Data Modeling easy
Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- A It fundamentally disables the database's ability to maintain high availability and prevents the cluster from electing a new primary node.
- B It slows down write operations (INSERT/UPDATE/DELETE) because the database must update the index structure alongside the raw data.
- C It requires the database to switch to a specialized, read-only filesystem mode that prevents new records from being appended to the disk.
- D It strictly limits the total number of rows that can be stored in the table to the maximum depth of the underlying B-Tree structure.
Answer: B. It slows down write operations (INSERT/UPDATE/DELETE) because the database must update the index structure alongside the raw data.
Indices provide a significant read performance boost by allowing the database to perform logarithmic lookups (O(log N)) instead of full table scans (O(N)). However, this optimization is not free: every write operation now incurs the additional cost of updating and rebalancing the index trees, and the index itself consumes extra physical storage space.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?
- You are modeling a 'Likes' counter for a global social network. You choose an AP model. What anomaly must your data model handle?