4 ms·

Postgres seems to have become the go-to relational database ever since MySQL fell in the hands of Oracle. Can anyone speak to how its json tree compares to Mon

Postgres seems to have become the go-to relational database ever since MySQL fell in the hands of Oracle. Can anyone speak to how its json tree compares to MongoDB's document store in practice?

diakritikal 9y ago
I'm not seeing it, just an uptick in usage. I find most places that previously used MySQL just use MariaDB instead.
- ma2rten 9y ago
  In my experience actually most people still use MySQL and not MariaDB.
  - cwyers 9y ago
    If you go to MySQL's website, they will list page after page of users, most of which you've heard of. If you go to MariaDB's website, they list users. Most of them you've never heard of.
  - sverhagen 9y ago
    Agreed, it seems many people look at Maria as a way out if MySQL itself or its licensing ever turns on them, without wanting to support them in the current tense.
brianwawok 9y ago
I don't think postgres is the default over mysql. I think WordPress and php guys default to MySQL. I think python and Django guys end up defaulting to Postgres. Rails and node.js I am less sure what the default is, maybe mongodb? I don't think either camp has really changed since Oracle came along. A lot of momentum in different stacks.
- bshimmin 9y ago
  The actual default for Rails is SQLite [1], though a lot of people (perhaps a majority) use PostgreSQL. You certainly can use MongoDB with Rails via Mongoid, though I'm not sure vast numbers of people do, comparatively. For Node, well, it varies, but at least once upon a time, people used to talk about the "MEAN" stack (MongoDB, Express, Angular, Node) - MongoDB and Node are certainly often used together. [1] Not MySQL - edited as per the below correction.
  - werdnapk 9y ago
    The default for Rails is sqlite and has been for quite a while now.
    - bshimmin 9y ago
      You're quite right. It's so long since I've used Rails with anything other than Postgres I knew that it couldn't be the default because of having to specify the configuration flag, but it escaped me that it would be SQLite. Thanks for the correction.
    - brianwawok 9y ago
      Well I guess Django ships with sqllite but postgres is the normal thing to flip on. Good to know rails is the same...
- schneidmaster 9y ago
  Yeah, as others have noted, the default for Rails is definitely not Mongo. SQLite is the default and thus ends up getting used a lot in development. Production is a relatively even split between Postgres and MySQL, although I think it tilts slightly towards Postgres in part because that's the default on Heroku. (I have no data to back this up, just my sense of things.)
- 0x6c6f6c 9y ago
  The momentum I've seen hasn't been MySQL -> Postgres but rather MySQL -> MariaDB. Oracle's control over MySQL and the shift towards Open Core over FOSS fundamentals has pushed some companies to use the compatible MariaDB option over it. Most Linux distros use MariaDB by default now, meaning more LAMP stacks by default having MariaDB over Oracle's MySQL. I'm certainly not saying MariaDB is taking over MySQL, just the trend I've seen being a RHEL Sys Admin.
borplk 9y ago
I have used its JSON features for real-world stuff and loved it. I have stored procedures doing a bunch of stuff and returning a nicely formatted JSON object that makes the job of the application code a lot easier. One thing I dislike is that the friction for initial development and prototyping is a little too high. You have to have scripts lying around to re-create the functions when you change it during dev. And logging/debugging/etc is harder and not as flexible.
- haasted 9y ago
  How close does it come to document databases when it comes to changing the JSON? Is it easy to increment a field, for example? Or add a property to an object?
  - e1g 9y ago
    PG JSON implementation is solid and supports nearly everything you want from a document database, with the added bonus of joins, queries, ACID, and tooling. However, syntax can be awkward at times - # Find a row by JSON property value select * from person where details->>'name' ilike '%james%' # check for property existence select * from person where details ? 'is_hidden' # Override a single property in a JSON field - update person set details = details || '{"phone":"911"}' # Alt increment existing value update person set details = jsonb_set(details, '{views}', (details->'views')+1) More examples at https://www.postgresql.org/docs/current/static/functions-json.html https://www.postgresql.org/docs/current/static/functions-jso...
LanceH 9y ago
Also postgres was picked up as an option on AWS.
- lohengramm 9y ago
  However RDS for PostgreSQL is still more expensive than RDS for MySQL, and I don't know why. If someone can enlighten me on that, I appreciate.
edoceo 9y ago
There are JSON and JSONB types that can store JSON, in one can make tables that are simply a PK and a JSON column. There are function/option wrappers around this type to in place update a JSON field. Syntax is well different than Mongo. Can get a relational-JSON store which Mongo don't really have. I used to use Mongo along side the PG for the JSON store but now am back all on PG with this JSONB type.
inopinatus 9y ago
I used jsonb as an ersatz EAV store for settings etc until my product iterations, or at least the data model underneath, began to stabilise. Great way to get started without getting bogged down in DBA or problematic migrations. Now the product's data model is mostly settled I am flipping over to regular fields, array types, and higher normal forms. Being able to do all this without changing DB backend has been a real boon, far less time bogged down in ops = far more time to create customer value.
philliphaydon 9y ago
In the dotnet world I'm helping contribute to a project called Marten which is a document database built on top of PostgreSQL. (My contributions are Low cos real life is in the way lately) The json support is amazing. It's really incredible.

