POSTGRESQL-ESSENTIALS practice question 5 of 15
Which PostgreSQL configuration parameter controls the amount of memory used for sorting operations before writing to temporary disk files?
Full set: 71 questionsThe workmem parameter specifies the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before it starts writing data to temporary disk files. This setting is applied independently to each sort or hash operation within a query. Complex queries may have several such operations running in parallel, each potentially using up to the workmem limit. Setting this value appropriately is crucial for performance, as in-memory sorts are significantly faster than disk-based sorts.
A. sharedbuffers: This parameter allocates memory for the main database buffer cache, which is shared among all server processes to hold data pages read from disk.
C. maintenanceworkmem: This is a separate memory pool used specifically for large maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
D. tempbuffers: This parameter sets the maximum memory for session-local buffers used for accessing temporary tables, not for internal sorting or hashing operations.
1. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.4. Resource Consumption.
On workmem: "Specifies the base maximum amount of memory to be used by a query operation (e.g., a sort or hash table) before writing to temporary disk files... Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to write data into temporary files."
On other parameters: The same section provides distinct definitions for sharedbuffers, maintenanceworkmem, and tempbuffers, clarifying their different roles in memory management.
2. Pavlo, A. (2023). 15-445/645 Intro to Database Systems Courseware, Carnegie Mellon University, Lecture #19: Query Execution II.
The lecture materials discuss query execution and memory management, explaining that operators like sorting have a memory budget (corresponding to workmem in PostgreSQL) and will spill to disk if that budget is exceeded. This confirms the role of workmem in controlling sort operations.
3. Stonebraker, M., & Rowe, L. A. (1986). The design of POSTGRES. ACM SIGMOD Record, 15(2), 340-355.
While a foundational paper, it outlines the architectural principles of managing resources for query processing, which are the basis for modern parameters like workmem. The design emphasizes efficient resource utilization for complex operations like sorting. DOI: https://doi.org/10.1145/16856.16888 (Section 5 discusses the executor and processing model).
Keep practicing
Take the free POSTGRESQL-ESSENTIALS practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other POSTGRESQL-ESSENTIALS practice questions
- Question 1What is the purpose of the autovacuum parameter in PostgreSQL?
- Question 2Which psql meta-command is used to display all roles and their attributes?
- Question 3SERIAL and IDENTITY columns can be used to add an auto-incrementing column in a Postgres …
- Question 4You are connected to a PostgreSQL database. After executing a complex query an error mess…
- Question 6You want to set the maximum time to complete client authentication to 10 seconds. Which p…
- Question 7You need to configure your server to accept TCP/IP connections from specific IP addresses…
