# maintenance_work_mem

> The memory ceiling for maintenance commands such as VACUUM, CREATE INDEX, and adding foreign-key constraints.
---

> [!NOTE]
> **Fact — official short description:** “Sets the maximum memory to be used for maintenance operations.”

## Identity {#identity}

| Field | Value | Meaning |
| --- | --- | --- |
| Type | `integer` | Upstream pg_settings type |
| Context | `user` | Settable by an ordinary user |
| Unit | `kB` | Raw unit |
| Range | `64` – `2147483647` | Raw limits in the last observed version |
| Enum values | — | — for non-enum types |
| Category | Resource Usage / Memory | Upstream classification |
| Latest boot value | `65536` | 64 MiB |
{.fields meta="-"}

## Lifecycle {#lifecycle}

| Fact | Value |
| --- | --- |
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |

## Default history {#default-history}

| Versions | Raw `boot_val` | Unit | Human value |
| --- | --- | --- | --- |
| PG9.0–9.3 | `16384` | `kB` | 16 MiB |
| PG9.4–19 Beta 3 | `65536` | `kB` | 64 MiB |
{.full-width caption="Measured PG9.0–19 Beta 3 boot defaults"}

## How it works {#mechanism}

maintenance_work_mem applies to maintenance operations including VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Because a session normally runs only one such operation at a time, it can usually be set higher than work_mem.

For parallel utility commands, PostgreSQL treats maintenance_work_mem as a limit for the entire utility command rather than granting the full amount to every parallel maintenance worker. CPU and I/O consumption can still rise with parallelism.

Autovacuum is a separate concurrency concern: when autovacuum_work_mem is -1, each autovacuum worker inherits maintenance_work_mem, so several workers can make the aggregate budget much larger than the single-operation value.

## Tuning advice {#tuning-advice}

> [!TIP]
> **Advice.** These are workload-specific starting points and must be validated with measurements.

| Workload | Guidance |
| --- | --- |
| OLTP | Use enough memory to keep routine vacuum and index work efficient, but budget explicitly for simultaneous autovacuum workers. If interactive latency matters and maintenance_work_mem is large, give autovacuum_work_mem its own lower ceiling. |
| OLAP | Larger values are often useful for index builds, vacuuming large relations, and restores. Schedule heavy maintenance and verify that concurrent maintenance, query memory, and the OS still fit together. |
| Small nodes | Keep the global value modest and temporarily raise it only for controlled maintenance sessions. Check autovacuum inheritance before increasing it. |
{.full-width}

## Pigsty {#pigsty}

Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.

| Template | Effective value | Versus upstream boot | Source expression |
| --- | --- | --- | --- |
| OLTP | `2048MB` | different | `{{ pg_maintenance_mem }}MB` |
| OLAP | `4096MB` | different | `{{ pg_maintenance_mem }}MB` |
| CRIT | `2048MB` | different | `{{ pg_maintenance_mem }}MB` |
| TINY | `2048MB` | different | `{{ pg_maintenance_mem }}MB` |
{.full-width}

> [!CAUTION]
> **Advice — pending human review.** Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 = 2048MB (dcs); OLAP: PG9.0–19 Beta 3 = 4096MB (dcs); CRIT: PG9.0–19 Beta 3 = 2048MB (dcs); TINY: PG9.0–19 Beta 3 = 2048MB (dcs). Advice, pending human review — Editorial inference: the larger OLAP fraction is intended to favor bulk maintenance, while inherited autovacuum exposure needs separate review.

## Common pitfalls {#common-pitfalls}

- Assuming the value is harmless because maintenance is infrequent while leaving autovacuum_work_mem at -1.
- Multiplying the limit by parallel maintenance workers; PostgreSQL applies it to the utility command as a whole.
- Using a permanently large cluster-wide value for a one-off restore instead of a scoped SET.
- Expecting more memory alone to solve maintenance dominated by locks or storage I/O.

## Related parameters {#related-parameters}

[`autovacuum_work_mem`](/parameters/autovacuum-work-mem/) · [`autovacuum_max_workers`](/parameters/autovacuum-max-workers/) · [`max_parallel_maintenance_workers`](/parameters/max-parallel-maintenance-workers/) · [`vacuum_buffer_usage_limit`](/parameters/vacuum-buffer-usage-limit/) · [`work_mem`](/parameters/work-mem/)

## References {#references}

- [PostgreSQL 19 Beta 3 — maintenance_work_mem](https://www.postgresql.org/docs/19/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)
- [PostgreSQL 18: Routine Vacuuming](https://www.postgresql.org/docs/18/routine-vacuuming.html#AUTOVACUUM)
- [Pigsty: Parameter Optimization Policy](https://pigsty.io/docs/pgsql/template/tune/)
- [PostgreSQL 19 release notes](https://www.postgresql.org/docs/19/release-19.html)
- [Machine-readable GUC export](/data/guc.jsonl)