Why I Migrated Away From MongoDB • Hacker News Board

Re: Why I Migrated Away From MongoDB

#191

Post by einhverfr » Tue, Sep 18, 2012, 7:05 AM UTC

jeltz wrote: post #120

voidr wrote: post #105

Relational databases are awesome if you are not dealing with huge amounts of data that your current hardware can't handle the relational way. There are some cases where you have a ridiculous amount of data(rows) and you simply can't store that in a relational database and you are happy to live without the benefits of relational databases. If you have millions of rows, you are probably better off with something like M…

A relational database at very modest hardware can handle millions of rows, and with a solid database, good hardware and a DBA who knows his shit you can handle billions. OpenStreetMap has over a billion nodes stored in a PostgreSQL database. http://www.openstreetmap.org/stats/data_stats.html My point is that you can get very far with a classic relational database before you have to scale vertically.

Billions of rows btw is not a problem even on modest hardware, with a half-decent db, etc. It's analytics on this where it become possibly an issue. Retrieving one row out of 1 billion is not that much more complex than retrieving one row out of 1 million, nor is it that much more expensive computationally assuming the right index is in place.

The problem comes with high concurrency, in particular very high write concurrency, or with very complex queries which require a lot of RAM to do properly. But that's where you need a solid db, good hardware, and a solid DBA.

Re: Why I Migrated Away From MongoDB

#192

Post by einhverfr » Tue, Sep 18, 2012, 7:11 AM UTC

AlisdairO wrote: post #54

gregjor wrote: post #44

Earlier quoted context omitted.

Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from…

Just in case you weren't already aware, the count(*) issue should be significantly improved for some queries in postgres 9.2, thanks to the addition of covering indexes.

There was discussion on pgsql-general a bit before the release. It appears that physically sequential scan of a table is about as fast as key-order sequential scan of a covering index.

To speed up a lot, I think you'd need to be able to scan an index in physical order which is not currently supported.

Now if you are using SSD's yeah, you could tweak the planner settings enough to cause it to make an index scan perhaps, and maybe you'd see a modest performance increase, but even there it appears the operating system's prefetch logic really does come to the rescue of a sequential scan of a table in physical order.

Re: Why I Migrated Away From MongoDB

#193

Post by jeltz » Tue, Sep 18, 2012, 7:28 AM UTC

einhverfr wrote: post #191

jeltz wrote: post #120

Earlier quoted context omitted.

A relational database at very modest hardware can handle millions of rows, and with a solid database, good hardware and a DBA who knows his shit you can handle billions. OpenStreetMap has over a billion nodes stored in a PostgreSQL database. http://www.openstreetmap.org/stats/data_stats.html My point is that you can get very far with a classic relational database before you have to scale vertically.

Billions of rows btw is not a problem even on modest hardware, with a half-decent db, etc. It's analytics on this where it become possibly an issue. Retrieving one row out of 1 billion is not that much more complex than retrieving one row out of 1 million, nor is it that much more expensive computationally assuming the right index is in place. The problem comes with high concurrency, in particular very high write con…

Very true. If you do not do much with your data it is trivial to handle.

Re: Why I Migrated Away From MongoDB

#194

Post by lmm » Tue, Sep 18, 2012, 7:38 AM UTC

einhverfr wrote: post #168

lmm wrote: post #167

Earlier quoted context omitted.

I'm not saying don't have a rigid schema, just don't enforce it at the storage layer. If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb.

> If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb. Only true in a case where you ahve to index everything you might want to search on, like with InnoDB. In PostgreSQL all you really need are your foreign key indexes and a couple (if that) of criteria indexes and you are good. That's more of an InnoDB limitation tha…

If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb.

Re: Why I Migrated Away From MongoDB

#195

Post by lmm » Tue, Sep 18, 2012, 7:47 AM UTC

adambard wrote: post #28

As a relative idiot when it comes to this sort of thing, I'd like to insert the following supplementary question: what is the sort of application/dataset for which Mongo is particularly suited? I've used it on small projects, and have enjoyed it. Perhaps my data has just been simple/loosely-coupled enough to never run into these problems? I read a lot of posts like this on HN before every trying Mongo, so I've at lea…

Documents with lots of optional fields, or lots of fields that can hold multiple values.

Workloads where writes are rare or you have a single writer separate from your readers, most reads are simple fetch-by-ID, and more complex queries are unpredictable and/or suitable for overnight batch runs.

Workloads where performance doesn't matter but you want schemaless for convenience.

Re: Why I Migrated Away From MongoDB

#196

Post by einhverfr » Tue, Sep 18, 2012, 8:14 AM UTC

lmm wrote: post #194

einhverfr wrote: post #168

Earlier quoted context omitted.

