My current project is essentially a run of the mill document management system.
That said, there are some wrinkles (surprise, surprise). While some of the wrinkles are fairly specific to the project, I believe there are some general observations and questions that have come up which don't have a canonical answer (that I could find, anyway) and that are applicable to a wider problem domain. There's a lot here and I'm not sure it's a good fit for the StackExchange Q&A format but I think it a) an answerable question and b) non-specific enough that it can benefit the community. Some of my considerations are specific to me but I think the question could be of use to anyone faced with deciding on SQL vs NoSQL vs both.
The web app we are building contains data that is clearly relational in nature as well as data that is document-oriented. We would like to have our cake and eat it too.
TL;DR: I think #5 below passes the smell test. Do you? Does anyone have experience with such an integration of SQL and NOSQL in a single application? I tried to list all the possible approaches to this class of problem in below. Have I missed a promising alternative?
- There are many different classes of documents. The requirements already call for dozens of different documents. This number will only ever go up. The best possible case would be one in which we could leverage a simple domain specific language, code generation and a flexible schema so that domain experts could handle the addition of new document classes without the intervention of DBAs or programmers. (Note: already aware we are living out Greenspun's Tenth Rule)
- The integrity of previous successful writes is a central requirement of the project. The data will be business critical. Full ACID semantics on writes can be sacrificed provided that the things that do get succesfully written stay written.
- The documents are themselves complex. The prototype document in our specific case will require storage of 150+ distinct pieces of data per document instance. The pathological case could be an order of magnitude worse, but certainly not two.
- A single class of documents is a moving target subject to updates at a later point in time.
- We like the free stuff we get from Django when we hook it into a relational database. We would like to keep the freebies without having to jump back two Django versions to use the django-nonrel fork. Dumping the ORM entirely is preferable to downgrading to 1.3.
Essentially, it's a mishmash of relational data (your typical web app stuff like users, groups, etc., as well as document metadata that we'll need to be able to slice and dice with complex queries in realtime) and document data (e.g. the hundreds of fields which we have no interest in joining on or querying by - our only use case for the data will be for showing the single document into which it was entered).
I wanted to do a sanity check (if you check my posting history, I'm pretty explicit about the fact that I am not a DBA) on my preferred method as well as enumerate all of the options I've come across for others solving broadly similar problems involving both relational and non-relational data.
1. One table per document class
Each document class gets its own table, with columns for all metadata and data.
- The standard SQL data model is in play.
- Relational data is handled in the best possible way. We'll denormalize later if we need to.
- Django's built-in admin interface is comfortable with introspecting these tables and the ORM can live happily with 100% the data out of the box.
- Maintenance nightmare. Dozens (hundreds?) of tables with (tens of?) thousands of columns.
- Application-level logic responsible for deciding exactly which table to write to. Making the table name a parameter for a query stinks.
- Basically all business logic changes will require schema changes.
- Pathological cases might require striping data for single forms across multiple tables (see: What is the maximum number of columns in a PostgreSQL table?).
- We would probably need to go find a real, honest-to-God DBA who would no doubt end up hating life and us.
2. EAV modeling
There is just a fields table. Entity-Attribute-Value modeling is already well understood. I've included it for completeness. I don't think any new project being started in 2013 would go with an EAV approach on purpose.
- Easy to model.
- More difficult to query.
- DB layer no longer has a straight-forward representation for what constitutes one app-level object.
- We would lose DB-level constraint checking.
- Number of rows on one table will grow 100s-1000s of times faster. Likely future pain point, performance-wise.
- Limited indexing possible.
- DB schema is nonsensical as far as ORM is concerned. Batteries included web app stuff is preserved but custom data models are going to require custom queries.
3. Use PostgreSQL hstore or json fields
Either of these field types would do the trick for storing schemaless data within the context of a relational DB. The only reason I don't jump to this solution immediately is it is relatively new (introduced in version 8.4 so not that new), I have zero previous exposure to it and I am suspicious. It strikes me as wrong for precisely the same reasons I would feel uneasy throwing all my nice, easily normalized data into Mongo - even though Mongo can handle references between documents.
- We get the benefits of the Django ORM and the built-in auth and session management.
- Everything stays in one backend that we've previously used on other projects successfully.
- No experience with this, personally.
- It doesn't look like a very highly used feature. It looks like they get recommended quite a bit to people looking at NOSQL solutions but I don't see a lot of evidence that they are being chosen. This makes me think I must be missing something.
- All values stored are strings. Lose DB-level constraint checking.
- The data in the hstore will never be displayed to the user unless they specifically view a document, but the metadata stored in more standard columns will be. We will be beating that metadata up and I worry the rather large hstores we will be creating might come with performance drawbacks.
4. Go full bore document-oriented
Make all the things documents (in the MongoDB sense). Create a single
collection of type
Document and call it a day. Bring all peripheral
data (including data on user accounts, groups, etc) into mongo as
well. This solution is obviously better than EAV modeling but it feels
wrong to me for the same reason #3 felt wrong - they both feel like
using your hammer as a screwdriver too.
- No need to model data upfront. Have one collection with documents of
Documentand call it a day.
- Known good scaling characteristics, should the collection need to grow to encompass millions or even billions of documents.
- JSON format (BSON) is intuitive for developers.
- As I understand it (which is only vaguely at this point), by being paranoid with regard to write-concern level even a single instance can provide pretty strong data safety in the event of anything and everything up to a hard drive crash.
- The ORM is out the window for Django trunk. Freebies that go out the window with it: the auth framework, the sessions framework, the admin interface, surely many other things.
- Must either use mongo's referencing capabilities (which require multiple queries) or denormalize data. Not only do we lose freebies that we got from Django, we also lose freebies like JOINs we took for granted in PostgreSQL.
- Data safety. When one reads about MongoDB, it seems there is always at least one person referring to how it will up and lose your data. They never cite a particular occurrence and it might all just be hogwash or just related to the old default fire and forget write-concern but it still worries me. We will of course be utilizing a fairly paranoid backup strategy in any case (if data is corrupted silently that could well be immaterial of course..).
5. PostgreSQL and MongoDB
Relational data goes in the relational database and document data goes
in the document-oriented database. The
documents table on the
relational database contains all of the data we might need to index or
slice and dice on as well as a MongoDB ObjectId which we would use
when we needed to query for the actual values of the fields on the
documents. We wouldn't be able to use the ORM or the built-in admin
for the values of the documents themselves but that's not that big of
a loss since the whole app is basically an admin interface for the
documents and we would have likely had to customize that specific part
of the ORM to an unacceptable degree to make it work just the way we
- Each backend does only what it is good at.
- References between models are preserved without requiring multiple queries.
- We get to keep the batteries Django gave us as far as users, sessions, etc are concerned.
- Only need one
documentstable no matter how many different classes of documents are created.
- The less often queried document data is strongly separated from the far more often queried metadata.
- Retrieving document data will require 2 sequential queries, first against the SQL DB and then against the MongoDB (though this is no worse than if the same data had been stored in Mongo and not denormalized)
- Writing will no longer be atomic. A write against a single Mongo document is guaranteed to be atomic and PG obviously can make atomicity guarantees but ensuring atomicity of write across both will require application logic, no doubt with a performance and complexity penalty.
- Two backends = two query languages = two different programs with dissimilar admin requirements = two databases vying for memory.