
PostgreSQL 9.2 beta adds JSON - voodoochilo
http://www.h-online.com/open/news/item/PostgreSQL-9-2-beta-improves-scalability-adds-JSON-1573815.html
======
stephen
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-kidd
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/>

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

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

~~~
gphil
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.

~~~
benhoyt
Thanks, that's interesting. So it only kicks in when your index covers all the
columns in the "what" (select) clause. That tends not to be true for tables
I've designed/worked with. I guess you could index differently to take
advantage of this though.

~~~
jeltz
Yes, databases which support index-only scans tend to encourage this pattern
of adding extra columns in your indexes. In some cases this can be a big gain,
but larger indexes means that the indexes are slower for queries which do not
need the extra columns or need to read the entire row anyway.

Another set of queries which benefit from index only scans are those with
EXISTS or JOINs just used for filtering the result set. In PostgreSQL 9.1 they
always needed to read part of the actual row to check the visibility
information. In 9.2 it will for VACUUMED pages only need to check the index
and the visibility map.

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

~~~
fusiongyro
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_
There's no native function, but it can easily be implemented. See sickpig's
answer (sibling to your post) for a Javascript-based solution.

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

~~~
bsg75
Which Redis types specifically?

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

~~~
pimeys
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.

------
brlewis
More discussion at <http://news.ycombinator.com/item?id=3472177>

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

------
fauigerzigerk
Covering indexes is really good news!

