Can somebody recommend a database with an API like Mongo's, but performance and durability more like Postgresql or Oracle's?
What I want to do is throw semi-structured JSON data into a database, and define indexes on a few columns that I'd like to do equality and ranged queries on. Mongo seems ideal for this, but I don't needs its performance, and want durability and the ability to run the odd query which covers more data than fits into RAM, without completely falling over.
Right now, the alternative is to do something like the following in Postgres, and have the application code extract a few things from the JSON and duplicate them into database columns when I insert data.
CREATE TABLE collected_data(
source_node_id TEXT NOT NULL,
timestamp INTEGER NOT NULL,
json_data TEXT);
CREATE INDEX collected_data_idx ON collected_data(source_node_id, timestamp);
CouchDB fits the bill. It's all about documents, persistence and defining indexes for range-queries.
Keep in mind two things:
1. It's all on the disk. So, while its throughput is excellent (thousands or more requests per second), each individual request has a latency of ~10ms
2. You define your indexes beforehand (called 'views' in couch terminology), and then you can only make simple queries on them - like by a specific key or by a range of keys. It takes some learning.
If you and your app are ok with both, go for Couch.
First, I would take some of the discussion about MongoDB loosing data with a grain of salt - especially since the really harsh critique is coming from an unknown source, as far as we know, it could be a sinister competitor spreading BS. MongoDB makes the up front choice of performance and scalability over consistency, they have never pretended that was not the case. That does not mean that MongoDB looses data left and right, it means that in the choice between bringing a production app to a halt and loosing data, MongoDB will opt to keep your app running.
Second, and this is a shameless plug, I really believe that the DB I work for (Neo4j) is a good answer to your question. Neo4j makes the same consistency vs. uptime decision that classic RDBMes do. In the choice between bringing a production app to a halt and loosing data, Neo4j will opt for saving the data.
So, to answer your question: Neo4j lets you store semi-structured documents in a manner somewhat similar to MongoDB, with the added comfort of full ACID compliance. It also lets you specify indexes to do exactly what you describe.
A PostgreSQL-specific alternative might be to write triggers in one of the provided procedural languages to turn your JSON into something indexed or materialized elsewhere.
Do either of those work for you?
Also, purely out of curiosity, do you have a design reason for only wanting to store schema-less JSON, or have you just been burned by slow database migrations in the past?
There seems to be a big community of people who really want to reject schema and use JSON for everything, and I'm really curious if they (a) don't understand relational databases, (b) are getting some surprising productivity gains somehow, (c) have been burned by slow database migrations in the past, or (d) some other reason.
All of the above would work, but feels less than ideal. I'm pretty comfortable using a well-schema'd relational database to manage data, but I don't think it fits something I'm working on atm.
I'm collecting and parsing data from a few different types of sources (think: some web page scrapers, Twitter, RSS feeds) for later analysis. I want an intermediate data store where I can throw all of the data together for querying in the short term (within days).
Some of the features I extract from it will probably be stored for longer-term use in a regular database. The JSON itself I expect won't ever be referred to in the long term.
If I can think of a new piece of data I might want to look at, it's very appealing to be able to just print it out in one of the data-gathering programs, without having to touch the entire stack top-to-bottom, deploy a new schema, etc.
What I want to do is throw semi-structured JSON data into a database, and define indexes on a few columns that I'd like to do equality and ranged queries on. Mongo seems ideal for this, but I don't needs its performance, and want durability and the ability to run the odd query which covers more data than fits into RAM, without completely falling over.
Right now, the alternative is to do something like the following in Postgres, and have the application code extract a few things from the JSON and duplicate them into database columns when I insert data.