Thread: Which database?

Which database?

From
Kaarel
Date:
Hi

I am fairly new to databases, but my boss has assigned me to choose a
database system for our company. I know of Oracle databases but they are
way too expensive so it's not an option for us. I did some research and
came up with two free database systems that seemed to be more popular -
MySQL and PostgreSQL. The problem is that I can't make a decision
between the two because I have practically no experience. Some say MySQL
is easier to use, some say PostgreSQL is more robust etc. Most articles
seem to celarly favor one or the other. It is very hard to find an
objective comparison. And I have to explain my boss why I chose either
one. So perhaps some of you can give me some reasons why to choose MySQL
over Postgre.

Thanks,
Kaarel
kaarel@future.ee


Re: Which database?

From
Ewald Geschwinde
Date:
Kaarel wrote:

> Hi
>
> I am fairly new to databases, but my boss has assigned me to choose a
> database system for our company. I know of Oracle databases but they
> are way too expensive so it's not an option for us. I did some
> research and came up with two free database systems that seemed to be
> more popular - MySQL and PostgreSQL. The problem is that I can't make
> a decision between the two because I have practically no experience.
> Some say MySQL is easier to use, some say PostgreSQL is more robust
> etc. Most articles seem to celarly favor one or the other. It is very
> hard to find an objective comparison. And I have to explain my boss
> why I chose either one. So perhaps some of you can give me some
> reasons why to choose MySQL over Postgre.
>
> Thanks,
> Kaarel
> kaarel@future.ee
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Depends on your needs
Mysql for smaller things.

Postgresql for bigger projects.
Stored procedures, triggers.
Can handle big amounts of data (1 Billion I have seen ).
It's more ansi compliant than mysql.

The conclusion is: if you have a small non critical applikation that
have only selects you better go with mysql.
But if the thing will get bigger you will get to it's limits.

This problem you don't have with postgresql

Regards Ewald Geschwinde




Re: Which database?

From
"Merlin Moncure"
Date:
Kaarel wrote:
Hi

I am fairly new to databases, but my boss has assigned me to choose a
database system for our company. I know of Oracle databases but they are

way too expensive so it's not an option for us. I did some research and
came up with two free database systems that seemed to be more popular -
MySQL and PostgreSQL. The problem is that I can't make a decision
between the two because I have practically no experience. Some say MySQL

is easier to use, some say PostgreSQL is more robust etc. Most articles
seem to celarly favor one or the other. It is very hard to find an
objective comparison. And I have to explain my boss why I chose either
one. So perhaps some of you can give me some reasons why to choose MySQL

over Postgre.

Thanks,
Kaarel
kaarel@future.ee
---------------------------------

Well, posting on the advocacy list as you did, you should not expect too
many pro-mysql statements here.  However, I have worked with both
databases and I will do my best to give a fair comparison.

The short answer:
I would use mysql if:
1. I needed to deploy on windows with a simple installation (note:
changing soon!)
2. I expected a massive load on a mostly static database and planned to
use replication.  Postgres is also getting replication soon, but several
months away yet.
3. I planned to use some tools which were mysql specific.
4. My data requirements are not overly complex.
5. My database is not critical to my daily operations (to be fair: many
high profile sites use mysql to back their database, but this rule
applies to the average developer, at least, it applied to me).

Otherwise I can tell you that postgres is a better database for most
applications.  It is has a much richer feature set and is highly
configurable.  You will not be unhappy if you choose postgres, I can
promise you that.  Also, if you are learning database programming
postgres has better procedural support and good constraints support.
These features are very good because they promote good database design.
Honestly, unless you have some pretty stiff requirements either database
will probably work and thus becomes a matter of taste.  However, unless
you meet the criteria above, there is not good reason not to choose
postgres.

Regards,
Merlin





Re: Which database?

From
Jean-Michel POURE
Date:
Dear Kaarel,

> I am fairly new to databases, but my boss has assigned me to choose a
> database system for our company.

If you are learning databases (for example if you do not know the difference
between a table and a view), you may choose solutions like MySQL or MS
Access. Personnaly, I have been using MySQL during 3 months when learning SQL
and then I switched to PostgreSQL because of MySQL inability to parse complex
queries and its poor ODBC interface.

On the converse, if you are looking for a comprehensive business-oriented
solution, you may consider using a bundle of PostgreSQL database server,
pgAdmin3 graphical interface and PhpPgAdmin 3.0 web interface.

Downloading and testing is easy:

- PostgreSQL 7.3.2 : ftp://ftp.postgresql.org/pub/binary/v7.3.3/RPMS
- pgAdmin3 snapshots (Win32 and GNU/Linux GUI) : http://snake.pgadmin.org
- PhpPgAdmin 3.0 RC-1: http://phppgadmin.sourceforge.net/

