Which PostgreSQL configuration parameter controls the amount of memory used for sorting operations before writing to temporary disk files?

The 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).