Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!


Forgot your password?
Databases Programming Software IT

Comparing MySQL and PostgreSQL 2 902

Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today. Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?
This discussion has been archived. No new comments can be posted.

Comparing MySQL and PostgreSQL 2

Comments Filter:
  • by Anonymous Coward on Monday September 05, 2005 @03:06PM (#13484567)
    SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.
    • by ray-auch ( 454705 ) on Monday September 05, 2005 @03:21PM (#13484644)
      SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company

      No, it's like MySQL _sold_ them something.

      There are (I expect) a large number of people reaing this who believe that SCO is not a company you should do business with.

      It would be interesting to know why MySQL did business with SCO, maybe on principle they turn no customer away, or maybe a need for money overrode. The latter case might be a legitimate concern for the community.
      • by Saeed al-Sahaf ( 665390 ) on Monday September 05, 2005 @04:27PM (#13485057) Homepage
        No, it's like MySQL _sold_ them something.

        Well, MySQL AB is a for-profit company, they sell things to people. And, last time I looked into it, SCO wasn't gassing people or mowing down rain forrest, or something. Sure, they are obnoxious, but the truth is, so are many commercial companies we deal with every day.

        But does it even matter? The jokes on SCO, they paid to use something in a product very few companies will buy.

        More fun than dragging out this, would be guessing what non-issue Slashdot will toss up on their front page next in an attempt to stir shit and boost their dieing readership?

        • The jokes on SCO, they paid to use something in a product very few companies will buy.

          More precisely:
          The joke's on SCO, they paid for something that very few companies have to pay for, to use in a product that very few companies will buy. Additionally, for those companies already own UnixWare or OpenUNIX, MySQL AB already provides installation instructions [mysql.com] and patches [mysql.com] for them. Finally, for the exceptionally lazy, SCO themselves provide a GPL'd version for you to download for free [sco.com]!

          More on-t
    • Not exactly ... (Score:4, Insightful)

      by khasim ( 1285 ) <brandioch.conner@gmail.com> on Monday September 05, 2005 @03:35PM (#13484727)
      If I go to the store and buy a copy of MSOffice, that's one thing.

      If I get a site license from Microsoft, that's something else.

      If Bill Gates and I do a press release about our new partnership, that's an entirely different thing.

      SCO and MySQL AB did the press release thing. That's not the same as SCO buying a license to distribute.
    • by burnin1965 ( 535071 ) on Monday September 05, 2005 @04:05PM (#13484935) Homepage
      Get your facts straight coward:

      "As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs"

      http://www.mysql.com/news-and-events/news/article_ 948.html [mysql.com]

    • by tangledweb ( 134818 ) on Monday September 05, 2005 @09:30PM (#13486555)
      So what exactly is the difference between the MySQL-SCO relationship and the PostgreSQL-SCO realtionship that were announced [eweek.com] at about the same time?

      MySQL has only one commercial vendor, who helpfully call themselves MySQL AB, so even Slashdot readers can understand what they sell. So SCO made a deal with them to compile and test a certified MySQL binary for SCO.

      PostgreSQL has had a number of failed commercial vendors over the years, but one current one is EnterpriseDB. Maybe not having the word PostgreSQL in the company name confused slashdot readers who think Walmart sell Wals?

      eWeek report [eweek.com] it as the same deal. "SCO has added open source database vendors MySQL and EnterpriseDB to its partner list, said SCO President and CEO Darl McBride"

      What is the difference?

      Oh, I forgot. This is slashdot where MySQL is evil because they charge for some things and where we all sit around and pretend that MySQL does not have transactions and that PostgreSQL vacuum is a good thing.

      Yay for Postgres/Perl. Boo for MySQL/PHP. Can I have mod points now?
  • Yawn.... (Score:3, Insightful)

    by truckaxle ( 883149 ) * on Monday September 05, 2005 @03:09PM (#13484583) Homepage
    This is a prefect flame fest topic - great scheduling for a vacation day. I will venture to guess the posts will be well into the 1000's Now what does it matter if Mysql partnered up with SCO. SCO as a O/S provider is history may a well extract a scraps of meat from the bones.
  • Another question (Score:5, Insightful)

    by Eightyford ( 893696 ) on Monday September 05, 2005 @03:10PM (#13484589) Homepage
    Sure it's slashdot and we all love free software, but how do these two compare with oracle, sql server and other non-free db's?
    • Re:Another question (Score:5, Interesting)

      by Jerry ( 6400 ) on Monday September 05, 2005 @03:27PM (#13484686)
      PostgreSQL compares very well to Oracle.

      I use PostgreSQL as a test database against which I write and test QT applications. I can switch an app between the two backends by changing only a few lines of code and recompiling, or I can build the switching capability into the app. Using PostgreSQL reduces the number of access licenses required for Oracle, or doesn't waste existing connections.

      If I had my way I'd use PostgreSQL as the primary database, but some folks believe you've gotta pay money or the app isn't any good. As long as it's their money and not mine.
      • Re:Another question (Score:3, Informative)

        by kimanaw ( 795600 )

        ...but some folks believe you've gotta pay money or the app isn't any good.

        There are good reasons to pay someone for support, if the people you're paying know their stuff. If you're building enterprise level, mission critical data warehouses, you'll want immediate access to expert help when things go horribly wrong. And Sorbannes/Oxley reinforces that need.

        For those seeking paid support, there are several companies working to do interesting things with Pg:

        • GreenPlum [greenplum.com] also working to enhance Pg with th
    • Re:Another question (Score:5, Informative)

      by ron_ivi ( 607351 ) <.moc.secivedxelpmocpaehc. .ta. .ontods.> on Monday September 05, 2005 @03:40PM (#13484766)
      This page [arvin.dk] is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.
      • Re:Another question (Score:3, Informative)

        by ttfkam ( 37064 ) *
        Damn! DB2 looks damn good in that comparison.

        I loved the CHAR type section, specifically the MySQL entry:
        Breaks the standard by silently inserting the string, truncated to specified column CHAR-length.

        (It's actually not completely silent, as it issues warnings if values were truncated: If you manually check for warnings, you will know that something bad happened, but not which of the rows are now invalid.)
    • They don't compare very well at all... Oracle doesn' hold a candle to postgres.
    • No benchmarks here but benchmarks are largely useless in the database world anyway unless they are run on your specific application.

      Oracle: Very portable database, replaces many OS functions and is extremely tunable. Downside: $$ and the fact that the tuning options are extremely complex allowing your DBA to spend all his time tuning the database, and your second DBA to spend all his time tuning the tables..... (/sarcasm)

      One of the odd problems with Oracle is that empty strings and nulls are seen as equivalent (and Oracle DBA's seem to think that an empty string and a null are the same thing). The general concensus in the RDBMS industry is that these are not the same.

      MS SQL Server: A Windows-only RDBMS which is tightly integrated with Windows in terms of memory management. Quite extensible, less costly and simpler to administrate than Oracle. Will tie you to Windows. Troubled security history.

      PostgreSQL: An Open Source RDBMS designed to target Oracle's market. Extremely powerful and full featured. Attempts to tune itself to the greatest extent possible and relies on the OS for additional tuning. Downside is that it is not as widely used as the others listed above. Stored procedures are available in a much wider number of languages than in any other RDBMS in this comparison.

      MySQL: A popular open source database manager (neither really relational nor a management system). Provides a simple non-standard subset of SQL for the interaction with various resources. Downside is that it does not do much integrity checking and does not enforce much integrity (valid dates include 0000-00-00 and 2004-02-31). Furthermore it will *truncate* numbers that are too large to fit in a number field making it unfit for any purpose where it must track money. It is more widely used than any other open source RDBMS.

      FirebirdSQL. A good RDBMS designed really for Windows but ported over to UNIX/Linux. Fairly extensible and stable but largely undocumented. Lacks many of the data types available in all other databases listed here.
  • Helllooo?? Editors?? (Score:4, Informative)

    by wfberg ( 24378 ) on Monday September 05, 2005 @03:11PM (#13484590)
    Not only is this article just 2 links to some other slashdot articles, but the "comparison" of mysql and postgres article from 6 years ago.. Doesn't compare them! It's an article, like this one, asking for some comments.. So not only does this article add no news for nerds, it even misrepresents links to this very site, which the editors, again, are too lazy to even follow? Come on, people!

    This sort of whoring-for-comments article should be a poll.
  • Also (Score:5, Funny)

    by TheRaven64 ( 641858 ) on Monday September 05, 2005 @03:14PM (#13484608) Journal
    I have been wondering, which is better, vi or emacs?
    • Re:Found one! :) (Score:3, Informative)

      by llefler ( 184847 )
      Ok, that's a silly review. For MySQL they claim to be comparing the features of 4.1.x, and yet under features: views, schemas, subselects, stored procedures, triggers - yes (>=5.0)

      If they're going to compare releases, compare apples to apples. Either pick stable releases or development releases. And FWIW, nobody that I know personally has been able to get MySQL 5.0 to run reliably, if they can even get it to launch. If we use MySQL for a project, it's 4.1.
  • Spatial databases (Score:5, Informative)

    by MostlyHarmless ( 75501 ) <artdentNO@SPAMfreeshell.org> on Monday September 05, 2005 @03:23PM (#13484662)
    I don't have any thoughts about the more general question, but PostgreSQL is much better at storing spatial data than MySQL. MySQL has spatial functions built in, but it only supports a subset of the OpenGIS functions [mysql.com] (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS [refractions.net], which supports the full OpenGIS specification and a bunch of other extension functions besides. I've used MySQL by default simply because it is more familiar to me, but I've switched to PostgreSQL for my current project simply because of the spatial data module.
  • by michalf ( 849657 ) on Monday September 05, 2005 @03:23PM (#13484664) Homepage
    there is a short (decent) comparison at this url [poznan.pl].
    From my point of view (web application developer, Ozone [ozoneframework.org] framework author and the author of a few rich-content websites I can say for sure: I am more than happy to discover PostgreSQL. Why? More Oracle-like, transactions, nested transactions, views, sql-schema... I doubt MySQL 5.0 will come even close to the standard of PosgtreSQL.
    Some can say MySQL is fast. No, it is not. When you run more than 100 users at once PostgreSQL is faster. MySQL has stupid table-locking mechanism that decreases performance significantly under high load.
    I would say: PostgreSQL seems to be slower, is not perfectly optimized, but much better goals in its design were used. And one of the goals ic SQL conformance. MySQL is FAR from the SQL standard.
    If you want to migrate from MySQL to e.g. Oracle - it is a pain. But PG is much closer to it.
    IMHO PostgreSQL is an industry-standard database and we use it for almost every project now. We have used MySQL some time ago and believe me - the difference is huuuuuge. PG is a real database. MySQL seems like a table-managing-application ;-)

    best regards - michal
    • Do you know MySQL has had transactions for years now? And SAVEPOINT [mysql.com] transactions (like Postgres [postgresql.org], I believe). And views [mysql.com] in 5.0. The table-locking engine you're thinking of (MyISAM) hasn't been current for maybe 4 years. If you can't handle 100 users at once blame your design, not MySQL -- MySQL powers Slashdot :)
      • by ttfkam ( 37064 ) * on Monday September 05, 2005 @04:52PM (#13485200) Homepage Journal
        MySQL 5.0 is still in development. 4.1 is still the most up-to-date production branch.

        You're right that InnoDB has supported row-level locking for some time now. PostgreSQL uses MVCC so that you don't need a lock at all most of the time.
        MySQL powers Slashdot
        Considering how often Slashdot goes down when they have a cluster of MySQL boxes for redundancy, that's hardly an endorsement of quality.
    • by philovivero ( 321158 ) on Monday September 05, 2005 @05:22PM (#13485346) Homepage Journal
      I've read a few of the replies to this story. It's interesting to read some of the pro-PostgreSQL peoples' opinions. They're rather dated.

      The more I learn about MySQL (from the perspective of someone who was initially gung-ho about PostgreSQL), the more I realised the shortcomings of MySQL weren't really shortcomings. They were misunderstandings. Yes, this can sometimes be as bad, when a default option is a stupid option (like table-level locking, as the parent and other PostgreSQL fans complain about).

      Then I quit that job and went to work at Friendster, which is also a big MySQL shop. What I learned then was that when used properly, MySQL can scale to amazing proportions. Millions of transactions per hour (I won't be too specific being as I don't want to be sued into oblivion now that I'm an ex-Friendster employee).

      Keep in mind that Friendster isn't alone. Google and Yahoo! use MySQL. For production loads. Big, big production loads.

      What I didn't like about PostgreSQL was the weird licensing problems. Yes, bizarre as it may be, the BSD license they chose over GPL causes it to be bizarre. You can't get replication without downloading some weird third-party patch and recompiling (because the patch is GPL). Screw that. MySQL has it built in to the supported binaries you get from their site.

      Without replication, your DBMS is useless. It's pretty clear from reading the parent post that Michalf doesn't really understand replication. If he did, he might think a moment about his statement that MySQ can't scale to more than 100 users at once. Friendster had millions (at once). Yahoo! has at last estimate nearly a hundred million users at once.

      Last I checked PostgreSQL (admittedly, 6-9 months ago?) it just wasn't viable. Really replication was about the only thing holding it up, except I know another engineer who worked extensively with PostgreSQL internals (hacking it up to create a DBMS cluster, actually) and he said their I/O internals were bad/slow. Hopefully he's wrong, but I know before I deploy PostgreSQL I'm going to be carefully benchmarking it before doing so. Keeping in mind that I never deploy an RDBMS in a tiny little "more than 100 users" environment like the parent poster.

      Sorry for the long-winded rant. It's just that I've been wishing/hoping/praying PostgreSQL would be the winning RDBMS in this battle for years, and every time I think it's going to be any good, it goes and shoots itself in the foot somehow, which makes me sad. Currently, I'm still a fulltime MySQL DBA.

      Caveat: Much of what I've said here only applies in high volume RDBMS environments. If your environment is low volume, PostgreSQL may be a better choice.
  • by caluml ( 551744 ) <slashdot@spamgoe ... m.org minus poet> on Monday September 05, 2005 @03:23PM (#13484665) Homepage
    The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
    MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.

    But when I tried to do a query like this: SELECT * FROM foo where bar NOT IN (SELECT blib from wheee) - MySQL advised me that it "didn't do" "NOT IN" queries. I tried to work around it, but after trying all the JOINs I could, it just didn't seem like something that I could get round. (I wasted quite a long time trying to work around this, and although I'm sure that some really top DBAs out there can do it, I couldn't.)

    So, mysqldump > mysql.dump, and then restore into Postgres. :%s/mysql_/pg_/g in all my PHP files. Change mysql_error to pg_last_error, and fiddle with pg_num_rows, and it all worked. Moreover, one huge query that took 25 seconds to complete in MySQL (lots of JOINS and nastiness) took about 1 second in Postgres.
    I've never looked back. MySQL is now just coming to fill in all the gaps it's missing - but just go with Postgres. It's rather good.
    No mention of SQL servers can go without the Gotchas: Mysql [sql-info.de] and Postgres [sql-info.de]. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.
    • by croddy ( 659025 )
      mysql> select count(*) from users where id not in (1, 2, 3, 4, 5);
      | count(*) |
      |       93 |
      1 row in set (0.12 sec)

      mysql> create table strings (stuff varchar(8));
      Query OK, 0 rows affected (0.03 sec)

      mysql> insert into strings values ('abcdefghijk');
      ERROR 1406 (22001): Data too long for column 'stuff' at row 1


    • by DrXym ( 126579 )
      Besides these days, Postgres is very easy to setup. I use in Windows XP and it even comes with an installer, ODBC drivers, help and pgAdminIII. It works wonderfully. I even had it hooked up to the new OpenOffice 2.0 database application.

      I'm certainly no power user but Postgres strikes me as an extremely well featured DB. I use MSDE / MS SQL server at work for an app with transactions, stored procedures, triggers and views and expect it would be straightforward (not trivial but straightforward) to port it to

  • by ThinkThis ( 912378 ) on Monday September 05, 2005 @03:37PM (#13484750)
    Our company has developed an app used at several hundred sites on the Interbase/Firebird platform. (Firebird is now the only open source version). It is stable, quick, low maintenance with support for transactions, triggers, row level lockinge, etc. I would consider MySQL for web development because it comes preinstalled on many hosts and because of the number of tools available.
    • The last time I used Firebird on a major project, it sucked so badly [honeypot.net] that I wrote a program [honeypot.net] to convert its databases to PostgreSQL (even if they contained unreadable rows that kept the normal utilities from working). Unless Firebird's undergone the same kind of rewrite that Netscape did while becoming Mozilla, I wouldn't touch it with a ten-foot pole.

      To each his own, of course, and the situation may indeed have improved. I don't see any clear advantage that it has over PostgreSQL, though, and I doubt it

      • by Unordained ( 262962 ) <unordained_slashdotNOSPAM@csmaster.org> on Monday September 05, 2005 @05:54PM (#13485504)
        I had mod points, and as a Firebird user, I was going to mod up the grandparent -- Firebird is very often ignored despite a host of positive features.

        But as you had a bad experience, and you link to your problem list, I thought I'd respond instead. Better to answer questions than just mod up friendlies.

        - Database path: Yes, firebird supports aliases. Our app doesn't use them, but they're there.
        - ISQL: I hear Oracle's SQL*Plus isn't much better. However, I use isql every once in a while, and I have command-history, backspacing, etc. available to me. From what I hear, it's more of a function of the shell you use (around isql) than isql itself. If you set up your environment properly, isql and its ilk automatically get command-history. (That's what I'm told, note. Anyone who can explain this is welcome to. I'm not a sysadmin.)
        - Never seen it freeze.
        - Corruption: we've had exactly one database issue, where it seems a backup/restore script ran in the middle of the day, restoring the database to its state from 4 hours earlier. In 4 years of use, with somewhere around 60 users in a medical clinic/insurance/billing environment, we've had no corruption. Using forced-writes is important, however. The careful-write strategy is really, really reliable, but it still can't protect you from faulty hard drives or operating systems that refuse to send data to the disk in the order requested (cf. Windows). M1 Abrams tank story, anyone?
        - IBDataPump and other third-party tools exist for some of the other features you're interested in. I'm not sure I know how even I feel about some things only being offered by third-parties. Oracle's tools suck enough people buy other products ... heck, why bother? Just develop a good RDBMS with a good API, and let others fight it out? (That's an open question.)

        Feature-wise, and maybe target-audience-wise, Firebird and PostgreSQL are similar. Stored procedures, triggers, check constraints, MVCC (Postgresql seems to have copied MVCC off of Interbase, note), savepoints/nested (but not concurrent) sub-transactions, etc. It lacks a lot of the UDT (type) features of PostgreSQL (you can define domains, but not entirely new datatypes) -- note that Postgres was specifically designed with UDT's in mind. Firebird does support UDF (function) features though, and you can get some of the same flexibility that way if you're masochistic (save data in octet or blob fields and use UDF's to interpret the data). Pg also has neat SP language support, letting you write your SPs in a variety of languages -- Fb doesn't. Unlike Postgres, it's really easy to install, particularly on windows (that was a problem for Pg up until semi-recently) and it practically maintains itself. (Happily, the Pg team eventually got their vacuum, equivalent to Fb's sweep, to not take down the database, so Pg can now run 24/7 too.) Fyracle has been trying to make Firebird more Oracle-like in SP language support and some of Oracle's more interesting query abilities (CONNECT BY). Yes, I occasionally get feature-lust and look at other DBMS's. I don't need Oracle features, but Pg features would sometimes be nice. But I don't use Pg, so I don't know what annoyances it has that Pg users would be thinking about. Maybe it's all-around better, I don't know.

        Both are really good projects, with their own strengths. I would say comparing Firebird and PostgreSQL is a much fairer comparison than Pg and MySQL or MySQL and Fb. Pg and Fb are more of a 'niche' comparison. MySQL has nowhere near the features of either of them, isn't nearly as safe, and just isn't designed with the same requirements in mind.

        Every single experience I've had with MySQL has been one of "fixing" stuff for a MySQL user who just couldn't get things to work. Joins that wouldn't work (but should have), joins that were slow, data being eaten ... And then there's reliability ... ugh. MySQL just wasn't designed with data integrity in mind, while Pg and Fb were. "Foreign key constraints can be
  • Web Tools (Score:3, Insightful)

    by mikeboone ( 163222 ) on Monday September 05, 2005 @03:39PM (#13484759) Homepage Journal
    So far I've stuck with MySQL for most of my projects since phpMyAdmin is so much better than phpPgAdmin. I can almost always get a web-based database tool running on the platform I'm developing for.

    If there's a better web interface for Postgres than phpPgAdmin, let me know so I can try it.
  • Heavy (Score:5, Informative)

    by HadenT ( 816717 ) on Monday September 05, 2005 @03:41PM (#13484776)
    I'm using PostgreSQL and MySQL, from my experience:
    1. I've never encountered corrupted data with mysql (It seems to be urban legend), and I have worked on tables with billions rows for two years.
    2. PostgreSQL has more features and/or is more complete (simple example can be auto_increment vs. sequences)
    3. PostgreSQL is heavier, and I hate statistics collector subprocess via udp (which seems to be eating 1-2% cpu all the time)*
    4. mysql isn't much (if any) faster.

    * - it's unlikely but possible my configs are to blame.
  • good one (Score:3, Funny)

    by qda ( 678333 ) on Monday September 05, 2005 @04:01PM (#13484907) Homepage
    "Since then both databases have evolved to wherever they are today." Thank you for that profound insight..
  • by melted ( 227442 ) on Monday September 05, 2005 @04:19PM (#13485013) Homepage
    One thing to consider - collations and Unicode support. Believe it or not, folks, Postgres does NOT support case-insensitive string comparisons. Or, more exactly it does, but you end up doing full table scan and converting everything into upper/lowercase, which is not an option on all but the smallest of the datasets. And even converting to upper/lowercase is a BIG problem for PostgreSQL, because it's UNICODE support is quite poor. So if your project has even remote possibility of using non-English textual data in lookups, steer clear of PostgreSQL.

    There's a discussion about including support for IBM ICU, but as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.

    MySQL is much better in this regard.
  • The scoreboard (Score:5, Informative)

    by ttfkam ( 37064 ) * on Monday September 05, 2005 @04:20PM (#13485018) Homepage Journal
    http://www.huihoo.com/postgresql/mysql-vs-pgsql.ht ml [huihoo.com]

    Changes/corrections since that study was made:

    PostgreSQL now natively supports BLOBs directly in tables (bytea type) as opposed to using oid references.

    PostgreSQL has always had "better than row level" locking, Multi-Version Concurrency Control.

    PostgreSQL has added Java and Ruby to its list of stored procedure languages.


    Now, here's the caveat. MySQL 5.0 is still marked as a "development release (use this for previewing and testing new features)" so I didn't include it in the above. If we include MySQL 5.0, we must also include PostgreSQL 8.1, currently in beta.

    MySQL 5.0 adds views, stored procedures, triggers, cursors, the bit data type, up to 65K varchar fields, two new storage engines (federated and archive), and a strict mode.

    PostgreSQL 8.1 adds two-phase commits, a role system, shared row level locks using SELECT, and many speed improvements.

    The strict mode in MySQL is most exciting to me. I always bought the argument that MySQL could have fewer features in exchange for greater speed. But there is no excuse (in my opinon of course) to accept random strings into numeric fields and other such contrivances (MySQL gotchas [sql-info.de]). Data integrity in a database should not be an optional feature.
  • by MikeFM ( 12491 ) on Monday September 05, 2005 @04:23PM (#13485036) Homepage Journal
    I write my db code to abstract all my queries away from the rest of my program. You access the queries I've writen via a XML-RPC interface. That way if you need to switch db all you have to do is rewrite your queries and none of your app code needs to change.

    Issues like speed, resource usage, sql features, etc really don't matter very much as for 99% of db applications it just makes no difference at all and for the 1% it does then you'd better hire someone that knows the differences in dbs without having to look it up on Slashdot.

    Most of the time it is better just to use the basics. I see people doing EVERYTHING in the db. Stored procedures, adding 1 + 1, massice complicated joins that'd be easy to do in anything other than SQL, etc. A big mistake. The db is the single hardest portion of your application server to replicate and load balance (even with the db supporting things like clustering). It's better to write your glue code in a normal language and just abstract that glue code as a sepperate service and use the db just for storing and retrieving data.
    • by tzanger ( 1575 ) on Monday September 05, 2005 @05:14PM (#13485305) Homepage

      I disagree.

      Putting everything (by everything I mean business logic) in the DB is the only sane way to keep your data consistent across multiple access methods. You simply can't thow data at a DB and then try to code and maintain consistent business logic in a half dozen client apps. You might be able to get away with a shared client access lib but even that can get messy.

      Let's face it: Your data's in the DB. Why pull it all into the application to work on some small subset? Do all the queries and joins and clauses and increments in the database. The DB knows best where the data is and how you're going to be tinkering with it (so long as you give it sufficent hints), so it's the only sane method to access your data in a logical fashion. That's precisely why all these scripting languages and language interfaces exist.

      I too use XML-RPC and SOAP (moreso the latter it seems, as XML-RPC is a little too light IMO) to access my data, but you can bet your sweet bippy I'm having the DB do as much as possible in order to transfer as little data as possible across my app-db link.

    • by musicmaker ( 30469 ) on Monday September 05, 2005 @10:28PM (#13486810) Homepage
      An classic example of a MySQL Idiot. You put things in the database so that they are _atomic_. You want things to either compmletely succeed or completely fail. Most application layers cannot do this successfully, and you end up with corrupt data.

      And go ahead, tell my that having corupt data doesn't matter. Then I'll throw sarbanes-oxley at you and laugh.
  • Sorry... (Score:4, Funny)

    by biglig2 ( 89374 ) on Monday September 05, 2005 @04:27PM (#13485059) Homepage Journal
    ...does this count as a dupe? Or is there a statute of limitations thing going on here?
  • by leoboiko ( 462141 ) <leoboiko&gmail,com> on Monday September 05, 2005 @04:37PM (#13485112) Homepage
    I once [slashdot.org] asked on slashdot about why people use MySQL and how does it compares to PostGreSQL. Got a bunch of interesting responses.
  • by adturner ( 6453 ) on Monday September 05, 2005 @05:09PM (#13485275) Homepage
    Short story, mysql.com's interpretation of the GPL is frankly a lot more strict then mine or my reading of the FSF's FAQ on the GPL. If it wasn't for that, I'd still be using MySQL for my company's application since I'm more familar with it.

    Anyways, PostgreSQL IMHO has some things going for it:
    - More features like triggers, stored procs, schemas, subselects, etc then the current stable version of MySQL supports. About the only thing I find myself using are subselects which are just a nice to have.
    - Attempts to be "safer" with your data via WAL, etc. Good for unreliable environments.
    - Tends to follow the SQL standards closer then MySQL
    - Is BSD licenced so you don't have to worry about licensing issues.
    - #postgresql on freenode is great. The people there are intelligent, knowledgeable and friendly if you're not an *sshole. They've helped me a lot.

    The problems I have with PostgreSQL is that:
    - INSERT is very slow (about 3x slower compared to MySQL/InnoDB) for my dataset. The "answer" is to use the COPY command or disable your indexes/FK's which is f*cking lame since you loose all your relational integrity. I was willing to trade off performance for disaster prevention (system crash, power failure, etc) by disabling WAL, but you can't actually do that.
    - The OSS tools available aren't as good for postgres as they are for MySQL. I've yet to find anything as nice or complete as phpmyadmin for Pg or something that supports schema's for ER Diagrams. Frankly, I'm sick and tired of designing my DB in vim.
    - Having to run vacuum all the time to help the query optimizer figure things out. Why this doesn't happen automagically in the background without me having to worry about it is beyond me.
    - In general, I find the documentation on mysql.com superior to on postgresql.org, but #postgresql more then makes up for it.

    Frankly, all the technical "problems" in MySQL or Pg can be worked around if you're willing to think out side of the box.
    • by Khazunga ( 176423 ) * on Monday September 05, 2005 @07:16PM (#13485915)
      - INSERT is very slow (about 3x slower compared to MySQL/InnoDB) for my dataset. The "answer" is to use the COPY command or disable your indexes/FK's which is f*cking lame since you loose all your relational integrity. I was willing to trade off performance for disaster prevention (system crash, power failure, etc) by disabling WAL, but you can't actually do that.
      Wrap the inserts in a transaction. Auto-commits force OS syncs, and these are slooow.
      - The OSS tools available aren't as good for postgres as they are for MySQL. I've yet to find anything as nice or complete as phpmyadmin for Pg or something that supports schema's for ER Diagrams. Frankly, I'm sick and tired of designing my DB in vim.
      PhpPgAdmin is way better than vim. Granted, PhpMyAdmin is better, but the difference is no showstopper.
      - Having to run vacuum all the time to help the query optimizer figure things out. Why this doesn't happen automagically in the background without me having to worry about it is beyond me.
      Like pg_autovacuum?
      - In general, I find the documentation on mysql.com superior to on postgresql.org, but #postgresql more then makes up for it.
      Better than the interactive manual [postgresql.org]?
  • extensions (Score:3, Interesting)

    by InsaneCreator ( 209742 ) on Monday September 05, 2005 @05:44PM (#13485459)
    After 4 years of using both PostgreSQL and MySQL, I'd say that one of the biggest differences between them is their extensibility.

    If PG lacks a feature, you have a very good chance of finding a script or an extension which implements equivalent functionality. Materialized views, ordering by different locales and hierarchical queries are some examples of this.

    On the other hand, if MySQL doesn't have a feature you need, you're pretty much screwed.

System checkpoint complete.