Postgresql medium
What is the primary trade-off when using an `UNLOGGED` table for high-speed data ingestion or temporary staging tasks?
- A The table cannot be queried by more than one user at a time due to shared memory locks.
- B The data is not written to the WAL, making it much faster but data is lost during a crash.
- C The table is restricted to integer and boolean data types only to minimize storage complexity.
- D The database engine automatically converts the table to a read-only format after 24 hours.
Answer: B. The data is not written to the WAL, making it much faster but data is lost during a crash.
Unlogged tables offer superior write performance because they bypass the Write-Ahead Log. However, they are not crash-safe; after a server failure or unclean shutdown, unlogged tables are automatically truncated to zero rows.
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?