PostgreSQL 9.2 beta adds JSON

http://www.h-online.com/open/news/item/PostgreSQL-9-2-beta-improves-scalability-adds-JSON-1573815.html

by voodoochilo • 14 years ago

108 24 14 years ago

h-online.com

agotterer• 14 years ago

Will the JSON type have document indexing or is it just a text store that will validate that it's JSON.

pyrotechnick• 14 years ago

Looks like it's just validation: http://www.postgresql.org/docs/devel/static/datatype-json.ht...

sickpig• 14 years ago

just validation, json core type it's intentionally very minimal (see postgres hackers mailing list archives for more details)

anyway as a possible workaround just see andrew dustan's post:

http://people.planetpostgresql.org/andrew/index.php?/archive...

fusiongyro• 14 years ago

You don't necessarily need special support for that. There's no special XML indexing support, but you can use the XPath and XSLT functions to extract the data of interest and index that:

    CREATE TABLE foo (a_doc xml);
    CREATE INDEX doc_title ON foo ((xpath('/root@title', a_doc));

I don't see any JSON functions for extracting data in the devel documentation, but that's what you would be waiting on, and it could probably be added by an extension without having to wait for built-in support.

pygy_• 14 years ago

There's no native function, but it can easily be implemented. See sickpig's answer (sibling to your post) for a Javascript-based solution.

diminish• 14 years ago

I was suddenly excited for a JSON API support directly from PostgreSQL, been too naive. But I am still certain about the future.

gphil3• 14 years ago

I think the bigger deal (at least for most existing deployments) are the index-only scans. This is one of the only features of Oracle that I missed after switching to Postgres.

benhoyt2• 14 years ago

Can you please explain index-only scans, and what the advantages are (or why this isn't the default already)?

gphil3• 14 years ago

Sure. It's not the default already because the functionality did not exist at all in previous versions.

Say you have a table with a bunch of columns: (column1...column15) and you have an index on (column3, column4) and you run a query like:

  select column4 from table where column3 = x;

the DB will only read from the index (and not look at the table at all.) This is much faster as the index is much smaller in size than the table.

harshreality• 14 years ago

It's not the default already because pre-9.2 the MVCC implementation in Postgres didn't guarantee that an index has the same visibility as the table data.

http://rhaas.blogspot.com/2010/11/index-only-scans.html

j-kidd2• 14 years ago

The title of the linked article is actually "PostgreSQL 9.2 beta improves scalability, adds JSON". Not sure why it get truncated.

I, for one, am not excited by the JSON addition at all. On the other hand, index-only scanning, scalability improvement, and reduced power consumption are all very big deal.

jpitz• 14 years ago

I don't know. Index-only scans bring a level of feature parity with Oracle. Range types / range exclusion constraints introduce features that no other DBMS has - and solves a messy, hard problem that you pretty much have to hand-code for each scenario.

One of these is legitimately revolutionary, and it isn't covering indexes.

gphil3• 14 years ago

I have to admit that I am not as familiar with this feature, so I will check it out. I glossed over it as something I could already accomplish with a check constraint or a trigger, but I think you might be right that there's more to it than that.

brlewis• 14 years ago

More discussion at http://news.ycombinator.com/item?id=3472177

fauigerzigerk• 14 years ago

Covering indexes is really good news!

stephen• 14 years ago

Wow, range types with non-overlapping constraints:

http://www.postgresql.org/docs/devel/static/rangetypes.html

CREATE TABLE room_reservation ( room TEXT, during TSRANGE, EXCLUDE USING gist (room WITH =, during WITH &&) );

INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');

INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)'); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )).

Used to have to rely on the application to do these sorts of constraints.

j-kidd2• 14 years ago

Yeah, this is another big one. I first came across the temporal project [1] in year 2008. Glad to see that it finally makes it to release.

[1] http://pgfoundry.org/projects/temporal/

pimeys2• 14 years ago

It's kind of annoying to read so much good about PostgreSQL when you have hundreds of gigabytes of data in MySQL and no real reason to migrate to another database because of the expenses. I loved postgres in my previous project when I needed spatial indexing and I started to like its interface a lot.

It seems to be that Postgres is solving the problem of having different databases for your app. I would love to see the Redis datatypes built into it and Postgres might then become the strongest choice when selecting tools for my next app.

jeffdavis• 14 years ago

"I would love to see the Redis datatypes built into it and Postgres might then become the strongest choice when selecting tools for my next app."

Have you considered writing an extension to do that? It may be easier than you think; postgresql has a powerful extension mechanism (you can even do specialized indexing!).

As far as I can tell, JSON only made it into core because there are expected to be many users very quickly. But that doesn't scale -- it's better for users to look at pgxn.org and developers to use the extension mechanism. Once that becomes a matter of course, we'll see an explosion of specialized extensions.

bsg75• 14 years ago

Which Redis types specifically?

Have you seen?: https://github.com/dpage/redis_fdw

pimeys2• 14 years ago

Seems pretty experiemental. But just for a key-value store and using the json datatype, how good is the support for all these features in libraries like ActiveRecord pg adapter? I remember that two years ago features like bounding box selects required plain sql, although some quite bad libraries existed.

PostgreSQL 9.2 beta adds JSON

Related Stories