Thursday, October 17, 2019

Increasing PostgreSQL Maintenance Task Speed

I observed maintenance tasks, such as, indexing a large table on a PostgreSQL database took long time. Examining the PostgreSQL configuration, I saw the maintenance_work_mem is 64MB, the default value, e.g.,

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=#

To speed up the indexing processing, I temporarily increase it to 1GB without restarting the database server. The following transcripts shows the steps:

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=# ALTER SYSTEM SET maintenance_work_mem='1GB';
ALTER SYSTEM
postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 1GB
(1 row)

postgres=#



No comments:

Post a Comment