Bye Bye Mongo, Hello Postgres (2018) | Hacker News Reader

Bye Bye Mongo, Hello Postgres (2018)

theguardian.com

198 pointsmathattack74 comments

The takeway IMO is that the reason their transition was so seamless is because they took the time to do it 'the right way' - that is: set up the new system in parallel, duplicating the reads and writes, and verifying that things looked good every step of the way.

That gives you not only a long window to test and validate, but also the option to rollback if things suddenly don't look so good with the new system. It seems like people sometimes get impatient and want to do a 'big switchover' with insufficient production testing and no real rollback plan, but in my experience that's almost never a good idea.

Indeed, though that must have cost them twice as much to do the migration with the two systems being live in parallel. Some choose to avoid the extra cost.

Cost is not only measured in dollars for servers.

The cost of doing a massive, irreversible migration that doesn’t work seamlessly the first time is significant. And often more significant in engineering time, lost revenue from services going offline, trust both from external users and internal teams watching the shitshow, and possibly data loss or backwards-incompatible changes than those server dollars.

It’s funny that one consistent theme of the best engineers I’ve worked with is less their “amazing 10x ideas” than the number of “sounds like a good idea if you’ve never done it” ideas that they shut down. Huge migrations are one.

I had an example of that recently: we’ve been buying grocery boxes from Hungry Harvest. They announced a new website was coming a couple of weeks ago and had the usually puffery about how it’d be better in vague ways (the old one was not noticeably bad). Then the site was down longer than expected. Then everyone was told their order customizations were lost. Now it’s so bad that they’re skipping an entire week’s delivery for all of their customers.

I don’t know how they got there but that’s expensive, disrupts their supply chain, and I suspect they’d cheerfully pay four times as much not to have to slog through this mess.

They're running their own servers so these databases probably cost fractions of a cent per month.

Actually if you are not running your own servers you can just pay double for a month instead of buying the hardware and having it surplus when the migration is done.

One big drawback of postgres when updating data in a jsonb column is that it needs to rewrite everything even when you update the value of a single key, this gets very expensive if you need to update many rows. As far as I can tell, Mongodb handles this better. Other than this I also prefer postgres despite this limitation.

Mongo stores objects pretty much the same as PG here, a single key update requires a rewrite of the object. Maybe Mongo has some fancy algorithms to avoid full decode/reencode in some cases, but in principle the physical data formats are almost identical

In a tiny bit more detail - mongo uses BSON, which is effectively TLV encoded json. I'm not sure how storage of entire collections is done though.

https://en.wikipedia.org/wiki/BSON

> (...) BSON, which is effectively TLV encoded json.

For those who, like me, never heard of TLV prior to this post, it's type-length-value encoding.

https://en.wikipedia.org/wiki/Type-length-value

thanks for the link, you weren't alone

Disclaimer : I'm a Software Engineer at Oracle

In Oracle 21c and the Autonomous JSON Database, we introduced a new Native JSON Datatype with it's own Binary Storage format called OSON[1]. With OSON, you can perform partial updates on a json document. The added benefit is that this results in significantly lesser redo log size.

[1] https://blogs.oracle.com/jsondb/osonformat

Are there any (independent) benchmarks for how the performance of this compares to other database management systems?

I think standard Oracle license prohibite benchmarking.

This sounds like a fantastic reason among many to actively resist ever using their software.

The benchmark should really compare Oracle to PostgreSQL with however much extra RAM the cost of an Oracle license would buy.

previous discussion

https://news.ycombinator.com/item?id=18717168

When is Mongo ever a good fit? so I've heard stuctured logs, but they can be shoved in a dedicated RDBMS themselves, or just a file system. Unstructured data? But PG now has supports for XML or even JSON. I've heard it's also easier to administrate and scale, which I'm sure it is but then why some businesses do go back to MySQL or PG if Mongo is easier?

Does it take hold purely because it’s easy to install and the company has a hyper-aggressive salesforce?

There's a lot of beginner developers who have somehow not yet ran into problems that a proper relational OLTP would prevent, and love the initial ease of development that Mongo provides for small projects. Some of them go freelance as web developers and complete quite a lot of small projects for small clients without seeing any problems.

