Postgresql medium
You have a table where 99% of rows are 'Processed' and 1% are 'Pending'. How should you index this to optimize for 'Pending' searches?
- A Implement a Partial Index: index only the rows where the status is explicitly 'Pending'.
- B Implement a Cover Index: include all columns in the index to avoid accessing the heap.
- C Implement a Cluster Index: reorder the physical storage of the table based on status.
- D Implement a Unique Index: ensure that no two rows can have the same timestamp value.
Answer: A. Implement a Partial Index: index only the rows where the status is explicitly 'Pending'.
Partial indexes (using a `WHERE` clause) are much smaller and faster than full indexes. They reduce disk I/O and maintenance overhead by ignoring the 99% of 'uninteresting' data that will never be queried by that specific workflow.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?