PostgreSQL

is NOT your traditional

SQL database

Gülçin Yıldırım

May 2018, PHPKonf Istanbul

select * from me;

Lead of Cloud Development @ 2ndQuadrant

Board Member @ PostgreSQL Europe

MSc Comp. & Systems Eng. @ Tallinn University of Technology

Organizer @ Prague PostgreSQL Meetup

Writes on 2ndQuadrant blog

@kadinyazilimci

From Turkey

Lives in Prague

@apatheticmagpie

Github: gulcin

Agenda

  • Design choices of PostgreSQL

  • Arrays, Enum, JSON

  • JSONB and GIN 

  • Full Text Search in PostgreSQL

    • tsvector, tsquery

    • Ranking

    • Fuzzy search / Misspelling

    • Accent support

    • Language support

  • Why PostgreSQL?

Design Choices of PostgreSQL

  • Conventional Relational PostgreSQL
    • Tables, Columns, Rows, Query Processing
  • Object Relational PostgreSQL
    • Extensibility 
      • Rich type system
      • Wide variety of index types
  • Power of combining all
    • Following SQL standards
    • ACID properties

Arrays

  • Standard arrays
  • Array operators (@>, <@, &&, =, <> etc)
  • Search in the array
  • Process array elements from SQL directly  
  • Index them with GIN
    • This index access method allows PostgreSQL to index the contents of the arrays, rather than each array as an opaque value.

Arrays 

                                              Table "public.film"
        Column        |           Type           | Collation | Nullable |                Default
----------------------+--------------------------+-----------+----------+---------------------------------------
 film_id              | integer                  |           | not null | nextval('film_film_id_seq'::regclass)
 title                | text                     |           | not null |
 description          | text                     |           |          |
 release_year         | year                     |           |          |
 language_id          | smallint                 |           | not null |
 original_language_id | smallint                 |           |          |
 rental_duration      | smallint                 |           | not null | 3
 rental_rate          | numeric(4,2)             |           | not null | 4.99
 length               | smallint                 |           |          |
 replacement_cost     | numeric(5,2)             |           | not null | 19.99
 rating               | mpaa_rating              |           |          | 'G'::mpaa_rating
 last_update          | timestamp with time zone |           | not null | now()
 special_features     | text[]                   |           |          |
 fulltext             | tsvector                 |           | not null |

Arrays 

fts_demo=> Select film_id, special_features from film 
           where special_features @> array['Deleted Scenes'] limit 15;
 film_id |                  special_features
---------+-----------------------------------------------------
       1 | {"Deleted Scenes","Behind the Scenes"}
       2 | {Trailers,"Deleted Scenes"}
       3 | {Trailers,"Deleted Scenes"}
       5 | {"Deleted Scenes"}
       6 | {"Deleted Scenes"}
       7 | {Trailers,"Deleted Scenes"}
       9 | {Trailers,"Deleted Scenes"}
      10 | {Trailers,"Deleted Scenes"}
      12 | {Commentaries,"Deleted Scenes"}
      13 | {"Deleted Scenes","Behind the Scenes"}
      14 | {Trailers,"Deleted Scenes","Behind the Scenes"}
      19 | {Commentaries,"Deleted Scenes","Behind the Scenes"}
      20 | {Commentaries,"Deleted Scenes","Behind the Scenes"}
      23 | {Trailers,"Deleted Scenes"}
      26 | {Commentaries,"Deleted Scenes"}
(15 rows)

Arrays 

fts_demo=> CREATE INDEX idx_sp_features ON film USING GIN(special_features);
CREATE INDEX

fts_demo=> Explain analyze (Select * from film 
                            where special_features @> array['Deleted Scenes']);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=11.90..73.19 rows=503 width=386) (actual time=0.058..0.187 rows=503 loops=1)
   Recheck Cond: (special_features @> '{"Deleted Scenes"}'::text[])
   Heap Blocks: exact=55
   ->  Bitmap Index Scan on idx_sp_features  (cost=0.00..11.77 rows=503 width=0) (actual time=0.046..0.046 rows=503 loops=1)
         Index Cond: (special_features @> '{"Deleted Scenes"}'::text[])
 Planning time: 0.512 ms
 Execution time: 0.267 ms
(7 rows)

Enum

  • Lookup table
  • Stores integer instead of whole value in table
  • Denormalized, you don't need a separate table
  • Faster reads
  • Intended for static sets of values
  • Takes very little space, four bytes on disk
  • All of this is indexable! \o/

Enum

create type status as enum('backlog', 'in-progress', 'done', 'delivered');