MongoDB is really easy to set up and the library API is really good. This provides the affordance in the start of a project where you don't have to think that much about system load from users.

You don't have to think that much about your relational model, so you can postpone all kinds of nasty questions about data integrity and multiplicity early on. This enables a team to move fast and break things.

The switch to something like Postgres comes later in the project where you know your requirements better and you need a system where you have more control over your data. You might even have a person responsible for maintaining your data for you on the payroll.

Personally, I usually just grab Postgres these days and use `json` columns for the documents and loosely fitting data, then drag out things into a more relational model as we go.

I didn't really have practical experience in mongodb. However, after checking latest mongodb documentation, it seems they have magically solved those issues in the last few years

how about these assumptions or myths?

a. treat it as in-memory document database. expect to lose data if it does not successfully write to disk periodically.

b. concurrent reading and writing. it claims that it is resolved for specific scenario in multiple document-level concurrency,while put a lock on single document issue.

c. Total data storage should not be bigger than memory, although it was fixed in v3.2 with new database engine.

d. multi document queries introduced in 4.2 and 4.4, however it is conflicting with their own recommendations that says Each document should be independent, denormalized data model.

I like their syntax, feel so natural. postgres jsonb syntax is unreadable, a bit hacky for me. should i be concerned with mongodb myths?

If you mean fetching/updating foreign-key-related documents by "multi documents queries", I wouldn't do it in RDBMS too. There's no special magic inside RDBMS made those queries much better than fetching related docs manually, but there are always some dark corner cases which will bring your database to full stop in most unfortunate times.

Why do you think some simple joins would risk to "bring your database"? It is code which is used all the time by tons of applications and not "some dark corner cases".

Try to figure out how it will work if both tables are sharded between partitions and some junior engineer will implement `LIMIT x SKIP y` REST pattern on that `SELECT FROM a INNER JOIN b` request with non-primary index sorting, for example.

a) Depends if you run with replication and how you've configured your writeconcern (w and j values). https://docs.mongodb.com/manual/reference/write-concern/#ack...

This is giving you the choice where in the CAP triangle you want to be.

As you can see the default of w:1 and j:null when run in non replicated mode does not write journal to disk before ack. If you want better guarantees read some best-practice articles, most of them recommend w: majority, especially when run in replication.

c) no issue

d) $lookup works fine and is as old as 3.2, but as with any join, if you try to combine 2 tables with millions of rows each, performance will be bad. That said it's not as good as RDBMS here. No foreign keys or voodoo under the hood optimizations for example. Mongodb sales people will always say that you should model your data differently, without really giving good examples of such. But really - even if multi document queries exist, you should not continue modeling data as BCNF and think of mongo as a drop in replacement for RDBS, the domain should be suited for denormalized documents to begin with.

When you never need to work with two different documents at once, but need cheap scale. I once worked on a mobile game which saved player progress by uploading JSON to the server — the server never even parsed it, just saved directly to Mongo. And later retrieved it back when the player logged in.

That was the only time when I experienced a Mongo-based system which didn't suffer from any Mongo-induced problems.

Why even use a server and mongodb behind it for that when you can straight up save the document to Firestore from the game. No servers, no mongodb maintenence.

One is document-oriented database software while the other is a fully-managed document-oriented database-as-a-service, so not at all a useful comparison.

MongoDB offers a fully-managed database-as-a-service as well (Atlas), for what it's worth.

I swa a talk once from one of the facebook game companies who simply stored the player state in a file. There is nothing more simple! They simply loaded the files content into the memory as long as the player was playing and then serialized to file

Mongo used to be a good fit for some projects when JSON-in-postgres wasn't a thing. I work on both Mongo and SQL-heavy projects and I see no reason not to do everything with SQL.

Let me first say, I think all of the criticisms I am reading here are valid. That said, partitioning in PG still isn't great, and though it's better now than in say, v9.x, I'm not sure it's ever going to get where multinode NoSQL is without sacrificing core ACID that makes PG so great. I know it's not in keeping with the general HN dislike of Mongo, but if you have large scale data that has a stable partition key, data that you write and store authoritatively elsewhere like a relational DB, that you don't have to update with real time writes, but you need to do a lot of heavy reads of most of the fields, I think Mongo makes a lot of sense. To be clear, ALL of those things have to apply. And when you factor in ease of setup, adding new nodes, and training new devs that, doubt you will find a better solution.

