Proposal: backend "niceness" / session_priority

Hackers,

I have found myself needing to run some maintenance routines
(VACUUM, REINDEX, REFRESH MATERIALIZED VIEW mostly) at a lower priority
so as not to disturb concurrent *highly transactional* connections. This
issue is also noted within the TODO[0] list in the Wiki .

* There was some discussion on 2007 [1] regarding "Priorities for users
or queries?
<http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php>"

Since PostgreSQL lacks the resource management capabilities of 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 limitations of this mechanism.

Using renice(1) from outside is not only cumbersome and error prone
but very much unuseable for the use cases I am contemplating.

* 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)".
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.

* I have been playing with some code which uses a GUC for this purpose,
though only define/support three different priorities would make sense
for the final implementation IMO: NORMAL, LOW_PRIORITY, IDLE
Checked platform compatibility too: this behaviour can be
implemented on Windows, too. For everything else, there's nice (2)

However, there is a relatively minor catch here which is the reason
behind this e-mail: user interface

- Inventing a new "command" seems overkill to me. Plus, I don't know
what we could model it on --- given that the real solution for this
problem would be a fully featured "priority manager" ---

- I have been playing with a GUC that ignores being reset --- so as to
comply with nice's specification when not running as a privileged user
--- but I reckon that this behaviour might be surprising at best:
SET session_priority TO 'low'; -- Ok, low priority
VACUUM FREEZE my_test_table;
RESET session_priority; -- Nope, still low prio. Emit notice?

The way to reset the priority would be to RECONNECT. And this is my
main pain point.... though it does fullfill the need.

However, this approach does fullfill my needs and ---it seems---
the OP's needs: be able to run a maintenance task at a low priority
(i.e. disturbing other concurrent queries as little as possible).
Expected use case: cronjob running " psql -c 'SET session_priority TO
low; REINDEX blabla CONCURRENTLY; VACUUM foobar;'"

All suggestions welcome.

I'll be wrapping a more-or-less-done patch on monday if somebody wants
to take a look and criticize on actual code (I won't be working on this
tomorrow) unless somebody points me at a better solution

Thanks,

/ J.L.

[0] https://wiki.postgresql.org/wiki/Todo - Miscellaneous performance
[1] http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php
[2] https://wiki.postgresql.org/wiki/Priorities

[3] http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm
[4]
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.doc/com.ibm.db2.luw.doc-gentopic6.html
[5] https://msdn.microsoft.com/en-us/library/bb933866.aspx