14 ms·

Ask HN: Have you ever chosen Postgres over Mongo and regretted it?

233 points by chatmasta 8y ago

The past 2 years seem to have been the death knell for Mongo and the "NoSQL" hype, with Mongo moving toward SQL parity and SQL databases supporting JSON storage, like Postgres with JSONB. At this point it seems that there are very few workloads for which Mongo is the best option compared to Postgres+JSONB. I'm wondering... 1) Have you ever chosen Postgres over Mongo, and regretted it? Why? Did you end up switching to Mongo? Was it better? 2) Can anyone name a use case where Mongo is clearly a better choice than Postgres+JSONB?

paulie_a 8y ago
I would never choose mongodb after using it. Postgres has never failed me in a decade, where mongo is huge pain in the ass in the last year. Honestly the concept of schema less is stupid. You just eventually have to implement SQL to the documents. It's an obnoxious waste of time.
vorpalhex 8y ago
These are fundamentally different database technologies. Postgres is a powerful SQL based store while Mongo was originally built around the idea of a write never failing. Postgres tends to keep a speed edge but the jsonb syntax is awkward and some operations can be trickier for a developer to navigate. Scaling uses traditional sql strategies. Mongo has a pretty nifty query language but more difficult queries start becoming quite slow. Scaling is a bit different then Postgres. Like any tech choice, it's not about what is vogue or trendy but what best fits your problem space.
- davidgl 8y ago
  This is a recent attempt to make a nicer Document API for PG: https://github.com/robconery/dox https://github.com/robconery/dox
- deleted 8y ago
  [deleted]
- kqr 8y ago
  > built around the idea of a write never failing. What am I missing if I think this sounds like a very interesting thought experiment, but completely disconnected from reality?
  - zzzcpan 8y ago
    In distributed systems getting to a failing state is exactly the same as getting to a succeeding state, both have to make CAP consistency/availability trade offs. So in many cases not failing is just easier.
  - vorpalhex 8y ago
    The tradeoff is "consistency". As long as some node of Mongo is running, it'll do it's absolute damned best to accept a write, including buffering in memory and all kinds of other tricks. However, if you write to it and then immediately read.. you might not get back what you just wrote (but it will eventually show up in reads). Likewise, you might get different responses from different nodes in some weird situations. A few things to note: This weird behavior is only in a failure state (eg a network partition), and I believe it's no longer the default but can be enabled. It's very useful for lots of applications, but isn't the right choice for all applications.
reustle 8y ago
I've used both mongo and postgres in large production environment. For small projects that I plan on keeping small (like personal tools) I'm often happier when I leave it as mongo, vs dealing with schemas and migrations in the future.
- colonelpopcorn 8y ago
  I do like the "just throw whatever you want in this document" approach that Mongo has.
  - StavrosK 8y ago
    I've regretted that every time I've done it, with the usual "why is this document not like the others" failure case. Nowadays I use schemaless databases/fields extremely sparingly. Relevant talk: https://www.youtube.com/watch?v=BN8Ne2JCGBs https://www.youtube.com/watch?v=BN8Ne2JCGBs
    - tyfon 8y ago
      We are using azure documentdb for a project at work and it already has like 3-4 structural versions of the documents in the db that has to be accounted for by the applications and reporting tools, the latter of which I am working on. It is somewhat similar to Mongo. It's starting to become really painful and I'd be extremely happy if they moved it to a relational database.
      - StavrosK 8y ago
        That's been my experience too. Very rarely is your data model non-relational and schemaless, and if it isn't, you end up having to manage all the relations and cascades and everything yourself, which ends up being a huge pain.
        e12e 8y ago
        > Very rarely is your data model non-relational and schemaless This of course completely false - which is why we use filesytems and files, more than we use databases ;-) But what I think is less clear and obvious - is when is a document database better than files and the file system? This is kind of the question, are maildirs/mbox any good at storing mail - and are they any good at searching mail? And is sql better at either? This isn't clear cut - there are things like dbmail[d] that just uses postgres and layer imap on top/in front - while a lot of servers prefer some form of file storage augmented with custom indexing. [d] http://dbmail.org/ http://dbmail.org/
dijit 8y ago
I can think of reasons. I have a personal dislike of mongodb, for many reasons but I’m going to put them aside for a moment. MongoDB beats PostgreSQL because it’s relatively easy to shard and scale. If your application logic isn’t quite capable of sharding then mongodb handles this and replication for you. It’s also true that mongodb attempts quorum over consistency. Which is just fundamentally different than how PostgreSQL works, it is possible that mongodb can be faster in a very large mesh topology. What I am hoping for in future is that something like elasticsearch exists but with PostgreSQL as it’s engine instead of lucene. Such a system would be a complete death knell to mongodb.
- SOLAR_FIELDS 8y ago
  It's not native support but more than a couple times I have wondered how well this works: https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb Doesn't seem to be a whole lot of people trying it out, though the project is 'relatively' active.