There's still a rather large fanbase of Mongo on HN, it's just that we have to shut up most of the time.

I don't think anybody will downvote you just for making a point in favor of MongoDB, as long as it's valid.

If you are starting with things that are impossible to define (like the infamous phrase "internet scale") or are plain wrong, then you still don't have to shut up but maybe make sure you like your db of choice for the right reasons.

There are people who love mysql because they use PHP and others who love Mongo because they use nodejs. It's understandable: Those stacks have a lot of users and it's easier to get support. Nevertheless, it's not strictly a strong starting point when people are just discussing databases.

So, don't feel oppressed, just be clear with your reasoning.

> things that are impossible to define (like the infamous phrase "internet scale")

I like to think of that as "personal blog with a couple visits a day".

;)

I think Citus can be used effectively for that scenario now.

https://www.citusdata.com/

I doubt the guardian needs to shard though.

Mongo is very, I say, agile-friendly. With SQL I should always mind schema migrations, and think about it both ways. If I have large enough RDBMS setup with sharding and replication - altering large tables may even became a complete show-stopper.

With Mongo and null-friendly languages (Kotlin, PHP) I can just start writing documents with new fields right away.

Like the parent said, Postgres supports unstructured data with json fields. The syntax is "clean enough" and you can easily migrate to a full column with a single query.

