Postgresql medium
Which SQL feature should be used to efficiently traverse a self-referencing relationship, such as an organizational chart or file directory system?
- A LATERAL JOIN: Allows a join to reference columns from previously matched tables in the plan.
- B WITH RECURSIVE: A Common Table Expression (CTE) that iteratively calls its own result set.
- C WINDOW FUNCTION: Performs a calculation across a set of table rows related to the current row.
- D PARTITION BY: Groups the result set into distinct sub-sets for aggregate-level calculations.
Answer: B. WITH RECURSIVE: A Common Table Expression (CTE) that iteratively calls its own result set.
`WITH RECURSIVE` allows you to traverse graphs and trees in a single SQL statement. This is much more efficient than fetching data and building the hierarchy in the application layer.
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?