Switch to MongoDB

[davidread]

Why not replace our database with a JSON-oriented store like MongoDB? By removing the dictization code surrounding most db calls, it would remove a bit of code complexity and really speed up lots of operations which are currently not scaling well. And making the dataset structure more flexible will help mapping properly to hierarchical metadata standards found in DCAT and ISO19119.

It would be great to get thoughts on this. I guess it's weighing up whether the effort is worth the advantages. BTW I have no current experience of Mongo, but hopefully the gist is right.

Advantages:

• The dictized form of objects are used almost exclusively, yet dictization is painfully slow when want lists of datasets, organizations, tags etc. We've ended up using SOLR to store the dictized form of datasets and could do with a similar cache for all objects. There would be no dictization with Mongo, and no need for SOLR to cache. I'm confident that plenty of logic functions in CKAN would be much faster - not only lists of packages/groups/tags, but even for package_show because the SOLR cache is not available the first access, it is not used when you edit a dataset. The edit dataset page in DGU when there are 100 resources takes several seconds.
• Adding new fields to a package is easier in Mongo, and storing dicts and lists is beneficial too. e.g. every field can easily have multilingual versions added, with no more distinction between core fields and extras. Also when mapping DCAT or ISO19119 metadata to CKAN fields, the JSON structure makes it much easier than fudging it in linear extra fields which only store strings.
• Mongo has revisioning built in, eliminating the need for vdm & storing revisions, and also statefulness - we don't need the 'deleted' state any more (although we'd still need the state field for 'pending'). Just get rid of objects completely when you delete, and it simplifies lots of queries.
• The concept of a dataset would be more clearly defined - it would be a bit of JSON containing its tags, extras, groups, rather than a join of Package, PackageTag, PackageExtra, PackageGroup and Member.
• At the same time we could get rid of the word 'Package' everywhere, in favour of Dataset.
• Less code overall - lose most of the dictization.model_save/model_dictize

To discuss:

• The newest version of Postgres and SQLAlchemy do offer JSON types for columns, with new query functions but that is not as good as having the whole of an object as JSON, and I imagine the query system is not as mature or well-known as Mongo.
• When you do package_show, there are several dynamically-generated fields (num_resources, isopen, licence_title, metadata_created/modified) that we'll want to still return. These can be added in in a heavily cut-down package_dictize, but while we're redoing this code, it would be good to flag them up as dynamically generated, or even make them optional. This is useful info to people using the API.
• What are our first order objects? Are they datasets, or maybe they should be put inside organizations? That wed us completely to the one-to-many model of organizations, but it would make it simple when doing package_show and you need to resolve the organization details (for its title). This would still need to be done for groups though.
• I guess we'd use a mongo-based ORM instead of sqlalchemy - decide which.

Work:

• We'd add Mongo indexes to allow quick queries across datasets, such as 'which datasets have tag "river-quality"?' and 'which datasets are in organization "department-of-education"?'.
• We'd have to rewrite lots of logic code and tests to talk to Mongo rather than the ORM. and extension code that talks to the ORM. This code would be a marginally simpler, avoiding the joins within a package. There are 312 queries, although 120 of those are in tests and probably easier to sort out.

Disadvantages:

• You'd still need postgres in most installs. Several extensions use their own db tables. Most could be converted to Mongo, but it might not make sense. (e.g. ckanext-harvest, several DGU ones ckanext-dgu, ckanext-archiver, ckanext-qa, ckanext-report, ckanext-os, ckanext-ga-report). In particular ckanext-spatial uses PostGIS and should remain on that, so Postgres doesn't go completely - it becomes optional for when you use these particular extensions.
• Some extensions still query the core tables, even though they shouldn't with CKAN 2+ e.g. ckanext-dgu so this would require them to change.
• I think coders in general are less comfortable with mongo compared to a relational database. However syntactically it is no less known than SQLAlchemy. The only better thing for lowering the barrier to new coders would be if were to move to Django's ORM for Postgres.

[adamamyl]

mumbles something about "maybe one for CKAN 3.x, especially if CKAN3 gets written from scratch" (personal view, not an OKF one).

[wardi]

More use of postgres JSON features sounds good, like a single JSON field for extras instead of package_extras etc. I quite like postgres, we just need to be using it better.

[rossjones]

I think the idea is worthy of some experimentation. I agree with @adamamyl that fitting it into the current release cycle would be prohibitively hard/time-consuming/expensive, so perhaps we should have a prototype as the basis for discussion on what 3.0 looks like. I'm not overly keen on the idea of having to install postgres and mongo (some other document store) side-by-side. Surely one of the targets for 3.0 should be simpler install?

