Postgresql medium
Under what condition can PostgreSQL execute an 'Index-Only Scan' to satisfy a query without visiting the table's heap storage?
- A If the table has been fully vacuumed and the index contains all columns requested in the query.
- B If the query uses the `DISTINCT` keyword on a column that is marked as a Primary Key.
- C If the table is partitioned using the native declarative range-based strategy on date objects.
- D If the database is running on an NVMe SSD that supports direct-access memory mapping.
Answer: A. If the table has been fully vacuumed and the index contains all columns requested in the query.
Postgres needs to check the Visibility Map (maintained by VACUUM) to confirm that the row version is visible to the transaction. If it is, and all data is in the index, it doesn't need to read the table files, saving significant I/O.
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?