Similarly, using an ORM doesn't help you. All that does is hide the details from you. It still uses SQL underneath, but limits what you can do with it.

Let's talk schemas. You have one whether your database understands it or not. Trying to pretend you don't have one doesn't make managing your schema any easier. It doesn't make building indicies on fields any easier.

These are hard problem and pretending they don't exist by rolling your own implementation or hiding them under an ORM won't make them go away.

It's also not about it being a hard problem (which it incidentally is but that's another story). It's about controlling the scale of the problem as time goes on and sufficiently abstracting it without adding complexity. That's not hiding it - that's making it a non issue. Permanently.

I am only advocating using a minimal subset of the functionality to aid migration in the future between engines, platforms, versions and storage paradigms.

An ORM (in our case NHibernate) is a clean abstraction over the top which allows us to be completely mobile between vendors and versions. There is no NIH in it.

The world changes pretty quick. Those who adapt quickly stay around.

An ORM does not replace proper database design. And letting the ORM do all your work for you w/r/t to mobility means accepting that you will not have good access to anything but the least common denominator among databases. Which is approximately the SQL-92 standard.

Another choice, which doesn't involve cutting off one's nose to spite one's face, is to couple the ORM to a public interface which is defined in terms of a specific set of views and stored procedures. Then you can very easily write schema which conforms to this interface whenever you need to switch to a new DBMS. The new schema is then free to take advantage of whatever features that DBMS provides in order to achieve good performance.

And the potential performance gains can be huge. There are plenty of examples out there of replacing 1-hour basic SQL + in-app processing procedures with 100ms common table expressions and the like. If you've got someone who knows what they're doing in charge of it, the latter solution will often take quite a bit less time to write and debug, too.

Ah, therein lies the rub. Spot on.

My obversations:

The last decade has seen a gradual weening off of reliance on the database stack for processing any form of "logic". This has been a evolutionary disaster. When companies like Sun pushed out application platforms such as Java, Microsoft followed suite to remain in the game. That's where the action was and that's where their revenues came from for the good part of 1998-2008 (still does). As a result, the newcomer programmers find it unfathomable to position any amount of processing in the hands of RDBMSs. These newlings choke at the idea of stored procedures or triggers - citing that these constructs are a step in the WRONG direction. Set-based SQL operations remain an anathema for a large majority of the new programming stock.

This is a pity because the side-effect of this attention diversion has been poorer data modeling skills and gross underutilisation of new and often improved RDBMS concepts & tools.

In the end, these new-school graduates of denormalisers and NoSQLers will have to contend with the one and only critical challenge that RDBMSs set out to fix: data correctness.

Performance & scalability mean shyte if you can't guarantee correctness. Bring to the table ACID transactions and MVCC with your new concepts of scalability and performance - then we talk ... kinda thing.

AK

Believe me, we still need ACID/MVCC and by no means do I want to break the consistency part of CAP theorem if possible.

Also set operations break down under the following conditions:

1. when you have to aggregate a LOT of data together they don't scale. If it scaled well we wouldn't need OLAP solutions and could do everything with live, perfectly normalised data. That is just not the way it is.

2. They really don't work for temporal or complex non-uniformly structured data at all well.

You say you still need ACID, but you advocate manually writing joins on top of an ORM layer which does not give you ACID and introduces massive latency. Using an ORM on top of an a key value store doesn't absolve you of ACID or having joins. You still do joins and transactions, but you need to write them manually and test correctness manually. Both are very expensive to do correctly and running some unit tests is not sufficient to show correctness at this level. You are writing these things without recognizing what they are.

The whole NoSQL movement is fundamentally about trading correctness for performance, and usually read performance at that. There is nothing else new about NoSQL databases and ironically many even use a variant of SQL for queries! SQL is just a language, not an implementation.

As for aggregates, yes they are hard. By doing it yourself you are not making the problem less hard. Rather, you are possibly implementing solutions that RDBMS's can't yet do. I suspect that with a reasonable relational design you could probably do whatever aggregates you need to in a high performance manner while still taking advantage of the work done in an RDBMS.

