The NoSQL Store everyone ignores: PostgreSQL

The NoSQL Store everyone
ignores: PostgreSQL

Stephan Hochdörfer // September 22 2017

About me

Stephan Hochdörfer
Head of Technology, bitExpert AG
(Mannheim, Germany)
S.Hochdoerfer@bitExpert.de
@shochdoerfer
#PHP, #DevOps, #Automation
#phpugffm, #phpugmrn, #unKonf

End of relational databases?

Freedom of NoSQL?

NoSQL Database Types

Graph Database
Object Database
Column Store
Key-Value Store
Document Store

PostgreSQL as the "glue"

Peaceful coexistence...

Store documents in a
document-oriented fashion
Store relational data
in a relational schema

...but choose wisely

@dburkes amazingly enough, document databases aren't great at storing relational data. :p
— Sarah Mei (@sarahmei) 18. Januar 2011

Postgres Datatypes

"Schemaless" datatypes:

Array
hstore
JSON / JSONb
XML

Array

« [...] PostgreSQL allows columns of a table to be
defined as variable-length multidimensional arrays. »
- PostgreSQL documentation

Create array column

CREATE TABLE blogposts (
    title    text,
    content  text,
    tags     text[]
);

Multidimensional array FTW!

CREATE TABLE tictactoe (
  squares   integer[3][3]
);

Insert array

INSERT INTO blogposts VALUES (
    'A blogpost',
    'Lorem ipsum...',
    ARRAY['lorem', 'ipsum', 'blogpost']
);

INSERT INTO blogposts VALUES (
    'Another blogpost',
    'Lorem ipsum...',
    ARRAY['lorem', 'ipsum']
);

Accessing array elements

SELECT tags[3] FROM blogposts;

   tags
----------
 blogpost

(2 rows)

Filtering arrays

SELECT * from blogposts WHERE tags[2] = 'ipsum';

         title         |    content     |          tags
-----------------------+----------------+------------------------
 A blogpost            | Lorem ipsum... | {lorem,ipsum,blogpost}
 Another blogpost      | Lorem ipsum... | {lorem,ipsum}
(2 rows)

Filtering arrays

SELECT * FROM blogposts WHERE 'blogpost' = ANY(tags);

     title     |    content     |          tags
---------------+----------------+------------------------
 A blogpost    | Lorem ipsum... | {lorem,ipsum,blogpost}
(1 row)

Filtering arrays

SELECT * FROM blogposts WHERE tags && ARRAY['lorem', 'blogpost'];

         title         |    content     |          tags
-----------------------+----------------+------------------------
 A blogpost            | Lorem ipsum... | {lorem,ipsum,blogpost}
 Another blogpost      | Lorem ipsum... | {lorem,ipsum}
(2 rows)

Filtering arrays

SELECT * FROM blogposts WHERE tags @> ARRAY['lorem', 'blogpost']

     title     |    content     |          tags
---------------+----------------+------------------------
 A blogpost    | Lorem ipsum... | {lorem,ipsum,blogpost}
(1 row)

Updating arrays

UPDATE blogposts  SET tags = ARRAY['tag1', 'tag2', 'blogpost']
  WHERE title = 'A blogpost';

SELECT * FROM blogposts;

         title         |    content     |         tags
-----------------------+----------------+----------------------
 Another blogpost      | Lorem ipsum... | {lorem,ipsum}
 A blogpost            | Lorem ipsum... | {tag1,tag2,blogpost}
(2 rows)

Updating single array item

UPDATE blogposts SET tags[3] = 'tag3' WHERE title = 'A blogpost';

SELECT * FROM blogposts;

         title         |    content     |       tags
-----------------------+----------------+------------------
 Another blogpost      | Lorem ipsum... | {lorem,ipsum}
 A blogpost            | Lorem ipsum... | {tag1,tag2,tag3}
(2 rows)

Updating single array item

UPDATE blogposts SET tags[5] = 'tag6' WHERE title = 'A blogpost';

SELECT * FROM blogposts;

         title         |    content     |            tags
-----------------------+----------------+----------------------------
 Another blogpost      | Lorem ipsum... | {lorem,ipsum}
 A blogpost            | Lorem ipsum... | {tag1,tag2,tag3,NULL,tag6}
(2 rows)

Array functions

array_dims()
array_upper()
array_lower()
array_length()
array_prepend()
array_append()
...

hstore

« [...] data type for storing sets of key/value pairs within a single PostgreSQL value. [...] Keys and values are simply text strings. » - PostgreSQL documentation

Install hstore extension

CREATE EXTENSION hstore;

Create hstore column

CREATE TABLE products (
    name         text,
    price        float,
    description  text,
    metadata     hstore
);

Insert hstore dataset

INSERT INTO products VALUES (
    'Product A',
    123.3,
    'Lorem ipsum',
    'color => black, weight => 3kg'::hstore
);


INSERT INTO products VALUES (
    'Product B',
    45.0,
    'Lorem ipsum',
    'color => red'::hstore
);

