Don’t be thick – Use a “Thick Database” approach

The trend in recent years has been to decrease reliance on the DBMS (database management system).  Using this approach, all code and logic gets moved to the middle tier.  The database becomes nothing more than “a place to store persistent copies of our classes.” This post describes some of the thick database benefits.

Of all the system architectural misguided changes in direction that have been made in the last 15 years, this is arguably the silliest.  (I guess the list of silly things will be another post ). When Dulcian is called in to rescue a project, the most common way to refactor code into a working system is to move it from the application server to the database.

I know that there are Java developers who will tell you that they have rescued projects by doing exactly the opposite. They rescued projects by taking code written by weak database developers and rewriting it in modern, cool Java.  I am sure they are right.  Code that is written by junior developers with little or no direction in ANY architecture can be dramatically improved by a senior developer with architectural awareness in any OTHER architecture (or even in the same technology stack).

However, all things being equal, putting code in the database (thick database) will always be easier.  It is just simple logic. Having your code next to your data means less moving of data and fewer context switches.  Also, you cannot beat PL/SQL for quality of data manipulation (particularly when lots of data is involved).

Any advantage of load balancing is almost always overshadowed by the cost of context switches and data movement.

To be fair, there is one (and ONLY one) counter example that I have encountered in my career.  At a big oil company, they were doing massive linear programming problems that required matrix operations on huge matrices.  It was better to move the necessary data to a separate server to do the C++ heavy lifting rather than to do it on the database server.

In my experience, the thick database benefits (moving code to the database) are as follows:

1. About half as many lines of code are needed.
2. Performance is 10 times better.
3. The system requires one tenth the network traffic.
4. There is a 50% reduction of load on the database server.

I think that the last result is the most surprising. How can the database server work less if it is doing more?  The reason is that the cost of getting the data in and out of the database is  more expensive than leaving the data there and doing all of the processing in the database.

How “thick” is too thick?  If it is so cool to move things out of the application server and into the database, how far can you go?  To be honest, I am still tweaking the answer to that question. Right now, we have almost everything in the DB (even the UI model layer and all UI logic).  The only code that is not in the DB is a JavaScript library that we use as part of FormSpider environment and some minimal code in the application server.  We are contemplating taking the whole UI metadata out of the DB, but we not at all sure that it will buy us anything.  When Times10 gives us 100% PL/SQL and object type support then we will think harder about it I suspect.

With our “ultra-thick” DB approach, we have a lower HW footprint than comparable systems built in other technologies.  In one case, when refactoring from an old Oracle Forms system to a BRIM system the client went from over 200 servers to a single rack.

So, when in doubt, put your logic in the database.  You can’t be too thick.