It sounds like you don't understand the requirements of your application. If you need full ACID, your solution with nosql + ORM + manually written joins and queries is failing you. Unless, of course, if you are also writing your own global lock or using some form of lock-free algorithm.

NHibernate, SQLAlchemy, Hibernate are where you need to look and compare.

The ORM does a few joins where a performance gain is possible but we avoid them though as they are expensive on IO and transfer.

Most of our tables match the UI 1:1 and we populate these from the domain model when it is mutated or when we can afford it to be done (either directly or via message queues). Effectively they are materialized views which are updated when data changes or before it is required. Google CQRS.

I'm not suggesting NoSQL - just caution when using RDBMS features. I'm not an advocate of NoSQL.

[1] http://www.amazon.co.uk/Enterprise-Application-Architecture-...

In all those cases, the devil remains in the details. For example if you're just looking to add one or two columns to the result set, an INNER or OUTER JOIN usually reduces the amount of data that has to be shoved through the pipe. That's because you avoid transmitting the keys that are needed to join the data in-app. On the other hand, if you're joining a small number of larger values to a table with lots of rows, perhaps the join duplicates those values enough to really bulk up the result set. But it's effectively impossible to know whether that will be the case without measuring the performance for a couple different options.

Here's an alternative consideration: ORMs tend to encourage a style of coding wherein every column in the table is always grabbed all the time, regardless of how much of that information is actually needed for the task at hand. This is done in order to be able to generate full-fledged objects like the ORM wants to, and to reduce the amount of custom mapping that needs to be maintained. Now if you're looking for a needless waste of bandwidth, there's a needless waste of bandwidth.

If you're consistently having performance problems on joins, it's likely that the real culprit is poor configuration of keys and indexes. Or if you're not watching how the ORM chooses to join the data too closely, perhaps you've got some N+1 queries sneaking in there.

I take it you are aware of Brewer's CAP theorem? (http://www.julianbrowne.com/article/viewer/brewers-cap-theor...) If not, you should be.

Whoah. This is a type of sociopathic reasoning applied to data modeling. The premise is based on accepting compromise knowing fully the consequences of data corruption and yet failing to address the actual rider: data shall not be left in an inconsistent state. An argument turned on its head. Much like asking the question “is it ethical to be deliberately inaccurate on your IRS returns” is answered with “this is not a question about ethics”. Is it not a gigantic assumption to claim:

1. The compromise has “worked” for others (“most successful websites on the planet” [sic]) without providing any real evidence supporting this claim? What does “worked” mean? Is Twitter “ok” with providing suggestions for Followers which contain people already in the user’s follow list? What were the consequences of data inconsistencies in matters related to financial transactions? Was every requirement able to simulate a catch-all “place a back-order” solution? These are real questions that have to be answered before evangelising the compromise.

2. If it works for others (meaning: if you are willing to accept compromises about data consistency) this compromise ought to be brought to your garden-variety corporate environment? Gasp! As it is, volumes or scalability issues aside, corporate environments suffer from data inconsistencies, poor or no transactional control, ineffective referential integrity etc. There’s very little positive impact of encouraging this habit as an overt practise under the false generalised promise of “you will eventually need to scale to millions”.

They can keep their CAP theorem or BASE principles for the likes of social toys like Twitter and Facebook. When credit card companies and banks start accepting compromises on their transactions, I’ll start paying due attention.

Most of these large -- and particularly, global -- systems you speak of are eventually consistent also; they use the relational database as a potent backend member of a distributed system, able to check and deal with a great many constraint problems that can be localized.

If you look at NHibernate for example, if you do paging on SQL 2005+ it will use CTE yet on MySQL it will use LIMIT clauses underneath.

Some enterprise products have a really crappy schema with an ORM thrown over the top - that's where the pain is.

There's also no reason why you can't use multiple smaller databases. Use dblink. http://www.postgresql.org/docs/9.1/static/contrib-dblink-con...

