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

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.

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.

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

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.

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!

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

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

-faisal

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

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.

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.

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

  http://www.martinfowler.com/bliki/IntegrationDatabase.html
  http://www.martinfowler.com/bliki/ApplicationDatabase.html

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 http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html
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