The Basics - PostgreSQL: Up and Running - page 10

What's New in PostgreSQL 9.4?
At the time of writing, PostgreSQL 9.3 is the latest stable release, and 9.4 is in beta with
binaries available for the brave. The following features have been committed and are
available in the beta release:
• Materialized views are improved. In version 9.3, refreshing a materialized view locks
it for reading for the entire duration of the refresh. But refreshing materialized views
usually takes time, so making them inaccessible during a refresh greatly reduces
their usability in production environments. Version 9.4 removes the lock so you
can still read the data while the view is being refreshed. One caveat is that for a
materialized view to utilize this feature, it must have a unique index on it.
• The SQL:2008 analytic functions percentile_disc (percentile discrete) and per
centile_cont (percentile continuous) are added, with the companion WITHIN
GROUP (ORDER BY…) SQL construct. Examples are detailed in Depesz ORDERED
SET WITHIN GROUP Aggregates . These functions give you a built-in fast median
function. For example, if we have test scores and want to get the median score
(median is 0.5) and 75 percentile score, we would write this query:
SELECT subject , percentile_cont ( ARRAY [ 0 . 5 , 0 . 75 ])
WITHIN GROUP ( ORDER BY score ) As med_75_score
FROM test_scores GROUP BY subject ;
PostgreSQL's implementation of percentile_cont and percentile_disc can take
an array or a single value between 0 and 1 that corresponds to the percentile values
desired and correspondingly returns an array of values or a single value. The ORDER
BY score says that we are interested in getting the score field values corresponding
to the designated percentiles.
• WITH CHECK OPTION syntax for views allows you to ensure that an update/insert on
a view cannot happen if the resulting data is no longer visible in the view. We
demonstrate this feature in Example 7-2 .
• A new data type— jsonb , a JavaScript Object Notation (JSON) binary type replete
with index support—was added. jsonb allows you to index a full JSON document
and speed up retrieval of subelements. For details, see “JSON” on page 96 , and check
out these blog posts: “ Introduce jsonb: A Structured Format for Storing JSON ,” and
“ jsonb: Wildcard Query .”
• Query speed for the Generalized Inverted Index (GIN) has improved, and GIN
indexes have a smaller footprint. GIN is gaining popularity and is particularly handy
for full text searches, trigrams, hstore s, and jsonb . You can also use it in lieu of B-
Tree in many circumstances, and it is generally a smaller index in these cases. Check
out GIN as a Substitute for Bitmap Indexes .
• More JSON functions are available. See Depesz: New JSON functions .