create table issues
 (
   id bigint primary key,
   description   text,
   state status 
 );

insert into issues(id, description, state)
     values (1, 'Implement Job for Switching DNS API Call', 'backlog'),
            (2, 'Report an issue mechanism for customers', 'in-progress'),
            (3, 'Cost reports', 'done'),
            (4, 'Scheduled Jobs Mechanism', 'delivered');

fts_demo=> Select * from issues where state = 'in-progress';
 id |               description               |    state
----+-----------------------------------------+-------------
  2 | Report an issue mechanism for customers | in-progress
(1 row)

Enum

fts_demo=> set enable_seqscan = off;
SET

fts_demo=> create index idx_state on issues(state);
CREATE INDEX

fts_demo=> Explain analyze (Select * from issues where state = 'in-progress');
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_state on issues  (cost=0.13..8.15 rows=1 width=44) (actual time=0.007..0.008 rows=1 loops=1)
   Index Cond: (state = 'in-progress'::status)
 Planning time: 0.054 ms
 Execution time: 0.023 ms
(4 rows)

JSON

  • Validated as correct JSON
  • Stores as text
  • Keeps the same format as it sent
  • Useful if;
    • you want to store bunch of JSON (fast)
    • you don't need to search in JSON itself
  • Fast to write
    • you don't transform but only validate
  • More intensive to search
    • you obviously interpret it every time you access it

JSON

create table js(id serial primary key, extra json);
insert into js(extra)
     values ('[1, 2, 3, 4]'),
            ('[2, 3, 5, 8]'),
            ('{"key": "value"}');

fts_demo=> select * from js where extra @> '2';
ERROR:  operator does not exist: json @> unknown
LINE 1: select * from js where extra @> '2';
                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts

alter table js alter column extra type jsonb;

fts_demo=> select * from js where extra @> '2';
 id |    extra
----+--------------
  1 | [1, 2, 3, 4]
  2 | [2, 3, 5, 8]
(2 rows)

JSONB

  • JSONB is already stored in (internal binary format) interpreted form. This means:

    • storing take a little while longer (more CPU process)

    • but processing (retrieval) faster

  • The main thing is all JSON document can be indexed with a single GIN index.

fts_demo=> create index on js using gin (extra jsonb_path_ops);
CREATE INDEX

JSONB

