Live data from Hacker News

The Great Migration from MongoDB to PostgreSQL

infisical.com

281–290 of 339 posts

Re: The Great Migration from MongoDB to PostgreSQL

#281
post #246

PostgreSQL really is eating the database world. Although in this case, the authors originally chose an architecture that was poorly suited to their data model. They had relational data and put it in a non-relational store. This was obviously always going to cause problems.

Over the years I’ve learned two things when it comes to picking a database when starting a new project 1 - most data is inherently relational 2 - Postgres is pretty good at basically any problem you throw at it, pretty scalably. It will fall over at bonkers amount of scale in some cases but you probably won’t have bonkers amount of scale Thus, if you want to pick any new data store that isn’t Postgres for your projec…

It's a pretty good default stance, yeah.

We have been trying to convince people to use our new database [1] for several years and it's an uphill battle, because Postgres really is the best choice for most people. They really have to need our unique feature (version control) to even consider it over Postgres, and I don't blame them.

[1] https://github.com/dolthub/dolt

Re: The Great Migration from MongoDB to PostgreSQL

#282
I inherited a troubled project using MongoDB and learned a lot about what is, and isn't, really a problem with MongoDB [for this kind of project].

BSON is a terrible format, but I genuinely like that I can use the same schema all the way through the JSON APIs to the database. If they have to deviate in future for some reason, you can deal with it then.

If you are clever with generics, you can compose type-agnostic DB code with type-safe in-memory data structures, without needing any schema generation or ORM. It's a natural fit for data-oriented programming in my workhorses Go and Rust. Unfortunately, the Rust library is very poorly designed in many ways, making many basic things extremely inefficient, and that's not what you want in Rust. The Go library has really slow reflection-based (de)serialization, but other than that, it's very flexible and robust.

If you keep your queries simple, it's easy to mock out and combine with techniques like snapshot testing. Since BSON has a lossless representation in the form of extJSON, you can have human-readable snapshots, diff them easily in review, etc.

Doing bulk writes is easy and efficient, and doing sessions/transactions is easy, but you should basically never combine the two. The server can have its own configured timeout for a session, and if your bulk write exceeds that, it fails and retries, fails again, etc. This is a really serious design flaw that shouldn't have made it past review. If they wanted to enforce a maximum bulk write size, that should be known up-front, not depend on how many seconds it happened to take each time.

Writing data to a replica set is extremely slow, and every index you add makes it slower. This compounds further with the above.

I have concluded that MongoDB only makes sense for read-mostly workloads. If you're going to do your own in-memory data structures and validation anyway, writing out small infrequent changes, MongoDB doesn't do much for you but it also doesn't get in your way very much.

Re: The Great Migration from MongoDB to PostgreSQL

#283

I use MySQL for everything - always have. Can someone hit me with a few reasons why you would use Postgres over MySQL? I don’t have any familial affinity to any database, but I’m not sure what the benefits to Postgres are relative to MySQL.

PostgreSQL supports more SQL features and data types out of the box. Also, it looks like MySQL development has stalled after purchasing by Oracle. PostgreSQL has exciting new features in every release, I forgot when anything significant happened in the MySQL world. It's frozen for like a decade now. There're some new releases, but you won't find anything exciting in the change log.

Re: The Great Migration from MongoDB to PostgreSQL

#284
post #273

Earlier quoted context omitted.

> This is an area where MySQL is so significantly better than Postgres (because so many large Internet companies use MySQL) that it surprises me people aren't more unhappy with the state of things. I’m not sure precisely what you mean by “HA”, but, in my experience, out-of-the-box support for the most basic replication setup in MySQL is pretty bad. Just to rattle off a few examples: Adding a replica involves using my…

> Adding a replica involves using mysqldump That's one path, but it is not the only way, and never has been. MySQL 8.0.17 (released nearly 5 years ago!) added support for physical (binary) copy using the CLONE plugin. And MySQL Shell added logical dump/reload capabilities in 8.0.21, nearly 4 years ago. Third-party solutions for both physical and logical copy have long been available, e.g. xtrabackup and mydumper, res…

CLONE is indeed nifty. But why is it a plugin? And who don’t any of the major hosted services support it? (Or do they? The ones I checked don’t document any support.)

I wouldn’t call xtrabackup or mydumper an out-of-the-box solution.

Re: The Great Migration from MongoDB to PostgreSQL

#285

