Mongodb or PostgreSQL? Ah, that is the question...

[gotwf]

Greets All:

Previously only used Redis but bringing up some new forums what will likely have sizable user base. Seems:

1. Redis may be phased out at some point
2. These forums will be much busier than my Redis based.

Hence I am looking at alternatives. Note PostgreSQL support got committed. Kudos to the folks responsible for that. I am a seasoned systems engineer/admin and neither intimidate me, although I've only "played/tested" w/Mongo and never used in production.

Given the above, would those familiar, preferably with each, please share pros and cons of their experiences and any other relevant insights w.r.t. current NodeBB?

TIA-- o/

[julian]

Redis likely won't be phased out, the database abstraction layer was built on Redis and we really just generally have a soft spot for it

That said both Mongo and pgSQL are wonderful, but we have more experience with Mongo. We're actively looking for maintainers for the pgSQL adapter, in case things need fixing, but so far everything has been running quite well.

For what it's worth this forum is running on MongoDB

[scottalanmiller]

Our forum The Republic of IT (we are going through a rebranding) hits as high as 191m hits a month, and has run on MongoDB since day one and it has been awesome. The performance is incredible and maintaining it is super easy.

[gotwf]

Thanks bunches, guys. Appreciated.

Trawling the web it seems Mongodb has been loosing some of it's shine over the past couple years, with some fairly high profile sites switching over to pgsql since it now supports jsonb. For example:

Since The Guardian IS document centric, I suspect MongoDB must have been causing quite substantial amount of grief.

https://blog.shippable.com/why-we-moved-from-nosql-mongodb-to-postgressql

The impetus for such seems to generally stem from scale and maintenance related issues.

Then there is performance, which definitely counts in my book, but NOT at expense of reliability, maintainability, and security. To wit, I've also trawled the NodeBB forums at mongolassi and dailywtf sites. Former is pretty snappy from my workstation whilst the latter has about twice the lag (and I'm being generous here). Maybe hardware or network related, as I've yet to investigate either beyond a bit of clickery:

The Guardian is using AWS's proprietary enhanced PostgreSQL, however, so I'd really love to hear from the daily what the fsck now that they've been on pgsql for a while (as I presume they're not using aws?).

I figured I'd start with Mongo, as there's a postgres converter and hence offers opportunity to switch on down the road, but having outstanding issues since Dec. of 2018, sans any developer response whatsoever raises my eyebrows a bit:

https://github.com/BenLubar/nodebb-postgres-converter/issues

Yes, I know I need to do my own testing but .... I also don't want to invest time and effort repeating what others have already grokked, but maybe not communicated. Not really all that lazy - more impatient to get this latest project online and don't want to delay a couple weeks torture testing each.

Thanks again for your insights.

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

To wit, I've also trawled the NodeBB forums at mangolassi

That's awesome! ML is on MongoDB 3.4. It's running on Linode. I can get you any details you need.

[gotwf]

@scottalanmiller

Awesome. So, are you familiar w/those cases I linked? If so, any thoughts about their issues? Have you experienced any such?

I now do recall that I've used MongoDB in production, a couple thousand AWS and EC vm's several years back, but I was only involved in rolling out some monitoring and metric stuff for that gig so did not have direct hands on w/Mongo admin.

I KNOW PostgreSQL is capable of pretty much any and all heavy lifting and NOT going to fall over on me. I'm more concerned about the NodeBB "port" to pgsql, particularly since y'all be lookin' fer' an active maintainer o.O

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

Awesome. So, are you familiar w/those cases I linked?

Just a bit, I built MangoLassi and still run it

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

I KNOW PostgreSQL is capable of pretty much any and all heavy lifting andNOT going to fall over on me.

Absolutely, PostgreSQL is totally fantastic, I am a huge fan. But NodeBB designs around and tests around MongoDB. And we've had zero issues with MongoDB either. It's very solid, quite enterprise. I don't like their new licensing model, though, but that doesn't affect an open source project like NodeBB.

MongoDB has some quirky update stuff going from major version to major version, but simple stuff, you just have to be aware to prep the database before the move. Other than that, it is super stable and super fast.

[gotwf]

This post is deleted!

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

The Guardian is using AWS's proprietary enhanced PostgreSQL

Yeah, my guess is that a lot of their decision there was based around being on AWS and wanting a hosted SaaS based database rather than running their own.

[gotwf]

@scottalanmiller said in Mongodb or PostgreSQL? Ah, that is the question...:

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

The Guardian is using AWS's proprietary enhanced PostgreSQL

Yeah, my guess is that a lot of their decision there was based around being on AWS and wanting a hosted SaaS based database rather than running their own.

Hopefully the daily wtf notice this thread and weighs in w/their $0.02. Pretty please?

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

