Postgresql vs MySQL

Rails
21

On 17/11/2005, at 12:01 PM, Bill K. wrote:

On 11/16/05, Phillip H. [email protected] wrote:

ACID (ACID - Wikipedia is a fairly good outline).
The only real ACID backend MySQL has (InnoDB, nothing else is really
ACID) has recently been purchased by Oracle, who aren’t exactly best
mates with MySQL AB

That doesn’t sound good. What does this mean for the future of the
InnoDB engine? It’s open source, so they can’t remove it, but I
guess the support can substantially decrease.

As it’s open source I’m assuming someone else will maintain it, but
there wasn’t a huge development team beforehand. MySQL AB will
probably hire someone.

Not really true any longer. With some performance tuning - eg
appropriate indexes and cache sizes, PostgreSQL is supposed to be
pretty close to MyISAM’s speed, and that’s with ACID compliance.

Have there been any benchmarks published? Given my resources and
time, though, the most applicable benchmark would be performance
using standard configurations with simple modifications only – no
heroic reconfigurations. That’s also why I like MySQL. It has a
reputation for being fast and reliable out of the box.

Maybe I should do some, I have both MySQL and PostgreSQL on all my
machines. They’re not amazing machines, but I’ll see if I can get
some figures if I have time.

22

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 16, 2005, at 3:01 PM, Bill K. wrote:

Have there been any benchmarks published? Given my resources and
time, though, the most applicable benchmark would be performance
using standard configurations with simple modifications only – no
heroic reconfigurations. That’s also why I like MySQL. It has a
reputation for being fast and reliable out of the box.

All the AR-supported databases are fast and reliable out of the box.

All of the open-source options are very easy to set up and configure,
too.

Times (in bogus-units) for one run of the Active Record test suite:
1670 SQLite 2.8.16
1640 SQLite 3.2.7
1380 MySQL 5.0.15
1260 PostgreSQL 8.1

The typical MySQL complaints do not affect the everyday MySQL user
and are addressed in MySQL 5 nonetheless.

So, wherever you turn, you won’t go wrong. Don’t obssess over it.

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDe8hOAQHALep9HFYRAsqqAJ0TfRu0QrsiOSUbjs16nptqoadWugCgyWW0
95LmGhYJNfgGGneYYmgS0RM=
=P6A3
-----END PGP SIGNATURE-----

23

On Wed, 2005-11-16 at 23:33 +0100, Jeroen H. wrote:

I much prefer postgresql for all sorts of reasons. The only downside I
datetime values like 0000-00-00 00:00:00 and the whole set and enum
things are just ugly.

For what its worth… the largest record store (besides Amazon) is using
PostgreSQL… oh and some little ruby project called Rails.

I interviewed Jeremy (bitsweat) and Derek Sivers and asked them for
their thoughts on this…

-Robby

24

On 11/16/05, Robby R. [email protected] wrote:

I interviewed Jeremy (bitsweat) and Derek Sivers and asked them for
their thoughts on this…

Yeah, that was a good read. Sivers mentioned data-integrity issues as a
reason for the switch, and I got the impression it was because of the
numerous CDBaby subsystems (acct mgmt, audio backend, etc) that access
his
database independently of the web app. Do you know the particular
reasons?
Was it an “Application Database” versus “Integrated Database” issue, and
PostgreSQL is especially suited for the latter?

You also mention that as demand increases, MySQL needs more CPU relative
to
PostgreSQL. That seemed counter intuitive to me, given that MySQL has
presumably simpler database engines. Is the load handling related to use
of
pgsql’s stored procedures and the like?

-Bill

25

On Thursday 17 November 2005 00:01, Bill K. wrote:

On 11/16/05, Phillip H. [email protected] wrote:

ACID (ACID - Wikipedia is a fairly good outline).
The only real ACID backend MySQL has (InnoDB, nothing else is
really ACID) has recently been purchased by Oracle, who aren’t
exactly best mates with MySQL AB

That doesn’t sound good. What does this mean for the future of the
InnoDB engine? It’s open source, so they can’t remove it, but I guess
the support can substantially decrease.

