PostgreSQL introduced jsonb support

Binary JSON

PostgreSQL has introduce jsonb.. a diamond in the crown of PostgreSQL 9.4.Based on an elegant hash opclass for GIN, which competes with MongoDB performance in contains operator .

Feature’s documentation :

Feature’s story:

MoSQL live-replicating from MongoDB to PostgreSQL

MoSQL, a tool Stripe developed for live-replicating data from a MongoDB database into a PostgreSQL database. With MoSQL, you can run applications against a MongoDB database, but also maintain a live-updated mirror of your data in PostgreSQL, ready for querying with the full power of SQL.



Here at Stripe, we use a number of different database technologies for both internal- and external-facing services. Over time, we’ve found ourselves with growing amounts of data in MongoDB that we would like to be able to analyze using SQL. MongoDB is great for a lot of reasons, but it’s hard to beat SQL for easy ad-hoc data aggregation and analysis, especially since virtually every developer or analyst already knows it.

An obvious solution is to periodically dump your MongoDB database and re-import into PostgreSQL, perhaps using mongoexport. We experimented with this approach, but found ourselves frustrated with the ever-growing time it took to do a full refresh. Even if most of your analyses can tolerate a day or two of delay, occasionally you want to ask ad-hoc questions about “what happened last night?”, and it’s frustrating to have to wait on a huge dump/load refresh to do that. In response, we built MoSQL, enabling us to keep a real-time SQL mirror of our Mongo data.

MoSQL does an initial import of your MongoDB collections into a PostgreSQL database, and then continues running, applying any changes to the MongoDB server in near-real-time to the PostgreSQL mirror. The replication works by tailing the MongoDB oplog, in essentially the same way Mongo’s own replication works.


MoSQL can be installed like any other gem:


$ gem install mosql


To use MoSQL, you’ll need to create a collection map which maps your MongoDB objects to a SQL schema. We’ll use the collection from the MongoDB tutorial as an example. A possible collection map for that collection would look like:

      - _id: TEXT
      - x: INTEGER
      - j: INTEGER
     :table: things
     :extra_props: true

Save that file as collections.yaml, start a local mongod and postgres, and run:


$ mosql --collections collections.yaml


Now, run through the MongoDB tutorial, and then open a psql shell. You’ll find all your Mongo data now available in SQL form:

postgres=# select * from things limit 5;
           _id            | x | j |   _extra_props
 50f445b65c46a32ca8c84a5d |   |   | {"name":"mongo"}
 50f445df5c46a32ca8c84a5e | 3 |   | {}
 50f445e75c46a32ca8c84a5f | 4 | 1 | {}
 50f445e75c46a32ca8c84a60 | 4 | 2 | {}
 50f445e75c46a32ca8c84a61 | 4 | 3 | {}
(5 rows)

mosql will continue running, syncing any further changes you make into Postgres.

For more documentation and usage information, see the README.


MoSQL comes from a general philosophy of preferring real-time, continuously-updating solutions to periodic batch jobs.


MoSQL is built on top of mongoriver, a general library for MongoDB oplog tailing that we developed. Along with the MoSQL release, we have also released mongoriver as open source today. If you find yourself wanting to write your own MongoDB tailer, to monitor updates to your data in near-realtime, check it out.

DuckDuckGo serves 1 Million searches a day has published an interview with Gabriel Weinberg, founder of Duck Duck Go and general all around startup guru, on what DDG’s architecture looks like in 2012. You fill find detail on how they use memcached, postgreSql and many other great peace of software to serves 1 million search a day !

Can’t searching the Open Web provide all this data? No really. This is structured data with semantics. Not an HTML page. You need a search engine that’s capable of categorizing, mapping, merging, filtering, prioritizing, searching, formatting, and disambiguating richer data sets and you can’t do that with a keyword search. You need the kind of smarts DDG has built into their search engine. One problem of course is now that data has become valuable many grown ups don’t want to share anymore.



The full article on

PostgreSQL 9.2 introduced JSON built-in data type

PostgreSQL 9.2 has introduced a new feature related JSON; built-in data type. So you can now store inside your database directly JSON fields without the need of an external format checker as it is now directly inside Postgres core.

Built-in JSON data type.
Like the XML data type, we simply store JSON data as text, after checking
that it is valid. More complex operations such as canonicalization and
comparison may come later, but this is enough for now.
There are a few open issues here, such as whether we should attempt to
detect UTF-8 surrogate pairs represented as uXXXXuYYYY, but this gets
the basic framework in place.

A couple of system functions have also been added later to output some row or array data directly as json.

Add array_to_json and row_to_json functions.
Also move the escape_json function from explain.c to json.c where it
seems to belong.
Andrew Dunstan, Reviewed by Abhijit Menon-Sen.

What actually Postgres core does with JSON fields is to store them as text fields (so maximum size of 1GB) and top of that a string format check can be performed directly in core.

In case of a format error you will receive the following error message:

ERROR: invalid input syntax for type json


PostgreSQL Magazine Issue #01

You can have access PostgreSQL Magazine Issue #01 in various ways :