Introducing 

Prezi AI.

Your new presentation assistant.

Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.

Loading…
Transcript

Flexibility vs Rigidity

CREATE EXTENSION hstore;

SELECT 'a=>1,a=>2'::hstore;

hstore

----------

"a"=>"1"

(1 row)

SELECT 'author => "Monty Python", sketch => Spam'::hstore -> 'author' AS author;

author

--------------

Monty Python

(1 row)

Performance?

Hstore performance

JSON performance

JSONB performance

For everything else?

JSONB type performance in PostgreSQL 9.4

Marco Nenciarini

PostgreSQL consultant

2ndQuadrant Italia

  • CTO at 2ndQuadrant Italia
  • PostgreSQL database design and administration
  • Co-founder Italian PostgreSQL Users Group (www.itpug.org)

Postgres

Extensibility

Ability to renew itself, always

5432 ... Meet US!

Milan, 12 May 2015

Not Only SQL

Performance vs Durability

Contact

NOSQL vs SQL?

  • UK company founded by Simon Riggs (code committer)
  • Offices in Europe, America, Oceania and Asia
  • PostgreSQL platinum sponsor
  • Open source business model
  • PostgreSQL HA, Disaster Recovery, Scalability and Performance Leader
  • 24/7 Support ("follow the sun")

marco.nenciarini@2ndquadrant.it

@mnencia

mnencia

www.2ndQuadrant.it

XML

Copyright (c) 2012-2015, 2ndQuadrant

This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License

  • PostgreSQL 8.2 (2006)
  • Data type
  • Validation
  • Text
  • SQL:2003

Creation functions

JSONB

select xmlelement(name MeetUs,

xmlattributes(

'Milan' as location,

'12 May 2015' as date

),

'Interesting people');

xmlelement

---------------------------------------------------------------------------

<meetus location="Milan" date="13 May 2015">

Interesting people

</meetus>

  • Funzional indexes!
  • No GIN and GiST indexes

Summary

Output functions

SELECT query_to_xml($$SELECT id, name FROM sketches$$,

true, false, '');

query_to_xml

---------------------------------------------------------------

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>

<id>1</id>

<name>Dead Parrot</name>

</row>

<row>

<id>2</id>

<name>Spam</name>

</row>

</table>

Details: http://www.postgresql.org/docs/9.4/static/functions-xml.html

XPath support

  • XML data type
  • hstore data type
  • JSON data type
  • JSONB data type
  • Compared performance

SELECT xpath('/table/row[id/text()=1]/name/text()', $$

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>

<id>1</id>

<name>Dead Parrot</name>

</row>

<row>

<id>2</id>

<name>Spam</name>

</row>

</table>

$$);

xpath

-----------------

{"Dead Parrot"}

JSON

  • Introduced in PostgreSQL 9.4
  • Same advantages of JSON
  • Specialized binary format
  • Some differences
  • No duplicated keys
  • No keys ordering
  • Supports GIN and GiST indexes
  • Same functions of JSON
  • replace "json_" with "jsonb_"
  • Introduced in PostgreSQL 9.2
  • Text storage
  • Validation
  • Enhanced in PostgreSQL 9.3
  • Functions and operators
  • Hstore integration
  • Enhanced further in Postgresql 9.4
  • More creation functions
  • No GIN and GiST indexes

hstore

JSON values creation

JSONB: additional operators

  • PostgreSQL 8.2 (2006)
  • Binary storage
  • Key/value store
  • Flat
  • Keys and values are string only
  • GIN and GiST indexes support

jsonb @> jsonb

Containment

recursively convert a value to JSON, using (type based):

  • casting
  • textual representation

to_json(anyelement)

jsonb ? text

Key existence

'{"a":1,"b":2}'::jsonb @> '{"a":1}'

jsonb = jsonb

Equality

aggregates values as a JSON array

json_agg(record)

'{"a":1,"b":2}'::jsonb ? 'a'

'{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'

Details: http://www.postgresql.org/docs/9.4/static/function-json.html

Some other interesting functions

Details: http://www.postgresql.org/docs/9.4/static/hstore.html

  • json_each(json)
  • json_each_text(json)
  • json_object_keys(json)
  • json_array_length(json)
  • json_array_elements(json)
  • json_array_elements_text(json)

Some operators

Details: http://www.postgresql.org/docs/9.4/static/functions-json.html

'[1,2,3]'::json->2

hstore -> text

get value for key

'{"a":1,"b":2}'::json->'b'