Overall I'm a +1 on trying it out. Not necessarily because I think performance improvements will come from using mongo (I'm sure by the time journalling and fsync etc are turned on it won't be) but as David points out, from the amount of code that could be deleted.

Aside: How much of spatial actually requires postgis? How much could be done with Solr or ES geo filters?

[teajaymars]

Thanks for writing this up David. My perspective: If I were to start writing CKAN from scratch today, I would without question use a Document Database rather than something relational. Because in my experience CKAN is mostly about storing and retrieving a few kinds of documents, and executing Solr search across them. High-performance querying or clever join queries aren't a common use case; in fact the most common thing people do with CKAN is to populate package_extras and resource_extras because each new deployment requires a slightly different data model.

In fact, the existence of those *_extras fields (and their extreme usefulness) really hints that this is a flexible, extensible document storage engine with an awesome frontend and schemas and validation and all the other valuable features. Very few features take advantage of the relational data model, and none of them require it.

Switching to Mongo would make the application stack much more consistent and much easier to extend. (ie. easier for everybody who isn't core CKAN team!). Also to echo @rossjones we could delete a lot of code :-)

[amercader]

Even if MongoDB fitted the JSON side of things nicely there is still an awful lot of relational things on CKAN: orgs membership, ...

I think we could gain a lot by exploring Postgres JSON field, without having to start a refactoring that is unlikely to happen soon.

Aside: How much of spatial actually requires postgis? How much could be done with Solr or ES geo filters?

Not much, in fact if you are using the newest Solr based search backends (which you should if you have a significant number of datasets) you could perfectly avoid completely the need for PostGIS. I'd be happy to implement this if it helped / was useful

[teajaymars]

@amercader I'd need more persuading that those relationships really constitute a hard requirement for a relational database. You can just store an array of organisation IDs on a user document. The less common and more complex case is where you ask for "all the users who are members of this organisation", but MongoDB supports this -- you'd ask for all user documents where the "membership" array contains the given organisation ID.

A disclaimer; I've not yet built a project on a document database. But they seem to be targeted at the problem of extensible data models, projects which get really messy when you try to force them into a relational database. I agree the refactor would be so hard that it's basically academic, but -- academically -- I'd say CKAN's architecture would be much better served by a document DB than a relational DB. Unless there's something genuinely high-performance or infeasible which requires Postgres.

[wardi]

-1 from me, even for a hypothetical CKAN 3.0

I have much more faith in the skill of the postgres developers. If I lose data I'd like it to be my fault, not my data store's fault. Also it seems any time someone tries an apples-to-apples comparison of postgres and mongo, postgres ends up faster regardless.

Postgres already supports JSON text (faster read/write) and binary (faster select operations) and general-purpose indexes are rapidly improving (indexes on individual json elements already supported).

On the python side, SQLAlchemy is just awesome. We could use it better to get real atomicity when updating multiple complex related objects. With a small amount of work we can also get some really big performance gains.

Lastly, for system tools and developer expertise I doubt any data store will be able to match what's available for postgres.

[jpmckinney]

In my experience, I've deleted more code switching from Mongo to Postgres than the other way around. Mongo is terrible at ensuring data quality - you have do to it all yourself. With Postgres, you have foreign keys, for example, which are easy to take for granted until you start writing all sorts of code to ensure your Mongo database stays consistent. (Also, have you ever tried migrating Mongo? Remember it has no transactions.) @wardi is right - if you use Postgres correctly, you can cut code and have a good quality DB.

I think there are also too many extensions to CKAN to change something as fundamental as the DB.

I'll add that if the plan is to have both Mongo and Posgres - well, now you have two problems.

[davidread]

This has been a worthwhile look at the issues and I have come to some conclusions in favour of keeping Postgresql:

• I'm persuaded by @wardi 's view that Postgres 9.2 JSON fields would solve the flexibility issue I have with extra fields, so I'd be keen we embrace that as a minimum version of Postgres.
• All the other package elements that are currently linked are because of the links across to other packages - group, organization, tag & member - so you'd want foreignkeys and so it makes sense to have a relational model. And you'd get the mongo-ness by caching the dictized versions of objects. We currently cache datasets in SOLR, which might not be the obvious thing to pick, but it's doing a reasonable job and it saves adding a new dependency.
• The dictization code that might have been saved is about 3% of core, so not a big win.
• Postgres is more mature

So I'm closing this. Thanks all.