How to automatically reduce the growth of the Postgresql database?¶
These settings are configured automatically in 4.4 version.
If your database is growing too fast, you can try to hold back its growth with the aggressive parameters of “Autovacuum”. Autovacuum is an embedded process for maintaining the database structure in an ordered way.
Steps to achieve the goal:
- Open the linux console on the computer with the Staffcop Server installed
- Open the postgresql settings file for editing:
sudo nano /etc/postgresql/9.5/main/postgresql.conf
- Add the following lines at the end of the file:
autovacuum = on # Enable autovacuum subprocess? 'on' log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and autovacuum_max_workers = 5 # max number of autovacuum subprocesses autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 300 # min number of row updates before autovacuum_analyze_threshold = 200 # min number of row updates before autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.005 # fraction of table size before analyze
- Save the file by pressing Ctrl + x and pressing y
- Restart postgresql
sudo service postgresql restart
After that the database will grow at a moderate pace, and will be more predictable in size.