I've run Postgres at large scale (dozens of machines) at multiple companies. I've also run MongoDB at large scale at multiple companies. I like both generally. I don't really care about data modelling differences - you can build the same applications with approximately the same schema with both if you know what you're doing. I don't understand how folks seemingly ignore Postgres' non-existent out of the box HA and ho…

I feel like I have read this exact comment before verbatim

Re: The Great Migration from MongoDB to PostgreSQL

#286

I've run Postgres at large scale (dozens of machines) at multiple companies. I've also run MongoDB at large scale at multiple companies. I like both generally. I don't really care about data modelling differences - you can build the same applications with approximately the same schema with both if you know what you're doing. I don't understand how folks seemingly ignore Postgres' non-existent out of the box HA and ho…

Citus is open source and well financed. This comment may have made sense a few years ago, but no longer.

Re: The Great Migration from MongoDB to PostgreSQL

#287

Earlier quoted context omitted.

If mongo is just a document database, there's really no reason to use it over elastic. The query story is slightly nicer with mongo, but we're not doing relational algebra here, right? Elastic crushes mongo at literally everything else.

i guess elastic is more heavy setup for some POC.

That might have been true in the past but it's not hard to PoC with elastic using docker compose.

Re: The Great Migration from MongoDB to PostgreSQL

#288

I've run Postgres at large scale (dozens of machines) at multiple companies. I've also run MongoDB at large scale at multiple companies. I like both generally. I don't really care about data modelling differences - you can build the same applications with approximately the same schema with both if you know what you're doing. I don't understand how folks seemingly ignore Postgres' non-existent out of the box HA and ho…

Because vertical scaling can take you so far these days that 99% of companies will never, ever reach the scale where they need more. There is just few incentives. Especially since: - Servers will keep getting better and cheaper with time. - Data is not only in postgres, you probably have redis, clickhouse and others, so the charge is balanced. In fact you may have different dedicated postgres, like one for GIS tasks.…

> vertical scaling can take you so far these days that 99% of companies will never, ever reach the scale where they need more

its less about the scale and more about HA and service interruption: your service will be down if server dies.

Re: The Great Migration from MongoDB to PostgreSQL

#289
post #74

Earlier quoted context omitted.

Extensions and stored procedures are not; they've been around for longer. I do not actually use any of the others you listed (well, occasionally bitmap index scans, but it's not a killer). The main difference between 8.x and 15.x for me is the small stream of optimizer features and tweaks (e.g. at some point, it started being able to reorder outer joins).

Postgres 8 supported functions capable of returning tuples (RETURN QUERY), but lacked transaction control and other features that CREATE PROCEDURE added in Postgres 11 (2018). Extensions in Postgres 8 (and earlier) were also half-baked compared to 9.1 (2011), which added CREATE EXTENSION and other quality-of-life improvements including standardization. The latest complete implementations are what most people have in…

Sure, there are improvements, but it's not like Postgres was a radically different beast back then.

Would I pick Postgres 15 over Postgres 8? Yes, any day of the week. But I would also readily pick Postgres 8 over most of today's databases (including e.g. the latest MySQL and MongoDB versions), for most projects, if that was the choice I had.

Re: The Great Migration from MongoDB to PostgreSQL

#290
post #284

Earlier quoted context omitted.

> Adding a replica involves using mysqldump That's one path, but it is not the only way, and never has been. MySQL 8.0.17 (released nearly 5 years ago!) added support for physical (binary) copy using the CLONE plugin. And MySQL Shell added logical dump/reload capabilities in 8.0.21, nearly 4 years ago. Third-party solutions for both physical and logical copy have long been available, e.g. xtrabackup and mydumper, res…

CLONE is indeed nifty. But why is it a plugin? And who don’t any of the major hosted services support it? (Or do they? The ones I checked don’t document any support.) I wouldn’t call xtrabackup or mydumper an out-of-the-box solution.

What's wrong with CLONE being a MySQL plugin? I mean a good chunk of this page is people praising Postgres for its plugins.

As for support in hosted cloud providers, that's a question for the cloud providers, no one else can answer this. But my best guess would be because they want you to use their in-house data management offerings, snapshot functionality, etc instead of porting MySQL's solution into the security restrictions of their managed environment.

Yes, xtrabackup and mydumper are third-party tools, as I noted. If you needed something out-of-the-box prior to CLONE, the paid MySQL Enterprise Edition has always included a first-party solution (MySQL Enterprise Backup, often abbreviated as MEB). Meanwhile Community Edition users often gravitated to Percona's xtrabackup instead as a similar FOSS equivalent, despite not being a first-party / out-of-the-box tool.

Post reply on HN