I've never had a problem managing "schema and dependencies" on larger systems. Not sure what you are referring to there.

I'd rather have a slightly higher latency and be able to scale to 100,000 users.

Materialized views are not needed with CQRS architecture. ALL tables (or sets) are materialized views already. No joins are ever made.

dblink is ugly and if you ask me a problem, not a solution (latency, network io, memory).

I'm not sure what you classify as "larger" but I'm talking tens of Tb, 1500 tables and over 2 million LOC in the application.

http://www.databasesoup.com/2012/04/sharding-postgres-with-i...

Billions of records, 10,000 transactions per second. This was a couple of years ago.

Dunno if Skype is complicated enough for you.

http://wiki.eveonline.com/en/wiki/Tranquility#Database_Serve...

serving hundreds of thousands of users

That's not much...

- Compelxity, lock in: The DBMS is like any other software component: If you're actually using it to its capabilities, then it's trivially obvious that it should be difficult to replace, because there are a lot of capabilities you have to find substitutes for. True, you could avoid being coupled to your dependencies by choosing to reinvent wheels whenever possible instead. That would obviously reduce coupling. But bloating your codebase is generally not the best way to make your code more maintainable.

- schema: Pretty much any programming task is hell for people who aren't skilled at it. If you find that you're not very good at using the standard tools of a carpenter, that does not mean that the tools are fundamentally misdesigned. Much more likely, it means that you should be hiring someone else to do your carpentry for you. It's worth pointing out here that programmers shouldn't need to touch the schema in a well-designed system: They should be touching views and stored procedures that abstract away the implementation details.

I view it as an optimization problem. Based upon what you're building, the aforementioned issues are more or less likely to occur. Optimize for what is likely.

If I'm building an official site for a popular product, I'd bet on all the above being true.

Some of what I build, even if 100% of people who could use the product would use it, will run just fine with a single database server that costs in the area of $8k. Planning for scale out is a complete waste of time here.

Most of what I build starts out simple. Most of what I build starts out with 0 users. Will it be popular, will it last, will it need to scale? We have no clue. For these, we keep it simple: modularize our code based upon features, use our datastores to their fullest within features, and keep in mind how we might scale things out (which may ultimately be incorrect) - if we have to. Usually we don't have to. I believe it to be a fair balance in this scenario.

Also, given your constraints, I don't quite understand how you could find a future datastore that is a better solution. How can you exploit better solutions and avoid lockin at the same time? If a better solution comes along in the future, it seems like you would be using features that would lock your datastore to that newer, better solution. However, you cannot do this because you need to avoid lockin.

For a fresh start (greenfield) it's probably fine.

Unfortunately for 90%+ of IT products, it's not a greenfield. That's where the problem comes.

Damn expensive, eh?

As it happens I bought a couple boxes that size just recently. Except with 256G RAM instead of 96G. They cost $8500 per shot. That's not even 2 months of salary for a single developer.

Don't underestimate Moore's Law.

How much does the storage subsystem cost?

You know the whole stack of fully redundant iSCSI/FC kit behind it?

15x 146G SAS spindles are included with the price.

You know the whole stack of fully redundant iSCSI/FC kit behind it?

Why, yes? We do run a FC fabric and various DAS boxes for various purposes, but not on the DB servers.

I'm not sure what you're arguing about here, the figures (labor vs hardware cost) are not even in the same ballpark.

As a rule of thumb: If a piece of gear can save your team 1 month of work then it usually pays for itself.

In the US I've heard good things about ThinkMate. E.g. this is close to our configuration but with a different chassis; http://www.thinkmate.com/System/RAX_QS5-4410

http://blogs.msdn.com/b/elee/archive/2009/03/11/source-code-...

In general throw money at the problem first, resort to code last

What happens when someone has to do a SQL update manually? Referential integrity can save you from shooting yourself in the foot there. SQL injection? Much less likely if you're using stored procedures with parameters as your client interface.

