the elephant in the enterprise - home > ch open
Post on 19-Oct-2021
1 Views
Preview:
TRANSCRIPT
Who we are
20.02.2020 Page 2 The elephant in the enterprise
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
Daniel 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
The elephant in the enterprise Before we start
We have a PostgreSQL user group in Switzerland! > https://www.swisspug.org
Consider supporting us!
20.02.2020 The elephant in the enterprise Page 5
The elephant in the enterprise Before we start
We have a PostgreSQL meetup group in Switzerland! > https://www.meetup.com/Switzerland-PostgreSQL-User-Group/
Consider joining us!
20.02.2020 The elephant in the enterprise Page 6
The elephant in the enterprise Before we start
Upcoming community conferences, Swiss PGDay 2020 > https://www.pgday.ch
20.02.2020 The elephant in the enterprise Page 7
The elephant in the enterprise Before we start
Upcoming community conferences, PGConf.DE 2020 > 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 The elephant in the enterprise Page 10
PostgreSQL is different How does that "PostgreSQL" thing work?
The PostgreSQL license explicitly allows 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 > non-commercial
> all volunteer
> a free software project
There is no formal list of feature requirements required for development
"We enjoy allowing developers to explore the topics of their choosing, …"
> 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 does the community funds 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 )
The goal is to bring in features that the community agrees on.
Everybody wins!
20.02.2020 The elephant in the enterprise Page 13
What is the roadmap then? How does that "PostgreSQL" thing work?
"PostgreSQL is a non-commercial, all volunteer, free software project, and as such there is no formal list of feature requirements required for development. We really do follow the mantra of letting developers scratch their own itches"
> https://www.postgresql.org/developer/roadmap
Some companies wanted to change that at least a little bit > https://wiki.postgresql.org/wiki/Fujitsu_roadmap - 2018
> https://wiki.postgresql.org/wiki/NTT_roadmap - 2016
> https://wiki.postgresql.org/wiki/Postgres_Professional_roadmap - 2017
Finally they've come back to the usual community process
20.02.2020 The elephant in the enterprise Page 14
Who is controlling PostgreSQL then? How does that "PostgreSQL" thing work?
If you are looking for a PostgreSQL gatekeeper, central committee, or controlling company > give up - there isn't one
There is a core committee and committers > these groups are more for administrative purposes than control
The project is directed by the community of developers and users > anyone can join
> 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 controlling PostgreSQL then? How does that "PostgreSQL" thing work?
Who is the PostgreSQL Global Development 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 controlling PostgreSQL then? How does that "PostgreSQL" thing work?
Who is the PostgreSQL Core team? >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 controlling PostgreSQL then? How does that "PostgreSQL" thing work?
The current PostgreSQL core team and some major contributors > https://www.postgresql.org/community/contributors
20.02.2020 The elephant in the enterprise Page 18
Who is controlling PostgreSQL then? How does that "PostgreSQL" thing work?
So, once again, who is owning PostgreSQL?
The PostgreSQL community!
20.02.2020 The elephant in the enterprise Page 19
PostgreSQL development How does that "PostgreSQL" thing work?
How does it work then when it comes to development? >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 development How does that "PostgreSQL" thing work?
Let's start from the very beginning >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 development How does that "PostgreSQL" thing work?
What do you need for PostgreSQL?
Who in this room does not have an email address?
20.02.2020 The elephant in the enterprise Page 22
PostgreSQL development How does that "PostgreSQL" thing work?
All you need to become part of the community is 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 development How does that "PostgreSQL" thing work?
Even patches and new features start with an
20.02.2020 The elephant in the enterprise Page 24
Commitfests How does that "PostgreSQL" thing work?
PostgreSQL is completely open and 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 tested? How does that "PostgreSQL" thing work?
The build farm > https://buildfarm.postgresql.org/cgi-bin/show_status.pl
20.02.2020 The elephant in the enterprise Page 26
PostgreSQL community communication 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 run on? How does that "PostgreSQL" thing work?
Linux (all recent distributions)
Windows (2000 SP4 an later)
FreeBSD, OpenBSD, NetBSD
Solaris - AIX - HP/UX
OS X
20.02.2020 The elephant in the enterprise Page 28
Where does PostgreSQL run on? How does that "PostgreSQL" thing work?
20.02.2020 The elephant in the enterprise Page 29
Who is running PostgreSQL? How does that "PostgreSQL" thing work?
20.02.2020 The elephant in the enterprise Page 30
Where does the logo come from? How does that "PostgreSQL" thing work?
Why did the community chose the elephant?
[…] but if you want an animal-based logo, how about some sort of elephant? After all, as the Agatha Christie title read, elephants can remember … - David Yang, 1997-04-03 20:36:33
20.02.2020 The elephant in the enterprise Page 32
What is the most important feature of a database Enterprise features
Atomicity, Consistency, Isolation, Durability
A
C
I
D
Only valid data is saved
Transactions are all or nothing
Transactions do not affect each other
Written data is never lost
20.02.2020 The elephant in the enterprise Page 33
What is the most important feature of a database Enterprise features
PostgreSQL is fully ACID compliant and implements three of the four levels defined in the SQL standard > (Read uncommitted)
> Read committed (the default)
> Repeatable read
> Serializable
20.02.2020 The elephant in the enterprise Page 34
What is the next most important feature? Enterprise features
Reliable backup & 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 the next most important feature? Enterprise features
Reliable backup & restore - you need tools for backup & restore management
pg_basebackup & pg_receive_wal Catalog
Retention policies
20.02.2020 The elephant in the enterprise Page 36
What is the next? Enterprise features
When you have > a trusted database system (ACID)
> a reliable solution to backup and restore the system
What else do you need?
20.02.2020 The elephant in the enterprise Page 37
High availability Enterprise features
High availability - you need tools for automated failover and controlled switchovers
read & write streaming replication
asynchronous
This is community PostgreSQL Tools
20.02.2020 The elephant in the enterprise Page 38
Logical replication Enterprise features
In-core logical replication
read & write streaming replication
asynchronous
This is community PostgreSQL
20.02.2020 The elephant in the enterprise Page 39
Foreign data wrappers Enterprise features
SQL/MED Management of External Data
20.02.2020 The elephant in the enterprise Page 40
Foreign data wrappers Enterprise features
When PostgreSQL 9.1 was released in 2011 > 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
Partitioning Enterprise features
Partitioning > 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 text search Enterprise features
Build-in full text search > PostgreSQL comes with full text by default
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_tsquery > 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 wide range of data types, even for NoSQL workloads (without 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
Indexing Enterprise features
Various index implementations >Who many index types does PostgreSQL provide by default?
7! B-Tree (balanced tree)
GiST (generalized search tree)
SP-GiST (space partitioned gist)
GIN (generalized inverted index)
BRIN (block range index) Hash
Bloom
20.02.2020 The elephant in the enterprise Page 45
Transactional DDL Enterprise features
In PostgreSQL DMLs as well as DDLs are 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
Extensions Enterprise features
PostgreSQL is extensible by 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
Extensions Enterprise features
The PostgreSQL extension network > https://pgxn.org/
> Countless extensions for various use cases
20.02.2020 The elephant in the enterprise Page 48
Procedural languages Enterprise features
How many options do you have for server side programming? > 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 of those can be used to write functions & triggers
20.02.2020 The elephant in the enterprise Page 49
JIT Enterprise features
Just-in-time compilation (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 The elephant in the enterprise Page 51
The elephant in the enterprise 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
The elephant in the enterprise Beyond community PostgreSQL
EnterpriseDB > 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
The elephant in the enterprise Beyond community PostgreSQL
Citus Data, now part of Microsoft 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
The elephant in the enterprise Beyond community PostgreSQL
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
The elephant in the enterprise Beyond community PostgreSQL
FUJITSU Enterprise Postgres > 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
The elephant in the enterprise 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
The elephant in the enterprise Beyond community PostgreSQL
YugabyteDB > 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 The elephant in the enterprise Page 59
Getting support Support policies
Usually, there is one major release every 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
Community Support Getting support
General PostgreSQL related questions > pgsql-general@postgresql.org
Performance related PostgreSQL questions > pgsql-performance@postgresql.org
Many other lists for specific areas > https://www.postgresql.org/list/
But, before you post 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
Community Support Getting support
The PostgreSQL wiki > https://wiki.postgresql.org/wiki/Main_Page
Planet PostgreSQL > https://planet.postgresql.org/
PGTune (to start with the PostgreSQL configuration) > https://pgtune.leopard.in.ua/#/
20.02.2020 The elephant in the enterprise Page 63
Best practices The elephant in the enterprise
PostgreSQL is not Oracle nor is it MS SQL Server nor is it MySQL/MariaDB >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 practices The elephant in the enterprise
Even if PostgreSQL is easy to setup and to get started with >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 practices The elephant in the enterprise
The operating system needs to be prepared for PostgreSQL > 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 standard deployment for all this! > You automate your stuff, don't you?
20.02.2020 The elephant in the enterprise Page 66
Best practices The elephant in the enterprise
Decide on your deployment procedures >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 The elephant in the enterprise Page 68
Conclusion
PostgreSQL can pretty much handle any workload
Choosing the right toolset around PostgreSQL is essential
Make yourself familiar with PostgreSQL wordings and features
There are many forks/extensions of PostgreSQL for various requirements
(Open Source) Communities are driven by innovation, not financial aspects > Transparency and open discussions are key
20.02.2020 The elephant in the enterprise Page 69
Conclusion
Do not fear to become part 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 the documentation and test, test, test > There are many features not available in other systems
>Other features might be missing but there are workarounds for sure
top related