Supercharge your PostgreSQL with extensions

Supercharge
your PostgreSQL
with extensions

Brought to you by Alexey Vasiliev, Railsware

Alexey Vasiliev

Web and Mobile Developer (Ruby, Java, JavaScript, Objective-C, C/C++, Golang, Elixir/Erlang), DevOps
Open-Source libs: PGTune, SQL Joins Visualizer, RWbox, Go-Kinesis, ElixirV8, WebP-ffi, Zopfli-ffi, MongodbLogger, SMTRails, SHTRails, ...
Open-Source books: Cooking Infrastructure by Chef, Setting up and scaling of PostgreSQL (Russian)
Leading RWpod podcast about Ruby and JavaScript

What is PostgreSQL?

PostgreSQL logo PostgreSQL ("Postgres") - is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. Based on the SQL language and supports many of the features of the standard SQL:2011. PostgreSQL evolved from the Ingres project at the University of California, Berkeley. In 1982 the leader of the Ingres team, Michael Stonebraker, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project

PostgreSQL contrib

auto_explain
citext
dblink
fuzzystrmatch
hstore
intagg
intarray
ltree
pg_archivecleanup
pgbench
pg_buffercache
pgcrypto
pgstattuple
tsearch2
uuid-ossp
many more...

Citext

$ CREATE TABLE users (
	name text NOT NULL,
	username text NOT NULL
);
$ CREATE UNIQUE INDEX unique_username_on_users
  ON users (username);

Citext

INSERT INTO users (name, username) VALUES ('John Doe', 'john');
INSERT INTO users (name, username) VALUES ('Doe, John', 'JOHN');
				-----------+----------
				   name    | username
				-----------+----------
				 John Doe  | john
				 Doe, John | JOHN
				-----------+----------

Citext

CREATE UNIQUE INDEX unique_username_on_users
  ON users (lower(username));

INSERT INTO users (name, username) VALUES ('Doe, John', 'JOHN');
-- ERROR:  duplicate key value violates unique
  constraint "unique_username_on_users"
-- DETAIL:  Key (lower(username))=(john) already exists.

Citext

SELECT * FROM users WHERE username = 'john';
-- ----------+----------
--    name   | username
-- ----------+----------
--  John Doe | john
-- ----------+----------
-- (1 row)

Citext

SELECT * FROM users WHERE username = 'jOhN';
-- ------+----------
--  name | username
-- ------+----------
-- (0 rows)

Citext

SELECT * FROM users WHERE lower(username) = lower('jOhN');

-- ----------+----------
--    name   | username
-- ----------+----------
--  John Doe | john
-- (1 row)

Citext

Provides a case-insensitive character string type, citext

CREATE EXTENSION IF NOT EXISTS citex;
CREATE TABLE users (
	name text NOT NULL,
	username citex NOT NULL
);
CREATE UNIQUE INDEX unique_username_on_users
  ON users (username);

Citext

SELECT * FROM users WHERE username = 'jOhN';

--- ----------+----------
---    name   | username
--- ----------+----------
---  John Doe | john
--- (1 row)

Uuid-ossp

# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION
# SELECT uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 16778dae-c41f-447f-ace9-554e7e524d55
(1 row)

Uuid-ossp

CREATE TABLE pets
(
  id text NOT NULL DEFAULT uuid_generate_v4(),
  name text NOT NULL
);

Ltree

$ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';
 user_id |           path
---------+--------------------------
       6 | 0001.0003
       8 | 0001.0003.0001
       9 | 0001.0003.0002
      11 | 0001.0003.0002.0001
       2 | 0001.0003.0002.0002
       5 | 0001.0003.0002.0003

Ltree

$ EXPLAIN ANALYZE SELECT user_id, path FROM
  comments WHERE path <@ '0001.0003';
                  QUERY PLAN
-----------------------------------------------------
 Index Scan using path_gist_comments_idx on comments
 (cost=0.00..8.29 rows=2 width=38)
 (actual time=0.023..0.034 rows=12 loops=1)
   Index Cond: (path <@ '0001.0003'::ltree)
 Total runtime: 0.076 ms

Pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL

$ SELECT crypt('pa$$w0rd', gen_salt('bf'));
                            crypt
--------------------------------------------------------------
 $2a$06$U5HTeL6FQrdtTdbFm8oF8OPlZQeNmKDIyJVU0zlB7hYkOYuXJolNq
(1 row)

Auth_delay

Causes the server to pause briefly before reporting authentication failure, to make brute-force attacks on database passwords more difficul

# postgresql.conf
shared_preload_libraries = 'auth_delay'

custom_variable_classes = 'auth_delay'
auth_delay.milliseconds = '500'

Auto_explain

