Re: Article on MySQL vs. Postgres - Mailing list pgsql-hackers

Tim Perdue wrote:
> On wednesday or thursday, I'm going to be publishing my article on MySQL
> vs. Postgres on PHPBuilder.com.
>
> Before I do that I want to confirm the major problem I had w/postgres:
> the 8K tuple limit. When trying to import some tables from MySQL,
> postgres kept choking because MySQL has no such limit on the size of a
> row in the database (text fields on MySQL can be multi-megabyte).
   I  just committed the first portion of TOAST. Enabling lztext   fields to hold multi-megabytes too. But it's not
the answer   to  such  big  objects.   I  have plans to add an Oracle like   large object handling in a future
version.

> I actually intended the article to be a win for Postgres, as I've used
> it and had good luck with it for such a long time, but if you look at
> the results below, it seems very positive for MySQL.
   It's never a good plan to have an initial intention which  of   the  competitors  should  finally  look  good.  It's
visible   between the lines.
 

> Performace/Scalability:
>
> MySQL was About 50-60% faster in real-world web serving, but it crumbles
> under a real load. Postgres on the other hand scaled 3x higher than
> MySQL before it started to crumble on the same machine. Unfortunately,
> Postgres would probably still lose on a high-traffic website because
> MySQL can crank out the pages so much faster, number of concurrent
> connections is hard to compare. MySQL also seems to make better use of
> multiple-processor machines like the quad-xeon I tested on. Postgres
> never saturated all 4 processors as MySQL did.
   The  question  in  this  case  is  "what  is  real-world  web   serving"?  To  spit  out  static  HTML  pages
loaded into a   database? To handle discussion forums like OpenACS with  high   concurrency and the need for
transactions?
   Web  applications  differ  in  database  usage as much as any   other type of application. From huge amounts of
static,never   changing   data   to   complex   data  structures  with  many   dependencies constantly in motion.
There is  no  such  one   "real world web scenario".
 

> Tools:
> MySQL has some nice admin tools that allow you to watch individual
> connections and queries as they progress and tools to recover from
> corruption. I haven't seem any similar tools for postgres.
   Yepp, we need alot more nice tools.

> Long-term stability:
> Postgres is undoubtably the long-run winner in stability, whereas MySQL
> will freak out or die when left running for more than a month at a time.
> But if you ever do have a problem with postgres, you generally have to
> nuke the database and recover from a backup, as there are no known tools
> to fix index and database corruption. For a long-running postgres
> database, you will occasionally have to drop indexes and re-create them,
> causing downtime.
   Not true IMHO. We had some problems with indices in the past.   But you can drop/recreate them online and someone
running a   query  concurrently  might  just use a sequential scan during   that time. All other corruptions need
backup and  recovery.   WAL is on it's way.
 

> Usability:
> Both databases use a similar command-line interface. Postgres uses
> "slash commands" to help you view database structures. MySQL uses a more
> memorable, uniform syntax like "Show Tables; Show Databases; Describe
> table_x;" and has better support for altering/changing tables, columns,
> and even databases.
   Since professional application development starts with a data   design, such "describe" commands  and  "alter"
features are   unimportant.  The  more  someone  needs them, the more I know   that he isn't well educated.
 
   Productional installations don't need any "alter" command  at   all.  New  features  are  developed  in the
developmentarea,   tested with real life data in the test environment and  moved   to  the  production  server
including a maybe required data   conversion step during a downtime.
 
   24/7  scenarios  require  hot  standby,  online  synchronized   databases  with  hardware takeover. All that is far
awayfrom   our scope by now.
 

> Features:
> Postgres is undoubtedly far, far more advanced than MySQL is. Postgres
> now supports foreign keys, which can help with referential integrity.
> Postgres supports subselects and better support for creating tables as
> the result of queries. The "transaction" support that MySQL lacks is
> included in Postgres, although you'll never miss it on a website, unless
> you're building something for a bank, and if you're doing that, you'll
> use oracle.
   FOREIGN KEY  doesn't  help  with  referential  integrity,  it   guarantees  it.   No  application  must ever worry
ifit will   find the customer when it has a problem  report.  It  does  a   SELECT  and  has  it  or  it would've never
foundthe problem   report first - period.
 
   And for big, functional expanding web sites, it does so  even   if  one  of  a  dozen  programmers  forgot  it
once. If the   constraint says you cannot delete a customer who payed  until   end  of  the year, the database won't
letyou, even if one of   the 7 CGI programs that can delete customers doesn't check.
 
   Transactions are the base for any data integrity.  Especially   in the web environment. Almost every web server I've
seenhas   some timeout for CGI, ADP, ASP or whatever they call  it.  As   soon  as  your  page needs to update more
thanone table, you   run the risk of getting aborted  just  between,  leaving  the   current  activity half done. No
matterif a database supports   FOREIGN KEY. I could live without it,  but  transactions  are   essential.
 
   Fortunately  the MySQL team has changed it's point of view on   that detail and made some noticeable advantage into
thatarea   by  integrating BDB. The lates BETA does support transactions   including rollback as they announced. As far
asI see it, the   integration  of  BDB only buys them transactions, on the cost   of performance and maintainence
efford.So the  need  for  it   cannot be that small as you think.
 
   Final notes:
   I  hate  these "MySQL" vs. "PostgreSQL" articles that want to   say "this one is the better". Each one  has  it's
advantages  and disadvantages. Both have a long TODO.
 
   Your  article  might  better  analyze  a  couple of different   "real-world web services", telling what DB usage
profilethey   have  and  then  suggesting which of the two databases is the   better choice in each case.
 
   MySQL is a tool and PostgreSQL is a tool. But as  with  other   tools, a hammer doesn't help if you need a screw
driver.
   Please  don't intend to tell anyone either of these databases   is "the best". You'd do both  communities  a  bad
job. Help   people  to  choose the right database for their current needs   and tell them to reevaluate their choice
forthe next project   instead  of blindly staying with the same database. We'll end   up with alot of customers using
bothdatabases  parallel  for   different needs.
 
   At  the  bottom  line  both  teams  share the same idea, open   source. Anyone who pays a license fee is a loss
(looser?)for   all of us.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #