Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

merlyn (47)

merlyn
  merlyn@stonehenge.com
http://www.stonehenge.com/merlyn/
AOL IM: realmerlyn (Add Buddy, Send Message)
Yahoo! ID: realmerlyn (Add User, Send Message)

PAUSE-ID: MERLYN [cpan.org].
See my home page [stonehenge.com].

Journal of merlyn (47)

Monday March 24, 2003
12:03 PM

PostgreSQL and MySQL - better explained

[ #11202 ]
Here's the thing. If you need any of the features of PostgreSQL that MySQL doesn't provide, like:
  • subselects in updates/deletes
  • stored procedures
  • stored procedures in different languages
  • ability to create different languages
  • inheritance
  • speed with transactions (more mature transaction implementation)
  • ability to survive larger number of connections
  • wealth of data types
  • views
  • Triggers
  • Rules
  • Subselects in target lists
  • NUMERIC type of arbitraty precision
  • PL/pgSQL, PL/Tcl, PL/Perl, PL/Pythin
  • Multiversioning
  • User-definited data types, operators, and functions
  • Write-ahead logging
  • Partial indexes
  • Functional indexes
  • Rollbackable DDL (CREATE/DROP TABLE, etc.)
  • Absolutely 100% free
  • Referential integrity

then yes, you need PostgreSQL. (This is a composite list gleened from an email archive. Sorry if there are overlaps.)

On the other hand, there are some applications that will never need any of these things. But, can you really count on that?

MySQL is slowly gaining one feature at a time from PostgreSQL. Sure, it has transactions now, but how mature is the implementation? And it has subselects, but those were introduced in Pg more than a couple of years ago, and have had a lot of time to get more and more optimized.

So, my advice is, if you need a real database, you can't pick MySQL at this point. And even if you think you can get by with MySQL, who is to say that you won't need more features later?

As for support, RedHat's database is PostgreSQL, so you can buy commercial support easily if you're already in the RedHat camp.

About the only thing that MySQL does better right now is replication. But there's a kludgey solution for Pg right now, and a better solution in the next release.

No, Pg's not for everyone. Just the ones that are coming down from Oracle, looking for an "open sourced" Oracle. If you're happy with DBM, MySQL will make you happier. But at some point, real apps need at least one or two of the things in the list above. And then you either migrate, or grimace. {grin}

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • by ziggy (25) on 2003.03.24 12:33 (#18256) Journal
    For the record, I've been developing database-backed apps for over a decade. I've used a variety of commercial RDBMSes, including some embedded databases, as well as low-grade hacks: text files, CSV, DBM and the like.

    One feature I cannot seem to live without is the ability to do complex subselects and joins. It's a thorn in my side when I can't do this with MySQL, but somehow I manage to not need it when I'm hacking with MySQL. Another favorite feature of mine is the ability to create and query against views. In most cases, views are a nice-to-have feature that can be routed around with sufficiently tricky SELECT statements.

    I've also never written a stored procedure in PL/SQL or any other language, nor have I ever needed a trigger (except for the odd hack to enumerate records). Again, I've seemed to manage somehow.

    I'll grant you that a lot of people need these features in their apps, but MySQL sheer existance and popularity never ceases to amaze me with what people can do with basic, rudimentary features. If shops like Yahoo! can use MySQL as heavily as they do, it can't suck that much. Sure, they could come up with a BerkeleyDB-based solution, but it's much more productive for them to use a real RDBMS with SELECTs, ORDER BY and the ability to index on demand. That would make about as much sense as throwing out their Perl, PHP, Java, etc. programs to rewrite their entire infrastructure in FORTH.

    MySQL is slowly gaining one feature at a time from PostgreSQL. Sure, it has transactions now, but how mature is the implementation?
    That's FUD and you know it. Either their ACID compliance is 100% or it isn't. You cannot have 99 44/100% data integrity in a relational database. (Both offer row locking, although some of MySQL's table types are explicitly less granular.)
    And it has subselects, but those were introduced in Pg more than a couple of years ago, and have had a lot of time to get more and more optimized.
    More FUD. Either they work or they don't.
    So, my advice is, if you need a real database, you can't pick MySQL at this point. And even if you think you can get by with MySQL, who is to say that you won't need more features later?
    And my point remains that most people don't choose a database based on a lengthy feature list. There are always other factors. MySQL has done a great deal to give voice to some of the them: size of the user community, number of addon utilities, ease of use, ease of administration, quantity of worthwhile documentation, etc.
    As for support, RedHat's database is PostgreSQL, so you can buy commercial support easily if you're already in the RedHat camp.
    RedHat isn't really pushing RHDB anymore. Word on the street is that this was a bargaining chip against Oracle to force them to more actively support Oracle on RHLinux. On paper, it looks like a nice option. In reality, I don't think its as nice as the marketing department would have you believe, nor is it an option if you're not a RHLinux user.
    • Both offer row locking

      Actually, PostgreSQL implements a solution called "Multi Version Concurrency Control" or MVCC, which is billed as "better than row-level locking." This is even more advanced that what Oracle uses, although I would not expect that gap to stay open long.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • MVCC is exactly the same as what Oracle uses.
        • Um, not according to my O'Reilly Oracle 8, 8i, and 9 Essentials book which describes row locking in great detail. Google for some combination of PostgreSQL, better than row-level locking, and MVCC for their claims that they have it and Oracle doesn't.

          Poking around oracle.com with google, I see this [oracle.com], which suggests MVCC for queries, but I believe PostgreSQL uses it for all aspects of a transaction, including DML as well. MVCC replaces row-level locking, so I wouldn't expect to see references to row-level

          --
          J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • MySQL also implements multiversioned database [innodb.com].
        --

        Ilya Martynov (http://martynov.org/ [martynov.org])

  • And even if you think you can get by with MySQL, who is to say that you won't need more features later?

    I think you could just as easily say, even if you think you can get by with PostgreSQL, who is to say that you won't need more features later?

    I had a recent meeting with a big company who wants to do something interesting with Wikis and weblogs. The original Wiki is written in a couple of hundred lines of Perl and uses flatfiles to store the pages. Ward said he'd tried DBM but it was actually slo

  • Absolutely 100% free

    I notice you list that in a list of features PostgreSQL has that MySQL does not. Care to elaborate on why?

    --
    J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
    • Incidentally, I'm all for PostgreSQL, myself. I was just wondering about this item.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
    • MySQL is dual licensed. Either you take the standard GPL version, or you can pay for a commercial license with more flexability. PostgreSQL is BSD licensed, so do what you want with it and be happy.
      • I think I sort of knew about the dual licensing, but it seems like to me if it is available optionally under GPL, it's free. And I drank enough FSF koolaid to kill some of you people. :)

        I was curious if he meant there was some restriction somewhere people didn't know about; if he meant the dual licensing; if he meant the whole mysql.com vs. mysql.org fiasco; or if he just got carried away and started listing PostgreSQL features in general.

        --
        J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
        • You can bundle PostgreSQL as part of a commercial closed-source product that you sell for profit. Can you do that with MySQL?

          --
          /-\
          • Well, that's not a "freedom" I value, although I can see that many other people do. Is that what Randall meant?

            --
            J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
  • You might as well get Hummer, because you just might find yourself needing to mount anti-tank missles for your morning commute. If that happens, you're regret having bought that fun little Cooper Mini.

    Seriously: I've build many database-backed applications over the past 15 years, and have used only a few of the features on your list. Easily half of those applications needed nothing more than MySQL provides in 4.0 (e.g., ACID transactions). A big problem with stored procedures is that people reach for them

  • All the databases mentioned so far have their place.. One reason I havent gotten into Postgres yet is because a Win32 version that doesnt run on Cygwin is hard to find (someone please toss me a link so I can install it at home and try it out)

    MySQL has a native Win32 version which makes it much easier to install and try out (and yes, there are those among us who actually use Windows based servers, I am one of them :D).. Postgres, thus far, does not.. that is one reason why the only databases I've tried (and