NoSQL is less rigid but your code pretty much has to be as rigid, right?
In the process of pushing it up to the app, you lose ACID guarantees. Without ACID guarantees, your ability to view/update a snapshot in time of all your data becomes a very difficult problem to solve. As such, it's hard to say the two methodologies are equivalent.
When you start putting relational data into a non-relational DB, you are going to have a bad time.
isn't a collection pretty much like a table
No, it isn't. A table (or relation) has predefined columns, and each row adheres to the data type and constraints for each column. If you were to model a NoSQL "collection" as a table, you'd end up with a sparse matrix where the number of columns could be equal to number_of_rows * number_of_distinct_attributes of the original collection.
you have to tie two collections together with a common ID
Nothing in SQL requires you to join collections based on a common ID (though it is the most useful). But still, an SQL join has a predefined result structure regardless of the actual table contents or join condition. There is no predicting the data structure of a NoSQL join.
Compare this to something like Elasticsearch, which is well-known (and well-documented) to have data loss in certain situations.
Frankly I think it's still an awful choice given it's broken replication model.
How well does MongoDB support this need, and how hard is it to achieve equivalent results with Postgres? What are the drawbacks?
I think there is a difference, especially in practice. Relations are a first class citizen in relational databases. Integrity is enforced. Most types of operations can be bundled up in transactions.
That abstraction differs from non relational data stores and you have to design your application to fit it.
I'm guessing that other databases have similar implementations.
Though no API to hook into it, as far as I know.