- wenc 8y ago
  Yes. I think the major advantage of NoSQL databases was easy horizontal scaling (shard + scale + seamless failover) at a time when it was difficult to do these things with SQL databases. However, NoSQL's original advantages have started to fade with db's like Citus and TimescaleDB, and even FB's MyRocks. NoSQL databases are optimized for straight data retrieval and horizontal scaling. If you want to dump lots of naturally unstructured or semi- or variable-structured data (like logs) into a distributed data store and retrieve them quickly without doing any non-trivial manipulation on them, Mongo will likely fit the use case. It's fast, strongly consistent, supports secondary indices etc. However, as soon as you need to manipulate your data (i.e. analytics use cases), you're much better off doing a bit of data modeling up front and using a SQL database.
DanHulton 8y ago
Postgres cannot (without some wild and wooly SQL) do an increment upset in a JSONB field. I.e. If I wanted to add 1 to a JSONB attribute that may not exist, I cannot do this, not simply. I'd, of course, love to be wrong about this, but when I looked into it about six months ago, the best solution I could find was a hack that had untested performance characteristics. While in Mongo, it's a very simple, standard operation.
- robbyt 8y ago
  Generally speaking, I like to use json tables for inserts only.
- brandur 8y ago
  See the `||` operator and `jsonb_set` (use Cmd-F): https://www.postgresql.org/docs/current/static/functions-json.html https://www.postgresql.org/docs/current/static/functions-jso... From the docs for the `||` operator: > Concatenate two jsonb values into a new jsonb value And for `jsonb_set`: > Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. You'd use this function in conjunction with an `UPDATE` statement to get the result that you want.
- chatmasta 8y ago
  If you are incrementing a field, then by definition you know it exists and is an integer. In that case, what advantage are you gaining by storing it in JSONB? Just factor the integer field out of the JSONB column and into the table schema.
  - rpedela 8y ago
    I deal with messy data often and there are definitely times when a JSON row should contain a field but doesn't. If I also needed to increment the field as part of cleaning the data, it would also be nice to upsert in the cases where the field is missing. Extracting a field into its own column may make sense sometimes, but usually I just need the data to be correct and stored rather than be queried or updated frequently so adding another column is a waste of time and space.
013a 8y ago
Generally speaking, I don't like MongoDB. Though it has little to do with the database itself: its more so a combination of the limited use-cases of NoSQL and the lack of managed hosting options directly on AWS or GCP (Azure can kind of do Mongo via CosmosDB, but I don't use Azure and don't plan to at this time). If I've just got a small project that needs to throw data somewhere, I'll probably reach for Redis, DynamoDB, or (if its simple enough) just serializing JSON files to S3 (this is an underutilized way of storing structured data if all you need is key-value lookup with no latency or consistency guarantees, very cheap). Its likely my project is already on App Engine or Lambda, so I don't want to have to go to a second login portal just to manage the database. I don't hate NoSQL. I prefer SQL, but I also respect the fact that if you combine something like Mongo with in-app schemas, and are careful about keeping those up to date, writing proper migrations, etc, you can get a very production ready and highly scaleable DB out of Mongo. Its not as bad as people make it out to be.
- mcfedr 8y ago
  For hosting options I've recently discovered mongo atlas, and been very happy basically. It's dead simple and solid. Just like using RDS or cloud SQL.
tnolet 8y ago
Practical example from the last ~8 months. I started building my somewhat non-trivial Saas [0] with MongoDB + NodeJS. Getting started was quick. Soon however, things started playing up: - transactions - aggregations (avg, percentiles), rollups etc. - simply having a relational model to express the business domain I switched to Postgres and I've been more than happy with it. Heroku hosting is top notch. Libraries are pretty mature. I use the JSONB and Array datatypes not heavily even. Turns out an "exact" data model is quite useful on anything none-trivial. As always, YMMV [0] https://checklyhq.com https://checklyhq.com
- nautilus12 8y ago
  Think they asked about the opposite case.
  - rhencke 8y ago
    I think OP's comment is a pretty clear answer to question 1: "Have you ever chosen Postgres over Mongo, and regretted it?" OP chose Postgres over Mongo, and did not regret it.
  - ris 8y ago
    And he answered it, with the answer being "no".
- mau04 8y ago
  Which ORM do you use?
  - tnolet 8y ago
    I use knex and objection.js
themtutty 8y ago
tl;dr Nope, although Mongo is fun to ride around in the backyard.
deleted 8y ago
[deleted]
Cieplak 8y ago
I have never regretted it. We dropped Mongo completely about a year after JSONB support was released for Postgres. JSONB indexes perform very well, and can be tuned with different index types (e.g. btree, gin, hash). You get the best of both worlds, i.e. relational models with joins and aggregates, as well as flexible and dynamic document storage. If you want a pure JSON interface to your database, use PostgREST: https://postgrest.org/en/v5.0/api.html https://postgrest.org/en/v5.0/api.html
- sv12l 8y ago
  Just checking, as far as I understand, at the moment Postgres can't add statistics on the JSONB type. Did that affect your query optimizer by any chance? or noticed any degradation in performance?
  - netcraft 8y ago
    You can get around that with functional indexes https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-and-statistics/ https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-a... I'm not working with tens of millions of records, but I haven't needed to go beyond GIN indexes yet - might just be our documents or our workload though. ymmv.
    - ris 8y ago
      But ... once you're deciding what expression indexes to add you effectively have a schema, yeah?
      - qaq 8y ago
        well thats not too different from NoSQL you will get something like GIN index by default at best. In mongo you also have to create indexes.
- ganonm 8y ago
  My experience with JSONB is that you should think very carefully before choosing to use it as it can become a real footgun if used inappropriately. An example of a (possibly) good use case would be e.g. user preferences/settings - there is often a lot of them and the relationship between them is often hierarchical. You could use e.g. a junction table that maps users to settings, but this can be unwieldy. A JSONB value can just be sent directly to client. It is possible to be reasonably certain that you won't want to do aggregate SQL operations on the entire table and thus just being able to do CRUD operations on single rows of the table avoids the pain of JSONB aggregate queries. For the record, I would probably still use a SQL solution in this case. An example of a bad use case (which I saw in the wild) would be lumping entire user conversations (think Facebook comments) into a single JSONB value. This might naively seem like a time saver since you don't need a 'conversations' table, a 'conversation_messages' table nor the JOIN to aggregate the required data to reconstruct an entire conversation. However, as soon as you want to do _aggregate_ operations across several/all conversations it becomes a real hassle. Say for example you wanted to gather all messages that had been sent in the last hour - using a traditional SQL solution, this would be trivial and highly performant. With a JSONB solution you have to hope you have a JSONB index on the timestamp and then you also have to work out the correct JSONB syntax to do the query. Constructing non-trivial JSONB queries can get really gnarly - trust me, I've been there. From my (somewhat) limited experience with JSONB I would now generally avoid it except for the cases where either the user wants to quite literally store JSON (and now we get some nice JSON validation for free) _or_ for the case where I am absolutely 100% sure I will never need to do any non-trivial aggregate operations on the JSONB and that the upfront implementation cost of a SQL solution would be stupendously high compared to JSONB. An example that comes to mind would be an Evernote style application where the user data is semi-structured and we don't generally speaking want to run aggregate operations on it (it's their private data).
harrisreynolds 8y ago
We are building a tool [1] that pulls in data from several different external services as part of our data visualization platform. For this use case MongoDB, with its schema-less design, is perfect. We are however using Postgres for our internal app database where it is easy to manage our schema a priori. [1] https://chart.ly https://chart.ly
qaq 8y ago
Nope but I would imagine in a consulting context one can miss out on the whole and now let's switch to PostgreSQL to address all the Mongo issues revenue stream
rjkennedy98 8y ago
Maybe not Mongo itself but schema-less NoSQL databases are very useful for Data Hubs that integrate many different data sources. If you work as I do on a team that has 100+ members, trying to integrate 25 billion documents with 30+ datasources and 60+ APIs, then a schema-less database where you can more flexibly handle changes to the document model (which are happening every day) is ideal. Trying to build that in MySQL would be a disaster.
- arcticfox 8y ago
  Right, but you could just build that in Postgres with JSONB
  - shoo 8y ago
    So the question might be: when would building it as postgres + jsonb not be appropriate?
  - ruiquelhas 8y ago
    MySQL also has JSON support (https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json.html) and even a Document Store plugin (https://dev.mysql.com/doc/refman/8.0/en/document-store.html https://dev.mysql.com/doc/refman/8.0/en/document-store.html).
- overcast 8y ago
  The vast majority of projects are basic CRUD operations, using relational data. Not something you should ever do in a NoSQL database. That's the problem, people were using it for EVERYTHING. Your use case is very niche.
- sixdimensional 8y ago
  I like using tools like Apache Drill for the schemaless use case. I apply that in use cases where you’re dropping files with maybe similar schemes into a folder and doing exploratory analysis on the way to a structured solution, or for quick throwaways, or even for data integration ELT type solutions, as you mentioned. This use case works well with data vault-like architecture (or “data lakes”). Actually, anything that can read and perform a SQL query against a standards based file in a file system (e.g. CSV, JSON, Parquet, etc. on HDFS or regular FS). Apache Hive, PrestoDB, Spark, or even some RDBMS which support Foreign Data Wrappers can do this, although some of those require a schema to be specified for querying even if the underlying files can be of different schemas. Drill doesn’t have that limitation and can literally query without metadata. Schema / data types matter so true schemaless can be difficult unless you manually map data types, but it has a place.
jaxn 8y ago
Not Mongo, but I am eyeing CouchDB, solely because of PouchDB. I am just not finding an easy and well-supported way to build an offline-first SPA/PWA backed by Postgres without handling all of the data syncing manually.
- option_greek 8y ago
  Woudn't Vue with Sequelize+postgresql solve your usecase ?
  - pier25 8y ago
    (I'm not jaxn) That solution does not sync. Also, we've found that Knex with Objection is much better than Sequelize.
alexmorse 8y ago
nope
pjmlp 8y ago
No, because I only use RDBMS systems, never had a business reason to jump into the NoSQL wave.
kevinoid 8y ago
My example is a bit dated, but may be illustrative: I chose PostgreSQL over MongoDB for a MEAN webapp (making it PEAN?) project started in 2013 and regretted it. I was constantly struggling with the poor support for SQL in Node at the time and spent way too much time fixing bugs or adding workarounds for deficiencies in the SQL abstraction libraries and ORMs, or writing bespoke SQL and object mapping. After switching to MongoDB, I spent significantly less time on storage issues and development speed increased noticeably. Later we did run into several issues caused by MongoDB which could have been avoided by using SQL (mostly related to consistency, constraint enforcement, and problems with MongoDB's aggregation capabilities). MongoDB got us to market faster, but had higher costs in the long-term. I think there are two lessons that I'd take away: 1) There are costs to choosing a less popular technology combination, even if the individual technologies might be better (for your particular needs) in isolation. 2) Sometimes it's better to choose the more costly technology in the long-run to avoid costs early (i.e. incurring some technical debt can be the right choice). MongoDB vs SQL for us was an example of both of these. Note: I assume SQL libraries in Node have matured significantly since 2013, so I wouldn't necessarily recommend MongoDB over SQL for new projects starting today.
- tonious 8y ago
  IMHO, this is more of an indictment of the ORM (sounds like sequelize) than anything else.
  - kevinoid 8y ago
    Partly it is. I had issues with both sequelize and any-db. However, since not using them resulted in a lot of bespoke SQL and object mapping, I found MongoDB preferable to SQL without these libraries as well.
    - bkrn 8y ago
      Isn't all the code bespoke? Or, less snarkily, what's wrong with writing SQL when your ORM starts leaking abstractions?
    - 8y ago
WA 8y ago
I’d like to repeat my comment from another recent thread: I mostly used MySQL. But for my app, I now use CouchDB. It has its limitations, but the automatic sync between devices is pure gold. I haven't written a single line of sync code and it just works. It baffles me a bit that CouchDB isn't more popular and that people rather use MongoDB for NoSQL, which doesn't come with the same syncing capabilities. Adding to this: if you want sync out of the box and like NoSQL (and your app isn’t super complicated), CouchDB is clearly better than Postgres (and Mongo of course).
- brightball 8y ago
  Sync is the primary use case for CouchDB (and PouchDB). It really deserves more attention for that IMO. It's not ideal for a lot of other use cases, but where it fits it shines.
  - WA 8y ago
    I just wonder if there is a single reason why anyone would use MongoDB over CouchDB/PouchDB.
    - brightball 8y ago
      If you remember when Mongo first came out, it made a splash with a lot of write focused benchmarks. They were achieved by RAM buffering the writes and then persisting to the disk periodically. Couch never led in benchmarks and always focused on consistency or both performance and data. That’s not as flashy with the “OMG Benchmarks!” crowd, in my experience at least. Couch operates entirely over REST last time I checked as well, which makes it a bit clunky for a lot of use cases. Couchbase attempts to be the fast version of Couch and seems to do pretty well.
- anthony_barker 8y ago
  my only two issues with couchdb are the query language has some learning curve and it would be nice if they had good support for public/private key authentication like lotus notes had.
tonious 8y ago
It's funny to see these things come around and go around. ~1998, there were quite a few megabytes burnt on discussing Postgres vs. MySql. At the time, MySQL was not ACID compliant, and was (rightly) derided as a database shaped object, but not an actual database. It was faster, provided you didn't need get back the same data you put into it. MongoDB seems to be in a similar situation. Under the right conditions, it can be ACID compliant. But it's a database shaped object. As a developer, you have to actively manage aspects of your data that should be handled by the database itself. Every MongoDB project I've touched has run into a schema version issue. Sure, you can put freeform data into Mongo. But when you're querying your database, will a record made yesterday have the same shape as one made six months ago? How do you ensure that? You write model level code to normalize your data at run time, or you write migrations. Anyhow, to the matter at hand: With a well behaved ORM you can store certain properties in dedicated columns, and shove everything else into a jsonb blob. This gets the best of both worlds: indexed, relational entities, and effectively schemaless data. As your needs evolve, you can migrate properties out of the jsonb column and up to the table schema. This can be entirely transparent to your repo/service/controller level code. Now you can apply SQL level constraints to required fields. This has worked for me on a few different projects. 1) I've never regretted going with Postgres over MongoDB. 2) I can't think of a real application where I'd prefer Mongo. And the cases that get close, I'd probably jump to a stream processing system like Kafka and avoid database semantics entirely. A fully schemaless database is great, providing that all of your database applications are schemaless, too. Otherwise, you now have to manage that by hand. I don't begrudge Mongo's existence. It's a worthwhile experiment to move features in and out of the database semantic model. We go through cycles of decomposing and recomposing functionality. This is how we recombine tools to become other tools. But I do not see Mongo as the future of databases.
- jacquesm 8y ago
  > ~1998, there were quite a few megabytes burnt on discussing Postgres vs. MySql. At the time, MySQL was not ACID compliant, and was (rightly) derided as a database shaped object, but not an actual database. It was faster, provided you didn't need get back the same data you put into it. MySQL has come a long way since then though.
crtlaltdel 8y ago
most of the issues i have encountered and that i've heard peers complain of stack up to human-error and the inability to adequately plan for change...doesn't seem to matter what database or framework we're talking about...
philip1209 8y ago
No
scarface74 8y ago
For me whether to use Mongo or an RDMS is dependent on what I see the need to be for the data. If I see using the database as only backing storage for a domain specific microservice where all access should go through the service, I will probably use, MongoDB. C#/Linq/MongoDB is a powerful, under used combination. You get your data and model consistency via C#'s strong typing and it feels more like you are working with everything in memory even though your LINQ queries are being translated to MongoQuery. For instance, in C# with Mongo you're not working with an amorphous blob of weakly typed data. You're working with a Collection<Customer> and an IMongoQueryable<Customer> where you can write standard LINQ queries.... var seniors = from c in Customers where c.Age > 65 For storing data you can't put an Orders object in a Collection you defined to hold Customers. You don't get the whole object/relational mismatch with Mongo and as good as Entity Framework is, it's still brings along all of the issues that are unavoidable with an ORM. On the other hand, my experience with large Mongo clusters is write once/read many. Meaning once the documents got stored, they were never updated and it was very much a document store. I would still use an RDMS most of the time. But why choose? In the age of domain specific services and polyglot persistence, do both.
- jordanab 8y ago
  In the case of C#/.Net, there are more alternatives for document DBs with native Linq-based drivers. For example I've used RavenDB in a couple of web app projects, and it has the extra benefit of being fully transactional and having Lucene indexes built-in (effectively giving you a built-in auto-syncing Elasticsearch/Solr server). For another upcoming (cloud based) project we're planning to use Azure Cosmos DB.
vandahm 8y ago
In response to Question 2, here's the best I've got: At a previous job, we used MongoDB with a write concern of 0 to store logs in such a way that we could more easily search through them later on. The information we were logging was useful to us as developers but wasn't critical to the business, so we were willing to take some risks in order to achieve faster inserts. It worked OK for us at the time, but if I had to do it over again today, I would probably look at ELK before rigging something up with Mongo. In the larger sense -- at least for conventional web stuff -- I don't like Mongo. Usually, I'm building something that receives a web request, reads or writes to a database, then generates either a web page or a JSON object as a response. In theory, since you know what information you need to render the response, you should be able to include all of that into the structure of your Mongo document, then fetch everything you need with only one query. In practice, however, business and UX requirements tend to change quickly enough that it isn't feasible to do that. As your product evolves away from its initial design, it gets harder to bridge the gap without joins and transactions. At that point, at least for me, I just feel more comfortable using PostgreSQL.
- linsomniac 8y ago
  In support of ELK, I spent years glancing at all sorts of different options (fluentd, greylog, ELK, Splunk, logstash, loggly, ...). I had set up Elasticsearch but never got the parts working for log ingestion and groking. I got them loading but no groking. This was a "personal time" project at work and I just didn't have the time. Then we decided to wanted Apache logs to be centralized, so I set up an rsyslog server and fed the logs to that. Once I had that component running, I set up another ELK system and figured out the "groking", ended up using liblognorm. A few weeks later our Elasticsearch cluster had some problem and people had come to rely on it, so I started shoring up the deployment. Going from experiment mode to something more production ready. The stack I ended up with was rsyslog on the clients, shipping logs to the rsyslog server using RELP. The rsyslog server using omelasticsearch to send the logs to Elasticsearch, and liblognorm module in rsyslog to process the logs. liblognorm is kind of a pain in the ass, but it was built into rsyslog. If I were to do it again, I'd spend a bit more time figuring out if I could use grok instead.
33degrees 8y ago
JSON storage is only half the story, the other half is ease of scaling. Scaling Postgres is not simple, and given enough of the right kind of data, I could see why someone might chose MongoDB. But for quickly iterating on a schema for a new project, Postgres's JSON support is definitely the best of both worlds.
stevendgarcia 8y ago
I think it depends on what platform you choose. For my Ruby/Rails work postgres support has been nothing short of phenomenal. The addition of JSONB and hstore added the dynamic schema features of MongoDB that I'd used in a previous projects. Nowadays I miss none of those features, plus I get free fulltext search, Common Table Expressions and tons of other vetted goodies that come with postgres. However, working with NodeJS was a different story, as there seems to be a lot more community around projects like Mongoose as opposed to Sequelize. I found that bootstrapping a project from scratch was a lot easier and the pace of development was relatively hiccup free. The only downside for me was memory consumption. Mongo stores all data in memory for fast reads and this quickly proves unsustainable if you aren't prepared for it. By comparison, postgres has some sort of crazy developer voodoo behind it because it has way more features, yet consumes far less RAM and is more resilient to failure in my experience. NOTE: I haven't built a Twitter or anything "webscale" Most of my apps are for small to mid sized business so take my input here with the appropriate grain of salt
manishsharan 8y ago
Not Mongodb but Redis . I added Redis to my stack and I am absolutely loving it for what it does. I have SQL server for stuff that belongs in a RDBMS e.g. purchase data, accounts etc. . But the operations and analytics related stuff for my application goes into Redis. And Redis outperforms the SQL server for those purposes. If you can represent your data in terms of data structures as opposed to Table/records, you may want to take a look at Redis.
- cagmz 8y ago
  > the operations and analytics related stuff Can you elaborate on this? Is it ephemeral data that requires some operation done with it, and then it can be discarded?
tanin 8y ago
This is an opposite of what OP asked, but I hope it provides some useful insight. GIVE.asia recently migrated from MongoDB to Postgresql. We didn't really use any special feature from MongoDB, so it was basically a poor decision. One of the main problem we have with MongoDB was that there was no schema. When we changed schema (e.g. add column, delete column), we needed to write a migration script to update all the data inside that schema. It was burdensome, so sometimes we didn't do it. This led to a situation where different rows might have different columns. Postgresql (or mysql and others), on another hand, forces us to handle schema change immediately because the schema change (e.g. add column, remove column) applies to all rows immediately. That leads to cleaner data. Two situations you might need MongoDB (or other nosql): 1. having an extremely large data that it's impossible to modify all of them when changing schema, or 2. you actually don't care about schema that much (e.g. logs)
- kthielen 8y ago
  > 2. you actually don't care about schema that much (e.g. logs) I would argue that this is one of the most overlooked cases _for_ schemas, because it's where schemas are the most complex and also where traditionally we've had few options for really representing that complexity. This is something I've tried to change with the hobbes project, where we have a small header-only lib to efficiently record "log data" with inferred schemas into shared memory: https://github.com/Morgan-Stanley/hobbes#storage https://github.com/Morgan-Stanley/hobbes#storage Basically, in this view, the type for recorded log data is a variant over all possible log statements in an application. This is a very large and complex type, but in memory or on disk it's a simple representation -- a number for the constructor ID of the variant, and then the payload type that corresponds to that constructor. Then there's quite a lot that we build on top of that, but as a basic primitive I think that logging is actually a great case for "extreme schemas" rather than throwing out schemas entirely.
nasalgoat 8y ago
The only regret I have going with PostgreSQL over MongoDB is that the upgrade path is not easy. I can't just upgrade in place, or easily convert the older pgsql files to the new version, I need to spin up a new one and transfer the data via dump. That's why I'm still running 9.5 in production, because the associated downtime is too much.
HackerEthicDead 8y ago
No. Mongo is garbage.
lmm 8y ago
When you need to dynamically scale the "cluster membership" of a distributed datastore. When you need more data than a single instance can handle. I've worked on products that used Mongo and could not have worked with Postgres. Mongo has plenty of issues but Postgres barely has support for true master-master replication even with a static set of nodes, yet alone doing dynamic scaling. I tend towards Cassandra rather than Mongo, but if we're talking about advantages for NoSQL more generally I'd say: when you need to do reporting queries without them interfering with live queries. When you want index updates to happen behind "live" changes for performance reasons. When you want a query language that makes sense as a programmer and lets you understand query performance from reading the query. When you want a nonblocking client library in the JVM ecosystem. (Hell, when you want your client library and the ecosystem on top of it to have first-class support for your database's features - Postgresql may have first-class support for JSON but that doesn't mean your ORM does). All these things are possible with SQL, but harder.
- chaostheory 8y ago
  CitusDB? https://www.citusdata.com/ https://www.citusdata.com/ There are a few other solutions for postgres as well.
  - lmm 8y ago
    There are various efforts, but none of them is mature/established enough that I'd trust it (and I heard of specific issues with some of them, though I think citrusdb is newer than when I last looked). Maybe this one works, but without an established community around it I'd rather stick to cassandra.
    - chaostheory 8y ago
      Citus is almost a decade old now. Others like Greenplum are even older. I like Cassandra, but like most NoSQL, if you want to use it as a general purpose datastore; there's a lot of work to do for devs
      - 8y ago
adreamingsoul 8y ago
1) YES. But the schema design was terrible, which isn't a fault of Postgres. The result was a SQL database that didn't scale well, was brittle, required lots of compute resources, and wasn't always redundant. The original codebase existed before me and my team didn't have the approval from management to fix the core issues, so we chose Postgres as the replacement for MySQL. Since then, we've built new versions of those products, re-architected, and invested 100% into the cloud. Now that we are serverless, cellular, and highly-available we use a NoSQL database that "scales". The schema concepts from the relational era to the non-relational era haven't changed that much, but we did learn from others mistakes and changed the types of the worst offenders to be JSON. (Which Postgres now supports, yay!) Now I live in a world were instead of querying for the data from a relational database, I instead have to load more data than I need into "serverless" memory, join the relations, and return the output. That requires at least a single database request for each relational object, increased lines of code, increased complexity, and expensive monthly service costs. 2) We all know that NoSQL is great for non-relational data, but most of my projects have relational data objects. Example. A system has users; a user has notes; a note has attributes. 99% of the time, I'm willing to bet a relational database is needed, and I have yet to find a project that benefited from NoSQL. Ironically, I've been thinking about the SQL to NoSQL trend for the last couple of weeks and asked myself similar questions. I came to the conclusion that a relational database is not only more natural and more efficient but cheaper in the long run. Just my thoughts.
taf2 8y ago
MongoDB is a fine database IMO. The main issue I have with it, is that they dropped SQL interface and instead invented a less expressive query syntax. Really there are some great features to MongoDB and it's relatively easy to maintain. I wish it had better tooling for modifying existing document structures such as ALTER TABLE... but the automatic sharding is nice.
squirrelicus 8y ago
Mongo wins when two things are true 1) Your use cases are get doc, put doc, delete doc. No aggregations, OR you have a very healthy sharding key. 2) You need to go to the 1 terabyte - 100 terabyte scale for a very small cost in humans and machines. For those of us who have dealt with (or dealt with the human/machine cost of) a TB+ Postgres or MySQL database, Mongo can be a blessing if you data can be organized in a proper NoSQL fashion. It's true, you can scale RDBMSs into the terabytes range with modest cost with excellent performance. BUT the singular guiding principle to achieve this is integers, integers, integers. In general, RDBMSs start shedding performance the more non-integer columns you have in your relevant tables. If you have less than 100GB of data, it will never matter what database you choose -- it's all about table structure and queries. MySQL, Postgres, Mongo, etc. Unless you choose sqlite of course :P
- Jedd 8y ago
  > In general, RDBMSs start shedding performance the more non-integer columns you have in your relevant tables. The more non-integer indexes, I'd suggest. TimescaleDB under Postrgres goes a long way to improving scaling of certain (NoSQL-type content) large databases.
  - squirrelicus 8y ago
    I think we're both right for sure, but even just having varchars in your critical join tables is a big perf killer given there'll be fewer rows read per page fault(unless you're just index scanning/joining of course)
madhadron 8y ago
I've been at two companies that used MongoDB for high velocity, high volume data (packet capture and analysis). It was a bad decision that we tried to change after the fact both times.

StavrosK 8y ago
> This of course completely false - which is why we use filesytems and files, more than we use databases Filesystems and files are good at a lot of things, but not really great at anything. They're a general-purpose abstraction, but the relational model is much more specific.
- e12e 8y ago
  Of course. I agree with the points made here and in sibling threads about schemas and sql databases. But I also think it's hard to dispute the fact that most data lives in files and file systems. And I think developers too often compare various no(t only)sql solutions with rdms' - without really considering "file-based sql" (sqlite) - or simply file system/DAV based storage (webdav, s3 and work-a-likes, local files, nfs etc). And again - I think it can be easy to see some examples where files are great (store the images for an image gallery), or where relational model is great (transaction data). But I think it can be much harder to see where file storage, or file storage augmented with some form of index - is beaten by a nosql dbms.
msbarnett 8y ago
> This of course completely false - which is why we use filesytems and files, more than we use databases ;-) But consider: there is no application in existence that attempts to work with the contents of every kind of file that can possibly exist on a filesystem. The only kinds of information that any application can reasonably aggregate across all files are those which conform to the standard metadata schema the filesystem applies to all files. Creating an application that aggregated anything else across all files is essentially impossible -- no sane human would attempt to write an application whose functionality depended on them writing parsers for every single file format that could possibly be found in a filesystem. And yet, for most uses of MongoDB it is acting as the datastore for a single application, which in order to do any number of things normally demanded of applications (queries, aggregations, CRUD, etc) must be able to read every variety of document that anyone has ever thrown into the datastore, whether those variations were planned (lets add the following fields to all new Comment documents) or accidental (whoops, a bug led to 3 days worth of Comments missing one or more of the following fields). In the latter scenario, the "it's like a filesystem, store whatever you want!" actively works against the controlling the complexity of the application, to the programmer's great detriment.

