elephants vs. dolphins: comparing postgresql and mysql for use in the dod

Elephant vs. Dolphin

Comparing PostgreSQL and MySQL in the DoD

James Hanson

[email protected]

[email protected]

@jamey_hanson

Freedom Consulting Group

http://www.freedomconsultinggroup.com

14-Ju1-2015

What are the similarities and differences between

PostgreSQL and MySQL?

Highlight features that support projects delivering

capabilities in our environment

Elephant vs. Dolphin in the DoD

PostgreSQL Migration Team 14-Jul-2015

2

vs.

They are both Relational Database Management

Systems (RDBMS)

Store data in tables and views

Relate tables with foreign keys that are automatically

updated with triggers

Interact with data using ANSI SQL

Support procedural programming language(s)

Support "transactions"

COMMIT statement to make a permanent change

Support multiple, concurrent connections working with the

same data

How are PostgreSQL and MySQL similar?


3

On-line ("hot") backups and database exports

Full or partial replication from Master to Standby

Including “hot” Standby for reporting

Multiple Standby with automatic failover

Clustering support with virtual IP interface

Command-line interface that can run scripts

Bulk load tools for OS files

Connection pooling tool(s)

Both have the expected suite of tools


4


5

Both have a GUI


6

Both have a GUI

Free and “freemium” versions

PostgreSQL has an active FOSS development

community.

MySQL FOSS development forked to MariaDB

EnterpriseDB only supports PostgreSQL.

Oracle also supports (protects) Oracle RDBMS

Both are included in CentOS / Fedora / RHEL

Similar distribution and business models


7

Dr. Google has multiple, conflicting performance

comparisons. An (over)simplification …

MySQL is considered to be slightly faster in simple

(i.e. no join) queries

PostgreSQL is faster in queries with joins and more

complex transactions

Similar performance


8

Magic Quadrant for Operational Database

Management Systems. http://www.gartner.com/technology/reprints.do?id=1-

23A415Q&ct=141020&st=sb

What does the Gartner say?


9

http://www.gartner.com/technology/reprints.do?id=1-23A415Q&ct=141020&st=sb






10

Strategic planning assumptions

By 2017, the "NoSQL" label will cease to distinguish

DBMSs, which will reduce its value and result in it falling

out of use.

By 2017, all leading operational DBMSs will offer multiple

data models, relational and NoSQL, in a single platform.

EnterpriseDB (i.e. PostgreSQL) has higher

Completeness of Vision and Ability to Execute



11

The MySQL™ software delivers a very fast, multi-

threaded, multi-user, and robust SQL (Structured

Query Language) database server. (https://dev.mysql.com/)

MySQL implements a subset of ANSI SQL and features

that are used by simple and (typically) ORM-based

applications.

PostgreSQL delivers a fast, robust implementation of

the ANSI SQL standard with NoSQL*, geospatial,

row-level-security and partitioning support. It is also

extensible with multiple programming languages.

* JSON, XML, key-value-pair, limited graph queries and natural

language full-text search.

Different missions and visions:


12

https://dev.mysql.com/

https://dev.mysql.com/

Why the passion?

13

MySQL PostgreSQL

Oracle owns MySQL, which effectively means it will never

have a feature set that competes with Oracle RDBMS.

Not everyone wants their database to support Larry’s

private island and America’s Cup

Business reasons …


14

PostgreSQL’s mission is bigger than MySQL’s and

so it does more

Full implementation of ANSI SQL (vs. MySQL subset)

Native NoSQL support including JSON, XML, key-value-

pair and recursive (graph) queries.

Only Oracle RDBMS has these.

GeoSpatial support with PostGIS

Support for common language procedural extensions

Java, Python, Perl, Program/R

Application-customized data types including IPv4/6,

range, array and ENUM

Technical reasons …


15

Analytic functions (a.k.a. Windowing functions)

Calculate moving average and similar statistics that can

only be done in the application tier with MySQL

How does that impact my mission?


16

Find events within a user-select box, IP-location,

nearest event(s), event(s) within radius

Use internal mapping and related services … for free

GeoSpatial data


17

Validate IP’s on input (hard to do with IPv6)

Find IPs within subnet (CIDR notation) or range

Associate array’s of ports and/or MAC addresses

with IP(s)

PostgreSQL lets you think like a router when working

with IPs rather than thinking like a parser

IPv4 / 6 data types (+ MAC)


18

Beyond the Relational model for …

Large data sets that need to be stored efficiently

Focus on queries, relationships and analysis

(vs. transactions)

Read-only data

Does any of this sound applicable to our domain?

Arrays


19

Similar to Apache SOLR, but automatically updated

and part of the database

Not as fast or full-featured

Ranked results base on search-term frequency

weighted for document size. Similar to TF/IDF.

Highlighted surrounding phrases

Full-text search w/ranked results


20

Leverage the skills your team has today

(any) Procedural language support


21

Per Gartner – “By 2017, all leading operational DBMSs will

offer multiple data models, relational and NoSQL, in a single

platform.” (PostgreSQL does today)

JSON support similar to MongoDB, but integrated with the

rest of your data

NoSQL support … JSON & XML


22

HSTORE data type stores key-value pairs as a column in a

relational table

NoSQL support … key value pair


23

PostgreSQL recursive* queries support graphs. * “recursive”

is the ANSI-SQL term.

Similar functionality to Neo4J and SPARQL, but on smaller

data sets

NoSQL support … graph data


24

PostgreSQL and MySQL are both good products for

simple, pure-relational applications using a subset of

ANSI-SQL

They have similar performance & tools

Both are free and “fremium”

But MySQL will never have the features of Oracle RDBMS

… because Larry Ellison won’t allow it.

MariaDB has FOSS active development, but it is a fork – not

MySQL

Summary


25

Consider PostgreSQL if, over the lifespan of your project,

you ever envision …

Using JSON, XML, key-value pair or other NoSQL data

Validating, storing or analyzing IPv4/6 or MAC addresses

Points on a map, selecting records based on a bounding-box,

searching for nearby/far-away records or looking for

communication that crosses a geographic boundary

Performing moving averages, Excel pivot-table queries or

advanced statistics

Leveraging your team’s Java, Python, Perl, TCL or Program /R

skills

Using (simple) graph analysis to find a path between records

Summary


26

Contact any member of the PostgreSQL Migration Team

James Hanson, Chris Fort and Russell Janusz

Connect to our YUM repository (or GUI installer) … and go

Contact Corporate Hosting … and let them host your

PostgreSQL database and VM

Contact Aaron Pestel or James Hanson for capability and

license information on EnterpriseDB

OK great … how do I move forward?


27

Are

there

any

Questions

or

follow up?

PGConf US, NYC 26-

Mar-2015

28

elephants vs. dolphins: comparing postgresql and mysql for use in the dod

Documents

dod postgresql migration

join queries postgresql

freemium versions postgresql

mysql similar

multiple standby

mysql software

oversimplification mysql

multiple data models