Colton Shepard PostgresOpen 2019 What is all this, anyways? - - PowerPoint PPT Presentation

▶
colton shepard postgresopen 2019 what is all this anyways
SMART_READER_LITE
LIVE PREVIEW

Colton Shepard PostgresOpen 2019 What is all this, anyways? - - PowerPoint PPT Presentation

Nov 17, 2022 490 likes •873 views

Colton Shepard PostgresOpen 2019 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

slide-1
SLIDE 1

Colton Shepard PostgresOpen 2019

slide-2
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
slide-3
SLIDE 3

Good use cases

Entity–attribute–value model tables

slide-4
SLIDE 4

Customer Story!

slide-5
SLIDE 5

Less Good Use Cases: Statistics Gathering Issues

slide-6
SLIDE 6

Other Less Good Use Cases

slide-7
SLIDE 7

Customer Story: Heap Analytics

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

slide-8
SLIDE 8

Customer Story: Anonymous, Inc.

slide-9
SLIDE 9

Operators

slide-10
SLIDE 10
  • > and ->>
slide-11
SLIDE 11

The ? operator

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

slide-12
SLIDE 12

The ? operator

What about this array of strings?

slide-13
SLIDE 13

The ? operator

slide-14
SLIDE 14

The @ operator

slide-15
SLIDE 15

#> and #>>

slide-16
SLIDE 16

Operator-friendly indexes

slide-17
SLIDE 17

B-Tree

slide-18
SLIDE 18

B-Tree Function Index

slide-19
SLIDE 19

Hash

slide-20
SLIDE 20

Hash function indexes

slide-21
SLIDE 21

GiST, SP-GiST, and BRIN

slide-22
SLIDE 22

GIN

This

slide-23
SLIDE 23

GIN with jsonb_path_ops

slide-24
SLIDE 24

GIN with jsonb_ops (default)

slide-25
SLIDE 25

Special mention: GIN function index with gin_trgm_ops

slide-26
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

slide-27
SLIDE 27

Postgres 12: The jsonb_path_query operator

slide-28
SLIDE 28

Postgres 12: jsonb_path_query with .* and .**

slide-29
SLIDE 29

Postgres 12: jsonb_path_query with .**{level}

slide-30
SLIDE 30

Postgres 12: jsonb_path_query with arrays

slide-31
SLIDE 31

Postgres 12: the jsonb_path_exists operator

Gives bool indicating whether the path exists.

slide-32
SLIDE 32

Postgres 12: the jsonb_path_match operator

This allows for any logic that’ll output Boolean results

slide-33
SLIDE 33

Postgres 12: the jsonb_path_query_array operator

Much like jsonb_path_query, but you get an array instead:

slide-34
SLIDE 34

Postgres 12: the jsonb_path_query_first operator

Get first matching result:

slide-35
SLIDE 35

Conclusion

slide-36
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/

slide-37
SLIDE 37

@Azure Database for MySQL, PostgreSQL & MariaDB