I like that I can easily build a view that includes "columns" from JSONB and normal table columns and clients are none the wiser. The clients get a consistent contract (the view schema) which can be backed by whatever tables make sense (or don't make sense but are used anyways).

I definitely appreciate the flexibility of a document store but I despise not having schemas for clients. Interface contracts reduce errors and ultimately make life easier for everyone. The database also has tons of information in RAM it can use to search and sort data, pushing those operations to the client balloons bandwidth requirements unnecessarily.

If you maintain clean layered code, accessing every collection via corresponding set of repository/data object classes - you will be just fine with MongoDB. But you better do that with SQL database too, anyway.

Mongo has schema enforcement, and if adding index it will keep and serve data straight from RAM.

Having used Django a lot in the past, migrations aren't that much of a problem with a decent tool. Mongo is still quite crap to work with in general.

I’m storing data from a game’s api where each object has an unknown number of unique attributes. Each attribute can have a different type of data, including a nested structure. Additionally, each object’s data is subject to change massively between different versions of the game.

> OpsManager didn’t really deliver on its promise of hassle-free database management.

I'm a Pgsql fan yet the months of work that followed don't seem justified. I wonder if they're still happy with it given how document oriented journalism appears to be from the outside.

In my experience many domains that seem document-oriented suddenly reveal themselves as relation-oriented as soon as you start building.

This goes both ways to an extent. Any data that you can model in Mongo, can be modelled in a SQL database. There's often the argument that documents aren't structured, but in many cases, there's structure if you categorise them well. If one is storing truly randomly-structured records in a document-database, I would question why, as they could store the documents as files instead.

The other way is that you could store documents that fit a relational structure, into a document-store. It depends on use-cases, and those use-cases exist and are valid.

We can store KV structures in a SQL database, but we're seeing projects using Cassandra and co.

You can indeed model relational domains using document stores, but soon you'll start having keys everywhere to glue the different documents together.

This may not be that big of an issue if you have a few different types of entities and you're not keeping that many relationships between them. But you're still going to face problems when you need to change the relationships, an issue which is trivial to resolve in a relational DB. If there are more than a few relationships and they're even just moderately unstable, you've probably made a critical mess of the application.

> We can store KV structures in a SQL database, but we're seeing projects using Cassandra and co.

I think a key difference here is illustrative: you typically only see that when the problem is well understood to need the characteristics of those specialized KV stores and were willing to pay the costs of working with those trade-offs.

In contrast, Mongo was overwhelmingly favored people who were speculating about problems they might have without much experience supporting that call. Usually they never actually came close to that level, likely because they were spending their time on the much harder problem of trying to build SQL database semantics into their application instead of working on their business.

The key lesson I drew is the powerful value of sticking with proven tools unless you know you can’t. Reading about things FAANG work is cool but people cost themselves a lot by not asking how many orders of magnitude separate their workload and team size from yours.

I have experienced the worst of both worlds. Storing JSON documents in a text field in MySQL (the version used didn't yet support JSON), and at my current place we are using Mongo for relational data.

I started doing a demo rewrite of my app in Postrgres from Mongodb. The reality is that even though Portgres seems more stable etc, making queries in Mongodb is much easier and fun.

Postgres JSON operators are cryptic and made my head hurt every time I wanted to accomplish something more complex.

To express it differently, if mongo and postgres where identical operationally, I would choose mongo every day. More friendly and fun. But there are so many horror stories that I am still not sure that it is the right choice for a safe production environment.

I don’t know I was exited to use json for a couple of months but then I needed the same data for analytics and I quickly missed sql.

Some Mongo pro please answer something to this, as without enough experience this is the biggest issue for me in my head.

Mongo's aggregation pipeline is pretty straightforward, so not sure what they're looking for.

Today it’s just fine. I think a decade ago it was pretty risky but databases take 10 years to do properly.

Are there tools for auto converting mongo queries to postgres’ query format? The mongo drivers are Apache licensed so in theory someone could use them as the basis for a driver that speaks to postgres instead, right? I see tools like that showing up in my search results but since I have no experience with them I won’t link to any, if anyone here does have such experience sharing their results would be appreciated.

The thing is, 99% of all JSON-in-DB uses people are advertising (insofar as traditional RDBMS are concerned) are incorrect or at least suboptimally designed. It’s extremely convenient but at the same time, it’s a major cop-out. Designing a database correctly (in the right denormalized form, with the right indexes, with correct relationships, with optima choice of primary keys, etc) is hard work, but it’s critical work to properly take advantage of all the algorithms and optimizations baked into the database engine. I see lots of people migrate from mongo and co to Postgres because they realize it’s a more appropriate data store but I also find them trying to hold on to as much of the original mongo approach as possible rather than just throwing in the towel and embracing traditional db interfacing.

JSON support in Postgres is really not intended to replace - even as a stopgap - what nosql was used for.

> Postgres JSON operators are cryptic and made my head hurt every time I wanted to accomplish something more complex.

I first started working with JSONB columns in Postgres around 2 years ago, I think, and had the same initial impression - the operators are certainly quite alien compared to more typical SQL, and it took me a while to figure out how to do more complex things.

I kept at it though, and now it all feels more natural. I haven't touched Mongo for about 10 years, so it wouldn't be fair of me to compare, but I love the performance and power of JSONB in Postgres. And having all this right alongside regular relational tables in the sr database is fantastic.

I often work with TimescaleDB too, which again just works alongside everything else, in one database - Postgres really is an incredible database!

I honestly wonder, for a team that is unfamiliar with both psql JSONB and mongodb, which of those two would be easier to learn? And later on, which of those two would make the team more productive? I think this is the most important.

To be fair, I use mongoDB for 8 years now compared to less that 1 year of psql JSONB, so I can't really answer objectively.

We used mongo where each DB instance had about a 1gb/s of conti uous read/write traffic, and each instance used around 1TB of memory. So it will probably work for you.

There were some issues, some our fault, some Mongo's, but I think anything will have teething problems at that scale.

Just make sure you don't treat it like a bucket. Use well defined schemas and indexes, like with any DB.

There aren't "so many horror stories" there are the same small number of ancient horror stories replayed over and over.

All newspapers sites should use Cassandra on the backend. It is columnar ...

you would think that the hate that mongodb (and mongodb inc) gets right now that the stock should be shorted to oblivion by now.

long overdue.

I don't think the hate on MongoDB or NoSQL itself is justified. The hate should be directed at developers or CTOs that go "all in" on it because of hype or lack of skill and the illusion that not having to deal with schemas is somehow a silver bullet.

NoSQL databases are great for storing arbitrary JSON-like structures, retrieving them by ID and occasional queries/aggregations on the inner fields of those structures.

NoSQL is absolutely not suitable for most business logic and the lack of a schema is actually a major drawback. It feels like a solution (because inserting invalid data will succeed on NoSQL compared to a conventional DB which will reject it based on constraint violations, missing fields or mismatched column types) but in reality you're just kicking the problem down the road and it will come back to bite you because your application now has to be able to deal with this inconsistent data (and in most cases that isn't accounted for with dealing with NoSQL, and the result is predictable).

I've been on a project where the main database was MongoDB and while it worked fine for the most part, we'd get exceptions when the application tries to read some records (most likely from earlier on in the business' lifetime) that had missing keys and would predictably explode. This isn't the fault of MongoDB by itself - the whole point of it is to be able to deal with unstructured data - but the fact that someone chose to use it while they actually needed structured data and constraints to ensure only valid data is inserted in the DB, which traditional relational databases do provide. Of course, it's easier to blame MongoDB rather than admit "we've been stupid and shouldn't have chosen the wrong tool for the job".

I hate on Mongodb partly because of the super aggressive salesforce that tries to sweet talk less-technical managers and grab internal "champions".

It's a poster boy for making a shoddy product and then trying to paper over the deficiencies with sales and marketing.

Part of this marketing includes creating a super easy, slick set up for beginners and just burying the grenades for beginner users to discover later when they're already locked in.

NoSQL is indeed appropriate sometimes, but Mongo never is.

Personally I would never use NoSQL, or any datastore, without a schema. And yes, there is value in using NoSQL with a well defined schema (for example if you like Mongo's query language).

In your case you didn't pick the wrong tool - your type system and interfaces to the DB (ORM) were probably lacking.

Woah, I didn't know it was public. Went up a ton in the last 2 months too..

> Composer, and its database, originally started their lives in Guardian Cloud – a data centre in the basement of our office near Kings Cross

yes

So if I understand this correctly, they didn’t have the correct understanding/skills to manage a Mongo DB, so they switched to one they were more familiar with.

I wonder what this ultimately cost. Migrating from a document model to a relational DB requires code change in pretty much every single location it’s used. That’s a lot of engineer time to develop, test, and do the data migration.

> they didn’t have the correct understanding/skills to manage a Mongo DB

According to the post they paid Mongo a lot of money for support and they didn’t have the correct understanding/skills either.

It sounds to me like the core problem is their requirement to run Mongo on their own AWS stack rather than Mongo’s, and while Mongo claimed to support it seems like they didn’t really.

No doubt this was a huge migration but from my personal perspective going from Mongo to Postgres feels like a move in the right direction in the long term. For one you have a lot more options for support!

If you hire a room full of developers at high salary and nobody is capable of acquiring "the correct understanding/skills" then maybe it's not such a straightforward technology.

It is reasonably straightforward to deal with relational databases in comparison, this is a well-known space.

> So if I understand this correctly

Clearly you don't.

They did have the correct understanding/skills to manage a Mongo DB, and on top of that they bough the MongoDB-Inc-recommended management tools and the MongoDB-Inc-recommended support plan.

Basically they were doing everything by the book, as advertised and advised by MongoDB itself.

Yet they were having problems, so much so that even MongoDB Inc's own support engineers weren't able to help.

After a great lenght of discomfort they evaluated that MongoDB was not a good fit for them, and moved to PostgreSQL.

> I wonder what this ultimately cost.

> That’s a lot of engineer time to develop, test, and do the data migration.

Downtime is more expensive.

EDIT: Also, the article says that they spent at least two months a year planning and executiting database upgrades, since the OpsManager tool from MongoDB Inc wasn't able to help.

Two months a year planning and executing upgrades isn't that outrageous-sounding to me. We did that for years on a commercially supported RDBMS (MS-SQL Enterprise in our case). It wasn't the whole engineering organization, but the overall "scale existing capabilities" effort was a 3K-10K hour project every year depending on how extensive the work was.

Well it kinda is if you bought a product that supposedly lets you do upgrades easily, from the vendor itself, and also have the support package from the vendor.

Reply on news.ycombinator.com