Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MongoDB queries don’t always return all matching documents

engineering.meteor.com

Post reply on HN
Print view View original

341–350 of 419 posts

Re: MongoDB queries don’t always return all matching documents

#341

Post by snaky »

jeltz wrote: post #311
snaky wrote: post #298

Earlier quoted context omitted.

> SQL constraints are very limited Oh really? What exactly cannot be expressed in PostgreSQL check constraint, given the fact you can write it in any programming language you prefer?

Arbitrary constraints which look at multiple rows require serializable isolation to work under concurrency, and most people do not run their databases at serializable. But you get pretty far with single row check constraints and exclusion constraints (exclusion constraints can be used for checking against range overlap).

There's no need of serializable level of isolation in common case. It depends on particular data model, particular access pattern, particular database implementation (MVCC or blocking, and how exactly transaction isolation levels are implemented - e.g. Oracle's "serializable" is closer to PostgreSQL's "repeatable read").

Anyway, any sufficient modern RDBMS implementation provides pretty good level of performance even with serializable isolation level, thanks to decades of tuning and research in the field.

Re: MongoDB queries don’t always return all matching documents

#342

Post by jeltz »

tremon wrote: post #325
jeltz wrote: post #311

Earlier quoted context omitted.

Arbitrary constraints which look at multiple rows require serializable isolation to work under concurrency, and most people do not run their databases at serializable. But you get pretty far with single row check constraints and exclusion constraints (exclusion constraints can be used for checking against range overlap).

Can you give a use-case for such a constraint that can not be avoided by a better data organization? I'm not sure why you would deliberately design your data with constraints based on other row contents. Also: how do you enforce such constraints with MongoDB exactly? If the answer is "do it in the application", then your answer applies to relational databases too.

A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).

When I have solved this case I have done it in the application by making sure we are only using a small set of carefully audited stored procedures to modify the event table.

MongoDB does not even try to solve this kind of problem, and I am the wrong guy to ask if you want someone to try to make a case for using it anywhere.

Re: MongoDB queries don’t always return all matching documents

#343

Post by eloff »

nrinaudo wrote: post #277
Lazare wrote: post #124

Earlier quoted context omitted.

> Because some people can't stand having to work with SQL,migrations,schema and constraints The thing is, if you actually try and write an app using MongoDB, you will rapidly find that you: 1) Have migrations (except they're going to be some scary ad hoc nodejs script that loop through your document store and modify fields on the fly). 2) Have schemas (except they'll be implicit and undocumented) 3) Constraints (exce…

I often liken NoSQL databases to dynamically typed languages. With a NoSQL database, you have an implicit schema, but it will only be enforced and fail at runtime - when your code expects a field but failed to find it, for instance. With a dynamically typed language, you have implicit types, but only enforced at runtime - when your code expects a value to be an int but finds a boolean, for instance. And both are fine…

You and Lazare are right on the money. And the thing with the database is that the code that inserts/updates it has to agree with all the querying code about what the implicit schema should be - but it's implicit and scattered around your code - so on a large team it's very hard for everyone to understand that implicit contract and it's going to be a constant source of production bugs.

Schemas don't change that much compared to code, having a strict schema enforced by the database saves you so much time and pain and downtime in the long run.

Re: MongoDB queries don’t always return all matching documents

#344

Post by infinite8s »

vegabook wrote: post #25

I have moved from Mongo to Cassandra in a financial time series context, and it's what I should have done straight from the getgo. I don't see Cassandra as that much more difficult to setup than Mongo, certainly no harder than Postgres IMHO, even in a cluster, and what you get leaves everything else in the dust if you can wrap your mind around its key-key-value store engine. It brings enormous benefits to a huge clas…

Did you evaluate Postgres with something like citus to handle timeseries data?

Re: MongoDB queries don’t always return all matching documents

#345

Post by tremon »

jeltz wrote: post #342
tremon wrote: post #325

Earlier quoted context omitted.

Can you give a use-case for such a constraint that can not be avoided by a better data organization? I'm not sure why you would deliberately design your data with constraints based on other row contents. Also: how do you enforce such constraints with MongoDB exactly? If the answer is "do it in the application", then your answer applies to relational databases too.

A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob). When I have solved this case I have done it in the a…

