JSONB Tricks: Operators, Indexes, and When to (Not) Use It | PostgresOpen 2019 | 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
   PostgresOpen 2019
   

   View Slide

2. 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
   

   View Slide

3. Good use cases
   Entity–attribute–value model tables
   

   View Slide

4. Customer Story!
   

   View Slide

5. Less Good Use Cases: Statistics Gathering Issues
   

   View Slide

6. Other Less Good Use Cases
   

   View Slide

7. Customer Story: Heap Analytics
   https://heap.io/blog/engineering/when-to-avoid-jsonb-in-a-postgresql-schema
   

   View Slide

8. Customer Story: Anonymous, Inc.
   

   View Slide

9. Operators
   

   View Slide

10. -> and ->>
    

    View Slide

11. The ? operator
    Does this string exist as a top-level key within the JSON value?
    

    View Slide

12. The ? operator
    What about this array of strings?
    

    View Slide

13. The ? operator
    

    View Slide

14. The @ operator
    

    View Slide

15. #> and #>>
    

    View Slide

16. Operator-friendly indexes
    

    View Slide

17. B-Tree
    

    View Slide

18. B-Tree Function Index
    

    View Slide

19. Hash
    

    View Slide

20. Hash function indexes
    

    View Slide

21. GiST, SP-GiST, and BRIN
    

    View Slide

22. GIN
    This
    

    View Slide

23. GIN with jsonb_path_ops
    

    View Slide

24. GIN with jsonb_ops (default)
    

    View Slide

25. Special mention: GIN function index with gin_trgm_ops
    

    View Slide

26. 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/devel/datatype-json.html#DATATYPE-JSONPATH
    

    View Slide

27. Postgres 12: The jsonb_path_query operator
    

    View Slide

28. Postgres 12: jsonb_path_query with .* and .**
    

    View Slide

29. Postgres 12: jsonb_path_query with .**{level}
    

    View Slide

30. Postgres 12: jsonb_path_query with arrays
    

    View Slide

31. Postgres 12: the jsonb_path_exists operator
    Gives bool indicating whether the path exists.
    

    View Slide

32. Postgres 12: the jsonb_path_match operator
    This allows for any logic that’ll output Boolean results
    

    View Slide

33. Postgres 12: the jsonb_path_query_array operator
    Much like jsonb_path_query, but you get an array instead:
    

    View Slide

34. Postgres 12: the jsonb_path_query_first operator
    Get first matching result:
    

    View Slide

35. Conclusion
    

    View Slide

36. 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.citusdata.com/blog/2016/07/14/choosing-nosql-
    hstore-json-jsonb/
    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/
    

    View Slide

37. @Azure Database for MySQL, PostgreSQL & MariaDB
    

    View Slide