PostgreSQL: Die NoSQL Datenbank

Stephan Hochdörfer // 10.02.2014

Über mich

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

  • #PHP, #DevOps, #Automation

Ende des relationalen Wegs?

"Freiheit" durch NoSQL?

Friedliche Co-Existenz...



  • Speichere relationale
    Daten relational
  • Speichere Dokumente
    dokumentenorientiert

...aber richtig entscheiden!



PostgreSQL als Bindeglied

Postgres Datentypen


"Schemafreie" Datentypen:

  • Array
  • hstore
  • JSON
  • XML

Array




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

Array Spalte anlegen


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

Daten als Array speichern


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

INSERT INTO blogposts VALUES (
    'Mein zweiter Blogpost',
    'Lorem ipsum...',
    ARRAY['lorem', 'ipsum']
);

Array Elemente abfragen


SELECT tags[3] FROM blogposts;
   tags
----------
 blogpost

(2 rows)

Arrays durchsuchen


SELECT * FROM blogposts WHERE 'blogpost' = ANY(tags);
     title     |    content     |          tags
---------------+----------------+------------------------
 Mein Blogpost | Lorem ipsum... | {lorem,ipsum,blogpost}
(1 row)

hstore




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

hstore Extension installieren


CREATE EXTENSION hstore;

hstore Spalte anlegen


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

hstore Daten speichern


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
);

hstore Keys abfragen


SELECT metadata->'color' FROM products;
 ?column?
----------
 black
 red
(2 rows)

hstore Keys suchen


SELECT name FROM products WHERE metadata ? 'weight';
   name
-----------
 Product A
(1 row)

hstore Key/Value suchen


SELECT name FROM products WHERE metadata @> 'color => black';
   name
-----------
 Product A
(1 row)

JSON




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

JSON Spalte anlegen


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

JSON Daten speichern


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" } '
);

JSON Daten speichern


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

JSON Daten abfragen


SELECT metadata->>'color' FROM products;
         ?column?
--------------------------
 black
 red
 ["red", "green", "blue"]
(3 rows)

JSON Inhalte indexieren


CREATE UNIQUE INDEX product_color ON products((metadata->>'color'));
INSERT INTO products VALUES (
    'Product D',
    21.95,
    'Lorem ipsum',
    ' { "color": "red" } '
);
ERROR:  duplicate key value violates unique constraint "product_color"
DETAIL:  Key ((metadata ->> 'color'::text))=(red) already exists.

XML




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

XML Spalte anlegen


CREATE TABLE imports (
    importDate date,
    content    XML
);

XML Daten speichern


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

XML Daten abfragen


SELECT xpath('/order/product/@id', content) FROM imports;
    xpath
-------------
 {1234,9876}
(1 row)

...und darüber hinaus



  • Foreign Data Wrappers
  • Conditional Indexes
  • Range Datentyp
  • Materialized views
  • ...







Vielen Dank für die Aufmerksamkeit!