Also, PostgreSQL is well documented:
- http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=index.html
- http://techdocs.postgresql.org/

Many projects are centralized at GBorg - http://gborg.postgresql.org

Don't look for complicated things: PostgreSQL main advantage is to answer
***all*** your needs at once, even the most advanced ones (views, triggers,
server-side languages, multi-byte support, etc...).

Cheers,
Jean-Michel



Re: Which database?

From
Jean-Louis Leroy
Date:
Jean-Michel POURE <jm.poure@freesurf.fr> writes:

> Personnaly, I have been using MySQL during 3 months when learning
> SQL and then I switched to PostgreSQL because of MySQL inability to
> parse complex queries and its poor ODBC interface.

Hi Jean-Michel :-)

Could you elaborate on this? Both on the ML and at the LSM?
--
Jean-Louis Leroy
Sound Object Logic
http://www.soundobjectlogic.com

Re: Which database?

From
Jean-Michel POURE
Date:
On Tuesday 10 June 2003 15:47, Jean-Louis Leroy wrote:
> Could you elaborate on this? Both on the ML and at the LSM?

MySQL has several well-known technical limitations, but I wron't discuss them
during a presenation at LSM for a question of respect. MySQL is free software
and as such I respect it.

I can only speak in public about PostgreSQL, not MySQL.

Cheers,
Jean-Michel


Re: Which database?

From
Kaarel
Date:
> 5. My database is not critical to my daily operations (to be fair: many
> high profile sites use mysql to back their database, but this rule
> applies to the average developer, at least, it applied to me).

Could You please explain this in more detail. Specifically I am afraid I
do not understand what do You mean by "backing a database", what is
considered not critical and why does this rule apply to the average
developer (why not for advanced developer).

Kaarel
kaarel@future.ee


Re: Which database?

From
Kaarel
Date:
> Depends on your needs
> Mysql for smaller things.

Does it mean MySQL is faster or more reliable with smaller amounts of data?

Kaarel
kaarel@future.ee


Re: Which database?

From
Bruce Momjian
Date:
Kaarel wrote:
> > Depends on your needs
> > Mysql for smaller things.
>
> Does it mean MySQL is faster or more reliable with smaller amounts of data?

I think it is faster for simple, read-only (SELECT) queries on small data
sets.

Large data set, complex WHERE clause, non-SELECT, PostgreSQL is probably
faster --- plus we have more of the commercial database features.

Think MySQL as MSAccess, PostgreSQL as Oracle.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Which database?

From
Ewald Geschwinde
Date:
Bruce Momjian wrote:

>Kaarel wrote:
>
>
>>>Depends on your needs
>>>Mysql for smaller things.
>>>
>>>
>>Does it mean MySQL is faster or more reliable with smaller amounts of data?
>>
>>
>
>I think it is faster for simple, read-only (SELECT) queries on small data
>sets.
>
>Large data set, complex WHERE clause, non-SELECT, PostgreSQL is probably
>faster --- plus we have more of the commercial database features.
>
>Think MySQL as MSAccess, PostgreSQL as Oracle.
>
>
>
This explanation hits mine with 100%
regards Ewald Geschwinde



Re: Which database?

From
"Merlin Moncure"
Date:
Kereel wrote:

Could You please explain this in more detail. Specifically I am afraid I

do not understand what do You mean by "backing a database", what is
considered not critical and why does this rule apply to the average
developer (why not for advanced developer).
_____________
From the developer's point of view, Mission critical means you get fired
if it stops working :)

More seriously, in a practical sense mission critical usually means
small amounts of downtime or data loss are not catastrophic.  A vast
array of web based applications fit under this category, such as forums,
log books, document serving, that type of thing.
I made a distinction between development and 'advanced development' to
try and explain the high profile systems like Slashdot that run mysql,
which (based on my experience with the database), is something I don't
really understand.  Also, they have replication and a full text
searching suite which are nice for web apps.

If you are working any type of financial transactions, use postgres, and
become familiar with transactions (database, not financial).

Merlin


Re: Which database?

From
Josh Berkus
Date:
Merlin,

> really understand.  Also, they have replication and a full text
> searching suite which are nice for web apps.

We don't have replication yet, but we do have full text searching ... in fact,
two different implementations.

MySQL is an excellent match for Slashdot because they would rather silently
lose 5% or even 25% of comments than have the site slow down for all users.
MySQL, with its "performance over everything" design strategy, accomodates
this.

