PostgreSQL 9.2 released
| From: | Selena Deckelmann <selena-AT-postgresql.org> | |
| To: | pgsql-announce-AT-postgresql.org | |
| Subject: | PostgreSQL 9.2 released | |
| Date: | Mon, 10 Sep 2012 05:57:18 -0700 | |
| Message-ID: | <CAN1EF+zbqQoXbffG7VG-7sjsdYGKbFpQeLpFcj6aagdCLrPinw@mail.gmail.com> |
10 SEPTEMBER 2012: The PostgreSQL Global Development Group announces PostgreSQL 9.2, the latest release of the leader in open source databases. Since the beta release was announced in May, developers and vendors have praised it as a leap forward in performance, scalability and flexibility. Users are expected to switch to this version in record numbers. "PostgreSQL 9.2 will ship with native JSON support, covering indexes, replication and performance improvements, and many more features. We are eagerly awaiting this release and will make it available in Early Access as soon as it’s released by the PostgreSQL community," said Ines Sombra, Lead Data Engineer, Engine Yard. Links - Downloads, including packages and installers: http://www.postgresql.org/downloads - Release Notes: http://www.postgresql.org/docs/9.2/static/release-9-2.html - Documentation: http://www.postgresql.org/downloads - What's New in 9.2: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQ... - Press Kit: http://www.postgresql.org/about/press/presskit92 Improved Performance and Scalability With the addition of linear scalability to 64 cores, index-only scans and reductions in CPU power consumption, PostgreSQL 9.2 has significantly improved scalability and developer flexibility for the most demanding workloads. Organizations like the U.S. Federal Aviation Administration and Heroku.com run applications on PostgreSQL, and HP has adopted it for their remote support software and to power their HP-UX/Itanium solutions. Improvements in vertical scalability increase PostgreSQL's ability to efficiently utilize hardware resources on larger servers. Advances in lock management, write efficiency, index-only access and other low-level operations allow the database engine to handle even larger-volume workloads. Numerically, this means: - Up to 350,000 read queries per second (more than 4X faster) - Index-only scans for data warehousing queries (2–20X faster) - Up to 14,000 data writes per second (5X faster) Also, the addition of cascading replication enables users to run even larger stacks of horizontally scaled servers under PostgreSQL 9.2. "NewsBlur, a social news reader, relies on Postgres for storing millions of sites and subscriptions. Solid and reliable for years," said Samuel Clay, founder of newsblur.com. "We're consistently on the bleeding edge (9.1 now, moving to 9.2 soon for the cascading replication alone) and it's been a pleasure since 8.4." Flexible Developer Support The flexibility of PostgreSQL is reflected in the diversity of organisations that have adopted it. For example NASA, the Chicago Mercantile Exchange and Instagram all rely on it to perform mission-critical applications. Version 9.2 extends this flexibility even further by including support for Range Types and JSON, allowing developers to use PostgreSQL in completely new ways. Range Types allow developers to create better calendaring, scientific, and financial applications. No other major SQL database supports this feature, which enables intelligent handling of blocks of time and numbers. With PostgreSQL 9.2, query results can be returned as JSON data types. Combined with the new PL/V8 Javascript and PL/Coffee database programming extensions, and the optional HStore key-value store, users can now utilize PostgreSQL like a "NoSQL" document database, while retaining PostgreSQL's reliability, flexibility and performance. "Native JSON support in PostgreSQL provides an efficient mechanism for creating and storing documents for web APIs. We use front-end libraries like jQuery to request tabular and tree-structured data; and the new features make it convenient and provide performance advantages in retrieving that data as JSON, " said Taras Mitran, Senior Architect, IVC Inc. -- Sent via pgsql-announce mailing list (pgsql-announce@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-announce
Posted Sep 11, 2012 0:48 UTC (Tue)
by aristedes (guest, #35729)
[Link] (13 responses)
1. Getting normal SQL columns in and out in json format instead of normal SELECT output? Wrapping SQL output is something every database wrapper/ORM already does for you.
2. Storing json blobs of data in a dynamic way without having to predefine which columns (keys) are available and still being able to search with an index on the data attached to those keys. This would be very cool, but I don't understand how it would work when the data structure isn't defined in advance.
3. Just storing a json blob of data without any new indexing of that data at all. Basically nothing more than a BLOB with a new name.
Why on earth would I want to push down my application logic into javascript within the database? What is the use-case of this approach rather than a standard MVC approach where the model (ActiveRecord, Hibernate, Cayenne, etc) contains the relevant database entity logic.
Posted Sep 11, 2012 1:06 UTC (Tue)
by JohnLenz (guest, #42089)
[Link] (5 responses)
At first glance, accessing the database directly from the javascript sounds horrible. But with the modern javascript frameworks like dojo or extjs, these have MVC frameworks built in. For example, dojo has dojo stores plus dijit views. ExtJS has their extensive modeling. Defining the model and having a full ORM in javascript means makes sense for these frameworks since then you can bind grids and other widgets to the model, automatically have widgets respond to changes in the data, etc. But having an ORM in javascript and a second ORM in the server, while it might make sense some of the time, is a bunch of extra work if the ORM in the server does nothing but pass objects to the client.
I don't know if PostgreSQL allows clients direct access quite like CouchDB, where queries are HTTP requests which return JSON bodies, but even if the server just passes the PostreSQL response to the client it is easier to have PostgreSQL return the json directly.
Posted Sep 11, 2012 4:37 UTC (Tue)
by aristedes (guest, #35729)
[Link] (2 responses)
Perhaps I am just used to thinking of a database as a big interchangeable repository of data and not an integral part of the application code. Even stored procedures are something I avoid.
I did however discover that you can create functional indexes on json data like this:
CREATE INDEX x_in_json ON mytable (jmember(jsonfield,'x'));
so I can see the point of being able to store certain types of data in this way and still have indexed access to it.
Posted Sep 11, 2012 13:35 UTC (Tue)
by beagnach (guest, #32987)
[Link]
there's a new generation of client-side JavaScript frameworks - http://backbonejs.org/ is what I'm most familiar with. The client side interface has become so rich that it now needs a proper ORM.
This is _not_ to say we do away with all the sever-side infrastructure you mention (though a few sites seem to go most of the way) - in general there seems to be a hybrid approach where a subset of date is mapped client side for manipulation by the rich interface there.
Of course you want to avoid duplication of models between client and server-side, and of course you still need to validate data - the trend is to reduce the server-side representation, not eliminate it completely.
Posted Sep 13, 2012 19:17 UTC (Thu)
by jberkus (guest, #55561)
[Link]
Well, the feature is certainly less compelling if that's your orientation towards database systems. However, a modern DBMS is a powerful, feature-rich parallel execution engine, and some users like to harness that capability. If you don't want to use it, you don't have to.
The main benefit to this is that some users can eliminate the need to use a document database alongside PostgreSQL in mixed environments. This simplifies administration and provisioning.
Posted Sep 11, 2012 8:18 UTC (Tue)
by robert_s (subscriber, #42402)
[Link] (1 responses)
It doesn't.
Posted Sep 13, 2012 19:00 UTC (Thu)
by jberkus (guest, #55561)
[Link]
My personal ambition is "CouchGres", which would be CouchDB-over-Postgres. ;-)
Posted Sep 11, 2012 1:06 UTC (Tue)
by dlang (guest, #313)
[Link]
It's less efficient than if you do plan things out and store things in the traditional way, but it may still be a lot faster than pulling the data all the way to the client, parsing it, and then using a part of it in a follow-up query.
Posted Sep 11, 2012 4:56 UTC (Tue)
by sciurus (guest, #58832)
[Link]
"The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value."
http://www.postgresql.org/docs/devel/static/datatype-json...
Posted Sep 11, 2012 9:53 UTC (Tue)
by intgr (subscriber, #39733)
[Link]
For one, every complex type can now be represented in JSON and transparently combined with other JSON fragments. Previously, the textual representation of complex types -- especially nested types -- was quite complicated and error-prone to parse by third party code. Even this alone is pretty useful because you have a guarantee that data stored in a "json" field is actually valid JSON Not quite there yet. It's already possible and useful to index certain json keys when combined with functional indexes (as shown by aristedes). It's also possible to use an hstore-like GIN/GiST indexing strategy to index arbitrary keys; it's not implemented yet, but only a matter of time before someone writes such an extension.
Posted Sep 12, 2012 9:17 UTC (Wed)
by ringerc (subscriber, #3071)
[Link] (3 responses)
Right now it's #3 with validation, plus some functions to turn result sets or arrays into structured JSON for easy consumption in applications. That's the main benefit at this point. However, future versions are extremely likely to add enhancements like: Because Pg is so extensible, much of that is likely to be available as backports into 9.2.
Posted Sep 12, 2012 9:32 UTC (Wed)
by andresfreund (subscriber, #69562)
[Link] (2 responses)
Posted Sep 12, 2012 10:29 UTC (Wed)
by yodermk (subscriber, #3803)
[Link]
Posted Sep 12, 2012 10:42 UTC (Wed)
by ringerc (subscriber, #3071)
[Link]
Right now the lack of the concept of a "scalar json value" (due to an IMO over-eager interpretation of the JSON "spec") is frustrating all sorts of things, including proper json constructor functions.
More general indexing might be done through GiST, but I imagine it'd be a pretty complicated job involving extracting paths-to-values.
Why json
Why json
Why json
Why json
>> no server application to manage sessions, authorisation, and enforce
>> data validation.
Why json
> repository of data and not an integral part of the application code. Even > stored procedures are something I avoid.
Why json
Why json
Why json
Why json
It's actually multiple things.
Why json
> 1. Getting normal SQL columns in and out in json format instead of normal SELECT output?
# select row_to_json(row('some text', 123.4, null, row(1,2,3), array[1,2,3,4,5], json '{"a":1, "b":{}}' ));
row_to_json
-----------------------------------------------------------------------------------------------------------
{"f1":"some text","f2":123.4,"f3":null,"f4":{"f1":1,"f2":2,"f3":3},"f5":[1,2,3,4,5],"f6":{"a":1, "b":{}}}
> 3. Just storing a json blob of data without any new indexing of that data at all.
> 2. Storing json blobs of data in a dynamic way without having to predefine which columns (keys) are available and still being able to search with an index on the data attached to those keys
Why json
Why json
Its easier for hstore because hstore isn't nested and not individually typed (everything is a string).
An efficient index over arbitrary nesting and several types isn't exactly easy and I am not that sure that will come that soon. An opclass for not-nested json objects or such would be relatively easy though.
Why json
Why json