PostgreSQL: Uncovering JSONB

Bringing the Best of Two Worlds Together

Transcript

1. PostgreSQL: Uncovering JSONB Bringing the Best of Two Worlds Together By Fabrício Ferrari de Campos Image from http://www.fanpop.com/clubs/bd/images/34275374/title/mammoth-wallpaper Vizir Talks #22
2. SQL NoSQL • Schemaless meets Relational • ACID (Atomicity, Consistency, Isolation, Durability) • Don’t lose data in the present • Decide in the future
3. About JSONB • Since Postgres 9.4 (dec - 2014) • Before that ◦ hstore ▪ Basic key/value store ▪ No nesting support ◦ JSON ▪ Simple text storage ▪ No good performance • JSONB ◦ Nesting support ◦ Saves as binary format ▪ Internally typed ◦ Great performance and querying support ◦ Supports index (full and specific)
4. Hello World example create table orders ( id uuid DEFAULT uuid_generate_v4(), total_value integer, items jsonb, payment_info jsonb );
5. Queries select count(*) from orders where payment_info->'address'->>'country' = 'Brazil' -- 1.72s select count(*) from orders where payment_info @> '{"address": { "country": "Brazil" } }' -- 1.65s select count(*) from orders where payment_info->'address'->>'country' in ('Brazil', 'Argentina') -- 2.6s select count(*) from orders where payment_info ? 'bank_account' -- 1.78s
6. Using index CREATE INDEX idx_orders_payment_info ON orders USING gin (payment_info->address) select count(*) from orders where payment_info @> '{"address": { "country": "Brazil" } }' -- 54ms (before index creation 1.65s) select count(*) from orders where payment_info->'address'->>'country' = 'Brazil' -- 1.72s
7. Using index select * from orders where payment_info->'customer'->>'id' = '38073' -- 1.51s CREATE INDEX idx_orders_payment_info_customer_id ON orders ((payment_info -> 'customer' ->> 'id')) select * from orders where payment_info->'customer'->>'id' = '38073' -- 3ms
8. Advanced queries select items->>'name' as name, count(*) as total from ( select jsonb_array_elements(items)::jsonb as items from orders where items @> '[{ "department": "Games" }]' ) as items where items @> '{"department": "Games"}' group by items->>'name' order by total desc limit 10 -- 9.2s
9. Our case
10. Our case • Why JSONB ◦ Schema flexibility ◦ Save external data ◦ Save metadata (ex: flags)
11. References • https://www.hagander.net/talks/schemaless_relational.pdf • http://pgeoghegan.blogspot.com/2014/03/what-i-think-of-jsonb.html • https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres