Schedule - PGConf.EU 2017
Lessons learnt writing PostgreSQL extensions for JSON(b)
Date: 2017-10-25
Time: 14:00–14:50
Room: Baltic II+III
Level: Intermediate
PostgreSQL is really powerful when dealing with structured data, but sometimes you can’t avoid having to work with data that is unstructured. That is the case at Geoblink, where we have to deal with data coming from several different providers, forcing us to combine the strengths of relational databases and NoSQL.
Fortunately for us, a great deal of work has gone into the last versions to develop the JSONB format, which allows proper indexing and fast result retrieval. However, JSONBs in PostgreSQL are notoriously hard to operate with, for example, to merge two jsons and add up values for properties of the same key.
In this talk we will share how we developed two PostgreSQL extensions in C to work with JSONBs and some lessons we learnt on the way. These extensions have allowed us to avoid the need of using NoSQL databases to persist unstructured data, storing it in PostgreSQL with the rest of our application data. We are now able to execute complex operations at retrieval time directly in PostgreSQL, decoupling this process from our back end services. This have resulted not only in a simpler infrastructure but also a boost in performance.
We will introduce the techniques to work with JSON from native code. In particular we will explain how to use JsonbIterators and JsonbValues for performant access, and how to build algorithms on top to execute standard JSON operations like merging fields or schema validation.
Slides
The following slides have been made available for this session:
