Postgresql hard
PostgreSQL 11+ introduced JIT compilation using LLVM. Why might JIT actually degrade the performance of high-velocity OLTP workloads?
- A JIT disables the use of parallel workers, forcing every single transactional query into a serial path.
- B The time required to compile the query plan can exceed the physical execution time of small queries.
- C JIT forces the database to use uncompressed memory pages, leading to massive cache thrashing.
- D The LLVM compiler requires a dedicated physical CPU core that is isolated from the rest of the OS.
Answer: B. The time required to compile the query plan can exceed the physical execution time of small queries.
JIT optimization (compiling SQL expressions to machine code) is powerful for long-running analytical queries. However, for a 1ms query, spending 20ms on compilation is a net loss. This is managed by the `jit_above_cost` setting.
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?