Filtering hstore dataset

SELECT metadata->'color' FROM products;

 ?column?
----------
 black
 red
(2 rows)

Filtering hstore dataset

SELECT metadata->'weight' FROM products;

 ?column?
----------
 3kg

(2 rows)

Search for a key in hstore

SELECT * FROM products WHERE metadata ? 'weight';

   name    | price | description |             metadata
-----------+-------+-------------+-----------------------------------
 Product A | 123.3 | Lorem ipsum | "color"=>"black", "weight"=>"3kg"
(1 row)

Search for key/value in hstore

SELECT * FROM products WHERE metadata @> 'color => red';

   name    | price | description |    metadata
-----------+-------+-------------+----------------
 Product B |    45 | Lorem ipsum | "color"=>"red"
(1 row)

Updating hstore dataset

UPDATE products SET metadata = 'color => black, weight => 5kg'::hstore 
WHERE name = 'Product A';

Partial hstore dataset update

UPDATE products SET metadata = metadata || 'weight => 1kg'::hstore 
WHERE name = 'Product B';

Removing item from hstore

UPDATE products SET metadata = metadata - 'weight'::text;

hstore functions

akeys()
avals()
hstore_to_array()
hstore_to_json()
...

hstore Example: Auditing

CREATE TABLE audit
(
    change_date timestamptz default now(),
    before                          hstore,
    after                           hstore
);

create function audit()
  returns trigger
  language plpgsql
as $$
begin
  INSERT INTO audit(before, after)
       SELECT hstore(old), hstore(new);
  return new;
end;
$$;

hstore Example: Auditing

create trigger audit
      after update on example
          for each row
execute procedure audit();

select change_date, after - before as diff from audit;

          change_date          |   diff    
-------------------------------+-----------
 2013-11-08 09:29:19.808217+02 | "f1"=>"b"
 2013-11-08 09:29:19.808217+02 | "f2"=>"c"

(2 rows)

Source: Auditing Changes with Hstore

JSON

« [...] the json data type has the advantage of checking
that each stored value is a valid JSON value. »
- PostgreSQL documentation

Create JSON column

CREATE TABLE products (
    name         text,
    price        float,
    description  text,
    metadata     json
);

Insert JSON document

INSERT INTO products VALUES (
    'Product A',
    123.3,
    'Lorem ipsum',
    ' { "color": "black", "weight": "3kg" } '
);


INSERT INTO products VALUES (
    'Product B',
    45.0,
    'Lorem ipsum',
    ' { "color": "red" } '
);

Insert JSON document

INSERT INTO products VALUES (
    'Product C',
    9.95,
    'Lorem ipsum',
    ' { "color": ["red", "green", "blue"] } '
);

Insert JSON document

INSERT INTO products VALUES (
    'Product D',
    9.95,
    'Lorem ipsum',
    ' { "color": ["red", "green", "blue", ] } '
);

ERROR:  invalid input syntax for type json
LINE 5:     ' { "color": ["red", "green", "blue", ] } '
            ^