jerf 8y ago

Even when I'm storing data in a schemaless fashion, I always use some sort of strongly-typed mechanism for generating the JSON that provides guarantees about the nature of the JSON I am storing. At the very worst I end up with multiple well-defined versions of the "schemaless" content. "Schemaless" here just means the default relational mechanisms don't work well for my use case for some reason. "True" schemalessness is a lie. You can't even hand arbitrary blobs of JSON to human beings and get consistent interpretations of them. There is always a schema. I'm not dogmatic about where the schema lives; relational DBs are great when your data fits them but there are times you need an "exception" to your relationships for some chunk of data. I'm fine with the schema being "this particular strong type in a programming language as rendered by this JSON renderer"; that's a fine schema too. But some schema always exists, and no matter how you slice it, you can not escape from issues of dealing with schema changes. Thinking you can just ignore the problem and just throw whatevs into the database is simply one of your options, one which may gain a lot of very, very short-term convenience for a loooot of long-term pain. As with all cost/benefit questions, there are times where that is the right choice... but it's a lot less often than the old NoSQL hype presupposed. (I've used this option when extracting things from an existing, stable DB and doing an ad-hoc one-off reporting analysis using a temporary database. I don't have to worry about breaking the stable DB when I'm only reading from it, and I can fluidly experiment and develop the one-off report easily. But it's really a rare use case.)

