No video of the event yet, sorry!

We were asked to design a system for storing and analyzing information about traffic accidents in the Philippines. The solution needed to support flexible metadata attached to each record, as well as allow geospatial analysis of records. We also needed to be able to make guarantees about data integrity.

To satisfy these requirements, we adopted a database architecture that is a hybrid of document-oriented and relational database concepts. We utilize JSONB columns to store schemas and much of the record data, but lean heavily on Postgres' foreign key, geometry, and timestamp columns to keep everything organized. This schema has proven both resilient and performant, enabling the generation of complex reports entirely within the web app's request-response cycle. The production system is currently being used in two pilot cities (Manila and Cebu), and contains around 50,000 records. Hardware requirements for the database server are modest.

This case study will include a deep dive into our hybrid schema. Attendees will gain an understanding of the relative strengths of JSONB, geometries, and relational database fields, and how they can be used in combination to both provide efficient queries and ensure data integrity.

Date:

Duration:

50 min

Room:

Conference:

PGConf Local: Philly 2017 [PgConf.US]

Language:

Track:

PostgreSQL

Difficulty:

Medium