Introducing Notification Center

Change is a constant. At Heroku, we often deliver important information to users about changes and events on the platform, their apps and their accounts. We use a variety of media to keep users informed about these changes, including email, Twitter, the changelog and the blog.

To help provide more direct and relevant information, we've added a new feature to Dashboard called Notification Center. We'll be using the Notification Center to keep you informed of important events affecting you and your apps. When new notifications arrive, you'll see a badge in Dashboard's header.

Read more →

Historical Uptime on Status Site

Until now, Heroku Status has been focused primarily on present platform health - providing current status, uptime for the current month, and recent incident history. Today we're announcing an addition to our status site: a dedicated page to view historical uptime. The new uptime page provides a longer-term perspective on Heroku uptime and incidents - perspective that is critical for transparency and continued trust in the Heroku platform.

Read more →

Building Apps Efficiently on Heroku

Whether you’re building your initial prototype or running a large scale profitable business, there will be times where you will have questions about building, maintaining, or troubleshooting your application. One of our goals is to help make your business successful by empowering you to build quality software with best practices, clear documentation on a stable erosion-resistant platform. When in doubt, there are several channels here at Heroku available to help you get the support you need.

Read more →

Heroku Platform API Hack-a-thon June 20th in SF

The new Heroku platform API is out in public beta. Come join our API team for an API Hack-a-thon on June 20th at Heavybit Industries (9th and Folsom) for an in-depth look. Doors open at 6:00p.

Read more →

JavaScript in your Postgres

The same JavaScript engine that powers the web today is now available in your database.

This is one more step in evolving a data platform to meet all of your data needs. With a key/value store inside Postgres you gained agility in working with your schema. This agility was further improved with the JSON data type in Postgres 9.2. With geospatial support you removed the need for relying on additional tools for building location based apps. And today we're continuing to expand, going beyond SQL bringing the full power of the V8 JavaScript engine to your Heroku Postgres database. This offering is available immediately in public beta on all production tier databases.

More on V8

V8 is a powerful and fast JavaScript engine that was developed by Google, in addition to powering Google Chrome it can be found in Node.js and MongoDB. From its initial design V8 was intended to work both for browsers to run client side JavaScript and be integrated into other projects such as powering server side execution in the case of Node.js.

PL/V8, thanks to a lot of work from Hitoshi Harada, is this same V8 but as a procedural language within Postgres. PL/V8 is fully trusted language giving you a peace of mind when it comes the safety of your data, but enables a whole new powerful set of functionality. Want to write functions on your data without touching pl-pgsql? Want to put documents within your database? Want to run your CoffeeScript unit tests closer to your data? You now can do all of it with PL/V8.

Getting started

If you’re already taking advantage of the JSON datatype for some of your applications and want to begin using PL/V8, now you can by simply enabling the extension:

> CREATE EXTENSION plv8;

From here we can create a simple JavaScript procedure that returns the values for an array of keys we pass in:

> CREATE OR REPLACE FUNCTION plv8_test(keys text[], vals text[]) RETURNS
text AS $$
var o = {};
for(var i=0; i<keys.length; i++){
 o[keys[i]] = vals[i];
}
return JSON.stringify(o);
$$ LANGUAGE plv8 IMMUTABLE STRICT;

Of note in the above function is IMMUTABLE and STRICT. Immutable specifies that the function given the same inputs will return the same result. The optimizer therefore knows that it can pre-evaluate the function. If you lie to the optimizer, it will give you wrong answers. Strict means that if you send in NULL values you’ll get a null result.

And then take advantage of it:

> SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Craig', '29']);
          plv8_test
-----------------------------
 {"name":"Craig","age":"29"}
(1 row)

More Advanced PL/V8 Usage

Lets take a look at a more practical use case. Given some example JSON data such as:

> SELCT * FROM zips;
                                 data
---------------------------------------------------------------------
 {"city": "ACMAR", "loc": [-86.5, 33.5], "pop": 6055, "state": "AL"}
 {"city": "ARAB", "loc": [-86.4, 34.3], "pop": 13650, "state": "AL"}
...

It may be common to filter this data for some report, i.e. all cities with population greater than 10,000. To do this you first create a function – by creating a generic function that returns numeric value of a given key from a set of JSON, you can also re-use it elsewhere:

> CREATE OR REPLACE FUNCTION 
get_numeric(key text, data json)
RETURNS numeric AS $$
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE FUNCTION

Then we can use the function in our query:

> SELECT * 
FROM zips 
WHERE get_numeric('pop', data) > 10000;
                                 data
------------------------------------------------------------------------
{"city": "PERU", "loc": [-89.1, 41.3], "pop": 10050, "state": "IL"}
{"city": "JUNO", "loc": [-84.1, 34.3], "pop": 10196, "state": "GA"}
...

Functional Indexes

The ability to use JavaScript as part of your query through user defined functions provides great flexibility and continues to expand beyond just including javascipt snippets inline in your queries. Postgres allows you to create indexes on any expression, including functions. With PL/V8, it is possible to create an index on the function above:

> CREATE INDEX idx_pop 
ON zips(get_numeric('pop'::text, data));

Functional indexes that take advantage of V8 can also prove some great performance benefits. By adding the above index the query time goes from 206.723 ms down to 0.157 ms.

Summary

The world of application development is rapidly changing delivering new tools every day to make you more productive. Postgres and the database world are no different, now with JavaScript and JSON support. This powerful functionality is now available on all Heroku Postgres production tier databases – run CREATE EXTENSION plv8; on your database to get started today.

Browse the blog archives or subscribe to the full-text feed.