Maintenance with maintenance_work_mem
This memory area is used by operations such as vacuum. As is the case with work_mem
, the space allocated will have an impact on how quickly the maintenance activities are completed. Typical activities using this area include analyzing, vacuuming, creating an index, reindexing, and so on.
Showing the impact of this value is pretty easy, as shown in the following command:
postgres=# \timing on Timing is on.
Tip
It's a good idea to use the timing setting when we are at the psql prompt.
There is a one column table with a few hundred thousand records:
postgres=# SELECT pg_size_pretty(pg_table_size('myt')); pg_size_pretty ---------------- 69 MB (1 row) postgres=# SELECT current_database(); current_database ------------------ postgres (1 row) Time: 1.152 ms postgres=# SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = 'postgres'; temp_files | temp_bytes ------------+------------ 4 | 120143872 (1 row) Time: 17.547 ms
As of now...