Why HStore2/jsonb is the most important patch of PostgreSQL 9.4

http://www.databasesoup.com/2014/02/why-hstore2jsonb-is-most-important.html

by andreypopp • 12 years ago

67 75 12 years ago

chrisfarms• 12 years ago

I'd also add that SPA[1]/client-side applications are also an important consumer of databases with decent JSON support.

The "backend" for these types of applications are often relatively thin API wrappers around a database, with the bulk of application logic on the client-side.

Postgres' JSON improvements over the last couple of years have made it a pretty good choice already for these sorts of things, but the JSON indexing improving would make this a no-brainer.

[1] http://en.wikipedia.org/wiki/Single-page_application

justincormack2• 12 years ago

Postgres has never been popular. Why the sudden race for popularity now? MongoDB is replacing MySQL not Postgres. I would be interested to know what proportion of Postgres users use the JSON features, I suspect it is still small, even amongst the Node users. The popularity of Postgres is growing at a manageable rate, not an exploding rate and that seems fine to me.

venus3• 12 years ago

Postgres has never been popular. [..] MongoDB is replacing MySQL not Postgres

I don't know what you are basing that on, but it's the complete opposite of what I am seeing. Postgres is now the default open source database as far as I can see, especially for Rails. MongoDB enjoyed a brief hype-fuelled day in the sun but is now viewed as a specialised tool whose choice over a more general RDBMS would require considerable justification.

I suspect the question of whether its popularity is "exploding" or not depends on your definition of the word. I have certainly noticed a very marked uptick in the last few years. Not exploding perhaps, but certainly ballooning.

justincormack2• 12 years ago

Thats what I meant by "growing at a manageable rate".

Well maybe Postgres and Mongo are replacing MySQL. I haven't seen Postgres being the default, but who knows, I mostly hang around with people who have been long term postgres users anyway.

threeseed3• 12 years ago

Postgres is now the default open source database

Really ? I still see MySQL and MongoDB both being the two most deployed.

masklinn7• 12 years ago

Postgres has never been popular.

Postgres is the "default" database for django installations.

MongoDB is replacing MySQL not Postgres.

That's part of the point, if user bases are switching databases, that's a chance for postgres.

I would be interested to know what proportion of Postgres users use the JSON features, I suspect it is still small

For good reasons since it's still young (first introduced in 9.2) and fairly limited (9.3 did add operator and indexation as well as a bunch of functions). I'm in a postgres shop and we aren't quite ready yet to drop support for all postgres versions preceding 9.3 (to my dismay).

raverbashing3• 12 years ago

"Postgres is the "default" database for django installations."

Is it? It is the recommended DB, between oh, MySQL, PostgreSQL, Oracle and Sqlite3

But I've yet to see an actual statistic on that.

With 3rd party plugins you can even use it on Microsoft SQL Server (though of course I don't recommend it unless it's a specific thing)

einhverfr8• 12 years ago

Postgres has never been popular.

Not my experience. I have found that PostgreSQL has a remarkably stealthy popularity. I went to a conference a couple years ago and everyone knew PostgreSQL, and even there were more booths advertising Pg services than MySQL services.

rdtsc3• 12 years ago

You are both right and wrong.

Postgres is not popular if we talk enterprise world that has been buying Oracle. Oracle is an immense behemoth in that space with skilled salesmen, and they make lots of money.

I once asked an Oracle salesman what he thought of MySQL and PostgreSQL and he laughed, and basically said they are seen as joke by his customers. Maybe he was brainwashed sure, but I suspect that reflects (reflected? that was 2-4 years ago) the reality in that market.

Now with companies outside the enterprise (startups) and for internal projects, I feel Postgres has been very popular. MySQL effectively become unappealing after its trademark issues. Postgres gained a some of those users. It also gained disillusioned NoSQL people, some didn't RTFM, some were mislead, some didn't understand the problem well enough and so on.

