Question :
A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has limited RAM so both our processes are running extremely slowly. My colleague’s query (the creation of a materialized view) typically takes 4 hours to complete but has now been running most of the day.
Since my colleague’s query is more important than mine and needs to be completed by a certain time; is there a way I can pause my query thereby increasing the available RAM and allowing my colleague’s process to complete?
My query is a large ‘update’ to a table and ideally I don’t want to kill it a have to start all over again. Is there anyway I can reduce or temporarily suspend the resources consumed by my process?
Postgres 9.4/Redhat 6.8
Answer :
If you have access to the OS account under which postgres runs, you can identify the PID of your process (using top, or ps, or pg_stat_activity), and then do a kill -SIGSTOP <pid>. Later you would then do a kill -SIGCONT <pid> to get it to run again.
This is not a risk-free thing to do. If you stop the process while it is holding a spinlock (pretty unlikely, but certainly possible), you could freeze up the entire database, until eventually it would realize something was wrong and then crash itself (terminating your colleague’s job). If you stop the process while it is holding a lightweight lock, it would also freeze the database (or parts of it) and it remain frozen until you continue the process and it releases the lock.
Also, the memory of the stopped process will not be released. The system will be able to swap that memory out without it constantly fighting to swap back in, and maybe that is good enough. But it would be better if it were actually released.
All in all, it is probably a better idea to cancel your update and repeat it later.