fts_demo=> explain analyze (select * from js where extra @> '2');
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on js  (cost=8.00..12.01 rows=1 width=36) (actual time=0.011..0.012 rows=2 loops=1)
   Recheck Cond: (extra @> '2'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on js_extra_idx  (cost=0.00..8.00 rows=1 width=0) (actual time=0.006..0.006 rows=2 loops=1)
         Index Cond: (extra @> '2'::jsonb)
 Planning time: 0.054 ms
 Execution time: 0.031 ms
(7 rows)

fts_demo=> explain analyze (select * from js where extra @> '[2,3]');
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on js  (cost=12.00..16.01 rows=1 width=36) (actual time=0.012..0.013 rows=2 loops=1)
   Recheck Cond: (extra @> '[2, 3]'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on js_extra_idx  (cost=0.00..12.00 rows=1 width=0) (actual time=0.007..0.007 rows=2 loops=1)
         Index Cond: (extra @> '[2, 3]'::jsonb)
 Planning time: 0.053 ms
 Execution time: 0.032 ms
(7 rows)

JSONB

  • Interpreted format is different than what you sent originally, it goes through normalisation:

    • keys are sorted

    • duplicated keys are removed and only first ones are saved

    • whitespaces removed etc.

  • Fits into JSON standard (JSONB is Postgres' JSON)

    • schemaless PostgreSQL 

    • heterogeneous set of documents all in a single relation

    • semi-structured data model

GIN

Generalised Inverted Index

Why?

forward indexes

list of documents and which words appear in them

  • there is almost no duplication 

backward (inverted) indexes

list of words and in which documents they appeared

  • it is efficient

  • duplicate data in values

  • the more duplication the more efficient indices

GIN

ID Document
1 PostgreSQL is awesome
2 Awesome things happen
3 PHP loves PostgreSQL
4 Istanbul is awesome too
5 Thanks PHP
Term Document ID
awesome 1, 2, 4
happen 2
is 1, 4
istanbul 4
loves 3
php 3, 5
postgresql 1, 3
thanks 5
things 2
too 4

inverted index simplified

posting list

key

(               ,           )

(               ,       )

GIN

  • GIN is an index that allows indexing of complex data types
    • Postgres data types extract keys and positions of them
    • Key is data type specific
    • In the case of JSON it can store of the paths of JSONB documents. This is its key.​
  • GIN is very efficient in duplicate keys (GIN keys)
    • Keys of JSON != Keys of GIN

  • GIN has more compact way of storing duplicate values (keys) than B Tree.

    • Which you get a lot in JSONB because you usually have similar structure in documents. Similar to full text engine’s indexing.

FTS in PostgreSQL

FTS in PostgreSQL

  • FTS is implemented in a similar fashion like JSONB type:
    • there are types like ts_vector which get text input and parses into lexemes
  • Difference between JSONB:
    • ts_vector only stores info that is useful for FTS while JSONB stores the actual document as well
    • that has affect on how it is used afterwards:
      • JSONB is used as column type while ts_vector is mostly used for creating indexes as index definition or compound values (indexing multiple columns at the same time)

tsvector

tsvector which is a type suited to full-text search

fts_demo=# SELECT to_tsvector('Happiness is an allegory, unhappiness a story.');
                 to_tsvector
----------------------------------------------
 'allegori':4 'happi':1 'stori':7 'unhappi':5
(1 row)

fts_demo=# SELECT to_tsvector('Happiness is an allegory, unhappiness a story.') 
           @@ 'happiness';
 ?column?
----------
 f
(1 row)

tsquery


fts_demo=# SELECT to_tsvector('Happiness is an allegory, unhappiness a story.') 
           @@ to_tsquery('happiness');
 ?column?
----------
 t
(1 row)

fts_demo=# SELECT to_tsvector('Happiness is an allegory, unhappiness a story.') 
           @@ to_tsquery('happiness & unhappiness');
 ?column?
----------
 t
(1 row)

 tsquery stores lexemes that are to be searched for

Querying

Select title, description
from
    (select title, description, to_tsvector(title) || 
            to_tsvector(description) as searchterm
    from film) as q
where q.searchterm @@ to_tsquery('Human & Database')
limit 5;

      title      |                                                        description
-----------------+----------------------------------------------------------------------------------------------------------------------------
 ANONYMOUS HUMAN | A Amazing Reflection of a Database Administrator And a Astronaut who must Outrace a Database Administrator in A Shark Tank
 HUMAN GRAFFITI  | A Beautiful Reflection of a Womanizer And a Sumo Wrestler who must Chase a Database Administrator in The Gulf of Mexico
(2 rows)
Select title, ts_rank(q.searchterm, to_tsquery('DINOSAUR | Feminist')) as searchrank, description
from
    (select title, description, setweight(to_tsvector(title), 'A') || 
            setweight(to_tsvector(description), 'B') as searchterm
    from film) as q
where q.searchterm @@ to_tsquery('DINOSAUR | Feminist')
order by searchrank desc
limit 5;


       title        | searchrank |                                            description
--------------------+------------+----------------------------------------------------------------------------------------------------
 ACADEMY DINOSAUR   |   0.425549 | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
 DINOSAUR SECRETARY |   0.425549 | A Action-Packed Drama of a Feminist And a Girl who must Reach a Robot in The Canadian Rockies
 CENTER DINOSAUR    |   0.303964 | A Beautiful Character Study of a Sumo Wrestler And a Dentist who must Find a Dog in California
 SPY MILE           |   0.165491 | A Thrilling Documentary of a Feminist And a Feminist who must Confront a Feminist in A Baloon
 BUNCH MINDS        |   0.151982 | A Emotional Story of a Feminist And a Feminist who must Escape a Pastry Chef in A MySQL Convention
(5 rows)

Ranking

1x

1x

0

3x

2x

Fuzzy Search Using Trigrams

Trigram?

"h"

"he"

"hel"

"ell"

"llo"

"lo"

"o"

hello

hallo

"h"

"ha"

 "hal"

"all"

"llo"

"lo"

"o"

fts_demo=# Create extension pg_trgm;
CREATE EXTENSION

fts_demo=# select similarity('hello','hallo');
 similarity
------------
   0.333333
(1 row)

Similarity and Distance

%,<%, <->
fts_demo=# explain analyze select description from film 
           where description %> 'Feminist';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on film  (cost=10000000000.00..10000000067.50 rows=1 width=94) (actual time=0.031..14.900 rows=84 loops=1)
   Filter: (description %> 'Feminist'::text)
   Rows Removed by Filter: 916
 Planning time: 0.046 ms
 Execution time: 14.919 ms

fts_demo=# CREATE INDEX trgm_idx ON film USING GIN (description gin_trgm_ops);
CREATE INDEX

fts_demo=# explain analyze select description from film 
           where description %> 'Feminist';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=76.01..80.02 rows=1 width=94) (actual time=0.113..1.945 rows=84 loops=1)
   Recheck Cond: (description %> 'Feminist'::text)
   Rows Removed by Index Recheck: 29
   Heap Blocks: exact=49
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..76.01 rows=1 width=0) (actual time=0.085..0.085 rows=113 loops=1)
         Index Cond: (description %> 'Feminist'::text)
 Planning time: 0.132 ms
 Execution time: 1.970 ms

Like Queries

LIKE, ILIKE, ~, ~*
fts_demo=# Explain analyze select description from film  
           where description like '%Feminist%';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=52.63..111.30 rows=81 width=94) (actual time=0.052..0.112 rows=84 loops=1)
   Recheck Cond: (description ~~ '%Feminist%'::text)
   Heap Blocks: exact=42
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..52.61 rows=81 width=0) (actual time=0.044..0.044 rows=84 loops=1)
         Index Cond: (description ~~ '%Feminist%'::text)
 Planning time: 0.108 ms
 Execution time: 0.135 ms
