Ask HN: Can I use the RDBMS as an application server?
2 comments
I used to be an Enterprise architect for a Fortune 500 company. We did many of the things you are asking about. Here are my answers:
1. Yes, you can. However, there will be tradeoffs. One important piece of sharing business logic involves sharing instances of running code, not just the logic itself. It's tough to implement a singleton in a set of stored procs. I'd recommend that you leave stored procs for the kinds of logic that you might code into a web service: transactional concerns that have a complicated set of steps.
2. Yes, that's a popular approach, and for good reason: the objects you create against the persistence store are sharable at the instance level.
3. It will scale less-well than an architecture with a solid middle tier, but it's impossible to put a user-count on it without knowing much much more. If you pick a commercial RDBS like Oracle or Sybase (and many people don't like to do that), you will find that there are a lot of expensive but effective scaling mechanisms to handle this. It's usually cheaper to invest in a decent middle tier, a caching server, and scale that way.
4. That's not necessarily true. Portability is nice, but there are many other reasons to pull the business logic out of the database. For example, it's hard (but not impossible) to implement an event-driven system where transactions will fire off other business events downstream when you're using a stored procedure set as your logic layer. You can do it, but you end up with triggers or batch jobs that reduce the real-time processing capabilities of the whole architecture.
5. P/L SQL is pretty powerful, but I still have my logic in objects rather than in Oracle. I can introduce caching and other sscaling strategies easier this way.
Though I use and like Oracle, I'd steer you away from it unless you have a really good reason to buy it. It takes a lot more care-and-feeding than MySQL, the licensing model becomes oppressive when you start to need to cluster the DB, and experts in the area are expensive.
Hibernate is a good framework. Why are you resisting the use of it? If you want to go heavy on the stored procedures, check out the iBatis framework. It's really good at ORM and stored procs.
1. Yes, you can. However, there will be tradeoffs. One important piece of sharing business logic involves sharing instances of running code, not just the logic itself. It's tough to implement a singleton in a set of stored procs. I'd recommend that you leave stored procs for the kinds of logic that you might code into a web service: transactional concerns that have a complicated set of steps.
2. Yes, that's a popular approach, and for good reason: the objects you create against the persistence store are sharable at the instance level.
3. It will scale less-well than an architecture with a solid middle tier, but it's impossible to put a user-count on it without knowing much much more. If you pick a commercial RDBS like Oracle or Sybase (and many people don't like to do that), you will find that there are a lot of expensive but effective scaling mechanisms to handle this. It's usually cheaper to invest in a decent middle tier, a caching server, and scale that way.
4. That's not necessarily true. Portability is nice, but there are many other reasons to pull the business logic out of the database. For example, it's hard (but not impossible) to implement an event-driven system where transactions will fire off other business events downstream when you're using a stored procedure set as your logic layer. You can do it, but you end up with triggers or batch jobs that reduce the real-time processing capabilities of the whole architecture.
5. P/L SQL is pretty powerful, but I still have my logic in objects rather than in Oracle. I can introduce caching and other sscaling strategies easier this way.
Though I use and like Oracle, I'd steer you away from it unless you have a really good reason to buy it. It takes a lot more care-and-feeding than MySQL, the licensing model becomes oppressive when you start to need to cluster the DB, and experts in the area are expensive.
Hibernate is a good framework. Why are you resisting the use of it? If you want to go heavy on the stored procedures, check out the iBatis framework. It's really good at ORM and stored procs.
Points 1-4 seem fine. Furthermore, I should point out that if you consider recent benchmarks, the bottleneck was not the DB but the framework on top (ie. Django or ROR). Generally DBs are extremely well designed.
Furthermore, it is still possible to cluster DBs as long as there is one write master and multiple read masters (of course, this does not apply when there is a high ratio of writes).
I am not an expert on clustering so you should check that last statement.
For statement #5: If your are concerned about SPs, I would strongly advise against MySQL. SPs in MySQL are very new and thus their implementation may not be quite up to par. You might want to consider PostgreSQL. The pgplsql SP language is supposed to be nearly equivalent to Oracle SPs. Furthermore, postgres also allows SPs in python, tcl, and perl which should cover pretty much all use cases for SPs though you sacrifice portability with languages other than pgplsql. Postgres also has much better scaling performance.
Furthermore, it is still possible to cluster DBs as long as there is one write master and multiple read masters (of course, this does not apply when there is a high ratio of writes).
I am not an expert on clustering so you should check that last statement.
For statement #5: If your are concerned about SPs, I would strongly advise against MySQL. SPs in MySQL are very new and thus their implementation may not be quite up to par. You might want to consider PostgreSQL. The pgplsql SP language is supposed to be nearly equivalent to Oracle SPs. Furthermore, postgres also allows SPs in python, tcl, and perl which should cover pretty much all use cases for SPs though you sacrifice portability with languages other than pgplsql. Postgres also has much better scaling performance.
1. Assuming portability of SQL and Stored Procedures is the least of my concerns (because, let's say, I want to -- by design -- support a single RDBMS vendor only), can I, in such a case, use an RDBMS (such as MySql) as the application server, with all the biz logic coded in the SPs?
2. The other popular approach these days apparently is (or, at least has been so... for, well, the last several years): 2.1 to use an RDBMS as a mere persistence mechanism (ignoring completely the 'R' in the term 'RDBMS'), 2.2 do a Relational-to-Object mapping (with a tool like Hibernate), 2.3 code all your business logic against these 'objects' (reside inside an App Server like JBoss)!
3. If I follow approach #1, how will my application scale as the number of users / load increase? Via clustering of the RDBMS server? Even with clustering, will approach #1 turn my application into a 2-tier client-server architecture which apparently got rejected/superseded by N-tier architecture by mid-90s itself? I've heard that client-server application architectures don't scale beyond a 100 concurrent users or so (not sure if this is true)! Assume that this is in context of a distributed, banking application... let's say for Citibank, and that they have hired (an enterprise dud such as) me to be their chief software architect :-) and that they have the time, money, and the resources for a full redesign/rewrite from scratch!
4. Based on what I know so far, I think approach #2 will be beneficial, if and only if either of these two hold: 4.1 the biz logic must be independent of the RDBMS vendor; and 4.2 the biz logic requires sophisticated algorithms for which SQL (even with SPs) is not the best language... thus forcing you to import the data from the RDBMS into a more convenient, 'object' environment of a general purpose, Turing-complete language such as Java/C++.
5. If let's say, 4.2 does not apply to my case, can point 4.1 be addressed via some source to source translation tools, that 5.1 are already out there, or 5.2 will be there in near future, or 5.3 can be hand-coded, inhouse without necessarily an advanced, guru-like expertise in compilers?
Basically, I'm finding myself overwhelmed with the tools, technologies, frameworks that are springing up by gazillions ... to the point where it's becoming humanly impossible for me to evaluate them, with every single one of their pros and cons fully understood.
Based on what I know and understand so far, this is what I probably would do: a) Use MySql because its FOSS; (or, use Oracle... I hear it's very powerful, rich in features). b) Make full use of -- nay, exploit -- the power of SPs, the relational paradigm, the 'R' in the RDBMS. c) For any sophisticated algorithms for which SQL/SP appears less than ideal... use JDBC (with parametrized queries) to convert R to O, and then exploit the power of OO/Java. d) Use JSP/Servlets/Tomcat for presentation/front end and for the sophisticated-logic coding of the preceding item (c). e) Use clustering at the dbms server, load balancing at Tomcat/Apache level.
Can't think of anything better at this point. So I humbly ask for your kind advice.