Postgresql hard
When should an architect choose the 'SP-GiST' (Space-Partitioned GiST) index type over the standard 'GiST' implementation?
- A When indexing data that is naturally balanced and fits perfectly into an R-Tree hierarchy model.
- B When indexing non-balanced, skewed distributions like URL paths, IP addresses, or disjoint spaces.
- C When the database is running in a multi-tenant environment with highly sensitive user privacy data.
- D When the target columns consist primarily of small, fixed-length integers that are frequently updated.
Answer: B. When indexing non-balanced, skewed distributions like URL paths, IP addresses, or disjoint spaces.
Standard GiST tries to keep its tree balanced (good for shapes). SP-GiST allows for unbalanced structures (like quadtrees or radix trees), which are much more efficient for data that partitions space recursively (like hierarchical strings or IP cidrs).
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?