Postgresql medium
Which index type should be implemented to allow for efficient 'containment' queries on a `jsonb` column (e.g., using the `@>` operator)?
- A B-Tree Index: Optimized for range-based comparisons and equality on scalar data types.
- B Hash Index: Designed specifically for fast equality checks on large variable-length strings.
- C GIN (Generalized Inverted Index): Designed to map composite keys to their containing rows.
- D BRIN (Block Range Index): Optimized for scanning massive tables with naturally sorted data.
Answer: C. GIN (Generalized Inverted Index): Designed to map composite keys to their containing rows.
GIN is the standard choice for JSONB. It indexes the internal keys and values of the JSON structure, allowing the database to instantly locate documents that contain a specific key-value pair without a full table scan.
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?