3 ms·

We've found the Postgres json store works just fine for our purposes, thank you very much.

devrandomguy 9y ago
The reason I rejected PG's JSON store, was it's inability to update fields inside of a JSON doc, without replacing the whole document. In your case, does this constraint push you to use more types of smaller documents, or do you just read the whole doc, update it in the application, and then write it back to the DB? Have you ever had an issue with conflicts, where multiple instances of the app read, modify and write different things to the same document at the same time?
- sbov 9y ago
  > Have you ever had an issue with conflicts, where multiple instances of the app read, modify and write different things to the same document at the same time? This problem is what pessimistic (select... for update) or optimistic (using a version column) locking is for. If you don't want any race conditions to sneak into your code, as a rule you should probably be using one or the other regardless of whether or not you use postgresql JSON.
- elcritch 9y ago
  Does PG's Jason support multi-master sync? Native db level support for that feature simplifies a lot of my use cases. It'd be interesting to see a SQLite/WebSQL <-> Postgres multi-master syncing system. It'd be the equivalent of CouchDB <-> PouchDB. Maybe even using the same CouchDB protocol! :-) CouchDB and Couchbase both support only whole document updates. So you get document conflicts in those document stores as well, meaning your app needs to understand and handle 409's. But those conflicts are relatively easy to handle in most cases, at the cost of a new round trip. Mostly it's a matter of downloading the new document state and merging your change to it and re-post. If you're using Redux/Vuex/Event Sourcing this becomes trivial to support. Another way to handle it is to split a single large document into smaller pieces and write a map/reduce view that returns a composite document. That should be possible in Postgres as well with a prepared statement.
- edoceo 9y ago
  JSONB data-type allows for specific field modifications in the JSON object. Operators look ugly in hand-crafted SQL but, works a treat.
coltonv 9y ago
Mongo really isn't about storing JSON. At least that shouldn't be it's selling point. The selling point that is since it's basically just a glorified key value store it's very replicable and distributable. Postgres is very much not either of those things. The JSON is nice for a few things and I use it sometimes but Postgres is and will always be extremely difficult to cluster and replicate.
- sgk284 9y ago
  Postgres 10 added support logical replication. It makes horizontal scaling fairly painless.
  - wutwutwutwut 9y ago
    Until you need to reseed the original master, want to do quick rolling restarts or want any automation in automatic failover. PG has a long way to go. A
  - manigandham 9y ago
    Logical replication by default doesn't even handle DDL statements so something as simple as adding a new column requires extra process. Postgres is probably the weakest of all relational databases when it comes to scalability, both vertical and horizontal.

The reason I rejected PG's JSON store, was it's inability to update fields inside of a JSON doc, without replacing the whole document. In your case, does this constraint push you to use more types of smaller documents, or do you just read the whole doc, update it in the application, and then write it back to the DB? Have you ever had an issue with conflicts, where multiple instances of the app read, modify and write different things to the same document at the same time?

sbov 9y ago
> Have you ever had an issue with conflicts, where multiple instances of the app read, modify and write different things to the same document at the same time? This problem is what pessimistic (select... for update) or optimistic (using a version column) locking is for. If you don't want any race conditions to sneak into your code, as a rule you should probably be using one or the other regardless of whether or not you use postgresql JSON.
elcritch 9y ago
Does PG's Jason support multi-master sync? Native db level support for that feature simplifies a lot of my use cases. It'd be interesting to see a SQLite/WebSQL <-> Postgres multi-master syncing system. It'd be the equivalent of CouchDB <-> PouchDB. Maybe even using the same CouchDB protocol! :-) CouchDB and Couchbase both support only whole document updates. So you get document conflicts in those document stores as well, meaning your app needs to understand and handle 409's. But those conflicts are relatively easy to handle in most cases, at the cost of a new round trip. Mostly it's a matter of downloading the new document state and merging your change to it and re-post. If you're using Redux/Vuex/Event Sourcing this becomes trivial to support. Another way to handle it is to split a single large document into smaller pieces and write a map/reduce view that returns a composite document. That should be possible in Postgres as well with a prepared statement.
edoceo 9y ago
JSONB data-type allows for specific field modifications in the JSON object. Operators look ugly in hand-crafted SQL but, works a treat.

coltonv 9y ago

Mongo really isn't about storing JSON. At least that shouldn't be it's selling point. The selling point that is since it's basically just a glorified key value store it's very replicable and distributable. Postgres is very much not either of those things. The JSON is nice for a few things and I use it sometimes but Postgres is and will always be extremely difficult to cluster and replicate.

sgk284 9y ago
Postgres 10 added support logical replication. It makes horizontal scaling fairly painless.
- wutwutwutwut 9y ago
  Until you need to reseed the original master, want to do quick rolling restarts or want any automation in automatic failover. PG has a long way to go. A
- manigandham 9y ago
  Logical replication by default doesn't even handle DDL statements so something as simple as adding a new column requires extra process. Postgres is probably the weakest of all relational databases when it comes to scalability, both vertical and horizontal.