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