I would love to be able to "nice" queries, a high "nice" attribute meaning, for the engine, "work on this whenever it does not slow down anything else", especially on the I/O side (à la Linux "ionice").
A "niced" query blocks (and its working set may even be "swapped out") as long as:
- the read buffer does not contain anything pertinent for it,
- there are "too much" pending I/O requests (<=> submitting an I/O request useful for this query would slow down other less-niced queries)
The "niceness" of a query may be dynamically modified by an authorized user.
Bonus:
- any role is associated to a minimal level of niceness (GRANT'able)
- the underlying logic interacts with the OS corresponding logic in order to take into account all I/Os (this is especially useful if PG runs on a non-dedicated server)
A "niced" query blocks (and its working set may even be "swapped out") as long as: - the read buffer does not contain anything pertinent for it, - there are "too much" pending I/O requests (<=> submitting an I/O request useful for this query would slow down other less-niced queries)
The "niceness" of a query may be dynamically modified by an authorized user.
Bonus: - any role is associated to a minimal level of niceness (GRANT'able) - the underlying logic interacts with the OS corresponding logic in order to take into account all I/Os (this is especially useful if PG runs on a non-dedicated server)