developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0

disturbedoctopusData Management

Nov 27, 2012 (4 years and 10 months ago)

202 views

2006-01-14
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
1
LiTH
RUT - developers handbook
9.74 Choosing PostgreSQL or
MySQL v 2.0
Johan Millving
SUMMARY
The primary goal of this document is to describe the pros and cons of the
two database management systems (DBMS’s) PostgreSQL and MySQL
helping you in deciding which of the two is a suitable choice for your
project.
The first part is written as a discussion wheather PostgreSQL or
MySQL will fit the task.The second part is focused on howyou get going
with each DBMS.
This RUT covers solutions availible as open source.
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
Field of application
2
1 Field of application
This process description is meant to be used when you need a database and
want to find out whether PostgreSQL or MySQL is the optimal choice for
your project.
Regardless of which systemyou choose this RUT will then guide you through
the first steps in setting it up and working with it. There is also overview of
some availible tools to make PostgreSQL and MySQL easier to work with.
2 Prerequisites
The purpose of this process is to compare two different DBMS’s given the
condition that you have already decided to use some sort of stand-alone data-
base systemfor your project,but the decision for which one to use has not yet
been made. It is not a complete description covering all possible choices but
rather a comparison between the two most common open-source systems.
3 Realization
This process is meant to assist you to make a well grounded decision whether
PostgreSQL or MySQL is the database management system (DBMS) that
best suits your needs.
3.1 Overview

Identify the needs of the project
Aims to determine which of the DBMS’s is most suitable for the project.

PostgreSQL installation and configuration
Describes how to install and setup your first database with PostgreSQL.

MySQL installation and configuration
Describes how to install and setup your first database with MySQL.

Using PostgreSQL
This part introduces some tools that are useful when dealing with Post-
greSQL.

Using MySQL
This part introduces some tools that are useful when dealing with MySQL.
3.2 Detailed description
Identify the needs of the project
The first question you should ask yourself is under which conditions your
software will be distributed. This will in it self have some effect on which
DBMS to use, since both PostgreSQL and MySQL are released under differ-
ent licence agreements. MySQL has a commercial licence and a free open-
source (GPL) licence. If you plan on using the GPL licence of MySQL you
must agree to release your own software under GPL as well, which amongst
Realization
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
3
other things means that you must make your source code availible to the
public. If this is not an option you must pay for the commercial licence.
PostgreSQL on the other hand is released under the Berkley licence (BSD)
which does not put any such restrictions on your source code as long as a
copy of the BSD licence text is included with it.
For further information regarding the licence agreements, see [MySQL,
2006] and [PostgreSQL, 2006] respectively.

Speed or data integrity?
Historically, there has for the most part been a consensus that Post-
greSQL was running considerably slower than MySQL. This has since
changed and PostgreSQL have gained a lot of speed starting with the ma-
jor version 7 release and some claimit nowto be even faster than MySQL.
To know for sure which database that suits your application you need to
perform benchmarks on the type of data that you will be storing. Post-
greSQL is also said to be better with a lot of concurrent reads and writes,
so you should consider if this situation applies to your application.
MySQL is very fast on simple SELECTs,i.e what websites usually consist
of.
Every database administrators worst nightmare is to get inconsistent da-
ta. If you for example have two tables where the data relates to each oth-
er,i.e.a contacts application.Table Astores names of contacts and table B
stores one or more telephone numbers for each contact in A. If you don’t
enforce referential integrity by using foreign keys (FK) it is possible to de-
lete a contact in table A but there will remain telephone numbers that are
now orphaned and can’t be used for anything useful. PostgreSQL have al-
ways supported FK.They also exist today in MySQL but you must use the
InnoDB table type.

Platform support.
Both PostgreSQL and MySQL has strong support for and has been devel-
oped for the UNIX world and runs stable on most UNIX variants. Post-
greSQL did not have Windows support until the latest version 8,and still
doesn’t run under Windows 95/98/ME or NT4. MySQL however can be
run under any of these platforms.
Early versions of PostgreSQL (6 and under) was known for it’s not so at-
ractive stability with randomdisconnects,core dumbs and memory leaks.
Since version 7 and up this has been improved considerably but you might
want to consider that MySQL has never had stability issues to such a
large extent.

Large objects
In PostgreSQL large objects has to be treated with special functions when
creating, reading and writing them to the database. PostgreSQL also
treats large objects on their own when storing them.This makes for some
complex overhead computation when dealing with large objects in Post-
greSQL that doesn’t apply to MySQL.MySQL treats large objects like any
other table entry.

