[Melbourne-pm] PostgreSQL and indexing with json types

Daniel Pittman daniel at rimspace.net
Wed Oct 3 18:40:57 PDT 2012

On Wed, Oct 3, 2012 at 4:56 PM, Toby Corkindale
<toby.corkindale at strategicdata.com.au> wrote:
> Just something interesting I noticed today..
> As you've probably already seen, PostgreSQL 9.2 added support for storing
> JSON documents in a 'json' type field. It also adds the V8 javascript engine
> as a first-class procedural language.

PostgreSQL, the best FOSS NoSQL available. :)

> Initially I thought that you'd probably end up with a lot of tables with key
> metadata separated out into your int/char/etc types so you could index it..
> but someone has pointed out that you can index into the json data using the
> v8 engine and Postgres' functional indexes.
> That is reasonably nifty. Currently you need to write a couple of helper
> functions yourself, but I suspect we'll see more of this brought into the
> built-in functions by 9.3.

Their XML and HSTORE engines have done unstructured data for a while,
and the indexing over them (which is pretty much equivalent to the
JSON indexing) is really, really good.  So, not only does it work, it
works damn well.

Better still, combining unstructured and structured storage in one
engine works well, and you get all the usual transactional magic.

Daniel Pittman
♲ Made with 100 percent post-consumer electrons

More information about the Melbourne-pm mailing list