'{"a":1,"b":2}'::jsonb @> '{"a":1}'

JSONB: jsonb_path_ops

'{"a": {"b":2}}'::json->'{a,b}'

hstore ? text

does hstore contain key?

jsonb @> jsonb

Containment

'a=>1,b=>2'::hstore -> 'a'

hstore @> hstore

does left operand contain right?

CREATE INDEX ON mydata USING GIN (jcol jsonb_path_ops);

Some operators

Supports GIN and GIST indexes

'a=>1,b=>2'::hstore ? 'b'

JSON -> int

Get JSON array element (indexed from zero)

  • Generic
  • Smaller
  • More efficient

Details: http://www.postgresql.org/docs/9.4/static/function-json.html

JSON -> text

Get JSON object field by key

The >> variant returns the result as text

JSON #> text[]

Get JSON object at specified path

Choosing the right data type

'a=>1,b=>2'::hstore @> 'a=>1'

Details: http://www.postgresql.org/docs/9.4/static/functions-json.html

Details: http://www.postgresql.org/docs/9.4/static/hstore.html

XML

  • Legacy data
  • Legacy application compatibility

With two '>'

Test data

hstore

  • flat structure, string only
  • Fast operations

CREATE TABLE reviews (review hstore);

COPY reviews FROM 'customer_reviews_1998.hstore';

COPY 589859

Time: 7348.897 ms

Customer review data from Amazon for the year 1998

  • 589859 record
  • 209MB on disk
  • converted from nested-JSON to hstore
  • 245MB on disk

JSON

  • If key order is important
  • You need duplicate keys
  • Fast data download

SELECT round(avg((review->'review_rating')::numeric), 2)

FROM reviews WHERE review -> 'product_category' = 'Home & Garden';

round

-------

4.59

(1 row)

Time: 148.975 ms

http://examples.citusdata.com/customer_reviews_nested_1998.json.gz

CREATE INDEX ON reviews USING gin(review);

CREATE INDEX

Time: 64761.753 ms

SELECT round(avg((review->'review_rating')::numeric), 2)

FROM reviews

WHERE review @> 'product_category => "Home & Garden"';

round

-------

4.59

(1 row)

Time: 7.642 ms

CREATE TABLE reviews(review json);

COPY reviews FROM 'customer_reviews_nested_1998.json';

COPY 589859

Time: 5360.982 ms

CREATE TABLE reviews(review jsonb);

COPY reviews FROM customer_reviews_nested_1998.json';

COPY 589859

Time: 8905.210 ms

SELECT round(avg((review#>>'{review,rating}')::numeric), 2)

FROM reviews

WHERE review#>>'{product,category}' = 'Home & Garden';

round

-------

4.59

(1 row)

Time: 2342.235 ms

SELECT round(avg((review#>>'{review,rating}')::numeric), 2)

FROM reviews

WHERE review#>>'{product,category}' = 'Home & Garden';

round

-------

4.59

(1 row)

Time: 235.420 ms

CREATE INDEX on reviews ((review#>>'{product,category}'));

CREATE INDEX

Time: 14697.687 ms

SELECT round(avg((review#>>'{review,rating}')::numeric), 2) FROM reviews WHERE review#>>'{product,category}' = 'Home & Garden';

round

-------

4.59

(1 row)

Time: 19.650 ms

CREATE INDEX on reviews USING gin(review);

CREATE INDEX

Time: 17825.265 ms

SELECT round(avg((review#>>'{review,rating}')::numeric), 2)

FROM reviews

WHERE review@>'{"product": {"category": "Home & Garden"}}';

round

-------

4.59

(1 row)

Time: 13.982 ms

CREATE INDEX on reviews USING gin(review jsonb_path_ops);

CREATE INDEX

Time: 6709.042 ms

SELECT round(avg((review#>>'{review,rating}')::numeric), 2)

FROM reviews

WHERE review@>'{"product": {"category": "Home & Garden"}}';

round

-------

4.59

(1 row)

Time: 8.423 ms

fast, but only when you search by product category

fast, for any search!

functional index, specific for this search

no indexes, parsing of every row, very slow

no indexes, 50% slower than hstore

20% slower than hstore

fast!

10 times faster than hstore, quite generic index, 57 MB

1 minute, generic index

without indexes

super fast, for any search! (using the @> operator)

27% faster than hstore

3 times faster than hstore, general purpose index, 75 MB

Learn more about creating dynamic, engaging presentations with Prezi