Postgresql vs MySQL


#1

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?

I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL
around at all.

On the other hand, Postgresql claims that “Windows does not support
Unicode” and you can’t
have Unicode fields on postgresql on Windows. This is a big mistake.
See:
http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html

Warren


#2

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

Windows. This is a big mistake. See:
http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html

I’ve been using PostgreSQL for a while now and I’ve never seen any
performance issues. My blog runs on Typo backed by PostgreSQL and my
latest rails app (http://wishlists.sitharus.com/ - very much in beta
at the moment) runs on Postgres.

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.


#3

— Warren S. removed_email_address@domain.invalid wrote:

Is there any reason to avoid using postgresql for
small web apps?

None at all, IMO.

That is, is it’s
overhead so large that the lighter MySQL will work
substantially better for small apps on
small machines?

I find PostgreSQL to be quite nimble. It’s my MySQL
process that seems to inexplicably use CPU cycles,
even when it’s not busy.

Or are they very similar in
performance and configuration?

I find PostgreSQL performs as well as, and probably
better. I also find it easier to configure and even
compile from source. PostgreSQL is even easier to
admin, since auto-vacuuming has been integrated into
it.

I’ve used MySQL but postgresql has its appeals and
I’m wondering if I need to keep MySQL
around at all.

Well, unfortunately there are many apps out there that
are coded only for MySQL (like Wordpress)…

On the other hand, Postgresql claims that “Windows
does not support Unicode” and you can’t
have Unicode fields on postgresql on Windows. This
is a big mistake. See:

http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html

What do you mean “a big mistake”? By Microsoft? Or
PostgreSQL?

csn

Warren


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


Yahoo! FareChase: Search multiple travel sites in one click.


#4

Robby,
I’m currently using MySQL, and one of the things that holds me back
about
PostgreSQL is CocoaMySQL, which is teh bomb, and rocks :). Do you know
of a
similar solution for PostgreSQL and an OS X client?

Dave


#5

An OSX port of PgAdmin III is in the works:
http://www.pgadmin.org/screenshots.php

I just use phppgadmin and psql (command line) for
everything.

csn

— Dave R. removed_email_address@domain.invalid wrote:

On 11/16/05 12:31 PM, “Robby R.”
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


Start your day with Yahoo! - Make it your home page!


#6

On Wed, 2005-11-16 at 21:16 +0200, 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?

I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL around at all.

PostgreSQL works great on small to large. I use it on simple stuff like
my blog, rubyurl, my bands websites for just a guest book… and I’ve
worked on systems that had a few TeraBytes of data in the past. Several
of our client projects at PLANET ARGON involve migrating from existing
MySQL schemas to PostgreSQL. Rails has actually helped speed up our
migration process to about 60 seconds[1] of waiting time… and maybe
5-10 minutes of getting AR::Migration setup.

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

The license is also a bit more attractive for commercial usage.

On a side note… you just started a holy war. :wink:

[1] http://rubyurl.com/Qwj

Good luck with your decision!

-Robby

p.s. constraints rock!


