Why

is

PostgreSQL

my

superhero?

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

PGDay.IT 2018

select * from me;

Board Member @ PostgreSQL Europe

Member @ Postgres Women 

Main Organizer @ Prague PostgreSQL Meetup

Cloud Services Manager @ 2ndQuadrant

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

BSc, Applied Mathematics @ Yildiz Technical University

Writes on 2ndQuadrant blog

From Turkey

Lives in Prague

@apatheticmagpie

Github: gulcin

PostgreSQL

Publisher: PGDG

First appearance: 8 July 1996, v6.0

Created by: Michael Stonebraker

Alter ego: Slonik

Team affiliations: ORDMS

Abilities

  • ACID

  • Fault Tolerance & Replication

  • Failover & Disaster Recovery

  • Scalability

  • Extensibility

  • Performance

  • JSON, FTS

ACID

Atomicity

Consistency

Isolation

Durability

Name: Luke Cage

Power: Durability / Endurance

WAL

Transaction Log

  • Fault Tolerance
  • Crash Recovery (local node)
    • Checkpoints
  • Replication* (remote node)
  • PITR* 

Name: Spiderman

Power: Wallcrawling

Replication

  • Physical Replication
    • Read Replicas
    • High Availability
  • Logical Replication
  • Multi-Master Replication*

Name: Luornu Durgo

Power: Duplication

PITR

Name: Flashback

Power: Duplication (Temporal)

Backups can be restored to a specific point it time. 

  • Base backup + WALs

 

Time Delayed Replicas

recovery_min_apply_delay ​

Backup

Recovery

Failover

Name: Wolverine

Power: Healing Factor




 



 

Barman

Repmgr

Merge Replication

Logical replication from multiple sources to a single target (by PostgreSQL 9.4)

Name: Kleinstock Brothers

Power: Merging

Speed

Pluggable Index API

Index Types:

  • B-tree
  • Hash
  • GIST
  • GIN
  • BRIN..

Index-Only Scans

Covering Indexes (PostgreSQL 11)

Name: Flash

Power: Superhuman Speed

CREATE UNIQUE INDEX unique_idx ON example(a, b) 
   INCLUDE (c);

Performance

Parallel query

  • Multiple processes
  • Parallel scans
  • Parallel joins
  • Parallel aggregations

MVCC

  • Writes do not block reads
  • Reads do not block writes

Name: Beast

Power: Agility / Reflexes

Big Data

Transformation of large amounts of data

  • MERGE command (PG 12)
  • CTEs (WITH queries)

Smaller, more digestible chunks of data 

  • Partitioning (improved in PG 11)

Fast data ingestion

  • COPY command

TABLESAMPLE

Name: Black Bishop

Power: Mass Manipulation

Query Planner

Planner / Optimizer

Name: Invisible Woman

Power: Invisibility

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

Security

SSL Support

SCRAM authentication

Host-based authentication

  • pg_hba.conf

Database Roles & Privileges

Row Security Policies

Name: Argent

Power: Force field 

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts 
    TO managers
    USING (manager = current_user);

Extensibility

Custom data types

  • CREATE TYPE

User-defined functions

  • CREATE FUNCTION

Extensible Design

contrib/

Extensions

  • BDR
  • pglogical

Name: Mr. Fantastic

Power: Elasticity

Full Text Search

Started as extension 

FTS data types

  • tsvector
  • tsquery

Querying

Stemming

Ranking/Boost

Misspelling

Name: Peepers

Power: Telescopic Vision

Multilingual

Collation Support

  • per-column
  • per-operation

Built-in FTS for many languages 

Accent support

Name: Wonder Woman

Power: Omni-linguism

CREATE EXTENSION unaccent;

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

FDWs

Foreign Data Wrappers

  • postgres_fdw
  • oracle_fdw
  • mongo_fdw
  • twitter_fdw

 

All implemented as extensions

Even the PostgreSQL one

Name: Terror

Power: Absorbs the talent of others

PostGIS

A spatial database extension

  • Support for geographic objects
  • Location queries to be run in SQL

Name: Daredevil

Power: Echolocation

SELECT superhero.name
FROM city, superhero
WHERE ST_Contains(city.geom, superhero.geom)
AND city.name = 'Gotham';

Evolution

Pioneer PostgreSQL

  • SSI (PostgreSQL 9.1)
  • JSON (PostgreSQL 9.2)
  • JIT (PostgreSQL 11)

Modern software architectures

Name: Darwin

Power: Reactive Evolution

Versions

A brief history of PostgreSQL

  • The Berkeley POSTGRES Project (1986 - 1993)
  • Postgres95 (1994 - 1996)
  • PostgreSQL (1996 -      )

Release cycles 

  • PostgreSQL 11 Sep 2018

Active development (~32 years)

Release Support Policy (EOL) 

Near-Zero Downtime Upgrades

Name: Eclipso

Power: Longevity

\infty
∞\infty

Tooling

PostgreSQL Ecosystem 

Automation Tools

​PG Extension Network: PGXN

Language Drivers

  • psycopg2
  • JDBC
  • npgsql

Name: Batman

Ability: Utilizes high-tech equipment

PostgreSQL in Cloud

Bi-Directional Replication

Global Database as a Service

  • Backup & Recovery
  • Autofailover
  • Scaling

Name: Eternity

Power: Omnipresence

Postgres Women

Wiki: Postgres Women

Mailing-List: PGSQL-WOMEN

Contact us!

           

 

 

 

 

 

Postgres Universe

Characters: Contributor Profiles

Hackers Mailing-List: PGSQL-HACKERS

PostgreSQL Europe: PGEU

Planet PostgreSQL: planet.postgresql.org

Join the force!

References

Questions?

Huge Thanks!

All my slides are here.

Made with Slides.com