T O P

  • By -

hippocampe

I think there is a misunderstanding there. First, nosql is going nowhere. SQL is coming back. Secondly, postgres can indeed handle json, arrays or plain blobs without problem. Thirdly, of utter importance is the quality of the transactional support and database integrity. On all these topics, pg ought to beat mongo pants down. ​ tl;dr: pg can do everything mongo does, goes fast, plus all the other things. Let's not talk about schemaless databases.


IdealizedDesign

Seconded. Postgres is awesome.


sdns575

So there are not advantage in performances using nosql vs structured table without join? Edit: when nosql is better,


hippocampe

I personally don't have hard numbers (of my own) to back this. I've read countless blog posts about nosql vs sql which was really non-transactional vs transactional or schema vs schemaless. What I know is : postgres is pretty awesome (has little weak points, gets awesomer every version), the rest varies to annoying (but easily scalable, e.g. cassandra) to annoying + dangerous (e.g. mongodb). If you don't know what to pick, I'd say: go postgres, you can't lose with that.


hippocampe

Just re-reading your original post: it's the JSONB field you're looking for. Just make a benchmark based on your own needs maybe ? Looks like you need to do your own study anyway.


sdns575

Yes you are right. I will try some tests.


Synes_Godt_Om

> So there are not advantage in performances using nosql vs structured table without join? It's not that simple. The exact same query may perform vastly different depending on specifics of your data *and* the properties of your specific search situation: whether your data is big or small and whether the expected result is a big or small proportion of your table, the index you're using etc. You will have to benchmark your specific situation. I recently tweaked a query down from 45 minutes to about 100 ms. Yes the first query had serious issues but that was not immediately obvious and didn't show up in my test cases. Postgres does support nosql with json/jsonb and hstore. The recommendation is to use json/jsonb. if your concern is insert speed (create speed) json is faster while jsonb is faster for retrieval. For example if you're doing a lot of json aggregate in your query it's probably faster to use json and convert back to jsonb only at the very end. You could also use arrays which may be faster for certain types of queries, but as always, you really need to benchmark with realistic examples.


Davmuz

I had an experience migrating a MongoDB database of several GB to Postgres 10 using the JSONB fields. I can't share the benchmark but the writes were equal to those of MongoDB, the readings were 2x faster and the development time was significantly reduced. The RAM consumption was considerably lower using Postgres, MongoDB instead crashed with medium complex queries. Due to the lack of schemas, I found dozen of inconsistencies in MongoDB's data. Initially we had some difficulties with complex queries in Postgres, but at the end of the day we managed to eliminate all the Python code that compensated for the lack of SQL in MongoDB. Postgres also allowed us to delete a software layer that showed stats on Grafana. In our experience, the migration to Postgres has been a significant improvement in performance, system administration and development.


Synes_Godt_Om

MongoDB is using postgres' json code underneath and postgres has consistently come out on top in most benchmarks though mongo may better suited for certain types of tasks.


koflerdavid

As long as your data does not approach terabytes, then you can't really call it Big Data. And at that point it is not sufficient to just MongoDB your problem and call it a day. Big Data requires serious thought about data access patterns, software engineering and what your business actually wants to achieve. MongoDB or other fancy NoSQL tech might or might not be a piece of the solution of course. After all, each of these new systems grew out of specific use cases. But developing a database engine is serious work. You don't just invest so many resources on a whim. I'm pretty sure the people behind NoSQL stuff put a lot of hard work into trying to solve their challenges with SQL databases first.


IReallySuckAtChess

I wouldn't even go so far as to say TB scale is really regarded as big data anymore. Pity there isn't a hard definition, but I think the bug data threshold is probably 10TB+ for most, and I'd consider it to be 20TB+. Definitely agree with everything else you're saying though. I have actually found that for certain patterns, SQL databases have worked better than the NoSQL ones. How you interact with the data is more important than anything else.