Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Citus Data

May 19, 2020
Tweet

More Decks by Citus Data

Other Decks in Technology

Transcript

  1. Colton Shepard
    SF Bay Area Postgres Meetup: Virtual
    May 2020

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  5. Customer Story!

    View full-size slide

  6. Less Good Use Cases: Statistics Gathering Issues
    https://www.postgresql.org/message-id/flat/20200113230008.g67iyk4cs3xbnjju%40development

    View full-size slide

  7. Other Less Good Use Cases

    View full-size slide

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

    View full-size slide

  9. Customer Story: Anonymous, Inc.

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  12. The ? operator

    View full-size slide

  13. The @ operator

    View full-size slide

  14. Operator-friendly indexes

    View full-size slide

  15. B-Tree Function Index

    View full-size slide

  16. Hash function indexes

    View full-size slide

  17. GiST, SP-GiST, and BRIN

    View full-size slide

  18. GIN with jsonb_path_ops

    View full-size slide

  19. GIN with jsonb_ops (default)

    View full-size slide

  20. Special mention: GIN function index with gin_trgm_ops

    View full-size slide

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

    View full-size slide

  22. Postgres 12: The jsonb_path_query operator

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  25. Postgres 12: jsonb_path_query with arrays

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  30. 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/

    View full-size slide

  31. @Azure Database for MySQL, PostgreSQL & MariaDB
    Twitter:

    View full-size slide