jmnicolas• 12 years ago

PostgreSQL is the best open source relational database and I hope it remains that way.

While I don't mind seeing some non relational additions like JSON, it shouldn't be detrimental to Postgres relational core.

It's not a pissing contest : if MongoDB is more popular than Postgres for non relational (NoSql) data so what ?

It's not like Postgres is a commercial product and need wider adoption to keep the parent company afloat.

masklinn7• 12 years ago

While I don't mind seeing some non relational additions like JSON, it shouldn't be detrimental to Postgres relational core.

The patchset in question is an improvement of hstore[0] (typed hstore, nested hstore) and unification of json and hstore (by using the binary hstore format for both). There is no more detriment to the relational core than when hstore or XML were originally added. Also, next-GIN[1] by the same people[2], not sure it's part of the patch mentioned.

[0] [PDF warning] http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2...

[1] [PDF warning] http://www.sai.msu.su/~megera/postgres/talks/Next%20generati...

[2] Who previously contributed GiST and GIN, FTS, ltree or intarray

jeltz7• 12 years ago

The GIN improvements were submitted as a set of separate patches and several of them have been merged into 9.4.

twic3• 12 years ago

They've still got a .su address! Old school!

jeltz7• 12 years ago

Adding to what masklinn said, JSON is just another type with related functions and operators. It uses the already implemented extensible type system, and is no different from any other type including varchar and integer.

einhverfr8• 12 years ago

PostgreSQL is the best open source relational database and I hope it remains that way.

Yes, and it will. In fact the best post-relational features of PostgreSQL work best when used in a relational way.

While I don't mind seeing some non relational additions like JSON, it shouldn't be detrimental to Postgres relational core.

As I usually tell people "Good object-relational design is usually good object-oriented design and good relational design, and that makes it really, really hard."

It's not a pissing contest : if MongoDB is more popular than Postgres for non relational (NoSql) data so what ?

I would agree. The fact is that for the last 10 years, PostgreSQL has been the choice of open source rdbms's in business apps for the last 12 years and I don't see that changing.

It's not like Postgres is a commercial product and need wider adoption to keep the parent company afloat.

Mindshare is important though.

sheff• 12 years ago

The linked post says : "I think we shouldn't release 9.4 unless it goes in."

I haven't been following the PG developers mailing list that closely recently and the article doesn't go into any details as to why it may be delayed.

From the link that @masklinn posted (Thanks, very interesting!) to a hstore presentation, it looks like the hstore side was developed by November last year, so I'm wondering why it wouldn't be ready for release by this September or whether its just the integration of hstore/JSON side of things that is the hold up.

