Postgres 9.2 highlight: JSON data type | Hacker News Reader

Postgres 9.2 highlight: JSON data type

michael.otacoo.com

FYI: Postgres also supports the hstore data type since 8.3. It is quite handy if you want to store key-values in your table

http://www.postgresql.org/docs/9.2/static/hstore.html

Interesting. How does PostgreSQL with hstore compare to a dedicated key-value data store?

I think this will be really cool when we can index and query on parts of the JSON structure, similar to hstore.

plv8.

    https://code.google.com/p/plv8js/

Besides the obvious (such as projecting a sub-datum from a json datum), you can also do data transformations (whatever javascript can give you) -- as a contrived example, the sine of a number or the sum of two numbers -- and, you can try projecting arrays or hstores from a json and then using GIN/GiST to do a more expensive but more powerful index over them.

A quick and dirty example from some time ago: https://gist.github.com/1152997

Some more exotic built-in indexing will probably show up in the coming years, but a lot of things can be accomplished relatively soon with this extension.

that's right. plv8 works. but it certainly does not feel like using a postgres index but a map-without reduce-framework. so i hope that built-in indexing will come sooner than later... :D

You kind of can using partial function indexes already but it's not very flexible.

At first, I thought this wasn't a big deal because JSON really is just text.

Then I realized that this will help in validation. Go Postgres!

...maybe my perspective is different, but WHY would you want to do validation of a "complex" field at the db level? (I mean, for a web app you end up with 3 (!!) layers of validation now: 1. client (javascript), 2. app (...), 3. db (javascript? PL/something?) and, as an added "bonus", you loose the ability to migrate to a different db

...all these db features seem like maintainance nightmare waiting to happen. ...why don't people keep the rdbms "slim" and mean and write the logic in your app code, where it belongs? (you don't even have version control for the code in stored db procedures and things like that!!)

> WHY would you want to do validation of a "complex" field at the db level?

Anything I build on top of this database now benefits from all of my consistency and integrity checks. While it won't prevent me from trying to put bad data in, it will prevent me from actually doing the storage (instead, it will remind me that I'm a terrible human and I forgot to validate certain parts of data).

> I mean, for a web app you end up with 3 (!!) layers of validation now: 1. client (javascript), 2. app (...), 3. db (javascript? PL/something?)

This is a legitimate concern, but not all applications need all these checks. Hopefully you've abstracted things apart enough so that you can build your application reusing logic where possible.

> as an added "bonus", you loose the ability to migrate to a different db

I hear this one a lot, and it's so misguided imo. Different database systems are implemented completely differently. If we strictly adhered to relational algebra and the relational model then we might be in a position where you could switch out databases and have the same performance. However, what we have no doesn't work like that. Even if you strictly use standard SQL, I can almost guarantee that for large databases you'll see difference performance characteristics over the different database implementations.

> why don't people keep the rdbms "slim" and mean and write the logic in your app code, where it belongs?

That isnt where it belongs at all. Your app should interact with the database, but the database should have the final say in guaranteeing consistency. This provides an excellent foundation that allow you to now layer future projects on top of, without being tied to a specific application framework/library/programming language. Remember, we already have the impedance mismatch so whenever you map from the database to your application, you make a decision about how that mapping is done. So even if you offload all the database stuff to your 'app', you limit yourself to what you can truly do with the database.

Seriously, let the database do its job and be a database.

> you don't even have version control for the code in stored db procedures and things like that!!

This is nonsense. http://pyrseas.projects.postgresql.org/ https://github.com/theory/sqitch https://github.com/depesz/Versioning. Do your research.

(you don't even have version control for the code in stored db procedures and things like that!!)

You should absolutely be storing your scripts to create your database schema, including stored procedures, in version control.

As in most things in software engineering, it's a matter of tradeoff. Consider for instance the case where multiple applications access the database. You could either build a service abstracting, or you could build your validation layer in the database via stored procedures (maybe you can't afford the latency of the service), in which case you'll appreciate the additional safety.

Or you could have used a domain with a validator written in PL/somelanguage as constraint

Why would anyone store JSON data in a relational database, thus losing the opportunity to join with the objects kept as JSON data ? I guess that I like normalized data too much and that some people have performance constraints, but still - a denormalized tables solves that problem and keeps output formatting sanely out of the data model.

Well technically you don't join (document store approach) or you keep your IDs and FK's as surrogates outside the JSON blob and use them as indexes only (hyprid mongo-ish store).

What it does give you is a JSON object store with somewhat better stability and real transactional guarantees compared to say CouchDB or MongoDB.

However, the issue I have with this approach is more that there is some opportunity to add more processing into the database black box, which is expensive to scale up. I'd rather it wasn't type aware of JSON (i.e. it was just a varchar(max) column) and all that processing sat in the application itself which is cheap to scale out.

Document as a blob, with identifiers and foreign keys surrogates as their own fields - OK, I could live with that hybrid approach.

CouchDB gives you /real/ transactions, they just don't span multiple documents.

Well that's not real transactions - that's ACID compliance...

and you also cannot have secondary indexes that way

If your master data source is JSON, then you can store your raw data as JSON and be able to do ad-hoc queries against it.

At the same time, you can break your JSON into relational tables so you can also operate on it in traditional ways.

...is there any point in making a db so "fat" feature wise? ...isn't a text field where I can serialize anyth anyway I like (with the related logic in my app of course) enough for 99% and things like this a feature that only benefits 1%?

Your database should be the central storage of this data and it should do it's hardest to guarantee that everything is consistent. Consistency means referential integrity over foreign keys, but it also means that values of attributes are well within their specified domain. And "D]7843bn;hdbnb" is not valid JSON, so the database should reject that.

If your database is already doing these checks, then it can simplify anything that builds on them, because you already have huge amounts of guarantees.

Other guarantees you would add are unique constraints, exclusion constraints, check constraints, and so on.

So it might only benefit 1%, but what's the problem with that? If you want to stuff JSON into a text field, no one is stopping you. Of course, now you'd have much less reason to do so, and would be loading a gun getting ready to shoot yourself in the foot...

...instead of "would be loading a gun getting ready to shoot yourself in the foot" I'd see it as building an app that could easily be maintained by a/more programmer(s) with no advanced DBA knowledge (less people with less expertise needed, and have the really smart know-it-alls work on more interesting things - and yes, I am thinking this from a MANAGER'S perspective, guilty on that ;) )

and that's why one reason for MySql popularity is its simplicity. Instead of having full alphabet of features it just has A and B, and its easy to learn. BTW I'm not a fan of MySql.

Indeed. But when it comes to learning, isn't learning the basic subset of Postgres commands just as easy as the basic subset of MySQL commands and so on? I don't see any difference in learning curve between the 2...

(but what I'm afraid of about adding all this features is that one day "take the basic set of Postgres features" will become more like "pick a subset of C++ features (that YOU THINK are basic)" and we know where this path goes...)

Reply on news.ycombinator.com