The real problem appears to be that MySQL AB is dual-licensing MySQL. In
addition to the GPL version they’re offering a commercial one. To be
able to do this with the included InnoDB, they in turn need a
commercial license of the latter, which is dual-licensed as well.

AFAIR, the commercial licensing terms MySQL AB has for InnoDB are to be
negotiated again some time next year. With Oracle being the new owner,
the deal might be worse than before. This is speculation, of course…

Michael

–
Michael S. Airtight arguments have
mailto:[email protected] vacuous conclusions.
Michael Schürig | Sentenced to making sense --A.O. Rorty, Explaining Emotions

26

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 16, 2005, at 3:40 PM, Bill K. wrote:

that MySQL has presumably simpler database engines. Is the load
handling related to use of pgsql’s stored procedures and the like?

I don’t know about > CPU as DB size increases (doesn’t make sense to
me), but PostgreSQL does do far better query planning. So if you do
complex queries involving multiple joins, subselects, or multiple
indexes, PostgreSQL blows MySQL and SQLite away.

Firebird support has just been added to Active Record (thanks to Ken
Kunz for the entire implementation). So there’s another open-source
DB in the fray. Whew!

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDe8x6AQHALep9HFYRAneeAKDAOhWZtctO8NWanvILAWcVznOKTACeJHGu
jn4PhtF16aMjvyz8N9WpvrQ=
=Jgwn
-----END PGP SIGNATURE-----

27

On Thu, 2005-11-17 at 01:02 +0100, Michael S. wrote:

AFAIR, the commercial licensing terms MySQL AB has for InnoDB are to be
negotiated again some time next year. With Oracle being the new owner,
the deal might be worse than before. This is speculation, of course…

Let’s not forget that MySQL and a little DB company named SAP teamed up
a few years back… and have taken a bite in Oracles pie.

…Oracle hasn’t forgotten.

-Robby

