The ele lephant in in th the enterpris ise Who we are Th The - - PowerPoint PPT Presentation
The ele lephant in in th the enterpris ise Who we are Th The Company > Founded in 2010 > More than 70 specialists > Specialized in the Middleware Infrastructure > The invisible part of IT > Customers in Switzerland and all over
The ele lephant in in th the enterpris ise
Who we are
20.02.2020 Page 2 The elephant in the enterprise
Th The Company
> Founded in 2010 > More than 70 specialists > Specialized in the Middleware Infrastructure
> The invisible part of IT
> Customers in Switzerland and all over Europe
Our Offer
> Consulting > Service Level Agreements (SLA) > Trainings > License Management
About me
20.02.2020 Page 3 The elephant in the enterprise
Da Daniel l Westermann
Principal Consultant Open Infrastructure Technology Leader +41 79 927 24 46 daniel.westermann[at]dbi-services.com @westermanndanie Daniel Westermann
20.02.2020 The elephant in the enterprise Page 4
Th The ele lephant in in the enterpris ise
Before we start We have a a Pos
ser group in in Switzerland!
> https://www.swisspug.org
Consider supporting us!
20.02.2020 The elephant in the enterprise Page 5
Th The ele lephant in in the enterpris ise
Before we start We have a a Pos
in Switzerland!
> https://www.meetup.com/Switzerland-PostgreSQL-User-Group/
Consider joining us!
20.02.2020 The elephant in the enterprise Page 6
Th The ele lephant in in the enterpris ise
Before we start Upcoming community conferences, Swis iss PGDay 20 2020 20
> https://www.pgday.ch
20.02.2020 The elephant in the enterprise Page 7
Th The ele lephant in in the enterpris ise
Before we start Upcoming community conferences, PGC GConf.DE 20 2020 20
> https://2020.pgconf.de/
Agenda
1.How does that "PostgreSQL" thing work? 2.Enterprise features 3.Beyond community PostgreSQL 4.Getting support 5.Best practices 6.Conclusion
20.02.2020 Page 8 The elephant in the enterprise
20.02.2020 Page 9 The elephant in the enterprise
1 2 3 4 5 6
How does th that "P "PostgreSQL" th thing work rk?
20.02.2020 The elephant in the enterprise Page 10
PostgreSQL is is dif ifferent
How does that "PostgreSQL" thing work? Th The PostgreSQL lice license expli licit itly allo allows s you
> To use, copy, modify and distribute the software
> for any purpose > without fee > without a written agreement
> https://opensource.org/licenses/postgresql
20.02.2020 The elephant in the enterprise Page 11
But how do they make money then?
How does that "PostgreSQL" thing work? PostgreSQL is is
> non-commercial > all volunteer > a free software project
Th There is is no
l lis list t of
irements requir ired for
"W "We enjoy allo allowin ing developers s to
the topics of
their ir ch choosin ing, g, …"
> https://www.postgresql.org/developer/roadmap
20.02.2020 The elephant in the enterprise Page 12
Come on, , they need money, , don't they?
How does that "PostgreSQL" thing work? How doe
the community fu funds its its work?
> A lot of companies all over the world hire people for working on the PostgreSQL source code
> EnterpriseDB > VMWare > RedHat > 2ndQuadrant > Fujitsu > NTT > ( many, many, many, more )
Th The goal l is is to
ring in in features th that th the community ag agrees on
Everybody win ins!
20.02.2020 The elephant in the enterprise Page 13
What is is the roadmap then?
How does that "PostgreSQL" thing work? "P "PostgreSQL is is a a non
l, all all volu lunteer, fr free so software project, an and as as su such th there is is no
l lis list of
irements requir ired for development. We reall lly do
llow th the mantra
lettin ing developers scr scratch th their own it itches"
> https://www.postgresql.org/developer/roadmap
So Some companie ies wanted to ch change th that at t le leas ast a a lit littl tle bit it
> https://wiki.postgresql.org/wiki/Fujitsu_roadmap - 2018
2018
> https://wiki.postgresql.org/wiki/NTT_roadmap - 2016
2016
> https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap - 2017
2017
Fin Finall lly th they've come bac ack to th the usu sual l community process
20.02.2020 The elephant in the enterprise Page 14
Who is is controll llin ing PostgreSQL then?
How does that "PostgreSQL" thing work? If If you ar are loo looking for a a Pos
central l commit ittee, or
ling company
> giv
give e up - th ther ere is isn't 't on
Th There is is a a cor
ittee an and commit itters
> these groups are more for admin
inistrative e purp rposes than control
Th The project is is dir irected by y th the communit ity of
and use sers
> anyone can joi
> all you need to do is to subscribe to the mailing lists and participate in the discussions
20.02.2020 The elephant in the enterprise Page 15
Who is is controll llin ing PostgreSQL then?
How does that "PostgreSQL" thing work? Who is is th the PostgreSQL Glo Global l De Development Gr Group?
> "The "PGDG" is an international, unincorporated association of individuals and companies who have
contributed to the PostgreSQL project. The PostgreSQL Core Team generally act as spokespeople for the PGDG."
> https://wiki.postgresql.org/wiki/FAQ#Who_is_the_PostgreSQL_Global_Development_Group.3F
20.02.2020 The elephant in the enterprise Page 16
Who is is controll llin ing PostgreSQL then?
How does that "PostgreSQL" thing work? Who is is th the PostgreSQL Cor
> A committee of five to seven (currently five) senior contributors to PostgreSQL who do the following for the
project
> (a) set release dates > (b) handle confidential matters for the project > (c) act as spokespeople for the PGDG when required > (d) arbitrate community decisions which are not settled by consensus
20.02.2020 The elephant in the enterprise Page 17
Who is is controll llin ing PostgreSQL then?
How does that "PostgreSQL" thing work? Th The cu current PostgreSQL core team an and so some major contrib ibutors
> https://www.postgresql.org/community/contributors
20.02.2020 The elephant in the enterprise Page 18
Who is is controll llin ing PostgreSQL then?
How does that "PostgreSQL" thing work? So So, on
again in, who is is ownin ing Pos
20.02.2020 The elephant in the enterprise Page 19
PostgreSQL develo lopment
How does that "PostgreSQL" thing work? How doe
it wor
then when it it comes to
> What about certifying operating systems? > What about bug tracking systems? > What about source code control? > What about decisions? Who decides what? > What about security fixes?
20.02.2020 The elephant in the enterprise Page 20
PostgreSQL develo lopment
How does that "PostgreSQL" thing work? Le Let's start fr from th the very ry begin inning
> What do you need when you work with a commercial vendor?
A support contract? A subscription? Money? Access to patches? Access to a support portal? Patience, a lot of patience?
20.02.2020 The elephant in the enterprise Page 21
PostgreSQL develo lopment
How does that "PostgreSQL" thing work? What do
Who in this room does not have an email address?
20.02.2020 The elephant in the enterprise Page 22
PostgreSQL develo lopment
How does that "PostgreSQL" thing work? All ll you need to
art of
the community is is an an
> Everything is based on mailing lists > All discussions happen via Email > No matter what you are looking for, there probably is a mailing list for it
> https://www.postgresql.org/list/
20.02.2020 The elephant in the enterprise Page 23
PostgreSQL develo lopment
How does that "PostgreSQL" thing work? Even patches an and new features start with ith an an
20.02.2020 The elephant in the enterprise Page 24
Commit itfests
How does that "PostgreSQL" thing work? PostgreSQL is is comple letely op
and tr transparent
> All features and patches that are going on are tracked online > https://commitfest.postgresq.sql
20.02.2020 The elephant in the enterprise Page 25
How PostgreSQL is is tested?
How does that "PostgreSQL" thing work? Th The build ild farm
> https://buildfarm.postgresql.org/cgi-bin/show_status.pl
20.02.2020 The elephant in the enterprise Page 26
PostgreSQL communit ity communic icatio ion
How does that "PostgreSQL" thing work? To
summarize
> New features > Patches > Bug tracking > Decisions > Source code control > Supported Operating systems
+ Commitfests + Commitfests + Commitfests https://git.postgresql.org/gitweb/?p=postgresql.git;a=summary https://buildfarm.postgresql.org/
20.02.2020 The elephant in the enterprise Page 27
Where does PostgreSQL ru run on?
How does that "PostgreSQL" thing work? Lin Linux (all (all recent dis istributions) Win indows s (20 (2000 SP SP4 4 an an la later) Fr FreeBSD, OpenBSD, , NetBSD So Solaris is - AIX - HP/U /UX OS S X
20.02.2020 The elephant in the enterprise Page 28
Where does PostgreSQL ru run on?
How does that "PostgreSQL" thing work?
20.02.2020 The elephant in the enterprise Page 29
Who is is ru runnin ing PostgreSQL?
How does that "PostgreSQL" thing work?
20.02.2020 The elephant in the enterprise Page 30
Where does the lo logo come from?
How does that "PostgreSQL" thing work? Why did id th the communit ity ch chose th the ele lephant? […] but if you want an animal-based log logo, how ab about so some so sort of
lephant? Aft fter all, all, as as th the Agatha Chri ristie ie title title read, ele lephants can an remember … - Da David id Yan ang, g, 19 1997 97-04 04-03 20 20:3 :36:3 :33
20.02.2020 Page 31 The elephant in the enterprise
1 2 3 4 5 6
Enterprise features
20.02.2020 The elephant in the enterprise Page 32
What is is the most im important feature of a database
Enterprise features Atomicity, Con
istency, Iso sola lation, Durabili ility
A C I D Only valid data is saved Transactions are all or nothing Transactions do not affect each
Written data is never lost
20.02.2020 The elephant in the enterprise Page 33
What is is the most im important feature of a database
Enterprise features PostgreSQL is is fu full lly ACID complia iant an and im imple lements th three of
the four le levels ls defin ined in in th the SQ SQL standard
> (Read uncommitted) > Read committed (the default) > Repeatable read > Serializable
20.02.2020 The elephant in the enterprise Page 34
What is is the next xt most im important feature?
Enterprise features Reli liable le bac ackup & restore
> pg_basebackup > WAL > archived WALs > PITR
Disk blocks Kernel disk buffer cache PostgreSQL Shared Buffer Cache Write Ahead Log fsync fsync
PG Backend PG Backend PG Backend
recovery
20.02.2020 The elephant in the enterprise Page 35
What is is the next xt most im important feature?
Enterprise features Reli liable le bac ackup & restore - you need tools ls for bac ackup & restore man anagement
pg_basebackup & pg_receive_wal Catalog Retention policies
20.02.2020 The elephant in the enterprise Page 36
What is is the next xt?
Enterprise features When you have
> a trusted database system (ACID) > a reliable solution to backup and restore the system
What else lse do
20.02.2020 The elephant in the enterprise Page 37
Hig igh avail ilabil ilit ity
Enterprise features Hig igh availa labili ility - you need tools ls for
automated fail ailover an and con
lled swit itchovers
read & write
streaming replication asynchronous
This is community PostgreSQL Tools
20.02.2020 The elephant in the enterprise Page 38
Logic ical l repli licatio ion
Enterprise features In In-core log logical repli lication
read & write
streaming replication asynchronous
This is community PostgreSQL
20.02.2020 The elephant in the enterprise Page 39
Foreig ign data wrappers
Enterprise features
20.02.2020 The elephant in the enterprise Page 40
Foreig ign data wrappers
Enterprise features When PostgreSQL 9.1 9.1 was rele leased in in 20 2011 11
> Support for SQL/MED was added > MED = Management of External Data > Foreign data wrappers (fdw) were born, https://wiki.postgresql.org/wiki/Foreign_data_wrappers > FDWs allow to access data that is outside of PostgreSQL using standard SQL
> This data is referred to as foreign data
> There are more than 70 Foreign Data Wrappers
20.02.2020 The elephant in the enterprise Page 41
Part rtit itio ionin ing
Enterprise features Par artitionin ing
> PostgreSQL supports range, list and hash partitioning > Sub-partitioning is supported as well > Partitions may be attached and detached > Currently no support for global indexes CREATE TABLE measurement_year_month ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate)); CREATE TABLE cities ( city_id bigserial not null, name text not null, population bigint ) PARTITION BY LIST (left(lower(name), 1));
20.02.2020 The elephant in the enterprise Page 42
Full ll text xt se search
Enterprise features Build ild-in in fu full ll text se search
> PostgreSQL comes with full text by default
tsq tsquery
> A tsquery value stores lexemes that are to be searched for
> and can be combined using the Boolean operators & (AND), | (OR), and ! (NOT) > as well as the phrase search operator <-> (FOLLOWED BY)
to_
ry
> creates a tsquery value from querytext > The querytext must consist of
> single tokens separated by the tsquery operators > & (AND), | (OR), ! (NOT), and <-> (FOLLOWED BY), possibly grouped using parentheses
20.02.2020 The elephant in the enterprise Page 43
Data types
Enterprise features A wid ide ran ange of
types, even for NoSQL workloads (without los losing ACID)
json smallint/int/bigint smallserial/serial/bigserial numeric decimal real double precision text char varchar boolean money date time interval timestamp/timestamptz enum types points lines boxes circles polygons paths inet cidr macaddr macaddr8 jsonb arrays uuid xml in4range in8range numrange tsrange tzrange daterange tstzrange composite types domain types OID types pg_lsn
20.02.2020 The elephant in the enterprise Page 44
In Indexin ing
Enterprise features Various in index im imple lementations
> Who many index types does PostgreSQL provide by default?
B-Tree (b (bala lanced tr tree) GiS GiST (generalized se sear arch tr tree) SP SP-GiST (sp (space par artitioned gis ist) GI GIN (generali lized in inverted in index) BRI RIN (b (block ran ange in index) Hash ash Blo loom
20.02.2020 The elephant in the enterprise Page 45
Transactio ional l DDL
Enterprise features In In PostgreSQL DM DMLs as as well ll as as DD DDLs ar are tr transactional
> Transactional DDL is great for updating schemas > Transactional DDL is great for application updates -> all or nothing BEGIN; CREATE TABLE T1 ( a int, b text, c date); INSERT INTO T1 VALUES ( 1, 'a', now() ); CREATE INDEX I1 ON T1 ( a,b ); INSERT INTO T1 VALUES ( 2, 'b', now() ); CREATE INDEX I2 ON T1 ( a,b,c,d); -- ERROR END; SELECT * FROM T1; -- THE TABLE DOES NOT EXIST, NOR DOES THE INDEX
20.02.2020 The elephant in the enterprise Page 46
Ext xtensio ions
Enterprise features PostgreSQL is is ext xtensib ible le by y default
> PostgreSQL comes with a wide range of default extensions > https://www.postgresql.org/docs/current/contrib.html
> Additional data types > Additional indexes > Additional languages > Additional debugging functionality > ...
20.02.2020 The elephant in the enterprise Page 47
Ext xtensio ions
Enterprise features Th The PostgreSQL ext xtension network
> https://pgxn.org/ > Countless extensions for various use cases
20.02.2020 The elephant in the enterprise Page 48
Procedural l lan languages
Enterprise features How man any op
server sid side programmin ing?
> PostgreSQL provides PL/pgsql by default > PL/Perl, PL/Python, PL/Tcl ship by default but are not installed > PL/Java > PL/R > PL/Ruby > PLv8 (Javascript)
All ll of
those can an be use sed to
rite fu functions & trigg triggers
20.02.2020 The elephant in the enterprise Page 49
JIT JIT
Enterprise features Jus Just-in in-time compila ilation (J (JIT)
> Very useful for complex queries and DWH use cases > Turning some form of interpreted program evaluation into a native program
> doing so at run time
> Accelerating expression evaluation and tuple deforming
> Tuple deforming is the process of transforming an on-disk tuple into its in-memory representation
postgres=# select name,setting from pg_settings where name like '%jit_%'; name | setting
jit_above_cost | 100000 jit_debugging_support | off jit_dump_bitcode | off jit_expressions | on jit_inline_above_cost | 500000 jit_optimize_above_cost | 500000 jit_profiling_support | off jit_provider | llvmjit jit_tuple_deforming | on
20.02.2020 Page 50 The elephant in the enterprise
1 2 3 4 5 6
Beyond communit ity PostgreSQL
20.02.2020 The elephant in the enterprise Page 51
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL Because of
> The PostgreSQL license > PostgreSQL's extensibility > The open community > The huge PostgreSQL eco-system
There is much more than community PostgreSQL (the following are just some examples)
20.02.2020 The elephant in the enterprise Page 52
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL Enterpris iseDB
> https://www.enterprisedb.com/ > Standard (community PostgreSQL) and Enteprise Editions (closed source)
> 24x7 Support > Tools > EDB EFM (Failover manager) > EDB BART (Backup and recovery tool) > EDB PEM (Postgres Enterprise Manager) > EDB containers for OpenShift, GCP, Pivotal > EDB xdb replication server (logical replication between Oracle and PostgreSQL) > Oracle compatibility in the Enterprise Edition > Various dbms_* and utl_* package implementations > PL/SQL out of the box > EDB MTK (Migration toolkit)
20.02.2020 The elephant in the enterprise Page 53
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL Citu itus Da Data, now par art of
icrosoft Azure
> https://www.citusdata.com > PostgreSQL sharding across nodes
> Adding nodes on demand to scale > Memory > Compute > Disk > Community version > https://github.com/citusdata/citus > All based on an extension
> Columnar store
> https://github.com/citusdata/cstore_fdw
20.02.2020 The elephant in the enterprise Page 54
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL Ti Timescale
> https://www.timescale.com/ > PostgreSQL optimized for time series management
> automatic partitioning across time and space > retains the standard PostgreSQL interface > community version > https://github.com/timescale/
> All based on an extension
20.02.2020 The elephant in the enterprise Page 55
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL FU FUJI JITSU Enterpris ise Pos
> https://www.postgresql.fastware.com/ > 24x7 support > Transparent Data Encryption (TDE) > Log encryption > In-memory columar indexes > Data masking and redaction
20.02.2020 The elephant in the enterprise Page 56
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL ZomboDB
> https://www.zombodb.com/ > Scalable text search for PostgreSQL > Combines PostgreSQL and Elasticsearch
> Elasticsearch is used an index type
> Community version
> https://github.com/zombodb/zombodb/
> All based on an extension
20.02.2020 The elephant in the enterprise Page 57
Th The ele lephant in in the enterpris ise
Beyond community PostgreSQL Yugabyt yteDB
> https://www.yugabyte.com/ > High-Performance Distributed SQL Database > Deploy across regions and clouds with synchronous or multi-master replication > Automated sharding > Community version
> https://github.com/yugabyte/yugabyte-db
20.02.2020 Page 58 The elephant in the enterprise
1 2 3 4 5 6
Gettin ing support
20.02.2020 The elephant in the enterprise Page 59
Gettin ing su support
Support policies Usu suall lly, th there is is on
ajor rele lease every ry year
> Each major release gets support for 5 years
Version Current minor Supported Released EOL
12 12.2 Yes OCT-2019 NOV-2024 11 11.7 Yes OCT-2018 NOV-2023 10 10.12 Yes OCT-2017 OCT-2022 9.6 9.6.17 Yes SEP-2016 SEP-2021 9.5 9.5.21 Yes JAN-2016 JAN-2021 9.4 9.4.26 Yes DEC-2014 DEC-2019 9.3 9.3.25 No SEP-2013 NOV-2018 9.2 9.2.24 No SEP-2012 SEP-2017 … … … … … 6.3 6.3.2 No MAR-1998 MAR-2003
20.02.2020 The elephant in the enterprise Page 60
Communit ity Su Support
Getting support Ge General l PostgreSQL rela lated questions
> pgsql-general@postgresql.org
Performance rela lated PostgreSQL questions
> pgsql-performance@postgresql.org
Man any ot
lists ts for
specific ar areas
> https://www.postgresql.org/list/
But, t, before you post a a question
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > https://wiki.postgresql.org/wiki/SlowQueryQuestions > Search the mailing list archives before you post your question!
20.02.2020 The elephant in the enterprise Page 61
Communit ity Su Support
Getting support Th The PostgreSQL wik iki
> https://wiki.postgresql.org/wiki/Main_Page
Plan lanet PostgreSQL
> https://planet.postgresql.org/
PGTune (t (to start with ith th the PostgreSQL configuration)
> https://pgtune.leopard.in.ua/#/
20.02.2020 Page 62 The elephant in the enterprise
1 2 3 4 5 6
Best practices
20.02.2020 The elephant in the enterprise Page 63
Best practic ices
The elephant in the enterprise PostgreSQL is is not Oracle le nor is is it it MS S SQ SQL Se Server nor
is it it MyS ySQL/Maria iaDB
> Don't compare features 1 to 1
> PostgreSQL has its own implementation of features > Things might not work the same as you know it from other database systems > Do things the PostgreSQL way
> Some pitfalls (especially when you come from Oracle)
> Give as much memory to the database systems as possible > Implement booleans as number(0,1) or varchar2(Y/N) > Trying to influence the optimizer/planner by using hints > Statement level transactions > Handling of NULL values > Use the numeric data type for all numeric values, even integers > Expecting implicit conversions > Implementing tablespaces for no specific reason
20.02.2020 The elephant in the enterprise Page 64
Best practic ices
The elephant in the enterprise Even if if PostgreSQL is is eas asy to
setup an and to
ith
> Avoid bad design decisions right from the beginning > It is cheaper to book a training than to correct your choices afterwards > Prepare to work with the community(ies) > Get familiar with PostgreSQL wordings > Get familiar with the PostgreSQL features > Read, learn, read (Documentation, Blogs, Conferences)
20.02.2020 The elephant in the enterprise Page 65
Best practic ices
The elephant in the enterprise Th The op
> Kernel parameters > Huge Pages > Transparent Huge Pages => turn it off > File systems > systemd or init.d? > Tuned profiles for RedHat based distributions > readahead on the block devices for mechanical disks > sudo rules > …
You
ll need a a standard deployment for
all th this is!
> You automate your stuff, don't you?
20.02.2020 The elephant in the enterprise Page 66
Best practic ices
The elephant in the enterprise De Decid ide on
> Use the packages provided by the community?
> yum.postgresql.org > RedHat, CentOS > apt.postgresql.org > Debian, Ubuntu > zypp.postgresql.org > SUSE, OpenSUSE
> Install from source code?
> https://www.postgresql.org/ftp/source/
> Deploy PostgreSQL containers?
> https://hub.docker.com/search?q=postgres&type=image > https://catalog.redhat.com/software/containers/explore > https://registry.opensuse.org/cgi-bin/cooverview
20.02.2020 Page 67 The elephant in the enterprise
1 2 3 4 5 6
Conclusion
20.02.2020 The elephant in the enterprise Page 68
Conclu lusio ion
PostgreSQL can an pretty much han andle an any y wor
Choosin ing th the rig right toolset ar around Pos
is ess ssentia ial Mak ake yourself lf famili iliar with ith Pos
ings s an and features Th There ar are man any forks/extensions of
arious requirements (O (Open So Source) Com
ies ar are driv riven by y in innovation, not fin financial l asp aspects
> Transparency and open discussions are key
20.02.2020 The elephant in the enterprise Page 69
Conclu lusio ion
Do Do not fear to become par art of
the community
> Register to a mailing list and start the discussion > Attend PostgreSQL conferences
> Submit abstracts around your use cases
> Report bugs > Help with translations
Read th the doc
and test, test, test
> There are many features not available in other systems > Other features might be missing but there are workarounds for sure
Any questions?
Please do ask!
We would love to boost your IT-Infrastructure How about you?
Zürich Basel Delémont Nyon Bern 20.02.2020 Page 70 The elephant in the enterprise