Indexing & Denormalization Strategies Quiz

Q1. Which index structure is optimized for high write throughput by batching writes and later merging data (compaction)? B-tree index
Log-Structured Merge tree (LSM-tree)
Inverted index
Bitmap index

Q2. Which type of index is typically used for full-text search, storing a mapping from terms to the documents or rows that contain them? Inverted index
Hash index
B-tree index
Bitmap index

Q3. Which of the following best describes a covering index? An index on multiple columns of a table.
An index that includes all columns of the table.
An index created to enforce foreign key constraints.
An index that contains all columns needed by a query, allowing it to be answered from the index alone.

Q4. Which is an example of a composite index? An index on (Column1, Column2) of a table.
An index on a subset of rows satisfying a condition.
An index including all columns needed by a query (covering index).
An index on a computed expression, e.g. LOWER(Column1).

Q5. What is a partial index in a relational database? An index that includes only some columns of the table.
An index that spans multiple tables to speed up joins.
An index defined with a WHERE clause so it only indexes certain rows.
An index that is not fully built until a query is executed.

Q6. What is a likely effect of having many secondary indexes on a heavily updated (write-intensive) table? Each write will be slower because it must update multiple indexes (increasing write amplification).
Write performance stays the same, since indexes only affect read queries.
The database will automatically reorganize the table into third normal form to reduce writes.
The table's primary key changes every time a new secondary index is added.

Q7. In an eventually consistent database, what issue can arise with secondary indexes? They eliminate the need for a primary key on the data.
Index updates might lag behind, so index queries can return stale data.
All queries using secondary indexes become strongly consistent reads.
They avoid writing to the base table, improving write throughput.

Q8. Why might a database design deliberately denormalize data? To strictly enforce third normal form (3NF).
To reduce data redundancy and save storage space.
Because relational databases cannot perform join operations.
To speed up read queries by avoiding joins, at the cost of duplicating data and complicating writes.

Q9. Which index type is most suitable for efficiently querying a JSONB column for a specific key-value pair in PostgreSQL? A GIN index on the JSONB column.
A B-tree index on the JSONB column (treating it as text).
A GIST index on the JSONB column.
A hash index on the JSONB data.

Q10. How can you index a computed value or expression (e.g. LOWER(column)) in PostgreSQL? Create a partial index that covers only the computed values.
It isn't possible to index a computed expression in a SQL database.
Create a functional index on the expression (e.g., an index on LOWER(column)).
Use a GIN index, since it can index arbitrary expressions.

Q11. What do database statistics (e.g., histograms on column data) help the query optimizer do? Store precomputed results of frequent queries for faster retrieval.
Estimate how many rows a query will return and the cost of operations, to choose an efficient plan.
Ensure the query returns only distinct (non-duplicate) results.
Enforce referential integrity (foreign keys) during query execution.

Q12. What does the SQL EXPLAIN command do? Executes the query and returns the result along with performance metrics.
Checks the query for syntax errors without executing it.
Rebuilds all indexes used by the query to ensure they're optimal.
Displays the query execution plan with estimated costs, without actually running the query.

Q13. Which maintenance task can help reduce index bloat in a PostgreSQL database? Increasing the index fill factor to 100%.
Backing up the table and restoring it from the backup.
Running VACUUM or REINDEX to reclaim space from the index.
Running EXPLAIN on the table to update index statistics.

Q14. If the 99th percentile (p99) query latency is high while the average latency remains low, what does this suggest? All queries are running slower than normal.
A small subset of queries are much slower than most (high tail latency), perhaps due to issues like index bloat or other outliers.
The median latency is equally high (close to the 99th percentile).
There's no performance issue as long as the average latency is low.

Q15. Which of the following accurately describes a difference between a DynamoDB Global Secondary Index (GSI) and a Local Secondary Index (LSI)? Global Secondary Indexes must be defined when the table is created, whereas Local Secondary Indexes can be added to an existing table.
Local Secondary Indexes each have their own separate throughput capacity, unlike Global Secondary Indexes.
A Global Secondary Index can use a different partition key than the base table, whereas a Local Secondary Index shares the base table's partition key.
DynamoDB secondary indexes are always strongly consistent, similar to indexes in relational databases.

system-design