> If you're doing an ad-hoc report then you wouldn't have indexes in place for it in the SQL case, so I don't see how it's any worse or harder in mongodb. Only true in a case where you ahve to index everything you might want to search on, like with InnoDB. In PostgreSQL all you really need are your foreign key indexes and a couple (if that) of criteria indexes and you are good. That's more of an InnoDB limitation tha…

If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb.

>"If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb."

What level of indexing is sufficient depends a great deal on the specifics of the database layout on disk. In InnoDB for example, sequential scans are very costly, and primary key lookups are very cheap. This is because the table is more or less contained in the primary key index and this must be traversed in key order since physical order is not supported. This means a sequential scan of a table means lots of random disk I/O and OS prefetching is useless.

So to address this you end up indexing everything you want to search on later. Note that non-pk indexes are a little slower in InnoDB because you have to traverse the index to find the primary key value, then you have to traverse the primary key index to retrieve the table info.

In PostgreSQL things work differently. The table is a series of pages on disk and rows are allocated from these as a heap. You can scan a table, but not an index, in physical order in PostgreSQL. Therefore typically PostgreSQL sequential scans on tables are lot faster than on MySQL because it is sequential, rather than random, page reads. Indexes point at the tuple ID which stores the page number and row number within a page. An index scan is a tree traversal followed by processing pages indicated in the tuple ID.

This leads to a bunch of interesting things: Adding indexes is usually a performance win with InnoDB. However for PostgreSQL, it will typically look up what indexes it has and balance index scans against sequential scans of tables. Unlike InnoDB, sequential scans sometimes win out planner-wise, esp. on small tables.

So what indexes you need depends quite highly on how things are organized.

Re: Why I Migrated Away From MongoDB

#197

Post by lmm » Tue, Sep 18, 2012, 8:42 AM UTC

einhverfr wrote: post #196

lmm wrote: post #194

Earlier quoted context omitted.

If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb.

>"If that level of indexing is sufficient I don't see why you can't just do the same thing in mongodb." What level of indexing is sufficient depends a great deal on the specifics of the database layout on disk. In InnoDB for example, sequential scans are very costly, and primary key lookups are very cheap. This is because the table is more or less contained in the primary key index and this must be traversed in key o…

That's all pretty interesting, but I still don't see what you get with postgres that you don't get with mongodb. Your database won't enforce your schema for you, but I don't see how that means "ad hoc reporting is impossible".

Re: Why I Migrated Away From MongoDB

#198

Post by stingraycharles » Tue, Sep 18, 2012, 1:54 PM UTC

OJ wrote: post #171

stingraycharles wrote: post #34

Earlier quoted context omitted.

"what is the sort of application/dataset for which Mongo is particularly suited" The majority of the NoSQL databases are based on Amazon's Dynamo: loosely coupled replication. MongoDB is one of the few (next to Hbase and a few others) that adopts Google BigTable's architecture: data is divided in Ranges, and each mongod node serves multiple Ranges. This means MongoDB is able to provide atomicity where it's harder wit…

You said that you don't like the availability issues then state "but at the moment MongoDB is about as good as it gets."? Just. Wow. Get your head out of the sand mate. MongoDB is nowhere NEAR as good as it gets.

I think you misunderstood what I meant; I meant that MongoDB is as good as it gets for our requirements, not availability-wise. I agree if availability is your only concern, there are far better solutions.

Re: Why I Migrated Away From MongoDB

#199

Post by stingraycharles » Tue, Sep 18, 2012, 1:56 PM UTC

edwardcapriolo wrote: post #50

stingraycharles wrote: post #34

Earlier quoted context omitted.

"what is the sort of application/dataset for which Mongo is particularly suited" The majority of the NoSQL databases are based on Amazon's Dynamo: loosely coupled replication. MongoDB is one of the few (next to Hbase and a few others) that adopts Google BigTable's architecture: data is divided in Ranges, and each mongod node serves multiple Ranges. This means MongoDB is able to provide atomicity where it's harder wit…

MongoDB is not even remotely close big table architecture. It has a different data model and a different sharding model, and just about a different everything.

I know that MongoDB is very different in architecture from BigTable (as opposed to HBase and BigTable, for example), but I always understood that the fundamental way they choose to assign and lookup regions to regionservers (or, in mongodb terminology, shards and shard servers) was based on the BigTable architecture.

Could you elaborate on the differences in the sharding model between the two?

Re: Why I Migrated Away From MongoDB

#200

Post by anthony_barker » Tue, Sep 18, 2012, 2:40 PM UTC

Made the same mistake on a banking project 10 years ago (with domino). The project in question was a project tracking database.

For accounting type problems use a relational database. For document driven items - e.g. a resume database - nosql works great. For a hybrid pick your battles... or use both.