Provides a means for logging execution plans of slow statements automatically, without having to run EXPLAIN by hand

# postgresql.conf
shared_preload_libraries = 'auto_explain'

custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = '3s'

$ UPDATE billing_states
	SET is_active=true;

or

$ DELETE FROM users;

Safeupdate

extension that raises an error if UPDATE and DELETE are executed without specifying conditions

Hstore

Storing sets of key/value pairs within a single PostgreSQL value

No need any more! We have JSONB!

PostGIS

PostGIS adds support for geographic objects to the PostgreSQL

$ CREATE TABLE cities (
id int4 primary key, name varchar(50),
the_geom geometry(POINT,4326) );

pgSphere

PgSphere provides spherical data types, functions, operators, and indexing

$ SELECT spoly '{ (270d,-10d), (270d,30d), (290d,10d) } ';
              spoly
-----------------------------------------------------
 {(4.71238898038469 , -0.174532925199433),
(4.71238898038469 , 0.523598775598299),
(5.06145483078356 , 0.174532925199433)}
(1 row)

Tsearch2

Build in in PostgreSQL in release 8.3

$ SELECT strip(to_tsvector('The air smells of sea water.'));
            strip
-----------------------------
 'air' 'sea' 'smell' 'water'

ZomboDB

Making Postgres and Elasticsearch work together

ZomboDB

$ SELECT * FROM zdb_tally('products', 'keywords',
'^.*', '', 5000, 'term');
         term          | count
-----------------------+-------
 ALEXANDER GRAHAM BELL |     1
 BASEBALL              |     1
 BOX                   |     1
 COMMUNICATION         |     1

PostPic

$ SELECT *,
thumbnail(the_img, 50) as thumbnail,
rotate_left(the_img) as rotate_left_img,
crop(the_img, 0, 0, 300, 200) as crop_img FROM images
WHERE date(the_img) > '2017-01-01'::date
AND size(the_img) > 1600;

Pg_cron

Pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or higher) that runs inside the database

# SELECT cron.schedule('30 3 * * 6',
$$DELETE FROM events WHERE
event_time < now() - interval '1 week'$$);

Procedural languages

PL/pgSQL
Perl
Python
Tcl
Java
JavaScript
R
Ruby
PHP
Scheme
C
sh
Golang
many more...

PLV8

CREATE OR REPLACE FUNCTION
psqlfib(n int) RETURNS int AS $$
 BEGIN
     IF n < 2 THEN
         RETURN n;
     END IF;
     RETURN psqlfib(n-1) + psqlfib(n-2);
 END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

PLV8

# SELECT n, psqlfib(n) FROM generate_series(0, 30, 10) as n;
 n  | psqlfib
----+---------
  0 |       0
 10 |      55
 20 |    6765
 30 |  832040
Time: 17082.250 ms

PLV8

CREATE OR REPLACE FUNCTION
fib(n int) RETURNS int as $$

  function fib(n) {
    return n<2 ? n : fib(n-1) + fib(n-2)
  }
  return fib(n)

$$ LANGUAGE plv8 IMMUTABLE STRICT;

PLV8

SELECT n, fib(n) FROM generate_series(0, 30, 10) as n;
 n  |  fib
----+--------
  0 |      0
 10 |     55
 20 |   6765
 30 | 832040
Time: 17.014 ms

Pg_trgm

Module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

Smlar

Effective Similarity Search in PostgreSQL

Foreign Data Wrappers

Postgres_fdw (vs Dblink)
Oracle_fdw
Mysql_fdw
Firebird_fdw
Sqlite_fdw
Tds_fdw (Sybase and Microsoft SQL Server)
Monetdb_fdw
Cassandra-fdw
Couchdb_fdw
Mongo_fdw
Neo4j_fdw
Redis_fdw
Riak-multicorn-pg-fdw
Multicorn
CSV, Files, Git, IMAP, Facebook, Twitter, S3, Google Spreadsheets, etc

Multicorn FDW

SQL
IMAP
CSV
File system
Sqlite
RSS
LDAP
Extend by Python

Materialized Views for FDWs

# REFRESH MATERIALIZED VIEW CONCURRENTLY my_view;

What about BIG DATA???

Cstore_fdw

Fast columnar store for analytics with PostgreSQL

Postgresql-hll

HyperLogLog data structure

# CREATE TABLE daily_uniques (
  date            date UNIQUE,
  users           hll
);

Postgresql-hll

SELECT date, hll_cardinality(users) FROM daily_uniques;
    date    | hll_cardinality
------------+-----------------
 2017-02-21 |            23123
 2017-02-22 |            59433
 2017-02-23 |          2134890
 2017-02-24 |          3276247
(4 rows)