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

Slide 1

Slide 1 text

Colton Shepard SF Bay Area Postgres Meetup: Virtual May 2020

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

Good use cases Entity–attribute–value model tables

Slide 5

Slide 5 text

Customer Story!

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

Other Less Good Use Cases

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Customer Story: Anonymous, Inc.

Slide 10

Slide 10 text

Operators

Slide 11

Slide 11 text

-> and ->>

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

The ? operator What about this array of strings?

Slide 14

Slide 14 text

The ? operator

Slide 15

Slide 15 text

The @ operator

Slide 16

Slide 16 text

#> and #>>

Slide 17

Slide 17 text

Operator-friendly indexes

Slide 18

Slide 18 text

B-Tree

Slide 19

Slide 19 text

B-Tree Function Index

Slide 20

Slide 20 text

Hash

Slide 21

Slide 21 text

Hash function indexes

Slide 22

Slide 22 text

GiST, SP-GiST, and BRIN

Slide 23

Slide 23 text

GIN This

Slide 24

Slide 24 text

GIN with jsonb_path_ops

Slide 25

Slide 25 text

GIN with jsonb_ops (default)

Slide 26

Slide 26 text

Special mention: GIN function index with gin_trgm_ops

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Postgres 12: The jsonb_path_query operator

Slide 29

Slide 29 text

Postgres 12: jsonb_path_query with .* and .**

Slide 30

Slide 30 text

Postgres 12: jsonb_path_query with .**{level}

Slide 31

Slide 31 text

Postgres 12: jsonb_path_query with arrays

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Postgres 12: the jsonb_path_query_first operator Get first matching result:

Slide 36

Slide 36 text

Conclusion

Slide 37

Slide 37 text

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/

Slide 38

Slide 38 text

@Azure Database for MySQL, PostgreSQL & MariaDB Twitter: