Postgres 9.4 feature highlight: Indexing JSON data with jsonb data type

http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-indexing-jsonb/

by ghosh • 12 years ago

200 39 12 years ago

michael.otacoo.com

misiti3780• 12 years ago

Does anyone have a link comparing postgres 9.4 jsonb and mongodb ?

Kassandry• 12 years ago

Right here. =) http://blogs.enterprisedb.com/2014/09/24/postgres-outperform...

zobzu• 12 years ago

The tldr:

    Ingestion of high volumes of data was approximately 2.1 times faster in Postgres

    MongoDB consumed 33% more the disk space

    Data inserts took almost 3 times longer in MongoDB

    Data selection took more than 2.5 times longer in MongoDB than in Postgres

tracker1• 12 years ago

Now if only I had a baked in solution for a replica set, with hot master failover and command forwarding in PostgreSQL like I get with MongoDB.

Quirky bolt-on solutions, and those requiring a contract with EnterpriseDB or others need not apply.

I really don't mean for this to be as snarky as it sounds... I'd really love these features in PostgreSQL along with PLv8, I'd rather use that than MongoDB... unfortunately having a good in the box solution for MongoDB over PostgreSQL is a bigger breaking point than lack of Joins in my use case.

arthursilva3• 12 years ago

I don't like this benchmark. But as long as you don't heavily rely on updates or big documents I'm sure Postgres is at least competitive with Mongo.

pilif6• 12 years ago

Now if only we'd finally get that release :-)

Over the last few years, September was always early christmas for me because in September we would be getting the new release. And despite this being a database, their x.y.0 releases were always rock solid (with the exception of 9.2.0 and an issue with IN queries and indexes), so I'm usually upgrading very early.

This year, it looks like the releases is bit late, also caused by some on-disk storage issues for jsonb, so of course I'd like them to spend all the time they need, but I'm still very much looking forward to playing with jsonb as this will provide a nice way of solving some issues I'm having.

I'm not using the beta releases for anything bigger than quickly seeing whether clients still mostly work because updating between beta releases is a PITA due to it requiring a full restore most of the time.

The database I'd like to use jsonb with is 600GB in size and restoring a dump takes 2-6 hours depending on the machine.

amitlan• 12 years ago

9.4RC1 this week!

http://www.postgresql.org/message-id/23861.1415719135@sss.pg...

crucialfelix• 12 years ago

Does it not work to use the same data dir and use pg_upgrade ?

I'm just about to upgrade 9.1 - 9.3 and am wondering if that method is advisable.

pilif6• 12 years ago

Yes. That works fine between official releases and I've been using it to upgrade in the last 3 years.

However, pg_updrade does not support upgrading between beta releases, which is one of the reasons why I stick to released versions.

hliyan• 12 years ago

This is terrific. I spent all of September looking at almost every NoSQL DB in town but nothing could handle the loads we were looking at. Then I found the Postres JSON data types but was sad when I saw it was missing the one thing that would make such a data type worthwhile -- indexing. Now we have it!

Edit: or should I say: we will have it soon?

ddorian432• 12 years ago

I think you can index even plain json columns, it just will be slower than jsonb.

pilif6• 12 years ago

No. You can't create an index on a json column that will be usable for arbitrary containment queries which is the awesome thing provided by jsonb.

What you can already do however is to create a functional index if you always search for the same elements in your JSON bodies.

This will then run at the exact same speed as any other btree index (which is still faster than the gin indexes for jsonb), but at a considerable loss of flexibility (or a ton of storage usage).

therealunreal• 12 years ago

It'd be interesting to see how this compares to hstore on flat data.

harel• 12 years ago

I'm patiently waiting for this release like I might wait for a good new film or a game. Don't think I was ever that 'excited' (if I can use that word) by a database point release.

I needed a document database/RDBMS hybrid combo for a piece of work I want to do, and was going to default to MongoDB but once I heard of 9.4 I decided to wait and see how it pans out.

mau• 12 years ago

Many people here are comparing this feature of Postgres with MongoDB or other document-oriented NoSQL dbs. I think the comparison is just wrong and unfair.

Even if I'm amazed by the performance of Postgres for this particular task (considering also that is a relatively new feature), I don't think performance is the reason why people are using NoSQL dbs. The problem that NoSQL dbs are helping with is scaling. I don't see this as a priority for an RDBMS such as Postgres. Take for instance MongoDB (just because it was named by many of the other comments here, but I guess the same apply to Couch or others): it's relatively simple to deploy a cluster with automatic sharding, replica, failover, etc.. because these are all builtin features.

andrewstuart2• 12 years ago

SQL DBs scale just as well as NoSQL and in the exact same ways: read only clones, sharding, federation, etc. YouTube runs on MySQL in case you're worried about scaling. It may be a tad easier with something like Mongo but it's not hard with MySQL.

IMO, NoSQL is attractive mostly because it's schemaless. You need to change up your model all the time, or handle arbitrary datasets but still query them? NoSQL may be the right tool for the job.

BenoitP• 12 years ago

handle arbitrary datasets

I was wondering: does the postgres json type does not fulfill all of MongoDB's current feature set?

nl3• 12 years ago

"NoSQL" encompases too wide a sphere to say something like the problem that NoSQL dbs are helping with is scaling.

Some NoSQL solutions are focused on extreme scale (Cassandara, HBase etc).

Some NoSQL solutions are focused on extreme performance (Redis).

Some NoSQL solutions are focused on developer ergonomics (MongoDB).

Postgres is an interesting option. It ticks the "good enough" box for just about everything, but doesn't ever seem to be leading the pack in any of the "NoSQL" categories. That's probably fine, since it does lead the "Open Source SQL Database" field, and that's a pretty important field to lead.

nl3• 12 years ago

Does anyone know where to find the 9.4 update JSON[1] features? I was sure it was coming, but I can't see it in the docs anywhere[2][3]?

[1] http://stackoverflow.com/questions/18209625/how-do-i-modify-...

[2] http://www.postgresql.org/docs/9.4/static/datatype-json.html

[3] http://www.postgresql.org/docs/9.4/static/functions-json.htm...

gdulli2• 12 years ago

Every reference to jsonb in those docs is the new 9.4 feature/type.

nl3• 12 years ago

No, how do you update a field within the JSON doc? That is missing in 9.3

Postgres 9.4 feature highlight: Indexing JSON data with jsonb data type

Related Stories