PostgreSql now both a relational and document database

— 2 minute read

Postgres 9.3 added support for new data types, JSON and JSONB; the latter of which is very similar to BSON used in MongoDb.

However, I was very disappointed because while it was possible to Create/Read/Update/Delete entire JSON objects, it was not possible to Create/Update/Delete individual keys within the JSON object - it was all or nothing, which I was not too happy about; even pestering PostgreSql’s developers intermittently about. Many others wanted to be able to do this as well.

postgres mailing list on JSONB create/update/delete

The half-baked solution at the time was to use HSTORE instead of JSON/ JSONB, HSTORE was essentially a hashmap, which, unlike JSON, only supported a single level of hierarchy, so using it was rather limiting.

Postgres 9.4 didn’t make any progress on this, but it looks like Postgres 9.5, still in alpha, has added support for per-key Create/Update/Delete operations.

The implications of this are profound, as I believe that this makes PostgreSql the first database to properly support both relational (table based) and document database paradigms.

Ever needed to do a join on two documents in MongoDb? You probably had to work around this by doing the join logic client side, and send more than one database query. Ever needed to store unstructured data in MySql? You probably had to use a text field or a blob, and write custom scripts to serialise/deserialise.

Well, soon we should be able to do both at the same time easily!