For me, the new PG feature - although its an outside project - which I am most looking forward to in the 9.4 time frame is a newer JDBC driver for Postgres ( https://github.com/impossibl/pgjdbc-ng ).

masklinn7• 12 years ago

http://postgresql.1045698.n5.nabble.com/jsonb-and-nested-hst... seems to be the relevant threads. Apparently there are issues over wire format, and jsonb being in core (and unless I'm misreading things jsonb is to be a new type separate from json, which is unwelcome news, I didn't know about that although it does make sense from a "binary copy of database files" sense)

jeremyjh3• 12 years ago

Chasing flavor-of-the-month seems like a pretty lost cause. I would like to see the patch in as well, but this seems like a pretty terrible reason for it.

crucialfelix2• 12 years ago

Its not flavor-of-the-month, its flavor-of-the-generation. That's a 4 or 5 year span- not a ship you want to miss. Django and Rails have brought significant Postgres adoption. If widespread OS adoption and support is a goal then it is wise to pay attention to the features and platforms that will drive adoption.

jeremyjh3• 12 years ago

MongoDB is "flavor-of-the-generation" ? If so, then that generation is truly lost.

wisty• 12 years ago

AJAX was flavour of the month in 2004. JSON has been flavour of the month since ~2006.

While that makes it about 10 years younger than Postgres and MySQL, it's still not a flash in the pan.

jeremyjh3• 12 years ago

You misunderstand me. I am not commenting on the nature of the feature, but rather the motivation cited in the SP for holding the release open to get it in. That reason could be summarized as "node.js and mongo!!".

rosser• 12 years ago

Because following the latest fashions is clearly the PostgreSQL Core Committee's raison d'être...

spikels5• 12 years ago

I disagree. Node and MongoDb have both had very strong growth for several years now across a wide variety of users from individual hackers to the largest companies. While I'm skeptical of MongoDb's long term prospects due to its underlying technology and tight sponsorship I can understand the appeal of it's ease of use, especially for development. Node and JavaScript, on the other hand, seem clearly here to stay. While far from perfect are becoming deeply embedded in many key areas.

And even if you don't like MongoDb or Node, JSON is clearly one of the most popular data formats - better support is surely a good thing.

jeffdavis2• 12 years ago

I agree in the sense that nobody should re-architect their product around a trend.

But this is different because the postgres architecture is very much capable of great support here, and getting even better. Postgres has the best support for adding in new types, with amazing capabilities around indexing.

I'm not just talking about different kinds of BTrees, I mean real non-scalar indexing strategies like inverted indexes (used for full text, but also great for JSON), similarity matching indexes, unbalanced indexes, spatial indexes, etc.

The author merely wants to use this infrastructure as intended for the JSON use case. Easier said than done, of course, but no architectural compromises need to be made in the process of providing this support.

einhverfr8• 12 years ago

I am interested in it for another reason.

I am working on a not-yet-released (needs test cases!) CPAN module called PGObject::Type::JSON.[1] This module would let you effectively grab JSON types from the db, serialize and deserialize on the db query so you don't have to worry about it.

The serialization and deserialization is cool, but the question is what you can do with it. With the json functions if they become more functional, we could use JSON as an input to stored procedures, allowing arbitrarily complex data types to be fed into or out of stored procedures easily without having to serialize in tuple/array format.

A single, cannonical, well supported format would be huge in that it would allow you to do things with the database with extraordinary ease that are not trivial to do today. For example, really good json support + composite types as tuple elements (including arrays of composite types) + really good indexing support should give you an ability to approximate the power (for at least some workloads) of full custom type development, without needing to go to C.

[1] https://github.com/einhverfr/PGObject-Type-JSON

pjmlp4• 12 years ago

Who cares about node.js? I am yet to see any Fortune 500 deployment on our consulting projects.

Following fashion usually doesn't lead to good results.

rpedela2• 12 years ago

I am not sure it is a good idea to use Fortune 500 companies as a signal for which technology to use. They are typically pretty slow to change.

pjmlp4• 12 years ago

I just came out of a project using AngularJS with a Spring backend, so they also use new stuff, not just everything that is the flavour of the month.

spikels5• 12 years ago

Walmart, eBay/PayPal, Yahoo, Amazon/Zappos, LinkedIn, CBS, Time Warner, GM, DowJones, Microsoft....

And those a just the ones I have happened to hear about with major node.js projects.

pjmlp4• 12 years ago

Those are Fortune 50, not Fortune 500. Note the extra digit.

jeffdavis2• 12 years ago

I think it's valuable to show how an "old, traditional"[1] system like postgres can actually be much more flexible and powerful even working in a fashionable environment.

If nothing else, it's a great exercise to make sure that postgres isn't missing out on a real, fundamental use case. Fashionable technologies don't always have something new to offer, but sometimes they help you see something from a new perspective.

[1] Postgres is very non-traditional in many respects, and I personally believe it's revolutionary; but many people perceive it as more traditional.

collyw3• 12 years ago

OK, I now know why it is the most important patch. Is there anywhere I can read what it is what it does exactly?

spikels5• 12 years ago

Maybe try searching for "Postgres 9.4 hstore". Just a thought but I'm to lazy to try. ;)

pfraze• 12 years ago

The node-pg driver by brianc is in good shape; I'm not sure why OP sees it as a major issue. Feel free to improve it, though.

dorfsmay3• 12 years ago

I don't tkink people care so much for storing json, rather than "talk" json to the database. A different interface with simple insert and update with json is typically what people are after. A sort of ORM that simplifies inserts and update (and retrieval per primary key even) where data is provided as json would be a much better tool to attract the newer generation of programmers.

And like some of the comments said, PLEASE auto-partitionning!

einhverfr8• 12 years ago

Agreed. How about something like:

"Here is a financial transaction. Please make sure it is balanced and post it."

This is not trivial to do right now. Top-notch JSON support would make it a breeze.

jeltz7• 12 years ago

Just having a JSON type should fix this without the need for a JSON based protocol. Actually the current JSON support in 9.3 should be enough for this. Just call a stored procedure with the JSON object describing the transactions.

rdtsc3• 12 years ago

This is not trivial to do right now. Top-notch JSON support would make it a breeze

What does it matter if it is JSON or not. In financial transactions you want:

* Maximum fault tolerance * Auditability * Security * Performance * Ability to roll it back with all the cleanly defined states in between * Ability to talk to legacy / standard systems

JSON-ifying the contents of the transaction is nice. But I wouldn't think that is the main pain point in the industry at the moment. It is really the behavior of the systems outside the transaction contents itself that matters.

jeltz7• 12 years ago

As far as I can tell hstore is one of the most popular extensions to PostgreSQL, so people do indeed care about storing non-relational data in their database.

spikels5• 12 years ago

In my opinion hstore is one of the most useful newer features in Postgress and hstore2 is a big step improvement on it.

sandGorgon2• 12 years ago

I would like to suggest a modification to the author's thought about an ORM. What would be very cool would be a PG extension that wraps a mongodb compatible Api on top of postgresql.

That should completely short circuit the whole value proposition discussion of pg vs mongo.

twic3• 12 years ago

This could be done fairly easily, perhaps more easily, as a standalone program, i think. Something that exposes a MongoDB-compatible API, and translates requests into libpq to go to a PostgreSQL backend.

You could run it either on the PG host, or on the same host as the apps wanting to talk to PG.

brannon• 12 years ago

My understanding of all of this is beginner level at best, but isn't that more or less what waterline does? https://github.com/balderdashy/waterline-docs

jeltz7• 12 years ago

The guy behind plv8 has already started such a project. I have no idea if it is production ready.

https://github.com/umitanuki/mongres

sandGorgon2• 12 years ago

this is very cool. I wonder what happened to it.

freyr• 12 years ago

Could someone fill me in about what's lacking with the node pg driver by brianc?

spikels5• 12 years ago

My understanding is that it will need to be updated to handle all the new hstore2 features.

saosebastiao• 12 years ago

I think this addition is extremely valuable, but courting node users is a weak argument for it. For me, it is more about the growing importance of semi-structured data, as well as other forms of data that are difficult to model outside of row-tuples and joins. Hstore was a hack...really useful, but a hack nonetheless. Hstore2 is a legitimate kv-store. Now if we could just do the same with a graph-store.

rdtsc3• 12 years ago

Just by observing from the outside, Node.js and MongoDB, don't know what it is about those two platforms, but the communities formed around them seem to attract a large number of people I wouldn't want to interact with. There is a lot of immaturity and drama. Maybe it is the unreasonable and over-hyped marketing, not sure, can't quite put my figure on it.

Go is also a new language and framework that is popular but it has a different feel, a better one. Python so far, has one of the largest and most pleasant community (given its size) I've seen.

Json/KV friendly features are welcome, but perhaps looking to impress Node.js and MongoDB users is just barking at the wrong tree.

Why HStore2/jsonb is the most important patch of PostgreSQL 9.4

Related Stories