StavrosK 8y ago
> "Schemaless" here just means the default relational mechanisms don't work well for my use case for some reason. Agreed, but you're basically doing things as they should be done. Not using the schema in a relational database because of valid reasons, still validating the schema, etc. I'm not sure if you're agreeing or if you meant it as a different viewpoint, but it sounds like we agree. > But some schema always exists, and no matter how you slice it, you can not escape from issues of dealing with schema changes. This is why I hate it when people say "oh I use MongoDB because I don't want to have to bother with migrations". No, your schema is always going to change, you're just going to migrate things in an ad-hoc manner instead of with a well-thought-out, established process.
- jerf 8y ago
  "I'm not sure if you're agreeing or if you meant it as a different viewpoint, but it sounds like we agree." Agreement. I was mostly arguing against a ghost argument of my past, where people get dogmatic that schemas must live in the database. I'm much less dogmatic about that. Mostly I just want DRY; the schema should try to live in as few places as possible, preferably one, though that is particularly hard here. But it doesn't bother me too much for the DB to not be any of them; in the past few years I think all my databases have ended up with JSON blobs in them. But by careful choice, and still with some careful design to it, not because "woo hoo, I can just throw whatever I want into these fields!".
  - StavrosK 8y ago
    Completely agreed, and I love postgres' support for JSON fields, I think it fills an important need. Not everything can be conveniently described with a schema.

