Postgresql medium
How can an administrator create an index on a large production table without locking out concurrent insert and update operations?
- A Use the `INDEX BACKGROUND` flag to offload the build to an idle maintenance worker thread.
- B Use the `INDEX ONLINE` flag to allow the table to re-order itself during live application traffic.
- C Use the `CONCURRENTLY` keyword to build the index without acquiring an 'Access Exclusive' lock.
- D Use the `DEFERRED` keyword to delay the physical index creation until the next cluster reboot.
Answer: C. Use the `CONCURRENTLY` keyword to build the index without acquiring an 'Access Exclusive' lock.
`CREATE INDEX CONCURRENTLY` performs the build in two scans, avoiding the exclusive lock that normally halts all writes. This is a critical operational tool for managing data at scale without incurring downtime.
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?