Advanced features.
PostgreSQL have a lot of advanced features that makes it an attractive
choice.
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
Realization
4
Views - With views you create a virtual table out of an SQL query or a
stored procedure. You can then do SELECTs on the view. This is used to
hide the complexity of the underlying tables.You can even attach triggers
to a view so it can handle INSERT and UPDATE commands.
Stored procedures (SP) - a SP is either a SQL query or a PL function that
is stored within the database. The advantage with a SP is that the query
can be pre-optimized and thus saving time at execution time. It can also
be used to build a API to the database so it’s possible to change the table
structure without rewriting the client application.
Procedural Languages (PL) - PostgreSQL supports a number of procedur-
al languages. You can for example run Python or Perl within your data-
base.

Conclusion
PostgreSQL has the advantage of more advanced functions and a more re-
lieable data integrity scheme where as on the other hand MySQL has the
advantage of speed and could possibly have the loss of advanced functions
turned to its favor.In the end it all depends on the demands on your appli-
cation combined with your personal preference.
MySQL could for example be more suited for a web based forum. Almost
only SELECT queries and there’s really no big harmif one post disappear
into a black hole.
PostgreSQL installation and configuration
If you’re using an operating system which have a packaging system (RPM,
deb) I highly recommend using that to install PostgreSQL.Not that it is hard
to install PostgreSQL from source. But your OS will be kept in a clean state
and you can easily upgrade or remove your installation.
Manual installation is covered in the file INSTALL in the packaged
distribution (http://www.postgresql.org/download/).
If you’re installation follows the standard PostgreSQL setup your systemwill
now have a user called postgres which is the user that the db runs as.With a
virgin installation that is the only user allowed to access the database. The
default configuration of PostgreSQL is to trust local users.If the current user
has an user in PostgreSQL the user is allowed access.The philosophy behind
this is if someone who shouldn’t have shell access on your server has it you’re
in bigger problems than to worry about unauthorized access to your
database.In order to create a database or a new user you have to become (in
unix use su,as it’s good practise not to let this user have a login) the postgres
user and then you can use two small helper application called createdb and
createuser
Create a user with “createuser -E -P niklas“. The -E flag means
encrypt the password and -P prompt for password. To see the other options
available type “createuser --help”.
To create a database you use “createdb -E LATIN1 -O niklas pumdb”.
The -E is used to set the character encoding to use LATIN1 which is needed
to get support for accented characters like the swedish åäö. If your database
Realization
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
5
is setup to use LATIN1 as default this isn’t needed.The -Ois to set the owner
to be the postgres user niklas. Otherwise the user running createdb will
become the owner.
If you want to remove the explicit trust of local users in PostgreSQL you have
to edit the file pg_hba.conf. At the end of this file there’s a row looking like
“local all all ident“, change ident to md5 and restart the database
server. To login to the database you must now use your password.
MySQL installation and configuration
The latest (open-source) version of MySQL can be downloaded from the
internet (http://dev.mysql.com/downloads/).
When installing on a Windows operating system the installer will
automatically install MySQL, start the server and secure the default user
accounts so you will pretty much be up and running at once. Similarly, the
easiest way of installing MySQL on a Linux systemwould be to use the RPM
packaging functionality.
On all platforms,your first concern would be that the initial accounts (root in
particular) in the grant tables have no passwords. If you do not assign
passwords this will allowanyone to access your database with root priviliges.
Using the mysqladmin program (included with the MySQL software) write
this at the command prompt:
shell> mysqladmin -u root password "pw"
shell> mysqladmin -u root -h host_name password "pw"
Where pw is your chosen root password and host_name is the name of the
host running your MySQL server.
To create your first database, enter the mysql shell and give the following
commands:
shell> mysql -u root -p
Enter password:******
mysql> CREATE DATABASE newdb;
Where newdb is the name of your new database.
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
Realization
6
Using PostgreSQL

psql
psql is the client program that ships with PostgreSQL. It’s a text-only
command line application. Despite that it’s really useful.
To connect to the newly created database you use “psql -U <username>
-d <database>“ and it’ll ask for your password.
unix:$ psql -U opassist -d opassist
Password:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
opassist=> \?
The examples are fromPUM1 04/05s project.,which used PostgreSQL for
as its database.
You are now ready to begin using your database. How to create and
change tables is out of the scope of this RUT.Fundamentals of database
systems (Elmasri & Navathe, 2000) is a good book on the subject.
The command I find the most useful in psql is \d. It shows which tables
you have and howthey look.It’s also used to get information on views and
stored procedures.
opassist=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+----------
public | assigned_to_module | table | opassist
public | categories | table | opassist
public | categories_id_seq | sequence | opassist
public | demand_spec | table | opassist
public | demand_spec_id_seq | sequence | opassist
public | has_permissions | table | opassist
...
(21 rows)
opassist=> \d assigned_to_module
Table "public.assigned_to_module"
Column | Type | Modifiers
--------------+---------+-----------
time_post_id | integer |
module_id | integer |
Foreign-key constraints:
"$1"FOREIGN KEY (time_post_id) REFERENCES time_posts(id)
"$2" FOREIGN KEY (module_id) REFERENCES modules(id)
Realization
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
7
You can also write sql directly on the prompt.
opassist=> select * from modules;
id | name | start_date
----+------+------------
3 | ett | 2004-12-05
1 | nr 1 | 2005-01-01
2 | nr2 | 2005-01-01
(3 rows)
Another useful command is \i wich reads instructions from a file.
The rest of the options are available with the help command \?.

phpPgAdmin
phpPgAdmin (http://phppgadmin.sourceforge.net/) is a web based inter-
face for PostgreSQL. It’s written in PHP and therefore require a webserv-
er with PHP to run.
The strength with phpPgAdmin is that it have GUI and it’s very easy to
look over the content in your tables.
The application is rather easy to use so it doesn’t need any further expla-
nations.

pgAdmin III
pgAdmin III (http://www.pgadmin.org/) is a standalone GUI application
that runs on Linux, FreeBSD and Windows. It’s used in a similar way as
phpPgAdmin.
Using MySQL

mysql
Mysql is the command-line client for executing SQL statements interac-
tively or in batch mode.After starting the programyou could for example
use the following commands to connect to a database and execute a SE-
LECT query.
shell> mysql -u username
mysql> SHOW DATABASES;
+----------+
Database
+----------+
mysql
test
mydata
+----------+
mysql> USE mydata;
Database changed
mysql> SELECT * FROM mytable;
Where mydata is the name of your database and mytable is a table in that
database.

mysqladmin
Mysqladmin, as the name suggests, is an administrative program which
you can use to add,delete users and/or grant users access to specific data-
bases.
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
Results
8
4 Results
This chapter shows which kind of results you will get from following this
process description.
4.1 Products
The product of this process is an installed and functional instance of either
PostgreSQL or MySQL.
4.2 Product templates
Heading is not applicable for this process.
5 Templates and forms
Heading is not applicable for this process.
6 Verification of results
Since the objective of this RUT is to have a working but empty database run-
ning the results are met when such exist.
7 Examples with explanations
Heading is not applicable for this process.
8 Solutions to common problems
Heading is not applicable for this process.
9 Adjustment to the PUM course
No adjustments done.
10 Measurement of the process
10.1 Resource measurement
10.2 Product measurement
The quality of a database is foremost a function of how its tables are de-
signed. I.e. efficient use of indexes, normalisation etc. If the database engine
History of the process
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
9
you are using fulfills your needs without the need for workarounds you have
a good quality on your project.
10.3 Forms for collection of data
Heading not applicable to this process.
11 History of the process
12 Changes not yet attended to
A possible addition to this RUT is a section which gives examples of the
advanced features (views, triggers, functions, etc) found in PostgreSQL.
A checklist containing featuring the advanced features in PostgreSQL. If
none is check there’s not a need to use PostgreSQL, atleast not for its
advanced functions.
13 References
13.1 Method description
[MySQL, 2006] - http://www.mysql.com
[PostgreSQL, 2006] - http://www.postgresql.org
[Axelsson, 2003] - RUT - developers handbook 7.7 Design of relationdatabas-
es
[Elmasri & Navathe, 2000] - Fundamentals of database systems, Third edi-
tion, ISBN 0-201-54263-3
13.2 Method evaluation
Heading not applicable to this process yet as it’s in its first version and ha-
ven’t been used yet.
Version
Date
Editor
Changes
1.0 2005-01-10 Niklas Alberth Document created.
2.0 2006-01-11 Johan Millving Focus changed from Introduc-
tion to PostgreSQL to compari-
son between PostgreSQL and
MySQL. All text regarding
PostgreSQL from previous ver-
sion is left unchanged and text
regarding MySQL has been
added.
RUT - developers handbook 9.74 Choosing PostgreSQL or MySQL v 2.0
References
10
13.3 Internal comments
The original author of this RUT was slightly biased towards using Post-
greSQL.