@scottalanmiller said in Mongodb or PostgreSQL? Ah, that is the question...:

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

The Guardian is using AWS's proprietary enhanced PostgreSQL

Yeah, my guess is that a lot of their decision there was based around being on AWS and wanting a hosted SaaS based database rather than running their own.

Hopefully the daily wtf notice this thread and weighs in w/their $0.02. Pretty please?

I assume that they are around on here, but don't know for sure.

[gotwf]

I guess I'd like to go with Mongo, but posts like this, especially since appear to be unresolved, or if resolved, not updated to clue in the rest of us.

https://community.nodebb.org/topic/12510/mongodb-crashes-sometimes

https://community.nodebb.org/topic/13574/nodebb-v1-11-1-suddenly-started-crashing/9

Combined w/the stuff I linked earlier... suffice to say like this makes an old grey beard very nervous.

[scottalanmiller]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

I guess I'd like to go with Mongo, but posts like this, especially since appear to be unresolved, or if resolved, not updated to clue in the rest of us.

https://community.nodebb.org/topic/12510/mongodb-crashes-sometimes

https://community.nodebb.org/topic/13574/nodebb-v1-11-1-suddenly-started-crashing/9

Combined w/the stuff I linked earlier... suffice to say like this makes an old grey beard very nervous.

Well, from a quick look...

The first is purely an unknown. There was just zero info in that thread, heaven only knows if MongoDB had a problem at all. He just mentioned that it wasn't running and didn't follow up. So that should be thrown out, it's not indicative of anything.

The second is a real issue, but really just tells us that like any database, it needs to be maintained. NodeBB doesn't require the latest and greated, but it does require relatively currently. Which you would want anyway. There is cause for concern, of course, as data was lost. But as a completely isolated case during an update, it seems like something to note, but not really worry about. The issue could easily not have been on MongoDB's side, especially given the resolution.

[julian]

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

Hopefully the daily wtf notice this thread and weighs in w/their $0.02. Pretty please?

@Ben-Lubar from TDWTF built the pgSQL adaptor that NodeBB uses, actually. They're on postgres at the moment.

[scottalanmiller]

@julian said in Mongodb or PostgreSQL? Ah, that is the question...:

@gotwf said in Mongodb or PostgreSQL? Ah, that is the question...:

Hopefully the daily wtf notice this thread and weighs in w/their $0.02. Pretty please?

@Ben-Lubar from TDWTF built the pgSQL adaptor that NodeBB uses, actually. They're on postgres at the moment.

And it is noticeably a lot slower. But that could be any number of factors, no reason to assume that it is the database. But for it to be the example "less responsive" previously, and then it turns out to be the PostgreSQL example, there is a reasonable chance that the DB is the thing making it slower.

[julian]

Right. Performance could be relative to resource utilisation, however. I know TDWTF had huge troubles with web scraper bots hammering the server to crawl their new content. They even blacklisted a number of user agents for that reason.

Might be the db, or might just be lots of bots crawling the site at any given time

[scottalanmiller]

@julian said in Mongodb or PostgreSQL? Ah, that is the question...:

Right. Performance could be relative to resource utilisation, however. I know TDWTF had huge troubles with web scraper bots hammering the server to crawl their new content. They even blacklisted a number of user agents for that reason.

Might be the db, or might just be lots of bots crawling the site at any given time

Very possible. Although we've seen some pretty heavy hammering from web crawlers in the past, but the site was able to take it. So even if it is web crawls, that might just expose that the DB can't keep up as well. It's a super popular site, and there are lots of factors. How are they caching, how much traffic are they seeing, how many writes are going on, what does the CPU, RAM, IO look like, what NodeJS version on they on, etc.

[gotwf]

Well ain't I a lamer... Does not seem that long since I toyed with MongoDB on SmartOS but Mongo has dropped support since 2017.

Fsck'ing Penguinistas. Too bad, too sad that their junior devs weren't smart enough to pick up a bit of real Unix.

Sure, I could spin up a Linux vm, but I already been there, done that and there are good reasons I prefer SmartOS (yeah, even over fbsd server side).

Thankfully NodeBB devs are NOT asshats and offer me options so I am off to test out the pgsql stuff.

Thanks bunches for all the discussion. Very informative and very much appreciated.

P.S.; any infos on pgsql versions? Docs just say 9.5+ but that was long ago and the need for new maintainer was mentioned so I wonder if post 9.x major revs are "supported"?

[JJSagan]

Pgsql v10 works perfectly for us (although our forum is tiny so it may not be a good test case). @Ben-Lubar however, did a number of performance comparison to mongo-DB, and it looks like pgsql is on par. You can follow his work in tdwtf. Good luck!