slezyr 8y ago

> fixing bugs or adding workarounds for deficiencies in the SQL abstraction libraries and ORMs TBH I had same experience with all ORMs I have used sqlpp(c++), odb(c++), diesel(rust).

sametmax 8y ago
ORM are one of those kind of libs where very few ones are great. Python has lots of them, and only SQLAlchemy can be considered of high quality. Not that Django's ORM, peewee or eloquent don't get the job done. But they only cover 80% of the use cases. SQLA gets you to 95%, and you bridge the five remaining percents with raw sql injected in the proper places for those rare projects where the extra is important. The cost of it is that it's a harder to use ORM, and a more verbose one. Which is why I generally use https://github.com/absent1706/sqlalchemy-mixins https://github.com/absent1706/sqlalchemy-mixins to make things easier. What's more, ORM for languages like c++ or Rust have it extra hard as they are used from a low level tech while SQL is quite high level and easy to manipulate. It's hence attractive to use it directly. If you use PHP, Ruby or Python, SQL doesn't seem that big of a gain anymore for the simple operations. Now diesel is quite young, and the rust ecosystem has proven to be moving not only fast, but generally in a sane direction, so I wouldn't count it out just yet. However, I've yet to find an even acceptable JS ORM. Not only do they fail to provide any decent operation beyond simple ones, but they have such poor introspection capabilities that they can't be used to create an ecosystem around them. Which is, after all, __the main point of an ORM__. Avoiding to write SQL is definitively NOT the most important feature of an ORM. The critical one is having a common API you can build upon to create form generators, auth systems, data validation units, serializers, automatic UI, etc. Without this, the case for using an ORM is way weaker.
lucio 8y ago
This article could be titled ORM considered harmful (2006) http://blogs.tedneward.com/post/the-vietnam-of-computer-science/ http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
- ris 8y ago
  There's a lot of hatred for ORMs that floats around but most of the ire seems to revolve around the cases where the ORM's flexibility doesn't allow it to expose the power of the underlying database properly. But people often fail to note how few and far between these examples are and how the vast majority of an app's queries tend to be dumb & boring and handled extremely competently by the ORM. Contrast this with the number of dumb typos & sql mistakes through verbosity that the ORM has saved. Don't get distracted by the 1 or 2 cases per app.

