Rails doesn't scale because RI/transactions in app not db!!!

rubyonrails-talk
1

Obviously a database addict.

Database 'gurus' who bang on about referential integrity and transactions are just like those who go on about how you can't right correct code without static typing and a compiler.

Basically they are terrified they might be wrong and out of a job.

Rails is an object oriented system. The integrity of the application depends upon the integrity of the object network within the application. And that depends upon your testing regime.

The database is treated as just a big hash table with knobs on. If the filesystem was clever enough we'd probably use that instead and save a lot of hassle. A RDBMS is frankly overkill in the context of a Rails app.

2

Neil Wilson wrote the following on 30.05.2007 18:27 :

Obviously a database addict.

Database 'gurus' who bang on about referential integrity and transactions are just like those who go on about how you can't right correct code without static typing and a compiler.

Basically they are terrified they might be wrong and out of a job.

Rails is an object oriented system. The integrity of the application depends upon the integrity of the object network within the application. And that depends upon your testing regime.

The database is treated as just a big hash table with knobs on. If the filesystem was clever enough we'd probably use that instead and save a lot of hassle. A RDBMS is frankly overkill in the context of a Rails app.    That's the other extreme point of view and I don't think it matches the reality more than the opinion of some DBAs willing to put all constraints in database.

I wouldn't have been able to code several of my Rails apps if it weren't for ACID support. I even had to use SERIALISABLE PostgreSQL transactions with nearly raw SQL UPDATEs to make sure that there was no way some buckets could have less than 0 in the DB under concurrent accesses.

Lionel.

3

So if the filesystem was ACID compliant and network available, would you miss the constant SQL parsing, the unused permissions system, consistency checks and stored procedures?

AFAICS Rails only uses a SQL RDBMS to store its data because that's the best alternative open to it at present. It doesn't need one.

NeilW

4

Neil Wilson wrote the following on 31.05.2007 11:35 :

So if the filesystem was ACID compliant and network available, would you miss the constant SQL parsing, the unused permissions system, consistency checks and stored procedures?    I'm not sure if ACID covers the SERIALIZABLE aspects. If it does, yes it would probably suit my needs. But wouldn't you get something really close to a RDBMS ?

The current state is quite nice for me too : - SQL parsing is a problem only because ActiveRecord doesn't support prepared statements (yet). And you need some standard to query the storage repository because doing full table scans on the client is simply out of the question. So doing some query syntax parsing isn't a problem, in fact it is a solution to avoid huge loads on your storage repository (the problem is that it isn't as optimised as it could yet, reparsing the same query again and again is a waste of time). - The unused permissions system is not a problem (the overhead is most probably negligible). - Stored procedures aren't a problem unless they are used :slight_smile:

So the current state isn't so bad in my opinion.

To handle a non-trivial amount of data without borking it or suffering performance problems, in my opininon you need network support, serializable transactions (even transactional schema updates are a big plus for migrations), indices, foreign keys and a rich query syntax : that's a simplified (in fact not so simple, MySQL didn't even support transactional schema updates last time I looked and serializable transactions were not available in earlier versions) RDBMS, trying to reinvent a simple one could be done, but for example I'm afraid getting to the point where PostgreSQL is would not be an easy task and I'm wondering if the result could be much faster or simpler.

Lionel.

5

An interesting discussion, that predictably has the people with database backgrounds taking a different view to the people with app server backgrounds - see if you can guess where my background is :o)

Some recurring points made here are:

Rails should work equally well with any database - one of the biggest failures of database centric projects, based on over 12 years experience according to Tom Kyte (Oracle expert) is people treating the database as a black box for keyed reads. To really make Oracle/ Mysql/Postgres/SQL Server scale, you have to use it the way it was intended. I know Oracle well, and to not use Bind variables is a cardinal sin and the most common thing done wrong by Java app developers (and Rails for that matter). Rails was designed to use Mysql primarily, which the last I read doesn't have any concept of bind variables, hence Rails not using them. Lets not even get started on the different locking, read consistency and transaction mechanisms used by each database, and powerful SQL constructs available in some DBs and not in others.

