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:


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);

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']);
(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;
 {"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:

get_numeric(key text, data json)
RETURNS numeric AS $$
return data[key];

Then we can use the function in our query:

FROM zips 
WHERE get_numeric('pop', data) > 10000;
{"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.


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.

Heroku at WWDC

Each year, more than 5,000 iOS and Mac developers from over 50 different countries gather at Moscone Center West for Apple's Worldwide Developer Conference, or WWDC. It's the one week developers get to learn about all of the shiny new devices and APIs that they'll use to make their next great apps.

Read more →

2X Dynos Enter General Availability

2X Dynos

Thousands of Heroku customers have already updated their apps to utilize 2X dynos since they entered public beta on April 5. By providing twice the memory and CPU share, 2X dynos help to improve app performance and efficiency.

2X Dynos enter General Availability today. Starting tomorrow, June 1, 2013, 2X dynos will be billed at the full $0.10 per hour rate.

Read more →

Heroku Platform API, Now Available in Public Beta

Today, we are excited to release our new platform API into public beta, turning Heroku into an extensible platform for building new and exciting services. Our platform API derives from the same command-and-control API we use internally, giving entrepreneurs and innovators unprecedented power to integrate and extend our platform. Some of the uses we’ve imagined include:

  • Building mobile apps that control Heroku from smartphones and tablets;
  • Combining Heroku with other services and integrating with developer tools;
  • Automating custom workflows with programmatic integration to Heroku

Platform API

The platform API empowers developers to automate, extend and combine Heroku with other services. You can use the platform API to programmatically create apps, provision add-ons and perform other tasks that could previously only be accomplished with Heroku toolbelt or dashboard.

Read more →

Postgres 9.3 Beta Available

With each new release, Postgres brings new powerful functionality to your fingertips – Postgres 9.3 is shaping up to be no different. Postgres 9.3 will include richer JSON operators for the JSON datatype, SQL-standard LATERAL subquery support, materialized views, and of course much more. Postgres 9.3 Beta was made available earlier this week and we’re excited to announce a public alpha of it ourselves.

You can get started immediately with the alpha today by provisioning a Postgres 9.3 database on our production tier:

$ heroku addons:add heroku-postgresql:crane --version=9.3
Use `heroku pg:wait` to track status.
! WARNING: Postgres 9.3 is in alpha. alpha releases have
!          a higher risk of data loss and downtime.
!          Use with caution..
Use `heroku addons:docs heroku-postgresql:crane` to view documentation.

Our support of Postgres 9.3 Beta is alpha and comes with several conditions:

  • The 9.3 beta version will be supported for 7 days after new beta or GA releases for PostgreSQL 9.3 occur. At which point existing databases will be deprovisioned or migrated to the newest release. At this time all Postgres 9.3 databases are running the GA release of PostgreSQL 9.3.
  • Heroku Postgres uptimes are expected uptime guidelines and not a guarantee or SLA for uptime of your production database. As with many alpha or beta features the expected uptime is lower for Postgres 9.3 beta.
  • Forks, followers, and other functionality may see a higher level of issues during the alpha and may cease to work entirely due to changes that could occur between 9.3 beta and GA.

We're very excited to make PostgreSQL 9.3 available to you today, nevertheless we urge caution when using it as this is a very early beta PostgreSQL release. Once you do provision your 9.3 beta database, we want to hear from you at Let us know how you’re taking advantage of new features, and especially if you encounter any bugs.

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