(7 rows)

Misspelling

fts_demo=# CREATE TABLE unique_lexeme AS 
           SELECT word FROM ts_stat(
           'SELECT to_tsvector(''simple'', first_name) || 
               to_tsvector(''simple'', last_name)
           FROM actor 
           GROUP BY actor_id');

fts_demo=# CREATE INDEX lexeme_idx ON unique_lexeme USING GIN (word gin_trgm_ops);
CREATE INDEX

fts_demo=# SELECT word from unique_lexeme
           WHERE similarity(word, 'sinatro') > 0.5
           ORDER BY word <-> 'sinatro'
           LIMIT 10;
  word
---------
 sinatra
(1 row)

Multilingual PostgreSQL

Built-in text search for Danish, Dutch, English, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish, Turkish.

Accent Support

CREATE EXTENSION unaccent;

SELECT unaccent('Gülçin Yıldırım Jelínek');
        unaccent         
-------------------------
 Gulcin Yildirim Jelinek
(1 row)

fts_demo=# CREATE TEXT SEARCH CONFIGURATION tr ( COPY = turkish );
CREATE TEXT SEARCH CONFIGURATION
fts_demo=# ALTER TEXT SEARCH CONFIGURATION tr 
           ALTER MAPPING FOR hword, hword_part, word WITH unaccent, turkish_stem;
ALTER TEXT SEARCH CONFIGURATION

fts_demo=# SELECT to_tsvector('tr', 'Gülçin') @@ to_tsquery('tr', 'gulcin') as result;
 result 
--------
 t
(1 row)

fts_demo=# set default_text_search_config to 'tr';
SET
fts_demo=# SELECT to_tsvector('Gülçin') @@ to_tsquery('gulcin') as result;
 result 
--------
 t
(1 row)

PostGIS

Geospatial search in PostgreSQL? Yes, ofc!

Why PostgreSQL?

Advantages of PostgreSQL over using a search engine:

  • You can use the existing relations
  • You can query related information (joins)
  • You can do all in one query (transactional)
  • When you update (insert, delete) your document, indexes are updated automatically
    • Rebuilding indexes are not a concern
    • FTS is always up-to-date (no 404)
  • Same ACID properties
  • You don’t need to maintain two techs (two dataset)

Why PostgreSQL?

JSONB

  • Stable schema and flexibly evolving data in the same database
  • Denormalisation without the downsides
    • No unnecesary tables
    • No unnecessary joins 
fts_demo=# Select first_name, last_name, education from staff;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
first_name | Mike
last_name  | Hillyer
education  | {"properties": {"university": {"type": "oxford"}, "high school": {"name": "harvard business school"}}, "dependencies": {"graduation-date": ["2017-11-10"]}}
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
first_name | Jon
last_name  | Stephens
education  | {"properties": {"university": {"type": "tallinn university of technology"}, "high school": {"name": "business school"}}, "dependencies": {"graduation-date": ["2017-10-23"]}}

References

Thank you! Questions?

MySQL

MySQL full-text search features are very limited. By default, there is no support for stemming nor any language support. I came across a stemming function which can be installed, but MYSQL doesn't support function based indexes. (http://rachbelaid.com/postgres-full-text-search-is-good-enough/)

 

  • Migrate to PostgreSQL
  • Investigate a search engine like SOLR and Elasticsearch

PostgreSQL is not your traditional SQL database

By Gülçin Yıldırım Jelínek

Made with Slides.com

PostgreSQL is not your traditional SQL database

This presentation is prepared for PHPKonf 2018 in Istanbul.

  • 3,419

More from Gülçin Yıldırım Jelínek