h1d 8y ago

Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an unnecessary and ineffective layer.

sanderjd 8y ago
I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM. I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.
- michaelt 8y ago
  How are you constructing queries? Entirely with bind variables. given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns. Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)
  - sanderjd 8y ago
    What are "bind variables"? I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)
    - michaelt 8y ago
      Bind variables are the ? in "SELECT fname, lname, pcode FROM cust WHERE id = ?" and they're a better choice than concatenating the ID you want into the SQL string because (a) it prevents SQL injection and (b) it lets the query optimiser cache plans. We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.
      - ntqz 8y ago
        Another bonus - depending on your DB/driver, it may set this up as a prepared statement, so executions after the first one happen much quicker.
    - gaius 8y ago
      What are "bind variables"? I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.
      - dahauns 8y ago
        No. "bind variables" are Oracle 101. Everyone else calls them "query parameters".
- h1d 8y ago
  What is wrong with writing a raw SQL for such a simple thing? I never want to fight with a tool for achieving such a simple query. Tools are supposed to help and when queries tend to get complicated, I think that's when ORM starts to fail with performance. So, I don't find a room for it. I'd rather learn how SQL works instead of how to get random ORM of the time to work like it should.
  - nulagrithom 8y ago
    > What is wrong with writing a raw SQL for such a simple thing? Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses. Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.
    - sanderjd 8y ago
      This resonates with me. But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.
      - ris 8y ago
        In all fairness a properly used sql connector library will hopefully do this too.
    - ntqz 8y ago
      Although the majority of what I do is in Python, I've taken to using Flyway for DB migrations. A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.
  - msbarnett 8y ago
    If you're not doing anything to manage and autogen your queries, does that imply that any change to the model's table entails manually updating all N different queries that touch the table in your application? I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs. You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.