RI should be in the app - Apps come and go, 5 years ago it was a Perl CGI app, then came PHP just before some pointy haired boss decided J2EE was the way to go. I bet that in all this time the database structure hardly changed at all. Lets not get started on all the various back end processes that may need to talk to this database to do their thing etc. The database simply must protect the data - constraints and foreign keys are the way forward here (I tend to avoid triggers like the plague). Now that said, why not let the App validate RI too? If the app server can spot that the Create Customer form was submitted with out a surname, and it can avoid a trip to the database when it knows that and generate a nice error message, thats fantastic! I feel these RI in app vs RI in DB debates are a bit like form validation in JS or form validation on the server - both gives the best user experience, failing to do it on the server lets hackers have fun corrupting your data.

New version of the app may require database changes - its a rare release that doesn't require some, I wouldn't loose any sleep over a few alter constrain sql commands in the scheme of things.

Testing will catch all bugs that could corrupt your data - so all tested software is perfect then? New bugs are never discovered? :o)

Just my two cents - For the record I love Rails and what it lets us do - having coded small apps using mod_plsql and Oracle stored procedures, Rails is light years ahead, but its important we don't get carried away and forget what databases were designed for!

6

I could see that claim for referential integrity, but transactions? How do you test the case where you lost power in the middle of a database update?

-faisal

7

If the filesystem was ACID compliant we'd live in a different world.

-faisal

8

I don't understand the "rails handles transactions in the app." It doesn't. The transactions are all handled by the database. You write Ruby code (Client.transaction do........) but that just starts a transaction in the db.

I've come up with a pretty simple strategy. Constraints like NOT NULL, length, etc are handled by my app. They're really easy to test, often change a lot, and over all are easier when managed by my app. I use foreign key constraints because my RDBMS is meant to handle relations.

I manage meaning in my app, and relations in the database. This strategy keeps me happy working with my code and lets me sleep easy at night.

Pat

9

You do. That's the point.

And it is a point that Pick, and to a certain extent OS400, worked out decades ago.

For some reason nobody ever integrated the DBMS into the Unix filesystem. Instead we have bolt on ones that duplicate a lot of what Unix does already.

10

I've come up with a pretty simple strategy. Constraints like NOT NULL, length, etc are handled by my app. They're really easy to test, often change a lot, and over all are easier when managed by my app. I use foreign key constraints because my RDBMS is meant to handle relations.

When you really understand how Oracle (perhaps other databases too?) optimizes its SQL query plans, adding a not null constraint to a column can help the optimizer take a better path to getting you the data you want. The more information you give the database, the better chance it has of doing its job in the optimal way.

11

RI should be in the app - Apps come and go, 5 years ago it was a Perl CGI app, then came PHP just before some pointy haired boss decided J2EE was the way to go. I bet that in all this time the database structure hardly changed at all. Lets not get started on all the various back end processes that may need to talk to this database to do their thing etc.

Apparently, no one has referred to these two (short) articles by Martin Fowler yet

  IntegrationDatabase   ApplicationDatabase

I'd say that Rails being opinionated considers databases to be the property of their applications. As elsewhere in Rails, you can work around this opinion, but conforming to it leads you on the path of least resistance.

Regarding sharing database content, Rails has an opinion, too: Offer a service to interact with this content. Doing so is the best way to ensure that the rules of your domain are enforced. These rules can hardly be expressed through referential integrity constraints alone. So, unless you're going to put all the business logic in the database, a service looks like the right thing.

The database simply must protect the data - constraints and foreign keys are the way forward here (I tend to avoid triggers like the plague).

As I say above, I think it is quite questionable whether the database *can* protect the data sufficiently using only these means.

All that said, I'm very much in favor of checking RI and NOT NULL in the database. It's not even particularly complicated. Have a look at

  http://agilewebdevelopment.com/plugins/enforce_schema_rules   http://www.redhillonrails.org/

The only snag I can think of is that FK constraints can bite you when loading fixtures for testing. AFAICT, when using foreign key constraints, there's currently no way to load fixtures containing mutual references.

This problem might be solvable by changing Fixtures#create_fixtures so that it issues

  SET CONSTRAINTS ALL DEFERRED

(see PostgreSQL: Documentation: 8.2: SET CONSTRAINTS to databases that support it. I haven't tried this, however, and as I don't need it currently, I'm not going to investigate it right now. So, if anyone wants to beat me at it, go ahead.

Michael