Proposal: backend "niceness" / session_priority - Mailing list pgsql-hackers

Hackers,<br /><br />     I have found myself needing to run some maintenance routines (VACUUM, REINDEX, REFRESH
MATERIALIZEDVIEW mostly) at a lower priority so as not to disturb concurrent *highly transactional* connections. This
issueis also noted within the TODO[0] list in the Wiki .<br /><br /> * There was some discussion on 2007 [1] regarding
"<aclass="external text" href="http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php"
rel="nofollow">Prioritiesfor users or queries?</a>"<br /><br />     Since PostgreSQL lacks the resource management
capabilitiesof the "Big Ones" ( Resource Groups - Red, WorkLoad Manager - Blue ) or the Resource Governor in MS SQL
Server,we can try and approximate the requested behaviour by reducing the CPU priority ("nice") of the backend in
question.Please note that we would be using scheduler priority to try and modulate I/O, though I'm aware of the
limitationsof this mechanism.<br /><br />     Using renice(1) from outside is not only cumbersome and error prone but
verymuch unuseable for the use cases I am contemplating.<br /><br /><br /> * Moveover, as seen in the "Priorities" wiki
page[2], there exists an extension providing a set_backend_priority() function, to be called
"set_backend_priority(pg_backend_pid(),20)". <br /> This approach is, sadly, not portable to non-POSIX operating
systems(e.g. Windows), and IMO quite too convoluted to use and tied to actual implementation details.<br /><br /><br
/><br/> * I have been playing with some code which uses a GUC for this purpose, though only define/support three
differentpriorities would make sense for the final implementation IMO: NORMAL, LOW_PRIORITY, IDLE<br />     Checked
platformcompatibility too: this behaviour can be implemented on Windows, too. For everything else, there's nice (2)<br
/><br/><br /><br /> However, there is a relatively minor catch here which is the reason behind this e-mail: user
interface<br/><br /> - Inventing a new "command" seems overkill to me. Plus, I don't know what we could model it on ---
giventhat the real solution for this problem would be a fully featured "priority manager" ---<br /><br /> - I have been
playingwith a GUC that ignores being reset --- so as to comply with nice's specification when not running as a
privilegeduser --- but I reckon that this behaviour might be surprising at best:<br />     SET session_priority TO
'low';   -- Ok, low priority<br />     VACUUM FREEZE my_test_table;<br />     RESET session_priority;        -- Nope,
stilllow prio. Emit notice?<br /><br />     The way to reset the priority would be to RECONNECT. And this is my main
painpoint.... though it does fullfill the need.<br /><br /><br />     However, this approach does fullfill my needs and
---itseems--- the OP's needs: be able to run a maintenance task at a low priority (i.e. disturbing other concurrent
queriesas little as possible). Expected use case:  cronjob running " psql -c 'SET session_priority TO low; REINDEX
blablaCONCURRENTLY; VACUUM foobar;'"<br /><br /><br /> All suggestions welcome. <br /><br /> I'll be wrapping a
more-or-less-donepatch on monday if somebody wants to take a look and criticize on actual code (I won't be working on
thistomorrow) unless somebody points me at a better solution<br /><br /><br /> Thanks,<br /><br />     / J.L.<br /><br
/><br/><br /> [0] <a class="moz-txt-link-freetext"
href="https://wiki.postgresql.org/wiki/Todo">https://wiki.postgresql.org/wiki/Todo</a>- Miscellaneous performance<br />
[1]<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php">http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php</a><br
/>[2] <a class="moz-txt-link-freetext"
href="https://wiki.postgresql.org/wiki/Priorities">https://wiki.postgresql.org/wiki/Priorities</a><br/><br /> [3] <a
class="moz-txt-link-freetext"
href="http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm">http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm</a><br
/>[4] <a class="moz-txt-link-freetext"
href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.doc/com.ibm.db2.luw.doc-gentopic6.html">http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.doc/com.ibm.db2.luw.doc-gentopic6.html</a><br
/>[5] <a class="moz-txt-link-freetext"
href="https://msdn.microsoft.com/en-us/library/bb933866.aspx">https://msdn.microsoft.com/en-us/library/bb933866.aspx</a><br
/>