–
/******************************************************

28

— Bill K. [email protected] wrote:

Other reasons I like MySQL:

  • It’s driven by a company and I believe this is a
    good way to drive open
    source projects. That’s not to say PostgreSQL lacks
    orbiting support
    companies, but MySQL is a fairly large company.

PostgreSQL seems to have a lot of backing from Redhat.
I know they fund one of the key core developers.
Redhat also repackages PG as their own database product.

Yahoo! Mail - PC Magazine Editors’ Choice 2005

29

On Wed, 2005-11-16 at 16:12 -0800, CSN wrote:

Redhat also repackages PG as their own database product.

They are backed by companies such as:

http://www.postgresql.org/about/sponsors

-Robby

–
/******************************************************

30

Robby R. wrote:

PostgreSQL is teh bomb…and it works GREAT with PostgreSQL. :slight_smile:

Lol. +1 from another satisfied PostgreSQL user. I’ve been developing web
apps using PostgreSQL for almost 1.5 years, it’s fast and never let me
down.

Vamsee.

31

On Thu, 2005-11-17 at 08:29 +1300, Phillip H. wrote:

As for the Windows Unicode issue, Postgres on Windows is a very new
system, it’s quite possible there are issues they still need to work
around. I don’t use Windows, so I wouldn’t know.

(snip)

Allow the UTF8 encoding to work on Windows (Magnus)

This is done by mapping UTF8 to the Windows-native UTF16
implementation.

–
/******************************************************

32

On 17/11/05, vern01 [email protected] wrote:

MySQL I know. PostgreSQL I don’t. And it’s definitely got a
steeper curve than MySQL.

That’s because you know mysql.

–
Rasputin :: Jack of All Trades - Master of Nuns
http://number9.hellooperator.net/

33

robby.lists wrote:

On Thu, 2005-11-17 at 08:29 +1300, Phillip H. wrote:

As for the Windows Unicode issue, Postgres on Windows is a very new
system, it’s quite possible there are issues they still need to work
around. I don’t use Windows, so I wouldn’t know.

PostgreSQL: Documentation: 8.1: Release Notes

(snip)

Allow the UTF8 encoding to work on Windows (Magnus)

This is done by mapping UTF8 to the Windows-native UTF16
implementation.

Everytime I think about migrating from MySQL to PostgreSQL I turn back.
Time. MySQL I know. PostgreSQL I don’t. And it’s definitely got a
steeper curve than MySQL.

34

Phillip H. wrote:

On 17/11/2005, at 8:16 AM, Warren S. wrote:

Is there any reason to avoid using postgresql for small web apps?
That is, is it’s overhead so large that the lighter MySQL will work
substantially better for small apps on small machines? Or are they
very similar in performance and configuration?

Define smaller machine. I have noticed that machines which will barely
run MySQL (my PDA) did not do so well with PostgreSQL, but on reasonable
machines (at least 64M or memory) the differences are not so much. It
depends on version, though, with Postgres 6.5 and 7.0.3, MySQL was
faster, but ever since 7.1, it has been about the same.

For little select only queries, MySQL may be a little faster, but for
insert/updates, Postgres will appear faster when there are multiple
concurrent clients because of MVCC being better than MySQL’s locking
scheme. Also, as queries get more complex, and have many joins and sub
queries, PostgreSQL is much faster than MySQL, or even Oracle from what
I have seen, although DB2 seems to be even better with the ugly queries.

I run PostgreSQL and Rails nicely on my performance test machine. It
is an AMD366 laptop with 160M or RAM. If it is OK there, I don’t worry
about performance tuning any more!

35

On Thursday 17 Nov 2005 10:56, vern01 wrote:

Everytime I think about migrating from MySQL to PostgreSQL I turn back.
Time. MySQL I know. PostgreSQL I don’t. And it’s definitely got a
steeper curve than MySQL.

Hardly. I recently switched to PostgreSQL after quite a few years of
MySQL
usage, and to be honest, other than having to read a few docs now and
then,
and cope with a few slightly different types (eg. no enum type), it was
extremely easy. Add PGAdmin3 to the mix, and it’s kids stuff.

Besides, if we all stuck only to the technology we knew, almost nobody
here
would have adopted Rails. Change is good… or would you prefer to use
PHP
forever? Sometimes you just have to learn new stuff - although I try to
do
that every day, really.

To be honest, there really isn’t that much in it, when it comes to MySQL
versus PostgreSQL, especially when using Rails. Postgres is a little
more
hardcore, MySQL is a little easier, but unless you require a specific
feature
of one or the other, or your application has extreme requirements, you
probably can’t go far wrong with either for the vast majority of
situations.

If other (non-Rails) applications will be accessing your database, I
would say
go with Postgres, purely because it gives you more control over the data
integrity (although this may well be addressed in MySQL5, which I am yet
to
use). With a Rails-only scenario, the boundary is less defined.

Personally, I love Postgres and I still like MySQL a lot too.

Just my 0.02 British Pounds Sterling. :slight_smile:

~Dave

–

Dave S.
Rent-A-Monkey Website Development
Web: http://www.rentamonkey.com/

36

On 17/11/05, Alan G. [email protected] wrote:

[email protected] wrote:

I have been using MySQL 5 with great success! Thought about
switching, but the MySQL license with 5 is much nicer for commercial use.

How so? I don’t know details about the MySQL 5 license but PostgreSQL
is basically a straightfoward *BSD type license. Or are you talking
about commercial support?

I read that as ‘much nicer (than mysql 4)…’

–
Rasputin :: Jack of All Trades - Master of Nuns
http://number9.hellooperator.net/

37

[email protected] wrote:

I have been using MySQL 5 with great success! Thought about
switching, but the MySQL license with 5 is much nicer for commercial use.

How so? I don’t know details about the MySQL 5 license but PostgreSQL
is basically a straightfoward *BSD type license. Or are you talking
about commercial support?

–
Alan G.
Cronosys, LLC http://www.cronosys.com
Phone: 216-221-4600 ext 308

38

Dick D. wrote:

How so? I don’t know details about the MySQL 5 license but PostgreSQL
is basically a straightfoward *BSD type license. Or are you talking
about commercial support?

I read that as ‘much nicer (than mysql 4)…’

Okay, just I wasn’t sure if had some objection to PostgreSQL’s licensing
for commercial use (which the license perfectly allows for).

–
Alan G.
Cronosys, LLC http://www.cronosys.com
Phone: 216-221-4600 ext 308