Over the past five years or so, NoSQL databases have enjoyed a spike in popularity compared to relational (i.e., SQL-driven) databases, thanks in large part to industries such as online gaming. A typical NoSQL database can write data 10 or 20 times faster than a relational one, although this speed comes at a price: The data is cached in memory longer before being written to disk, so the data update remains inconsistent longer. As a result, applications that require fast consistency may struggle with NoSQL. Waiting 20 minutes for your account to be updated after withdrawing cash at an ATM, for example, just isn’t acceptable. But in cases where the longer update time is acceptable, you might be able to use PostgreSQL to speed up your database; if you’re using a PostgreSQL relational database, then recent changes to JSON handling, introduced in version 9.2, mean you can use PostgreSQL for all your NoSQL needs. To find PostgreSQL-related jobs, click here. Why use PostgreSQL? Adding a new technology such as NoSQL to any technology stack will increase business costs—you need new staffers who understand the technology, plus you must adopt new technology (and extra hardware costs, such as servers and backups) for getting data in and out of the NoSQL datastore. But if you're running PostgreSQL, you can avoid most of these costs—it's the same hardware, and backup system. You just need to do a little programming.

PostgreSQL

First released in 1995, PostgreSQL has long been the second-most-popular open-source database (behind MySQL, available on every Linux hosting package). On the technical front, though, many consider PostgreSQL superior to MySQL, especially given uncertainty over the latter in the wake of Oracle buying Sun. Since PostgreSQL features binaries for most platforms and drivers for most programming languages, it's worth evaluating for your next open-source database project, whether it needs a relational database or NoSQL.

Why JSON?

JSON, short for JavaScript Object Notation, is a lightweight data-interchange format that is easy to read and write. It's how JavaScript holds objects in memory, and it's generally easier to work with than XML. Here's an example:

{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [

     {"value": "New", "onclick": "CreateNewDoc()"},

     {"value": "Open", "onclick": "OpenDoc()"},

     {"value": "Close", "onclick": "CloseDoc()"}

   ] } }}

NoSQL databases store data in JSON documents. This makes it easy to have lists or complex hierarchies of values within one document. Unlike a relational database, which stores values in a fixed type table structure, JSON documents are much more flexible; you can mix different data values in a document, and attach extra values to objects.

PostgreSQL and JSON

In PostgreSQL 9.2, the JSON data type was introduced along with a couple of associated functions; the next update, 9.3, saw an expansion in the number of JSON functions and operators. The current 9.4 beta (3) added the jsonb type, which is a binary version of JSON plus more operators and functions. The jsonb type is significant because unlike json documents, which are an exact copy of the input document and include white spaces between tokens, jsonb doesn’t preserve insignificant white spaces, the order of object keys, or duplicate keys. It's almost always better to store documents in jsonb than json as the comparison operators only use jsonb, not json. Before you start using JSON in PostgreSQL you should familiarize yourself with rfc7159, which covers using JSON as a data-interchange format. SQL queries in PostgreSQL use this format for JSON.

Working With JSON

I like the way the PostgreSQL online documentation lets you quickly flip from the 9.3 to the 9.4 page in one click, so you can see the jsonb type and the new functions. In addition to 9.3 and 9.4, there’s also the EnterpriseDB option, with the Postgres Extended Datatype Developer Kit (PGXDK), a free AMI that simplifies the development of Postgres-based applications that integrate NoSQL technologies with JSON and Python. EnterpriseDB is a third-party company that provides an enterprise version of PostgresSQL, along with support. If you want to go down the PostgreSQL JSON path, and are not already on EnterpriseDB, then I recommend you get started with 9.4, even at beta, and store text as jsonb. Here, for example, is a simple query.

select * from jsonb_array_elements_text('["alpha", "beta", "charlie"]')

This returns the three strings:

alpha beta charlie

Or how about:

select jsonb_array_length('["alpha", "beta", "charlie"]')

That returns “3.” Ready to get to work—or at least learn a little more? If you are new to JSON, start with the W3Schools JSON tutorial. Once you're up to speed with JSON, if you are new to NoSQL concepts, watch Martin Fowler's one-hour video on NoSQL. After that, you'll be ready to install PostgreSQL and can then start creating JSON using PostgreSQL JSON functions and operators. (Be aware that PostgreSQL JSON only stores documents in UTF-8 encoding and is quite strict about this encoding. Trying to use text encoded in UTF-16, UTF-32, etc. may cause errors.)

Related Articles

• A NoSQL Alternative: Disk Folders
• Are Database Administrators Doomed?
• Deflating the Hype Over In-Memory Databases

Image: pking4th/Shutterstock.com, The PostgreSQL Global Development Group