Vacuum: allow usage of more than 1GB of work mem
Claudio Freire <klaussfreire@gmail.com>
From: Claudio Freire <klaussfreire@gmail.com>
To: PostgreSQL-Dev <pgsql-hackers@postgresql.org>
Date: 2016-09-03T03:25:37Z
Lists: pgsql-hackers
Attachments
- 0001-Vacuum-allow-using-more-than-1GB-work-mem.patch (text/x-patch) patch 0001
The attached patch allows setting maintainance_work_mem or autovacuum_work_mem higher than 1GB (and be effective), by turning the allocation of the dead_tuples into a huge allocation. This results in fewer index scans for heavily bloated tables, and could be a lifesaver in many situations (in particular, the situation I'm living right now in production, where we don't have enough room for a vacuum full, and have just deleted 75% of a table to make room but have to rely on regular lazy vacuum to free the space). The patch also makes vacuum free the dead_tuples before starting truncation. It didn't seem necessary to hold onto it beyond that point, and it might help give the OS more cache, especially if work mem is configured very high to avoid multiple index scans. Tested with pgbench scale 4000 after deleting the whole pgbench_accounts table, seemed to work fine.
Commits
-
Prefetch blocks during lazy vacuum's truncation scan
- 7e26e02eec90 10.0 landed
-
Explain unaccounted for space in pgstattuple.
- 71f996d22125 10.0 cited