Jim Powers wrote:
> Neil Wilson wrote:
> > The point is that making sure that every query is constrained *is*
> > adding complexity. I might get it wrong. My tests might not be
> > complete. There might be a bug,
> In theory, yes. In practice I have never found this to be a problem if
> your users/groups/accounts model is remotely sane.
I don't think I'd go that far. Assume that your application's usage
grows unbounded (like an Internet site), you WILL eventually lose to
Well in my current case, all of my data is partitioned--nothing Client
A sees will ever be seen by Client B. I still think one DB all clients
is much easier.
As for losing to big numbers, I'm not sure exactly what you mean. In
any case, it all depends on your business. At small numbers the key is
to survive long enough to acquire customers. At medium numbers it's to
start making money. At large numbers things start to behave very
differently as off-the-shelf solutions kind of cease to work, period.
> > If you have a separate database (as in 'CREATE DATABASE', tied in
> > with its own database.yml per tenant - not separate mysql/pgsql
> > processes), then I can build my model as a single tenant system.
> > Problem solved (although managing it is another matter).
> You will spend ten times as much maintaining the app as you will
> building it. Building a system that's easier to develop but more
> costly to maintain is not the course I would take.
There is another way to do this: make the database connections dynamic
based on URL or some such. So, you wind up with a single DB connection
in your database.yml file that points to a DB that contains connection
information to the DB used by the client. Assuming that you will
eventually need to update software/schemas you can store a schema/app
version in this master DB lookup table as well. The assumption here is
that for a short period of time you will have two versions of
software/schema deployed as you roll out an update. This would go a
long way to reduce the pain of managing such a system. Now, as far as
convincing AR to work this way... I'm not sure, I am investigating the
possibility as well, and I think it can be done. It may need some
overrides (some of the 'find' methods in AR::Base may need to be
overridden to take a connection object).
This is a !@#$-load of tricky plumbing to avoid setting and watching
client entitlements on database rows. Let alone the havoc this could
wreak with managing the database--depending on which one you use, this
could complicate how you deal with tablespaces and such.
There are other significant benefits to this approach:
- Incremental application migration
This is beneficial if you want to maintain multiple software versions.
If that's the case then you might as well just install complete app
instances per client and be done with it. Been there many times, will
never do it again unless building something like an ERP where it still
makes some sense.
- Overall better performance
TANSTAAFL. If your database server is running twenty database
instances, there is going to be some kind of performance hit to that
versus one DB with tables 20 times larger. The overhead associated with
connection pools and query caches et. al. could in many cases be much
larger than the hit to scanning tables 20 times longer. I just don't
accept this as an open-shut benefit right off the bat.
- The ability to manage performance better (one big hot client can be
moved to their own Db server)
There's no reason you can't do this with a multi-tenant system too. For
that matter you can run a special client on their own complete system
instance with no or very little fancy plumbing.
The DB dumper is something that has to be maintained! You're not
getting out of the fact that you will have to do work to make it seem
like each tenant is an island.
Snap response: Implement some kind of to_sql method which can be called
recursively through the object tree, starting with the root object
representing a client. For all I know facilities for this already exist
within ActiveRecord which after all has to know how to generate SQL. Or
just serialize stuff into yaml, or something like that.
Not to mention that you may find (as I did) that clients want/like
human-readable backups, not SQL dumps.
Furthermore, what about a DB loader?
Read the infile, marshal it into your Model objects, then call the
appropriate new/create/save methods. Now you get all your application
validation goodies for free and there's no chance to create
relationships out of whack with anything else.
The separate DB approach means that you get dump/load for free, then
your only costs are related to how you manage getting a tenant to their
data, and migration issues (not trivial, I know).
I still think the "not trivial" aspect understates it by two-thirds. It
seems to me like you're building a unique configuration that will end
up having a lot more dependencies on the versions of the framework,
O/S, database config, etc. than is obvious from this vantage point. The
end result could be that every time you do a major rev of any piece,
you risk the whole thing falling apart and being the one guy in the
world with that specific problem, and needing to stay on MySQL 3.1 for
a year aftr its release until the low-priroity bug gets fixed. Yeah, I
know it's a hypothetical, but it's the kind of hypothetical that's
bitten me in the rear multiple times. The all-in-one approach has been
by far the easiest to maintain and operate of all the approaches I've
been involved with.
Generally agreed. But the "all tenants in one DB" causes significant
problems. The fact that it is essentially impossible to "isolate" the
performance and storage between tenants at run time generally makes
Rails less, how shall I say this? "agile". Although I don't think it
is too hard to rectify this.
Well like I said above I agree that it poses certain challenges--you
end up needing to build a high-performance application even though all
your customers are 5-seat installations. I do agree that this is
ultimately probably an issue best solved in the database, but I'm not
sure that the approach posited here isn't trading getting stabbed for