A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).

Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.

Re: MongoDB queries don’t always return all matching documents

#346

Post by StavrosK »

collyw wrote: post #336
StavrosK wrote: post #156

Earlier quoted context omitted.

The single biggest source of grief in our production database has been the one JSON field we used once to avoid adding another table. That goddamn thing has crashed the server so many times with invalid data, that I'm never using anything schemaless again. We recently migrated to a proper table and I'm thanking my lucky stars I finally got rid of that devil.

JSON field? You are lucky. We have a Pickled python object stored in our database. Wonderful for debugging when its pretty much unreadable.

My condolences :(

Re: MongoDB queries don’t always return all matching documents

#347

Post by SCdF »

skeoh wrote: post #103
fiatjaf wrote: post #100

CouchDB is simple and reliable. You can understand it from day one. I can't imagine why it isn't being used.

I really want an excuse to build something with CouchDB and PouchDB ( https://pouchdb.com/ ). Can you expand on your experiences with it?

A bit late to the party, but we use Couch + Pouch at work.

It is in some ways magic, and I literally don't know how we'd achieve what we do without it.

But.

The big pain for us is filtered replication: we have a bunch of cell phones (Pouch) that should only see some of the documents from the server (Couch). No mater how you slice it pretty much, you need filtered replication in there somewhere.

And it's sllllooooooowwwwwwwww. It basically canes a CPU core while it's running per user, which means you can have CORE user's replicating at any given time, which is terrible. There isn't really much a solution for it, except "don't use filtered replication".

I recently posted to the mailing list about it, you can read more here: https://mail-archives.apache.org/mod_mbox/couchdb-user/20160... (I'm the OP in that thread).

Re: MongoDB queries don’t always return all matching documents

#348

Post by s_kilk »

m_mueller wrote: post #337
s_kilk wrote: post #253

Earlier quoted context omitted.

Is CouchDB still alive? I spent a weekend playing with it in January, but it seemed to be a very quiet project, with the last stable release being almost two years ago.

Most of the activity happens at Couchbase now, the company that the inventor D. Katz founded based on CouchDB technology. You can still use Couchbase for free, but it's possible to pay for support. The coolest thing they have is Couchbase Lite, the mobile version of CouchDB, lets you replicate with your server. I find it a very interesting alternative to Core Data, parse and co. and we use it in production.

Good to know, will shift attention to Couchbase and re-assess. Thanks!

Re: MongoDB queries don’t always return all matching documents

#349

Post by snaky »

tremon wrote: post #328
chris_wot wrote: post #290

Earlier quoted context omitted.

There is no spoon?

There can be only cloud. Also, the cloud is Turing complete and has infinite tape.

The Eternal Mainframe in the sky. With diamonds.

http://www.winestockwebdesign.com/Essays/Eternal_Mainframe.h...

Re: MongoDB queries don’t always return all matching documents

#350

Post by jeltz »

snaky wrote: post #341
jeltz wrote: post #311

Earlier quoted context omitted.

Arbitrary constraints which look at multiple rows require serializable isolation to work under concurrency, and most people do not run their databases at serializable. But you get pretty far with single row check constraints and exclusion constraints (exclusion constraints can be used for checking against range overlap).

There's no need of serializable level of isolation in common case. It depends on particular data model, particular access pattern, particular database implementation (MVCC or blocking, and how exactly transaction isolation levels are implemented - e.g. Oracle's "serializable" is closer to PostgreSQL's "repeatable read"). Anyway, any sufficient modern RDBMS implementation provides pretty good level of performance even…

Not in the common case, but it is necessary in the general case. One of the main motivations I have heard from the few people actually using serializable in their systems is the ability to enforce arbitrary constraints under parallelism.

And, yes, performance should in general still be good, but there is less knowledge out there about how to solve the performance issues specific to serializable since there are few people who use it (at least in the PostgreSQL world).

Post reply on HN