On the other hand, if you were running a scheduling/financial/HR/inventory
application, silently losing 5% ... or even 1% ... of transactions under
heavy user load would not be acceptable ... better to have the database shut
down.  Then you use PostgreSQL.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Which database?

From
Robert Treat
Date:
On Wed, 2003-06-11 at 12:30, Josh Berkus wrote:
> Merlin,
>
> > really understand.  Also, they have replication and a full text
> > searching suite which are nice for web apps.
>
> We don't have replication yet, but we do have full text searching ... in fact,
> two different implementations.
>
> MySQL is an excellent match for Slashdot because they would rather silently
> lose 5% or even 25% of comments than have the site slow down for all users.
> MySQL, with its "performance over everything" design strategy, accomodates
> this.
>

To be fair you'd have to point out they made this decision "a long time
ago" back when mysql was faster than postgresql, and they could use
mysql's replication feature to "scale up" to the number of users they
needed to support.

These days that gap is virtually nonexistent especially when you
consider how much more efficient their code could be if they didn't have
to write all the transaction/data-checking/table-joining logic into
their code and could just let the database do it. Still I can't blame
them for not wanting to rewrite their app.

Along these lines, "The Register" (theregister.com) just decided to
switch their site to bricolage, which is PostgreSQL based.


Robert Treat

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: Which database?

From
"Merlin Moncure"
Date:
Robert wrote

To be fair you'd have to point out they made this decision "a long time
ago" back when mysql was faster than postgresql, and they could use
mysql's replication feature to "scale up" to the number of users they
needed to support.

These days that gap is virtually nonexistent especially when you
consider how much more efficient their code could be if they didn't have
to write all the transaction/data-checking/table-joining logic into
their code and could just let the database do it. Still I can't blame
them for not wanting to rewrite their app.

Along these lines, "The Register" (theregister.com) just decided to
switch their site to bricolage, which is PostgreSQL based.


Robert Treat

___________________________________________________________


Yes, and as I've mentioned in other posts there are some nice things
about mysql besides just being fast.  I would prefer to use postgres for
almost any type of project, but that does not necessarily mean mysql is
bad at all tasks.  I still give the mysql team credit for building,
writing, and maintaining a wildly popular open source application.

Open source success stories are good for everyone involved, even in a
single contested market like database servers.  Economic success is not
zero-sum.  I'm glad to hear they were able to procure investment.  That
is one more brick in the foundation in what is widely considered to be
the future of software development.  17m$ goes a long way to convincing
middle managers and petty officials that there really is something in
this open source business.  Indirectly this helps postgres as well; I
see the database applications market as full of potential right now.

I prefer postgres not just for technical reasons but for the spirit and
people behind the project.  So long as the postgres team still has that
mojo it will still be a better database, even if it gets less notice in
the press.

Merlin



Re: Which database?

From
Jason Hihn
Date:
PostgreSQL. There is no question. I've used both (plus Informix). Stick to
open source databases! They place nice with a whole lot more things. Plus
PostgreSQL (PSQL) has I think the highest SQL Spec compliance.

MySQL is fast, provided you don't use transactions. It is a little more
friendly, but only because it makes a lot of assumptions. MySQL expects you
to verify your data before insert, PSQL doesn't. Triggers, operators, etc
are non-existent or experimental in MySQL. I don't think views exist either.
If you get a MySQL implementation and a PostgreSQL implementation, and
compare the two, you'll find similar speed over all, but the PostgreSQL
implementation will be more hardy.

If you're going to be basing your company (or a significant portion of it)
on a database, and you have to go between the two, go with PSQL!.

Just my $.02




> -----Original Message-----
> From: pgsql-advocacy-owner@postgresql.org
> [mailto:pgsql-advocacy-owner@postgresql.org]On Behalf Of Kaarel
> Sent: Tuesday, June 10, 2003 8:12 AM
> To: pgsql-advocacy@postgresql.org
> Subject: [pgsql-advocacy] Which database?
>
>
> Hi
>
> I am fairly new to databases, but my boss has assigned me to choose a
> database system for our company. I know of Oracle databases but they are
> way too expensive so it's not an option for us. I did some research and
> came up with two free database systems that seemed to be more popular -
> MySQL and PostgreSQL. The problem is that I can't make a decision
> between the two because I have practically no experience. Some say MySQL
> is easier to use, some say PostgreSQL is more robust etc. Most articles
> seem to celarly favor one or the other. It is very hard to find an
> objective comparison. And I have to explain my boss why I chose either
> one. So perhaps some of you can give me some reasons why to choose MySQL
> over Postgre.
>
> Thanks,
> Kaarel
> kaarel@future.ee
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>