Re: Limiting the impact of schema additions/poor queries made by clients on production machines

On Oct 5, 2009, at 8:04 AM, Joshua Berry wrote:

> Our shop uses postgres for a dozen installations. The applications
> have some realtime performance requirements, and are just good enough
> to function properly. The problem is that the clients (owners of the
> production servers) are using the same server/database for
> customizations that are causing problems with the performance of our
> applications.
>
> Example of clients' customizations:
> * Large tables with text datatypes that get cast in the queries
> * No primary keys, indexes, FK constraints
> * Use of external scripts that use count(*) from table where id = x,
> in a loop from the script, to determine how to construct more queries
> later in the same script.

This sounds very familiar.

Is this just for reporting, or does it interact more closely with
the application?

If it's just reporting, then maybe a slony slave server, just for
reporting,
would help.

If it interacts more closely with the application then you're not
going to be able protect them from themselves programatically.

>
> The clients consider themselves experts and don't take
> suggestions/criticism well. If we just go ahead and try to port/change
> the scripts ourselves, the old code can come back, clobbering the
> changes that we made!

If personal feedback isn't going to work (and it sounds like it
isn't) then perhaps hacking up pgfouine a little to create a
dashboard showing where the database resource is going.
That way you have a neutral place to point at when they
complain the app is running slowly, and that "neutral" report
can communicate "you, the customer, are the problem because
your queries suck" in a more objective way.

>
> My question is this: how can we limit the resources to
> queries/applications other that what we create and deploy? Are there
> any pragmatic options in scenarios like this? We prided ourselves in
> having an OSS solution, but it seems that it's become a liability.

If the queries are operating on the same data as the production
app I don't see any good way to segment the IO and CPU resources
that's going to be idiot proof, especially of your customers are
going to demand full database access.

>
> We use PG 8.3 running on a range on Linux Distos.

Cheers,
Steve