The NoSQL Store everyone
ignores: PostgreSQL

Stephan Hochdörfer // 2017-12-05

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



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 fulltext search


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! Questions?


  • Stephan Hochdörfer
  • Head of Technology, bitExpert AG
    (Mannheim, Germany)
  • S.Hochdoerfer@bitExpert.de
  • @shochdoerfer

  • #PHP, #DevOps, #Automation
  • #phpugffm, #phpugmrn, #unKonf