/******************************************************


#7

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

Warren S. removed_email_address@domain.invalid wrote: Message 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?

I’ve used MySQL but postgresql has its appeals and I’m wondering if I
need to keep MySQL around at all.

On the other hand, Postgresql claims that “Windows does not support
Unicode” and you can’t have Unicode fields on postgresql on Windows.
This is a big mistake. See:
http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html

Warren


#8

On Nov 16, 2005, at 11:50 AM, Dave R. wrote:

CocoaMySQL, which is teh bomb, and rocks

Other nice MySQL tools are avaialble, for free, from MySQL itself,
are MySQL Administrator and MySQL Query Builder. Both are excellent
and run on OS X.

For pgsql check out pgAdmin III.

–Steve


#9

The “Big mistake” is in the Postgresql FAQ in saying that Windows
doesn’t support Unicode.
XP supports Unicode very well indeed. The FAQ I referenced prattles on
about Slovenian
code pages, of all things. Windows “Code pages” have been superseded by
Unicode. If you
go to http://msdn.microsoft.com and search for “Unicode” you get an
eyeful.

Having defended the undefendable, however, I’d like to know if
postgresql really still
doesn’t support Unicode on windows, as I plan to develop on WindowsXP
and deploy on Linux.

Warren S.


#10

PGAdmin 3 works fine on OS X.

On 11/16/05, CSN removed_email_address@domain.invalid wrote:

— Dave R. removed_email_address@domain.invalid wrote:

Rails mailing list


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


=> the blog from beyond <=
=> www.eyeheartzombies.com <=


#11

On Wed, 2005-11-16 at 12:50 -0700, Dave R. wrote:

Robby,
I’m currently using MySQL, and one of the things that holds me back
about
PostgreSQL is CocoaMySQL, which is teh bomb, and rocks :). Do you know
of a
similar solution for PostgreSQL and an OS X client?

Dave

I’m a command-line junkie for SQL. :slight_smile:

pgadmin exists… but I don’t use it myself.

With ActiveRecord::Migration + script/console, who really needs to do
much in the way of crazy sql stuff anymore?

-Robby


/******************************************************


#12

“On a side note… you just started a holy war.”

Sorry about the jihad – I promise not to actually kill anybody…

Warren


#13

I guess if you like transferring dumps all the time…

Go ask pg-general:
http://archives.postgresql.org/pgsql-general/2005-11/index.php

csn

— Warren S. removed_email_address@domain.invalid wrote:

to know if postgresql really still
removed_email_address@domain.invalid
Subject: Re: [Rails] Postgresql vs MySQL

http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html


Yahoo! FareChase: Search multiple travel sites in
one click.
http://farechase.yahoo.com


Yahoo! Mail - PC Magazine Editors’ Choice 2005
http://mail.yahoo.com


#14

Robby R. wrote:

The license is also a bit more attractive for commercial usage.

This can be pretty important for certain commercial uses. I’m fighting
with a commercial product now that chose MySQL as a base and I’m rather
disappointed that I’m forced to pay a yearly license fee as an end user.

I like the fact that PostgreSQL is built from the ground up as a real
SQL engine, with lots of features. It’s SQL syntax is standards based
and not as arbitrary as some other implementations. Assuming you are
properly using indexes, it is mighty fast for tables with many rows.
Something I always do it tweak the service config to give it tons of
memory, too.

I think most distinctions which gave MySQL an ‘edge’ in the past are
moot pionts now. It’s not significantly faster (if at all), it’s not
massively more popular (that is to an extent where it impacts support
and debugging, anyway), and companies seem to be less sensitive to
having to pay something to make them feel warm and fuzzy.

Phil


#15

CSN wrote:

— Warren S. removed_email_address@domain.invalid wrote:

Is there any reason to avoid using postgresql for
small web apps?

None at all, IMO.

I much prefer postgresql for all sorts of reasons. The only downside I
can think of is backup/restore is a little more straightforward on
mysql. Especially backing up and restoring a whole series of database in
one go. We had to write a little script to do that, but maybe its easier
these days…
Oh yeah and sometimes, even with 8.0 it’s a pain to change column’s
datatype, but maybe that holds true also for mysql if it’s using views
and stored procedures etc.

But I much prefer it over mysql as it has nice features and is more
standards compliant. In mysql for instance you can (could?) have
datetime values like 0000-00-00 00:00:00 and the whole set and enum
things are just ugly.

HTH,

Jeroen


#16

On 11/16/05, Warren S. removed_email_address@domain.invalid wrote:

http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html
Warren

I don’t think any of your worries have a real foundation, but IMO for a
small non critical webapp it really doesn’t matter which one you use.
Pick
the one you like and use it.

Chris


#17

On 17/11/2005, at 11:46 AM, Bill K. wrote:

Browsing around the web, I see many outdated comparisons of MySQL
and PostgreSQL, but no comprehensive pros/cons between the two
recent platforms, MySQL 4.1.x (and 5.0) versus PostgreSQL8.1. In
particular, I wonder about contentions that:

  1. PostgreSQLhas better data integrity than MySQL. This would make
    me opt for Postgres if I felt it were true. MySQL, though, has a
    pretty impressive array of large businesses using their software.
    Is there an architectural reason why Postgres 8.1 would be better
    than MySQL 4.1.x InnoDB for data integrity?

ACID (http://en.wikipedia.org/wiki/ACID 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.

Oh, and MVCC means Postgres is very fast with transactions.

  1. MySQL is faster than PostgreSQL. I believe this is mostly a
    MySQL MyISAM comparison, so it would only be useful for aspects of
    your web app that didn’t need transactional support.

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.


#18

Browsing around the web, I see many outdated comparisons of MySQL and
PostgreSQL, but no comprehensive pros/cons between the two recent
platforms,
MySQL 4.1.x (and 5.0) versus PostgreSQL8.1. In particular, I wonder
about
contentions that:

  1. PostgreSQLhas better data integrity than MySQL. This would make me
    opt
    for Postgres if I felt it were true. MySQL, though, has a pretty
    impressive
    array of large businesses using their software. Is there an
    architectural
    reason why Postgres 8.1 would be better than MySQL 4.1.x InnoDB for data
    integrity?

  2. MySQL is faster than PostgreSQL. I believe this is mostly a MySQL
    MyISAM
    comparison, so it would only be useful for aspects of your web app that
    didn’t need transactional support.

I have gone with MySQL because I took the path of least resistance from
my
first PHP/MySQL web apps. If 37signals say constraints are good, then I
guess it’s good to have a constraint of being one man with one (very)
small
brain and not that much time already trying to learn parts of an
OS-to-database stack. I’m not going to delegate business logic to the
database in the form of stored procedures. I’m still undecided whether
triggers and stored procedures make sense for my app.

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.
  • There’s a good selection of clients. Cocoa was mentioned. I’m using
    Webyog
    for Windows. DBDesigner and the upcoming MySQL Workbench are nice DB
    design
    tools.
  • No shortage of documentation and books, including proven strategies of
    replication at places like Yahoo.
  • (blue sky) Potential for radical upgrade using in-memory clusters.
    Yeah,
    I’m only dreaming about days when I’d actually need something like MySQL
    Cluster, and it’s a very different engine than standard MyISAM and
    InnoDB,
    but man, the specs look great. In a couple of years when memory is even
    cheaper and 64-bit CPUs are the norm for new server boxes, I wonder how
    much
    traffic a “simple” 3 box cluster could handle.

#19

On 11/16/05, Phillip H. removed_email_address@domain.invalid wrote:

ACID (http://en.wikipedia.org/wiki/ACID 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.

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.


#20

On Thu, 2005-11-17 at 12:06 +1300, Phillip H. wrote:

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.

Out of the box, PostgreSQL will be slightly slower. It assumes that you
are working with a bigger system and it can be fine-tuned to be fairly
fast.

The real test is when your database grows… MySQL gets slower…and
starves for more CPU…and PostgreSQL is the guy in the back room
playing Doom while it just works.


/******************************************************