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.

Why is PostgreSQL my superhero?

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

Made with Slides.com

Why is PostgreSQL my superhero?

This presentation is prepared for PGDay.IT 2018 as the keynote talk of the conference.

  • 3,077

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