You have lock-in all over the place regardless. Languages. Frameworks. ORMs. Operating systems. All are decisions that are painful to change when something "better" comes along. You're better off making those decisions and then leveraging them for all you can get. RDBMSs have been around longer than many languages and most modern frameworks, and are still a very good, probably the best, general purpose solution for data storage and management.

You don't do manual SQL updates. Why would you if you have an ORM? You update the domain and it does all of that stuff for you. I only care about the domain which is the stuff that makes the money - not writing SQL.

ORMs don't suffer from SQL injection as they use properly parameterised prepared statements.

Regarding lock in you are correct, but the RDBMS is the most politicised and expensive component which is why yo uneed the flexibility.

I am in no way saying "RDBMSs are shit - NoSQL forever!".

The DBA you refuse to hire would likely disagree.

Databases are for putting stuff in when you don't immediately need it. The DBA mindset pushes that the DB is the hammer for all nails.

You clearly are not in willing to reflect on the actual problems being presented to you. Instead you are clinging desperately to some sort of ideology that either you yourself came up with or someone sold you on.

Look at the basic problems. You are not solving them or making them go away by "abstracting them away." The reason is that these particular problems can't be abstracted away.

For example, you raise joins into the code that your programmers write instead of the RDMBS level. But you are still writing joins, they are just for loops on top of your ORM solution. Now you incur the cost of writing them manually each time and testing them, also dealing with ACID which almost surely you are not doing correctly. Or if you are, you are spending huge amounts of money to make it right. Even worse, your solution can't possible be as fast as the RDBMS solution since you need to go through all sorts of heavy abstraction.

You are not seeing the actual problems at all and not surprisingly demonizing the type of people who do understand them: DBAs (assuming we are talking about expensive professional DBAs).

The ideology came out of necessity and 20 years of watching people fall down the same holes.

Joins - loops on top of ORM? Doing ACID myself? Do you understand what a non-toy ORM does? Do you understand the separation between the data model and the domain model? Sounds like you don't.

http://docs.sqlalchemy.org/en/latest/orm/query.html

Note the section on joins. Their example is simple, a mapping between users and addresses. The ORM here generates a join in SQL. You are not avoiding it. The only alternative to generating SQL with a join is to write the join yourself. Such a thing would query users and then either lookup the address via an index or do a nested scan over addresses if you don't have an index. There is no way around it. Conceptually, a join is a description of a result set that you want. If you generate it, you are doing a join in some way.

If you are doing full ACID with complex queries, then you still need to understand transactions and all the associated complexities. An ORM doesn't make any of that go away by using terminologies like sessions. Rather it limits you to some subset of SQL and schema design.

If you want to limit your use of SQL for portability reasons, you can still do that without an ORM. At least that way you are more aware of what is actually going on under the hood and can more easily identify problems.

Redis solves a certain set of problems for me, Memcached as well. ElasticSearch solves another set, and RDBMS yet another. Square pegs for square holes, round pegs for round holes.

I can understand wanting to avoid Oracle lockin.... But Postgres?

Well we could shift a deployment to Amazon RDS (Oracle or MySQL) quite happily in a couple of hours without having to rewrite everything because we used all the postgresql features...

On the other hand, I could name quite a few people who went the other way, like Urban Dictionary who run on our http://postgres.heroku.com service, too. (I don't normally name customers, but he recorded a video saying he liked us so I think it's fair game. http://success.heroku.com/urbandictionary )

Also contrary to the norm here, some applications last a long time. The one I work with daily is actually 20 years old this year. In that time a lot has changed and in the next 20 years a lot will as well. It makes sense for long-lived applications to plan well ahead and consider paradigm and architecture changes.

This platform started in C++ on OS/2 with Oracle running on VAX/VMS at the back end. It went to Java/J2EE with an Oracle back end in the early 00's. It's now SQL Server and .Net.

Compare to compilers being better at writing machine code...

When you have the tools you have the power.

Probably about the same as you on just SQL Server and half as much again on Oracle...