How to automatically reduce the growth of the Postgresql database?

Note

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.