Zooming In
Let’s assume, as a slightly contrived example, that our application is suddenly having performance problems when it executes the query:
select * from demo.customers where balance > ?
This is not a very good query, but we’re stuck with it.
It turns out that someone has changed the customers table and added a new, very large column named photos, of type blob. That’s another questionable decision, but it’s out of our control.
Our application has no interest in these photos, but it did not specify which columns to retrieve, so now our query returns these photos, needlessly consuming bandwidth and memory.
We’ve already determined that updating the application is not an option, and neither is changing the database. So our only viable option is to have a proxy that will substitute our improved query for the old one.
Let’s see how this works with Gallium Data, a free database proxy:
Step 1: Install the Database Proxy
Installation is trivial — it’s a simple Docker image, so you can start it up in a minute.
Step 2: Configure the Proxy
Next, we need to set up the proxy so that it connects to the database server. All we need is the host name and port number of the database server. Database credentials are not required because authentication is pass-through. If we need to support encryption, we can either use a new certificate, or the same certificate as the database server (if available).
Step 3: Set up the Query Substitution
Next, we need to tell the proxy which query to catch, and what to substitute it with. That’s done with a request filter, which will look at all the queries coming in over the wire:
Replace:
select * from demo.customers where balance > ?
with:
select name, balance, address from demo.customers where balance > ?Step 4: Redirect the Application
Finally, we need to tell the application to talk to the proxy, rather than directly to the database. This is usually done by changing a configuration file, an ODBC/JDBC data source, or a command-line parameter.
That’s it — the whole thing can usually be done in a few minutes. Neither the application nor the database server will notice any difference. From now on, the “bad” query will be replaced with the “good” query every time the application issues it.
Once this is in place, doing the same thing for other queries is of course trivial — more request filters can be created and deployed in moments.
