SPECIAL INTEREST ACTIVITY
Comparison between MySQL and PostgreSQL for ease of
installation and administration from a layman’s perspective.
Introduction and B
Database management systems
are important for any business and today we find every
mom and pop business going online. I
this hands on activity
I am trying to evaluate
open source DBMS options MySQL and PostgreSQL from the point of view of a sma
(basically a business where there is minimal IT related
. Both these database are industrial strength databases and enjoy wide
following. Craigslist, Yahoo, Wikipedia, Flickr use MySQL and Genetec
h, BASF, Skype
. Any E
commerce business irrespective of its size needs to store
data about 1) Products offered 2) Customer related information 3) Order status 4)
Visitor browser behavior etc. also sometimes there is a need to embed a database in
the software solu
tions that the company is offering.
In this both the databases were installed on machine/s with Windows OS
1) XP Home Edition and 2) Vista Professional.
Tables were created and the experience was compared from point of view of an
cation similar to tumble book.com
In my position paper “
MySQL vs PostgreSQL for a Small Scale E
I came to a conclusion that there is not much to differentiate in these two options based
on technical issues ( w.r.t. our application) how
ever issues like ease of installation,
ease of administration, availability of support in case we run into trouble are going to
count a lot especially when you are not an IT person.
So to get first hand feel of what can happen as far as our application is
installed both the databases on my laptop
having Windows Vista Business and on my
desktop having Windows XP Home Edition.
Tables were created and I tried to store the swf files as blob type in tables. Now there
are arguments in favor of this a
nd against this but instead of getting into those details I
decided to save blobs in tables.
The max size of blob that is to be stored is 12MB.
HANDS ON COMPONENT
Download MySQL and MySQL Administrator
e creation and data insertion
1) CLICK ON “Catalogs” IN MYSQL ADMINISTRATOR
2) RIGHT CLICK ON “test” SELECT “Create New Schema”
) Enter table name say “myfirsttable” and with “columns and indices” tab selected add
columns say “col1” select a
ppropriate data type.
Click on “Apply Changes”
6) Click on “Execute”
7) Click on “Tools” Select “MySQL Query Browser” Click it
8) Select the table from Myfirstschema and run sel
ct * query….by clicking on the
9) To add data selec
t field and click “Edit”
10) Click on “Apply Changes”
11) To add data in LONGBLOB field
In MySQL Administrator Select “Startup Variables”, Select “Advanced
and adjust “Max Packet Size” adjust according to need and Click
Select file and click “Open” and click on “Apply Changes”
es encountered in installation
For the records after installing both the databases on XP Machine I had to format
the disc a
s CPU Usage was constantly 100%
Now this may or may not be
related to i
on Windows Vista Business
was possible only after turning
User Account Control Off.
MySQL Administrator is an excellent tool that allows you easy creation of tables, data
User administration etc.
Download PostgreSQL and pgAdminIII
CREATION OF TABLES IN PostgreSQL
Open pgAdminIII and double click on selected database server, enter password on the
popped up window and click OK
Now to insert data in our newly created table Click on “Tools” select “Scripts” and select
involved in download and table creations
There are so many options are available for downloading that it is quite confu
to decide the right download.
of “blob” pgAdminIII does not provide any sort of drag
and drop way I tried using Navicat also but that also offers no convenient way of
doing blob insertion
Installation of PostgreSQL along
with pgAdminIII was an easy job. As far as table
creation and User administration was concerned; for normal tables involving the usual
data types like integer, varchar; table creation and data insertion was quite simple,
however since we have to
files in our table
there is no convenient way to do it
and when I say convenient I mean sort of drag and drop. The other tool Navicat for
PostgreSQL also do not provide any convenient way to insert blobs in a table.
User community and available resources.
If we search for word PostgreSQL in Title
of book at Milner we get 3 results
A similar search for MySQL returns 35 results
Now this cannot be called as true reflection of support available for each database but
can be considered as a good indicator of
Both the databases are
easy to install and administer but if we must compare, then
MySQL is a bit more convenient.
For our application MySQL works out better
thanks to MySQL Administrator
actually create a table
which on extraction from the table
displayed the content as expected.
Despite trying for a long time I am not able to find a convenient way to insert blobs in
postgreSQL and I will continue my search in this regard. As far as MySQL is concer
I encountered no such issue which made me search the web for a solution.
So if I have to pick a choice at this juncture MySQL is a clear winner.