DETAIL:  Expected JSON value, but found "]".
CONTEXT:  JSON data, line 1:  { "color": ["red", "green", "blue", ]...

Filtering JSON document

SELECT metadata->>'color' FROM products;

         ?column?
--------------------------
 black
 red
 ["red", "green", "blue"]
(3 rows)

Filtering JSON document

SELECT metadata#>'{color, 2}' FROM products;

 ?column?
----------


 "blue"
(3 rows)

Updating a JSON document

UPDATE products 
SET metadata = ' { "color": ["red", "green", "blue", "yellow"] } ' 
WHERE name = 'Product C';

Updating a JSON document

UPDATE products
SET metadata = json_build_object('color', 'red')
WHERE name = 'Product C';

Updating a JSON document

UPDATE products
SET metadata = json_set("metadata", '{"color"}', '"red"'::JSONB)
WHERE name = 'Product C';

JSON functions

array_to_json()
row_to_json()
json_array_length()
json_object_keys()
...

Micro-JSON Webservice

SELECT row_to_json(products) FROM products WHERE name = 'Product C';

                             row_to_json
---------------------------------------------------------------------
 {"name":"Product C","price":9.95,"description":"Lorem ipsum",
  "metadata": { "color": ["red", "green", "blue", "yellow"] } }
(1 row)

Micro-JSON Webservice

SELECT row_to_json(t)
FROM (
  SELECT name, price FROM products WHERE name = 'Product C'
) t;

            row_to_json
-----------------------------------
 {"name":"Product C","price":9.95}
(1 row)

Micro-JSON Webservice

SELECT array_to_json(array_agg(row_to_json(t)))
FROM (
  SELECT name, price FROM products;
) t;

            row_to_json
-----------------------------------
 [{"name":"Product A","price":123.3},
 {"name":"Product B","price":45.0},
 {"name":"Product C","price":9.95}]
(1 row)

JSONb

« There are two JSON data types: json and jsonb [...]
The major practical difference is one of efficiency. »
- PostgreSQL documentation

Create JSONb column

CREATE TABLE products (
    name         text,
    price        float,
    description  text,
    metadata     jsonb
);

Insert JSON document

INSERT INTO products VALUES (
    'Product A',
    123.3,
    'Lorem ipsum',
    ' { "color": "black", "weight": "3kg" } '
);


INSERT INTO products VALUES (
    'Product B',
    45.0,
    'Lorem ipsum',
    ' { "color": "red" } '
);

Insert JSON document

INSERT INTO products VALUES (
    'Product D',
    9.95,
    'Lorem ipsum',
    ' { "color": ["red", "green", "blue", ] } '
);

ERROR:  invalid input syntax for type json
LINE 5:     ' { "color": ["red", "green", "blue", ] } '
            ^
DETAIL:  Expected JSON value, but found "]".
CONTEXT:  JSON data, line 1:  { "color": ["red", "green", "blue", ]...

Search for key/value in JSON

SELECT * FROM products WHERE metadata @> '{"color": "black"}';

    name    |  price  |   description   |          metadata
------------+---------+-----------------+----------------------------
 Product A  |  123.3  |   Lorem ipsum   | {"color": "black",
                                              "weight": "3kg"}

(1 row)

JSON key lookup

SELECT * FROM products WHERE metadata ? 'weight';

    name    |  price  |   description   |          metadata
------------+---------+-----------------+----------------------------
 Product A  |  123.3  |   Lorem ipsum   | {"color": "black",
                                              "weight": "3kg"}

(1 row)

JSON key lookup

SELECT * FROM products WHERE metadata ?| array['weight', 'color'];

    name    |  price  |   description   |          metadata
------------+---------+-----------------+----------------------------
 Product A  |  123.3  |   Lorem ipsum   | {"color": "black",
                                              "weight": "3kg"}
 Product B  |  45     |   Lorem ipsum   | {"color": "red"}

JSON keys lookup

SELECT * FROM products WHERE metadata ?& array['weight', 'color'];

    name    |  price  |   description   |          metadata
------------+---------+-----------------+----------------------------
 Product A  |  123.3  |   Lorem ipsum   | {"color": "black",
                                              "weight": "3kg"}

(1 row)

JSON keys lookup

CREATE INDEX metadata_fts ON products
   USING gin (( to_tsvector('english', metadata) ));

SELECT metadata->'color' FROM products
WHERE to_tsvector('english', metadata) @@ to_tsquery('red | black');

JSON vs. JSONb

Use JSONb for complex
operations
JSONb needs more
disk space than JSON
Use JSON just for storage
Use JSON when you need
to keep the structure

XML

« [...] it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it. » - PostgreSQL documentation

Create XML column

CREATE TABLE imports (
    importDate date,
    content    XML
);

Insert XML document

INSERT INTO imports VALUES (
    '2014-04-05',
    '<?xml version="1.0"?>
      <order customer="4711">
    <product id="1234">10</product>
    <product id="9876">50</product>
      </order>'::xml
);

INSERT INTO imports VALUES (
    '2014-04-05',
    '<?xml version="1.0"?>
      <order customer="5432">
    <product id="1234">3</product>
    </order>'::xml
);

Insert XML document

INSERT INTO imports VALUES (
    '2014-04-05',
    '<?xml version="1.0"?>
      <open></close>'::xml
);

ERROR:  invalid XML content
LINE 3:     '<?xml version="1.0"?>
            ^
DETAIL:  line 2: Opening and ending tag mismatch: open line 2 and close
      <open></close>
                    ^
line 2: chunk is not well balanced
      <open></close>
                    ^

Filtering XML document

SELECT xpath('/order/product/@id', content) FROM imports;

    xpath
-------------
 {1234,9876}
 {1234}
(2 rows)

Filtering XML document

SELECT UNNEST(xpath('/order/product/@id', content)::text[])
as productid, COUNT(*) as orders FROM imports GROUP BY productid;

 productid | orders
-----------+--------
 9876      |      1
 1234      |      2
(2 rows)

Search in XML document

SELECT * FROM imports WHERE xpath_exists('/order/product[@id=9876]', 
  content);

 importdate |             content
------------+---------------------------------
 2014-04-05 |       <order customer="4711">  +
            | <product id="1234">10</product>+
            | <product id="9876">50</product>+
            |       </order>
(1 row)

XML functions

xmlconcat()
xmlforest()
xmlagg()
xml_is_well_formed()
query_to_xml()
xpath()
...

To infinity and beyond!

Table partioning
Foreign Data Wrappers
Range datatype
Materialized views
Conditional Indexes
...

Thank you!

Do not forget to rate the talk:
https://joind.in/talk/acdb0

Thank you!

Stephan Hochdörfer
Head of Technology, bitExpert AG
(Mannheim, Germany)
S.Hochdoerfer@bitExpert.de
@shochdoerfer
#PHP, #DevOps, #Automation
#phpugffm, #phpugmrn, #unKonf