Hacker News Reader

Comment by billions | Hacker News Reader

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?

View on HN

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.

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.

The default for Rails is sqlite and has been for quite a while now.

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.

Well I guess Django ships with sqllite but postgres is the normal thing to flip on. Good to know rails is the same...

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.)

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.

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.

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

In my experience actually most people still use MySQL and not MariaDB.

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.

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.

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.

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?

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-jso...

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.

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.

Also postgres was picked up as an option on AWS.

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.

Reply on news.ycombinator.com