Postgres seems to have become the go-to relational database ever since MySQL fell in the hands of Oracle. Can anyone speak to how its json tree compares to MongoDB's document store in practice?

diakritikal 9y ago

I'm not seeing it, just an uptick in usage. I find most places that previously used MySQL just use MariaDB instead.

ma2rten 9y ago
In my experience actually most people still use MySQL and not MariaDB.
- cwyers 9y ago
  If you go to MySQL's website, they will list page after page of users, most of which you've heard of. If you go to MariaDB's website, they list users. Most of them you've never heard of.
- sverhagen 9y ago
  Agreed, it seems many people look at Maria as a way out if MySQL itself or its licensing ever turns on them, without wanting to support them in the current tense.

brianwawok 9y ago

I don't think postgres is the default over mysql. I think WordPress and php guys default to MySQL. I think python and Django guys end up defaulting to Postgres. Rails and node.js I am less sure what the default is, maybe mongodb? I don't think either camp has really changed since Oracle came along. A lot of momentum in different stacks.

bshimmin 9y ago
The actual default for Rails is SQLite [1], though a lot of people (perhaps a majority) use PostgreSQL. You certainly can use MongoDB with Rails via Mongoid, though I'm not sure vast numbers of people do, comparatively. For Node, well, it varies, but at least once upon a time, people used to talk about the "MEAN" stack (MongoDB, Express, Angular, Node) - MongoDB and Node are certainly often used together. [1] Not MySQL - edited as per the below correction.
- werdnapk 9y ago
  The default for Rails is sqlite and has been for quite a while now.
  - bshimmin 9y ago
    You're quite right. It's so long since I've used Rails with anything other than Postgres I knew that it couldn't be the default because of having to specify the configuration flag, but it escaped me that it would be SQLite. Thanks for the correction.
  - brianwawok 9y ago
    Well I guess Django ships with sqllite but postgres is the normal thing to flip on. Good to know rails is the same...
schneidmaster 9y ago
Yeah, as others have noted, the default for Rails is definitely not Mongo. SQLite is the default and thus ends up getting used a lot in development. Production is a relatively even split between Postgres and MySQL, although I think it tilts slightly towards Postgres in part because that's the default on Heroku. (I have no data to back this up, just my sense of things.)
0x6c6f6c 9y ago
The momentum I've seen hasn't been MySQL -> Postgres but rather MySQL -> MariaDB. Oracle's control over MySQL and the shift towards Open Core over FOSS fundamentals has pushed some companies to use the compatible MariaDB option over it. Most Linux distros use MariaDB by default now, meaning more LAMP stacks by default having MariaDB over Oracle's MySQL. I'm certainly not saying MariaDB is taking over MySQL, just the trend I've seen being a RHEL Sys Admin.

borplk 9y ago

I have used its JSON features for real-world stuff and loved it. I have stored procedures doing a bunch of stuff and returning a nicely formatted JSON object that makes the job of the application code a lot easier. One thing I dislike is that the friction for initial development and prototyping is a little too high. You have to have scripts lying around to re-create the functions when you change it during dev. And logging/debugging/etc is harder and not as flexible.

haasted 9y ago
How close does it come to document databases when it comes to changing the JSON? Is it easy to increment a field, for example? Or add a property to an object?
- e1g 9y ago
  PG JSON implementation is solid and supports nearly everything you want from a document database, with the added bonus of joins, queries, ACID, and tooling. However, syntax can be awkward at times - # Find a row by JSON property value select * from person where details->>'name' ilike '%james%' # check for property existence select * from person where details ? 'is_hidden' # Override a single property in a JSON field - update person set details = details || '{"phone":"911"}' # Alt increment existing value update person set details = jsonb_set(details, '{views}', (details->'views')+1) More examples at https://www.postgresql.org/docs/current/static/functions-json.html https://www.postgresql.org/docs/current/static/functions-jso...

LanceH 9y ago

Also postgres was picked up as an option on AWS.

lohengramm 9y ago
However RDS for PostgreSQL is still more expensive than RDS for MySQL, and I don't know why. If someone can enlighten me on that, I appreciate.

edoceo 9y ago

There are JSON and JSONB types that can store JSON, in one can make tables that are simply a PK and a JSON column. There are function/option wrappers around this type to in place update a JSON field. Syntax is well different than Mongo. Can get a relational-JSON store which Mongo don't really have. I used to use Mongo along side the PG for the JSON store but now am back all on PG with this JSONB type.

inopinatus 9y ago

I used jsonb as an ersatz EAV store for settings etc until my product iterations, or at least the data model underneath, began to stabilise. Great way to get started without getting bogged down in DBA or problematic migrations. Now the product's data model is mostly settled I am flipping over to regular fields, array types, and higher normal forms. Being able to do all this without changing DB backend has been a real boon, far less time bogged down in ops = far more time to create customer value.

philliphaydon 9y ago

In the dotnet world I'm helping contribute to a project called Marten which is a document database built on top of PostgreSQL. (My contributions are Low cos real life is in the way lately) The json support is amazing. It's really incredible.