PostgreSQL on VPS: how to calculate and set work_mem (updated guide)

This is an update to the guide "PostgreSQL on a VPS: how much RAM do you really need". The general approach to RAM sizing (how much to allocate to shared_buffers, how much to leave for the operating system) remains valid. Here we address a different parameter, which often causes confusion precisely because it is not "how much RAM does the VPS have" but "how much RAM can a single operation consume before writing to disk": work_mem.\n\nExamples tested on Ubuntu 24.04 with PostgreSQL 16 (the same commands apply to PostgreSQL 17).\n\n## What is work_mem\n\nwork_mem is the parameter that sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files, according to the official documentation. It is not the total memory allocated to PostgreSQL: it is the ceiling for each sort or hash operation. A complex query can execute multiple sort and hash operations concurrently, and each of these can use up to this amount of memory before starting to write to temporary files; furthermore, multiple sessions can do the same in parallel. Consequently, total consumption can be a multiple of the configured value.\n\nThe default value is four megabytes (4 MB), unchanged between PostgreSQL 16 and 17.\n\nOne detail not covered in the original guide: hash-based operations (hash joins, hash aggregations) are not limited solely by work_mem. The memory limit for a hash table is obtained by multiplying work_mem by hash_mem_multiplier, and the default value of hash_mem_multiplier is 2.0, which means that hash-based operations can use twice the base amount of work_mem. This means that with work_mem = 16MB a hash join can use up to 32 MB before spilling to disk.\n\n## How to view it\n\nsql\nSHOW work_mem;\n\n\nReturns the value active in the current session (e.g. 4MB).\n\n## The calculation formula\n\nThere is no universal value: it depends on how much RAM is free after removing shared_buffers and the margin for the operating system, how many concurrent connections the server must handle (max_connections), and how many sort/hash operations are typically run per query.\n\nStarting formula, conservative:\n\n\nwork_mem = available_RAM_for_queries / (max_connections × average_operations_per_query)\n\n\nExample with an 8 GB VPS, after reserving 2 GB for shared_buffers and operating system: 6 GB remains. With max_connections = 100 and an estimate of 2 average operations per query, the calculation gives 6144 MB / (100 × 2) = approximately 30 MB. In practice, on an entry-level VPS with few concurrent connections, values between 8 MB and 32 MB are the most common starting point before fine-tuning by observing the logs.\n\nThe opposite risk, setting the value too high, is real: with many active connections and complex queries, consumption multiplies and is the most frequent cause of out-of-memory on VPS with limited RAM. Better to start low and increase gradually, observing the effects.\n\n## How to set it\n\nTwo equivalent methods. In the postgresql.conf file (typically at /etc/postgresql/16/main/postgresql.conf on Ubuntu 24.04):\n\n\nwork_mem = 16MB\n\n\nOr from the psql command line, without touching the file:\n\nsql\nALTER SYSTEM SET work_mem = '16MB';\n\n\nALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf, according to the official PostgreSQL 16 documentation.\n\nIn both cases the parameter has "user" context: no server restart is required, just a reload. Values set with ALTER SYSTEM become effective after the next server configuration reload, or after the next restart for parameters that can only be modified at startup. For work_mem simply:\n\n\npg_ctl reload -D /var/lib/postgresql/16/main\n\n\nOr, from within psql:\n\nsql\nSELECT pg_reload_conf();\n\n\nIf you want to change the value only for the current session, without touching the global configuration:\n\nsql\nSET work_mem = '64MB';\n\n\nUseful for testing a value on a single heavy query before making it permanent, or for assigning it to a specific role with ALTER ROLE role_name SET work_mem = '32MB';.\n\n## Verifying the effect with EXPLAIN ANALYZE\n\nThe correct way to know if your chosen value is sufficient is to read the execution plan of a slow query, before and after the change:\n\nsql\nEXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;\n\n\nIn the output, the sort node line (Sort) reports the method used. If Sort Method: external merge Disk: ...kB appears, it means the operation exceeded the available work_mem and spilled to disk, with a cost in response time. If after raising work_mem the same node shows Sort Method: quicksort Memory: ...kB, the operation stays in RAM. This is the practical verification: not a theoretical number, but the execution plan line read before and after the change, on the same query.\n\nThe guide refers to PostgreSQL 16/17 on Ubuntu 24.04; configuration file paths may vary with other distributions.