Postgresql hard
Starting with PostgreSQL 12, what major change was made to the way Common Table Expressions (CTEs/`WITH` clauses) are handled by the query planner?
- A CTEs are now automatically encrypted in the WAL to prevent data leakage during asynchronous replication.
- B Non-recursive CTEs can be 'inlined' into the main query, allowing for cross-boundary optimization.
- C Recursive CTEs are now required to specify a maximum depth limit to prevent infinite loops at runtime.
- D The results of all CTEs are physically written to the `pg_temp` directory as unlogged columnar files.
Answer: B. Non-recursive CTEs can be 'inlined' into the main query, allowing for cross-boundary optimization.
Before PG 12, a CTE was an 'optimization fence'—it was always run and materialized first. Inlining allows the planner to 'push' filters from the main query down into the CTE, often leading to massive speed improvements as less data is processed.
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?