I converted NodeBB to use a PostgreSQL database. AMA.
-
I'm in the process of converting the 29,920,528 objects in the MongoDB collection to 29,920,528 rows in a one-column table named objects.
You don't have to ask about the stuff in the title if you don't want to.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
to 29,920,528 rows in a one-column table named objects
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
I'm in the process of converting the 29,920,528 objects in the MongoDB collection to 29,920,528 rows in a one-column table named objects.
You don't have to ask about the stuff in the title if you don't want to.
1 - Why?
2 - When will we get @shadowmod giving us forum stats again?
3 - Why?
-
@ben_lubar why one column ?
-
@timebandit said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
to 29,920,528 rows in a one-column table named objects
@accalia said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
I'm in the process of converting the 29,920,528 objects in the MongoDB collection to 29,920,528 rows in a one-column table named objects.
You don't have to ask about the stuff in the title if you don't want to.
1 - Why?
I wanted to see if it was faster to use a good DB badly than to use a bad DB.
@accalia said in I converted NodeBB to use a PostgreSQL database. AMA.:
2 - When will we get @shadowmod giving us forum stats again?
You'd have to ask @PJH. There are MongoDB backups in
/home/mongodb-backupsand a script to create one assudo /root/perform_backup.bash. The database takes about 3-4 hours to restore on my shitty repurposed Linux "server" from what I recall.@accalia said in I converted NodeBB to use a PostgreSQL database. AMA.:
3 - Why?
@PJH was the one running @shadowmod if I remember correctly. If someone wants to request a specific query, my MongoDB !!SCIENCE!! thread is still open.
@cabrito said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar why one column ?
It's basically a direct port of the MongoDB functions to SQL with no changes to the "database" "schema" NodeBB was already using.
Maybe I'll get it to pass test cases eventually...
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
3-4 hours to restore
Anyway, I'll have a look tomorrow if I get time.
-
@pjh said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
3-4 hours to restore
Anyway, I'll have a look tomorrow if I get time.
Most of that time is rebuilding the indexes.
Anyway, the command to restore the database that I use is:
mongorestore --host=wtdwtf-mongo --drop --archive=/data/backup/$archive -vvv(Obviously with some stuff before that to get the archive from the server and after that to clean up, but that's the important part.)
-
Is it faster? Does it use less memory?
-
@wharrgarbl probably, but it's not webscale !!!
-
Why not use a Hadoop HDFS cluster instead?
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
It's basically a direct port of the MongoDB functions to SQL with no changes to the "database" "schema" NodeBB was already using.
The "database" "schema" that drove me of all people - lord of shit database schemas - to give the hell up on that project in the discovery phase.
-
@bb36e said in I converted NodeBB to use a PostgreSQL database. AMA.:
Why not use a Hadoop HDFS cluster instead?
Because then you have several problems where you previously only had one:
Previous state problems:
- MangoDB
Future state problems:
- Hadoop
- HDFS
- Cluster
-
Incidentally, I expect the "path forward" to a real DBMS is to take this setup and, over time, build individual table schemas per object and have your stupid "collection" table be exposed as a view that unions JSON composed on the fly from the individual table schemas and from an underlying collection table for object types you haven't converted yet.
Once every object class has a table, then you hit the Node code and rewire everything to hit the base tables until, one day, you can strange the collection view.
Handling inserts to the collection view is a matter left to the reader.
-
@Weng The one collection business is only because Redis doesn't really have a concept of collections or tables, just objects. Mongo does, but we wanted to support both for the sake of supporting both.
Now with a workable Postgres adapter, then I'm thinking deprecation of the Redis driver may come sooner rather than later. Getting our mongo driver to use collections is something we do want to do...
-
@bb36e said in I converted NodeBB to use a PostgreSQL database. AMA.:
Why not use a Hadoop HDFS cluster instead?
With XML files stored in it?
++
-
@julianlam said in I converted NodeBB to use a PostgreSQL database. AMA.:
The one collection business is only because Redis doesn't really have a concept of collections or tables, just objects.
So don't use it?
"Yeah we did it this shitty way because this really shitty technology that's shit does it the same way." Great.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
I wanted to see if it was faster to use a good DB badly than to use a bad DB.
waiting eagerly for the results
-
@timebandit said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
I wanted to see if it was faster to use a good DB badly than to use a bad DB.
waiting eagerly for the resultsA few seconds ago, I finally got
./nodebb setupto not fail with a SQL error that was my fault, so hopefully I'll be able to test soon.The MongoDB-to-Postgres-column conversion is at 27,100,000 rows on the third attempt now that I've set it to remove NaNs (which I think were from the import script) and null bytes in strings (which were in posts that were imported).
-
@julianlam The test suite output is supposed to look like a Dwarf Fortress that just got visited by a dragon, right?
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
.... Why?
-
@tsaukpaetra said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
.... Why?
People posted some weird shit on Community Server.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
@tsaukpaetra said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
.... Why?
People posted some weird shit on Community Server.
No no, you misunderstand: Why is it still there?
-
root@what:/home/mongodb-backups# ls -larth | grep 08-08 -rw-r--r-- 1 root root 6.4G Aug 8 07:10 mongo-backup-2017-08-08.archive -rw-r--r-- 1 root root 898M Aug 8 07:20 mongo-backup-2017-08-08.archive.gz root@what:/home/mongodb-backups#Yeah - I think I'll be using gzip before bothering to download anything...
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
I converted NodeBB to use a PostgreSQL database
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
29,920,528 rows in a one-column table named objects
That was a brilliant bait-and-switch. :clap_tone0:
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
Most of that time is rebuilding the indexes.
You added in
ginindexes on ajsonbcolumn, I hope?btrees are probably not something we need for the forums, given that they don't help with looking up the key/value pairs...
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
3-4 hours to restore
$ time mongorestore --drop --archive=./mongo-backup-2017-08-08.archive [...] 2017-08-08T09:43:34.117+0100 0.objects 5.75GB 2017-08-08T09:43:34.121+0100 restoring indexes for collection 0.objects from metadata 2017-08-08T09:53:51.275+0100 finished restoring 0.objects (29968347 documents) 2017-08-08T09:53:51.281+0100 done real 24m55.283s user 2m9.720s sys 0m39.760sNow to learn about MongoDB and schemas...
-
Having worked a bit with PostgreSQL's JSON schemas, I expect mongo will actually prove to be faster for this kind of setup.
Mongo started really poorly, but the word on the hipster street is, they kept improving it over the years. These days, it's an almost decent database. Or so they say.
-
@cartman82 said in I converted NodeBB to use a PostgreSQL database. AMA.:
Mongo started really poorly, but the word on the hipster street is, they kept improving it over the years.
I don't think the problem was ever the software, the problem was people not understanding what it was for and using it in inappropriate contexts. (I know there were some software flaws, but it wasn't like sites running MongoDB all lost everything and went out of business all over.)
@cartman82 said in I converted NodeBB to use a PostgreSQL database. AMA.:
These days, it's an almost decent database. Or so they say.
They made concessions to the people using it "wrong". (Always a good strategy-- ask the Excel team.) Now it does atomic operations by default (at least when running on a single server) instead of eventual-consistency operations.
It's also worth mentioning that while MongoDB isn't any kind of paragon of usability, it's a hell of a lot more usable than its competitors in the same space. I'm currently doing work on an Elastic Search server, and goddamned. The only query tool it has is "use curl to send REST requests". Fucking seriously?
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
29,920,528 rows in a one-column table named objects.
Wait... so how exactly does one query that without doing a linear search of all 29,920,528 data objects?
-
@anonymous234 said in I converted NodeBB to use a PostgreSQL database. AMA.:
Wait... so how exactly does one query that without doing a linear search of all 29,920,528 data objects?
-
@weng said in I converted NodeBB to use a PostgreSQL database. AMA.:
Because then you have several problems where you previously only had one:
And this is a bad thing because... :^)
-
@timebandit I assumed PostgreSQL had some fancy JSON indexing thing that you could use to turn it into a discount NoSQL database.
-
@anonymous234 It looks like if you put your data into a JSONB column, it does.
-
@anonymous234 said in I converted NodeBB to use a PostgreSQL database. AMA.:
I assumed PostgreSQL had some fancy JSON indexing thing
Yes.
Sadly no anchors on that page, scroll to / search for
8.14.4. jsonb Indexing
-
@pjh said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
3-4 hours to restore
$ time mongorestore --drop --archive=./mongo-backup-2017-08-08.archive [...] 2017-08-08T09:43:34.117+0100 0.objects 5.75GB 2017-08-08T09:43:34.121+0100 restoring indexes for collection 0.objects from metadata 2017-08-08T09:53:51.275+0100 finished restoring 0.objects (29968347 documents) 2017-08-08T09:53:51.281+0100 done real 24m55.283s user 2m9.720s sys 0m39.760sNow to learn about MongoDB and schemas...
All of your commands will start with
db.objects.and most will start with eitherdb.objects.findordb.objects.aggregate.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
@pjh said in I converted NodeBB to use a PostgreSQL database. AMA.:
Now to learn about MongoDB and schemas...
All of your commands will start with
db.objects.and most will start with eitherdb.objects.findordb.objects.aggregate.Helpful.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
-
@anotherusername said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
Should've used Oracle...
-
@onyx said in I converted NodeBB to use a PostgreSQL database. AMA.:
@anotherusername said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
null bytes in strings (which were in posts that were imported).
Should've used Oracle...
Look at me being such a good boy and not using a blackface reaction image... but not closing my tags, because I'm not that good.
-
@blakeyrat I'm glad to hear that your opinion of MongoDB has changed over the years. Almost gives me hope that one day you'll like NodeBB too.
-
@julianlam said in I converted NodeBB to use a PostgreSQL database. AMA.:
@blakeyrat I'm glad to hear that your opinion of MongoDB has changed over the years. Almost gives me hope that one day you'll like NodeBB too.
Heh. That's not gonna happen.
-
@julianlam said in I converted NodeBB to use a PostgreSQL database. AMA.:
@blakeyrat I'm glad to hear that your opinion of MongoDB has changed over the years. Almost gives me hope that one day you'll like NodeBB too.
Stop reading Revelations! We don't need any apocalyptic-class scenarios around here!
-
@sloosecannon said in I converted NodeBB to use a PostgreSQL database. AMA.:
Heh. That's not gonna happen.
He did say "Almost gives me hope that one day you'll like NodeBB too"
-
@timebandit said in I converted NodeBB to use a PostgreSQL database. AMA.:
@sloosecannon said in I converted NodeBB to use a PostgreSQL database. AMA.:
Heh. That's not gonna happen.
He did say "Almost gives me hope that one day you'll like NodeBB too"
10 points to Gryffindor
-
@julianlam said in I converted NodeBB to use a PostgreSQL database. AMA.:
10 points to Gryffindor
But I'm with Ravenclaw
-
@timebandit said in I converted NodeBB to use a PostgreSQL database. AMA.:
@julianlam said in I converted NodeBB to use a PostgreSQL database. AMA.:
10 points to Gryffindor
But I'm with Ravenclaw
Exactly!
-
@julianlam I'll like it when it's good software.
Also my opinion of Mongo was never all that negative to begin with.
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
@julianlam The test suite output is supposed to look like a Dwarf Fortress that just got visited by a dragon, right?
-
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
@ben_lubar said in I converted NodeBB to use a PostgreSQL database. AMA.:
@julianlam The test suite output is supposed to look like a Dwarf Fortress that just got visited by a dragon, right?
i still see some test failures in there......
-
Points for effort though... much improved
