Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Comparison, questions for Postgres, MySQL

by talexb (Chancellor)
on Oct 07, 2002 at 20:05 UTC ( [id://203479] : perlmeditation . print w/replies, xml ) Need Help??

I was wondering if there were any opinions or PM nodes on the slightly off-topic subject of comparisons between Postgres and MySQL, specifically on the subject of triggers, views and rollbacks.

I have found this information about MySQL, the Postgres web site (for my area anyway) and found Persistence::Object::Postgres and Postgres information on CPAN. I was just curious if anyone else had comments or opinions.

--t. alex
but my friends call me T.
  • Comment on Comparison, questions for Postgres, MySQL

Replies are listed 'Best First'.
Re: Comparison, questions for Postgres, MySQL
by diotalevi (Canon) on Oct 07, 2002 at 21:54 UTC

    I'm not a DBA but I am a fan of PostgreSQL. What I do hear on the PostgreSQL lists is that several of the features that MySQL gained for version 4.0 have been standard for quite awhile now. I know my own development has expanded to use much of PostgreSQL's features including triggers, rules, functions, referential integrity and views. I really appreciate PostgreSQL handling all the details of what happens when I delete a record from a table. The normal referential integrity checks then go through the other tables that reference that table and ensure that the related information is suitably cleaned up. I could do all that work in MySQL but then that just means I have to write additional perl code to implement it myself when (in my opinion) the database should be able to handle that on it's own. Others can speak to the widely known scalability issues - multiple simultaneous clients bringing MySQL to a stand-still. In general, from my non-DBA perspective PostgreSQL beats MySQL on features and scalability.

    Others can speak to the variations between each on select/update/insert speed. I could (and initially did) go on about how PostgreSQL is supposed to be faster for things that aren't simple SELECT operations but... well... I was getting farther afield from my experience than I should. My perspective is that PostgreSQL is a database I can grow into - there is additional capacity for getting further into it should I need to. MySQL - well from what I hear I'd already be pushing it and frankly, I don't want to do that for data I'm responsible for. I would characterize it as a question on when you want to upgrade - do you want to upgrade to PostgreSQL now or upgrade later? I'm just doing that from the get-go.

    Oh yes, I should also mention that I never considered non-free databases since my perl/RDBMS work is all for non-profits which couldn't spend money. I have no opinion on 99% of the other databases except for Lotus Domino (which rawks) which I develop for and use on a daily basis.

    So... if MySQL has somehow implemented all the features I'm using in PostgreSQL then that'd be nice to hear as well. I won't switch but I'll at least learn the sucker. ;-)

    Update addition: I really like Ovid's characterization of the question over at 203521. I think my response is just a specific instance of the more general question. In my case PostgreSQL has features I need so... I use it. No rocket science there.

    __SIG__ printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::svref_2object(sub{})->OUTSIDE </code>
Re: Comparison, questions for Postgres, MySQL
by Ovid (Cardinal) on Oct 07, 2002 at 22:42 UTC

    Why are you comparing these two? If it's just curiosity, that's fine. If you're looking for a technology solution, have you identified your requirements? One way to approach a situation like that is to list your needs and wants across the top of a page, list the potential technologies down the side of the page and create a grid where you can check off features. Once you have candidates narrowed down, you can start looking at your candidates more in depth, as you are doing now.

    Of course, if you've already done that, feel free to disregard this post. I just like to occasionally remind people to find answers to questions, rather than pick the answers and then question them :)


    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

        Why are you comparing these two? If it's just curiosity, that's fine. If you're looking for a technology solution, have you identified your requirements?

      Those are valid questions.

      The database is going to be used as a librarian and a recorder of transactions. To me it sounds simple enough that MySQL would do fine, but the CTO wants to be sure that we can do things like views, triggers and rollbacks.

      My preference is for MySQL because I know it very well and it has always performed admirably, but if the CTO wants to be able to the fancier things, that's cool. I just wanted to hear about any differences or gotchas that I should worry about.

      And if it should happen that we don't use the fancy stuff, I can always go back to MySQL -- Perl lets me do that :) quite painlessly, I understand.

      --t. alex
      but my friends call me T.
(jeffa) Re: Comparison, questions for Postgres, MySQL
by jeffa (Bishop) on Oct 08, 2002 at 00:16 UTC
    When i first started working at with maverick and eduardo, i asked mav this very question. We were using MySQL at the time, and he explained that the MySQL people were more interested in speed than features. Conversely, the Postgres people were more interested in features than speed. Since we didn't need transactions or inner selects and we did need speed, maverick chose MySQL.

    Flash forward to now. Postgres has managed to finally catch up with MySQL in the speed department, but MySQL is still behind Postgres in the feature department. Me? I still use MySQL simply from habit, but i still haven't worked on anything serious enough that requires triggers or transactions. RI, views, and inner selects are good, but not completely necessary. If you need them or anticipate needing them (or any of the other features that Postgres has), use Postgres. As long as you are using Perl, you will be smiled upon favorably by the Gods. ;)


    (the triplet paradiddle with high-hat)

      You know... there is one thing that is either available in non-perl implementations for PostgreSQL that might make me envious of MySQL (assuming it has the feature). asynchronous queries. It galls me that the DBD::Pg driver has to fully complete the PostgreSQL query, copy the data into perl-space and /then/ you can work with it. (it might do lazy copying but that isn't the point). I'd really like it if PostgreSQL got that feature and for some uses that would be a Killer Feature. That's more a function of the existing driver though since PostgreSQL supports it, we just aren't taking advantage of it (yet). Or is there some reason that DBI can't handle aio?

      __SIG__ printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B:: +svref_2object(sub{})->OUTSIDE
      Since we didn't need transactions
      I'm always stunned when I read such a thing. It makes me wonder why you are using a relational database in the first place. If you don't use transactions, I guess you don't care about the integrety of you data. But if you don't care about that, why have it in the first place?


        Why did we use a relational database... well, there is really a simple answer for that. We didn't for 1/2 of our project, and we did for the other 1/2. :) Let's look at what we built, shall we?

        The "project" at surfari consisted off two main products, a website and a search engine. The website needed to keep basic user information, such as username, password, favorite sites, personal bookmarks, etc... Now, I understand your "stunned" outrage at a statement like: Since we didn't need transactions... but, let's analyze why it is that our esteemed friend jeffa would make such a dangerous statement.

        When does one "need" transactions? Well, first and foremost, when your data is valuable :) Let's analyze the different dimensions of data that we were storing, and let's see how transactions would have added value...

        • User information - Nope... it was a single "add user" button that pretty much just inserted or did not insert a user into the user table. Maverick's code checked the return status of that insert, and it was impossible for the user table to be left in an indeterminate state
        • User bookmarks - Well, let's see, you could either remove a bookmark, or add a bookmart to the bookmark table. This consisted of "look up the user id", and "add a bookmark ID to the bookmark table with a fake foreign index to the user id." Hm... doesn't really seem like the data could have been left in an indeterminate state here either...
        • Visit tracking - Let's see, each time the user hit a webpage, we stored where they'd come from (thank you HTTP referer!) where they were, etc... we didn't store how long they'd taken to get there, because we could batch process the time dimensionality offline and not negatively impact user experience. So, let's see, another atomic addition to a singular table... yet again, Transactions would not have helped us out here.
        • User preferences - Ah, customization! This was going to be what was going to make my 10% of the company in stock options enough to retire... you see, the user would love to spend hours on a shopping mall website setting up their own private color scheme! well, let's see, each time they changed a parameter, an update was fired off to their "customizations" entry for that particular parameter. Hm... another singular atomic transaction. Crap.

        Hm... So, why were we using a relational database in the first place?

        We were using the ability to store "fake foreign keys" (referential integrity wasn't insured by MySQL, but that's ok, it didn't change the value of our data) with great ease. We had an *incredibly* nice interface to a very customizable persistent data store... We had a really nice query language for our data store... and When we started doing collaborative filtering (Other users that like this stuff liked this other stuff), having it all in a relational database made that development *super fast*! Not to mention that with "time to market" constraints of Internet time, the fact that it provided all of this super duper functionality in an API we all already knew and loved made choosing MySQL a no-brainer. None of the features of the, admittedly incredible, Postgress would have made 1 lick of difference... after all, it's not like we were:

        Removing 100 dollars from debit table.
        Adding 100 dollars to credit table.

        :) God I love classical examples.

        So, where didn't we use relational databases? In our search engine! We took a DEC Alpha optimized B-Tree library, put an advanced forest-and-trees tree balancing and distributing decision walker in front of it, added a *sweet* splay-like aging cache mechanism in front of it to alleviate the von-neumann bottleneck, and took advantage of log2(n) as much as we could :)

        So... did we care about the integrity of our data? Sure! We were going to retire from the IPO... however, in *many* of the applications I have faced in my professional life, (which granted, have *not* been in the financial sector), all "logical transactions" have been of single statement cardinality.

        I advocate right tool for the right job... and in this case, the only reason a relational database was used was the really sweet query interface to a persistent data store, and the handy dandy functionaity provided by auto-increments, indexing, etc... I can guarantee you that any time I see a more complex data model, I reach for a transactional data store...

Re: Comparison, questions for Postgres, MySQL
by Steve_p (Priest) on Oct 07, 2002 at 22:03 UTC

    The standard MySQL's that you will see installed at most webhosting companies will not have any support for rollbacks (this requires additional configuration that isn't part of the standard Red Hat RPMs). I haven't been up on the lastest things with MySQL, but I believe that MySQL 4.0 may be adding support for subqueries, but I think triggers and views are still a ways off (version 4.1 was the last I heard).

    Postgres, on the other hand, has support for all three of these features in their current version, and have had all three for quite some time.

    Although I work with a heavy duty database at work on a daily basis, for my experimentation and "play" time, I use MySQL. Why? I've got a few reasons:

    • most webhosts support only MySQL
    • most open source web-based software supports MySQL
    • the majority of books discussing web-based programming only talk about MySQL

    To be honest, I don't have an opinion regarding PostreSQL since I've never used it. I'd be interested in hearing opinions from anyone who uses PostgreSQL regularly myself, especially regarding performance on a shared system.