JSONB Tricks: Operators, Indexes, and When to (Not) Use It | SF Bay Area PostgreSQL Meetup | Colton Shepard

When do you use jsonb, and when don’t you? How do you make it fast? What operators are available, and what can they do? How will this change? These are all very good questions, but jsonb support in Postgres moves so fast that it’s hard to keep up.

In this talk, you will get details on these topics, complete with practical examples and real-world stories:

*When to use jsonb, what it’s good for, and when to not use it
*Operators and how to use them effectively
*Indexing, operator support for indexes, and the tradeoffs involved
*Postgres 12 improvements and new features

Transcript

1. Colton Shepard SF Bay Area Postgres Meetup: Virtual May 2020
2. Who am I? Colton Shepard Former Citus Data employee, now at Microsoft thanks to the acquisition Postgres and Citus implementation and migration specialist Work with on-premise, Azure, edge, and other cloud environments
3. What is all this, anyways? JavaScript Object Notation Data Interchange Format RFC 7158 Human readable lightweight data format One of 2 Postgres JSON data types. Decomposed for storage, not stored as string • Fast and indexable • High disk usage
4. Good use cases Entity–attribute–value model tables
5. Customer Story!
6. Less Good Use Cases: Statistics Gathering Issues https://www.postgresql.org/message-id/flat/20200113230008.g67iyk4cs3xbnjju%40development
7. Other Less Good Use Cases
8. Customer Story: Heap Analytics https://heap.io/blog/engineering/when-to-avoid-jsonb-in-a-postgresql-schema
9. Customer Story: Anonymous, Inc.
10. Operators
11. -> and ->>
12. The ? operator Does this string exist as a top-level key within the JSON value?
13. The ? operator What about this array of strings?
14. The ? operator
15. The @ operator
16. #> and #>>
17. Operator-friendly indexes
18. B-Tree
19. B-Tree Function Index
20. Hash
21. Hash function indexes
22. GiST, SP-GiST, and BRIN
23. GIN This
24. GIN with jsonb_path_ops
25. GIN with jsonb_ops (default)
26. Special mention: GIN function index with gin_trgm_ops
27. Postgres 12 New datatype: jsonpath Provides a binary representation of the parsed SQL/JSON path expression 1. .key returns object member with specified key 2. .* returns all object members at current level 3. .** returns all object members at current level and below 4. .**{level} or .**{start_level to end_level} returns all at specified level(s) 5. [subscript, ...] returns the value at specified array location https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-OP-TABLE
28. Postgres 12: The jsonb_path_query operator
29. Postgres 12: jsonb_path_query with .* and .**
30. Postgres 12: jsonb_path_query with .**{level}
31. Postgres 12: jsonb_path_query with arrays
32. Postgres 12: the jsonb_path_exists operator Gives bool indicating whether the path exists.
33. Postgres 12: the jsonb_path_match operator This allows for any logic that’ll output Boolean results
34. Postgres 12: the jsonb_path_query_array operator Much like jsonb_path_query, but you get an array instead:
35. Postgres 12: the jsonb_path_query_first operator Get first matching result:
36. Conclusion
37. General resources to learn more https://www.youtube.com/watch?v=AeMaBwd90SI https://heap.io/blog/engineering/when-to-avoid-jsonb-in-a- postgresql-schema https://bitnine.net/blog-postgresql/postgresql-internals-jsonb- type-and-its-indexes/ https://www.postgresql.org/docs/12/functions- json.html#FUNCTIONS-SQLJSON-PATH https://paquier.xyz/postgresql-2/postgres-12-jsonpath/ https://www.citusdata.com/blog/2016/07/25/sharding-json-in- postgres-and-performance/
38. @Azure Database for MySQL, PostgreSQL & MariaDB Twitter: