Controlling performance impact of multiple users

This is an issue that I have never seen we;; ved with other databases I
have used:

We have an on-line application that is PSQL based. Users want to be able to
get reports from this database - both canned reports that we might write
for them, and ad-hoc reports where they use an odbc-based report generator
and reach into our database.

The issue is how to prevent users from hogging the system, and especially
from slowing down the on-line users. The on-line users are doing either
"heads down" intensive data entry/editing or are working telephone centers.
Hence slowing them down is a *very bad thing.*

  What would be nice is some way to limit a give customer (group of users)
in terms of how much database resource (I/O, CPU) that they use at any one
time.

Is there any way to do anything like this?

Other options we have are:
    scheduling reports to run "after hours"
    replicating the database to a reports-only server and let the report
users fight with each other without impacting the online users.
    charging users for the resources they use, with a steep prime time
charge (this is a very rational approach, but unlikely to be acceptible to
the users)

We thought of "nicing" the users on the theory that if their report
processes can't get much CPU, then they can't make as many database
queries, but in practice this doesn't work because:
    1) A single query from one of the users, if poorly formed, or if the
database doesn't support it well, can generate mammoth scans
         and merges.
    2) Our users are mostly runing ODBC from networked (WAN'd, btw)
desktops. Thus they are immune from these policies.


Any suggestions? Any way other folks have done this (PSQL specific or just
general tricks)?




John Moore

http://www.tinyvital.com/personal.html

UNITED WE STAND