Does anyone have a link to an unbiased write-up of the pros and cons between PostgreSQL and MySQL? -- Alex Ezell
Alex Ezell wrote:> Does anyone have a link to an unbiased write-up of the pros and cons > between PostgreSQL and MySQL? >Agghhh!! Stop posting this flamebait, please. Use google or whatever. It is not that difficult. - Adam
I don''t believe such a thing exists. Personally I use Postgres whenever possible, in preference to MySQL. That said, MySQL has the following advantages: - better GUI tools - better documentation, both online and books - greater mindshare. For Web-based apps in particular, a large majority use MySQL over Postgres - *much* easier to use, for simple apps or if you don''t have at least a moderate background in SQL beforehand - speed, but only for those DBs that are 99% read only. Once you start doing a large number of writes, the speed difference virtually disappears. That said, many types of apps (e.g. online stores) are 99% read only and very well suited to MySQL Now I''ve stated my preference, I use Postgres because: - it''s more of a "grown up" database than MySQL. If you know Oracle, SQL Server, DB2, etc., you''ll find Postgres OK and you''ll probably find MySQL''s limitations too much to bother with. Postgres doesn''t have the same feature set as Oracle, SQL Server, DB2, etc., but it''s steadily heading in the right direction - no nasty surprises. In my experience, Postgres does exactly what it''s supposed to, all the time. As I started using Postgres before MySQL, I don''t see any reason to adopt MySQL because Postgres does everything *I* want it to do with minimal fuss - foreign key integrity validation (don''t believe MySQL has this yet; could be wrong), stored procs (ditto), transactions (ditto again), triggers (ditto yet again), tablespaces (ditto once more). All of these mechanisms make life much easier for an app developer; if I can offload this sort of stuff to the DBMS, it''s that much less code I have to write and support personally. For example, if I have to implement an audit trail facility on an app, in Postgres I use one or more triggers to implement it; not a line of my app code gets changed and Postgres takes care of any corner case issues like having a power fail at the worst possible time... Also, that processing load is on the DB server, not the Web server, which is generally where I want it to be - standards compliance. Insofar as any DBMS can be said to adhere to standards, Postgres has it all over MySQL - ACID compliance - (the really big one) if you try to insert invalid data into a field, MySQL will often change what you''re trying to write and not return an error. In my experience, Postgres always returns an error. Given that I don''t want to have to re-read data I''ve just sent to MySQL to confirm that it saved what I wanted it to save, that''s an absolute showstopper for me personally For "simple" apps (and I''d wildly categorise that as having <= 6 DB tables), MySQL is probably the better choice most of the time, although I''d personally tend to use Postgres simply because I''m more competent with it. For more complex apps (>6 DB tables, again a wild generalisation), I''d go with Postgres because experience tells me that I''ll probably find I want to use features in Postgres that aren''t in MySQL. In terms of Rails, I''d categorise it as more MySQL-friendly at this point. I''ve got it working with Postgres, but Rails itself was initially designed to work with MySQL and it shows as you try to leverage Postgres'' features. I won''t be changing from Postgres any time soon, and I''ll continue using it with Rails, but it''s something to be aware of if you''re committed to Rails. I''m sure this will trigger (pardon the pun) a response from the MySQL advocates out there, and I''m interested in finding how MySQL has advanced since I last looked at it seriously. Dave M. On 6/18/05, Alex Ezell <taranis-QicVIiZSC4eOfmbybZFjldBPR1lH4CV8@public.gmane.org> wrote:> Does anyone have a link to an unbiased write-up of the pros and cons > between PostgreSQL and MySQL? > > -- > Alex Ezell > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Adam Majer wrote:> Agghhh!! Stop posting this flamebait, please. Use google or whatever. It > is not that difficult.Don''t be so hard on the guy... surely it''s a legitimate question and not intended as flamebait! I know the MySQL versus PostgreSQL debate can be quite heated, but this seems like a well behaved, helpful list, and not the kind of place you''ll find religious software debates going on, at least, not as far as I''ve seen. Having said that, it''s an old question, and Google would have been my first port of call. As is the case with almost all unanswered questions, to avoid being sent to that "www.justf***inggoogleit.com" site, one should Google first. In fact, having recently decided to switch from MySQL to PostgreSQL, Google *was* my first port of call, and (not surprisingly) turns up a WHOLE lot of results: http://www.google.com/search?q=mysql+versus+postgresql I think this is the article I read, but I can''t be sure as I read quite a few: http://www.databasejournal.com/features/postgresql/article.php/3288951 Personally, I''m not going to get religious about my database of choice. I''ve used MySQL for websites (both big and small, trivial and non-trivial) for years without any real problems, and am so far finding PostgreSQL to be great as well. In the future, I think I will use PostgreSQL quite a lot, but may still drop back into MySQL now and then when I just want to bash some simple website together for a friend so they can host it anywhere. The truth is, they''re both great for certain tasks, and they share a lot of common ground, it''s just knowing when to use which, or if you don''t plan to learn both, picking the one that suits your needs most of the time. If you don''t especially need the more heavyweight features that come with PostgreSQL (if you don''t know what they are, chances are you may not need them), my advice would probably be to go with MySQL, as generally hosting is more readily available. However, since Rails hosting isn''t exactly common, if you''re planning to host somewhere like Textdrive which does have PostgreSQL installed, you may wish to take advantage of that and use PostgreSQL instead. I decided the best path would be to make my transition from PHP+MySQL to Rails+PostgreSQL all in one go, since I''m already having to learn a new way of doing things for Rails, I might as well go the whole hog and switch database systems at the same time. Try and read as many comparisons between them as you can, and I think you''ll just end up instinctively knowing which one will work best for your needs. Cheers, ~Dave -- Dave Silvester Rent-A-Monkey Website Development Web: http://www.rentamonkey.com/
There ya go - MySQL already has transactions, and triggers and stored procs are currently in beta. Guess I''d better give it another look when the appropriate requirement surfaces again. Maybe I should have pointed out this: both Postgres and MySQL will be good fits for many/most Web apps, of the sort that Rails developers will be creating. Bottom line is that I doubt your app will succeed or fail just because you picked Postgres over MySQL, or vice versa. Part is the beauty of Open Source is that there can be several very fine products in the one space, and issues such as personal preference can hold sway over marketing, peer pressure etc. when making a choice. Dave M. On 6/18/05, Sascha Ebach <se-eFwX6J65rk9VioaHkBSlcw02NpfuEekPhC4ANOJQIlc@public.gmane.org> wrote:> David Mitchell wrote: > > I''m sure this will trigger (pardon the pun) a response from the MySQL > > advocates out there, and I''m interested in finding how MySQL has > > advanced since I last looked at it seriously. > > http://dev.mysql.com/ > > (Transactions have been in MySQL for many years now) > > Sascha >
On Sun, 2005-06-19 at 01:08 +1000, David Mitchell wrote:> There ya go - MySQL already has transactions, and triggers and stored > procs are currently in beta. Guess I''d better give it another look > when the appropriate requirement surfaces again.Yes, but MySQL doesn''t have plRuby. ;-) - http://moulon.inra.fr/ruby/plruby.html PostgreSQL, all the features you need next year... available last year. -Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com *******************************************************/
Alex Ezell wrote:> Does anyone have a link to an unbiased write-up of the pros and cons > between PostgreSQL and MySQL?Thanks Dave and David for exactly what I was looking for. Real-world explanations of why you use what you use. Of course, I went to Google first, but wasn''t satisfied with what I found. Then I came here. This is not my first trip on the development boat. I appreciate you taking the time to post your mature and well-reasoned opinions. Thanks again! -- Alex Ezell
On 18-jun-2005, at 17:51, Alex Ezell wrote:> Alex Ezell wrote: > > >> Does anyone have a link to an unbiased write-up of the pros and >> cons between PostgreSQL and MySQL? >> > > Thanks Dave and David for exactly what I was looking for. Real- > world explanations of why you use what you use. > > Of course, I went to Google first, but wasn''t satisfied with what I > found. Then I came here. This is not my first trip on the > development boat. > > I appreciate you taking the time to post your mature and well- > reasoned opinions. > > Thanks again!What I find nice about the mailing list is that even PgSQL/MySQL did not fluse into flamebait. Gotta love this list. -- Julian "Julik" Tarkhanov
> Yes, but MySQL doesn''t have plRuby. ;-) > > - http://moulon.inra.fr/ruby/plruby.html > > PostgreSQL, all the features you need next year... available last year.That''s great and all, but you don''t use many stored procedures in Rails apps. Doing so would limit you to PostgreSQL for your app. It is nice to have the option, if PostgreSQL dependance is not an issue. I''ve never used pl/pgsql, but I know that TSQL (MS SQL) is not exactly fun to program in. Thank god the next version will have c# stored procedures... -- rick http://techno-weenie.net
On Sat, 2005-06-18 at 11:45 -0500, Rick Olson wrote:> > That''s great and all, but you don''t use many stored procedures in > Rails apps.You might not. But, I use them quite a bit. Triggers, functions, etc... are quite useful and can keep your application finely tuned.> Doing so would limit you to PostgreSQL for your app.If I am building an open source app, thats one thing... If I am building a solution for a client, I take advantage of the programs that I use. Using procedural languages is something that I do need for projects. Sometimes it makes more sense for me to put some logic in the db, rather than in my Rails code. It''s like using views, but more intelligent views. I never assume that my Rails interface will be the sole interface to that data. Often times, a client might need a GUI program for some tasks and it''s much easier to call a function in the DB rather than rebuild it for each interface/application. Then again, I''m the kind of person that builds triggers that send emails when called. :-) -Robby -- /****************************************************** * Robby Russell, Owner.Developer.Geek * PLANET ARGON, Open Source Solutions & Web Hosting * Portland, Oregon | p: 503.351.4730 | f: 815.642.4068 * www.planetargon.com | www.robbyonrails.com *******************************************************/
Ditto - I''ve got 3 rules that I try to follow wherever possible:
1. When I''ve got database-internal stuff that has to happen *as a
result of data access or changes*, use triggers. The classic example
is an audit trail; if I have to keep track of who does what to various
tables in the database, I use one or more triggers to create entries
in an "audit" table automatically whenever e.g. an UPDATE occurs to a
table. I want the database to be taking care of that stuff for me; I
don''t want to be relying on users or external apps to do it for me
2. Nobody except the DBA gets SELECT/UPDATE/DELETE/INSERT access to
the database; they get access to the specific set of stored procedures
they need to do what they need to do. Even if I''m the DBA, I use
stored procs whenever possible. There''s many reasons for this; I can
tune or tweak stored procs for performance without breaking code, I
can limit what people are able to do to the smallest subset necessary,
I can ensure that stupid 10-way JOINs are prevented altogether, I can
build all sorts of error checking into the stored proc and *know* that
it can''t be bypassed by users, I can ensure that indexes are searched
in the appropriate order and we aren''t doing loads of table scans when
they''re unnecessary just because a user has his SELECT/WHERE clauses
in the wrong order, ...
3. Except for the simplest databases, people look at the data using
views, not SELECTS from tables. With a view, I can show them exactly
the data they need to see, and hide all the stuff they don''t want to
(or shouldn''t) see.
These aren''t rules I came up with in isolation - many DBAs work
strictly to these rules, because they can remove the cause of lots of
pain down the track.
For an example of rule 2, consider the case where you''ve got 3 tables:
- customers
- items (i.e. a bunch of things you''re selling)
- customeritems (i.e. which items have been ordered by which customer)
If a new customer fronts up and wants to buy something, without stored
procs I''m faced with doing the following:
- INSERT INTO CUSTOMERS ...
- SELECT cust-id FROM CUSTOMERS WHERE cust-name=''...''
- SELECT item-id FROM ITEMS WHERE item-description=''....''
- INSERT INTO CUSTOMERITEMS (cust-id, item-id, qty)
With stored procs, I can do this instead:
- EXEC ADDCUSTOMERITEM(''Bob Smith'', ''Mexican
rug'', 7)
and have the ADDCUSTOMERITEM stored proc do all the behind-the-scenes
work for me. It''ll search for ''Bob Smith'', and add
him if he isn''t
already a customer; it''ll search for ''Mexican rug''
and get the
item-id, then it''ll add an entry to CUSTOMERITEMS pointing to
Bob''s
cust-id, the Mexican rug''s item-id, and the quantity required. I can
also bolt in a bunch of error checks to ensure e.g. the customer''s
name is less than 30 characters long, that the customer was added
correctly, that qty isn''t a negative number, etc.
I code up ADDCUSTOMERITEM once, throw in a big pile of error checks,
test it, and then I simply call it over and over again from any app I
or anyone else may write in the future.
If I have to rely on developers doing the above
INSERT/SELECT/SELECT/INSERT waltz in their code, suddenly I''m adding
the opportunity for them to make mistakes. Furthermore, if I need to
add a couple of extra fields to the CUSTOMERS table later on, I can do
so and hopefully change the ADDCUSTOMERITEM stored proc so that
EXEC ADDCUSTOMERITEM works exactly as it did before I added the fields
- no user code needs to be changed. The end result is much more
maintainable.
For an example of rule 3, consider a table called EMPLOYEES. Without
an appropriate view, users may do SELECT * FROM EMPLOYEES and get all
the details of what''s in the EMPLOYEES table.
What happens if, later, I add a SALARY field to EMPLOYEES? Now I
don''t want people to be able to do a SELECT * FROM EMPLOYEES, because
then they''ll see what everyone earns - not good! I have to tighten up
security, and that''ll almost inevitably break apps that are hitting
the EMPLOYEES table.
A better solution is to have a view called e.g. VIEWEMPLOYEES, which
is just a SELECT NAME, PHONE, EMAIL, ... FROM EMPLOYEES under the
covers. If I remove users'' ability to do a SELECT ... FROM EMPLOYEES,
but give them SELECT ... FROM VIEWEMPLOYEES, they won''t notice any
difference. However, if I add a SALARY field to EMPLOYEES later, the
VIEWEMPLOYEES view won''t change because SALARY isn''t in its
SELECT
NAME, PHONE, ... list. I can create a separate view
VIEWEMPLOYEESPLUSSALARY and give access to it to specific
people/groups who need to see salary data, and limit it to only them.
All this stuff works in Postgres, and has for years. I can
INSERT/DELETE/UPDATE a VIEW, if I get a bit clever, which is
particularly handy for Rails. I''m still working out how best to use
Postgres with Rails, but that''s fine - Rails is massively more
productive than anything else I''ve tried, and I can put up with a bit
of trial-and-error to get the result I want with Postgres.
It sounds like MySQL is heading in that direction as well now, which
will be really cool; suddenly an extra option is emerging, which is
never a bad thing. MySQL is really well supported by Rails, plus
available at ISPs everywhere, plus there''s any number of books and
reference material available if/when you need them.
I like MySQL; I just like Postgres more.
Dave M.
On 6/19/05, Robby Russell <robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org>
wrote:> On Sat, 2005-06-18 at 11:45 -0500, Rick Olson wrote:
> You might not. But, I use them quite a bit. Triggers, functions, etc...
> are quite useful and can keep your application finely tuned.
>
> > Doing so would limit you to PostgreSQL for your app.
>
> If I am building an open source app, thats one thing... If I am building
> a solution for a client, I take advantage of the programs that I use.
> Using procedural languages is something that I do need for projects.
>
> Sometimes it makes more sense for me to put some logic in the db, rather
> than in my Rails code. It''s like using views, but more intelligent
> views. I never assume that my Rails interface will be the sole interface
> to that data. Often times, a client might need a GUI program for some
> tasks and it''s much easier to call a function in the DB rather
than
> rebuild it for each interface/application.
Just doing a fresh Rails install on OS X. When I do a ''gem install rails'' i get the following installed instead? *************** Computer:/usr/local/src/rubygems-0.8.4 root# gem install rails Attempting local installation of ''rails'' Local gem file not found: rails*.gem Attempting remote installation of ''rails'' Successfully installed rails_analyzer_tools, version 1.0.0 Installing RDoc documentation for rails_analyzer_tools-1.0.0... *************** hmmmm... -Sean
Hmm, it was picking up an old gems (0.8.4) Nevermind... -Sean Sean Stephens wrote:> Just doing a fresh Rails install on OS X. When I do a ''gem install > rails'' i get the following installed instead? > > *************** > Computer:/usr/local/src/rubygems-0.8.4 root# gem install rails > Attempting local installation of ''rails'' > Local gem file not found: rails*.gem > Attempting remote installation of ''rails'' > Successfully installed rails_analyzer_tools, version 1.0.0 > Installing RDoc documentation for rails_analyzer_tools-1.0.0... > *************** > > hmmmm... > > -Sean > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Sat, 18 Jun 2005, Rick Olson wrote:> That''s great and all, but you don''t use many stored procedures in > Rails apps. Doing so would limit you to PostgreSQL for your app.Many other things can do so as well. As somone else pointed out, MySQL is so limited in functionality that it can be essentially impossible to move a schema from PostgreSQL to MySQL. Another thing nobody''s mentioned yet is that you can get a lot more secure with PostgreSQL. For most web applications, the database login and password for the web user is going to be somewhere on the system accessable to that user and in plaintext, or something easily de-obfuscated. That means that someone who manages to get the privleges of that user is going to have the password, and can perform any actions he likes. (SQL injection attacks are a popular way of doing this.) However, with a bit of clever coding, you can minimize access anyway. Want to get user information or update it? Instead of SELECT * FROM user_info WHERE user_id = 1234 instead SELECT * FROM get_user_info(1234, ''that_users_password'') and have the function return nothing if the password is incorrect. Using techniques like this, you can put up one more huge wall between you and attackers. I''m now starting to contemplate how one could get ruby to do some of the work of generating these sorts of functions automatically. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On 6/20/05, Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> wrote:> > SELECT * FROM get_user_info(1234, ''that_users_password'') > > and have the function return nothing if the password is incorrect. > > Using techniques like this, you can put up one more huge wall between > you and attackers. > > I''m now starting to contemplate how one could get ruby to do some of the > work of generating these sorts of functions automatically.You can avert SQL injection attacks easily by using place holder instead of direct value and let the database perform the substitution. Better than dynamically generating sql statements, that are repeatedly parsed and re-planed by query engine. OTH db will understand and prepare a single query plan for: "SELECT * FROM user_info WHERE user_id = ?" first time as it was seen. Just my 0.02$, zsombor -- http://deezsombor.blogspot.com
On Mon, 20 Jun 2005, Dee Zsombor wrote:> On 6/20/05, Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> wrote: >> >> SELECT * FROM get_user_info(1234, ''that_users_password'') >> >> and have the function return nothing if the password is incorrect. >> >> Using techniques like this, you can put up one more huge wall between >> you and attackers. > > You can avert SQL injection attacks easily by using place holder > instead of direct value and let the database perform the substitution.Yes, and I do that. But that doesn''t defend against other attacks, such as your web server account being compromised. Keep in mind, that web server is probably one of the, if not the, weakest link on that machine. And that web server has a password to access your database. How much access do you really want to give it?> "SELECT * FROM user_info WHERE user_id = ?"If your web server account can do that, it can probably do SELECT * FROM user_info and grab all your user information. It doesn''t need that access; why are you giving it that access? cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On 7/1/05, Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> wrote:> > "SELECT * FROM user_info WHERE user_id = ?" > > If your web server account can do that, it can probably do > > SELECT * FROM user_info > > and grab all your user information. It doesn''t need that access; why are > you giving it that access?I dont see what additional protection is gained by opting for:> SELECT * FROM get_user_info(1234, ''that_users_password'')I mean if webserver account can execute this query why ''SELECT * FROM whatever'' is it more difficult? Zsombor -- http://deezsombor.blogspot.com
On Fri, 1 Jul 2005, Dee Zsombor wrote:> I dont see what additional protection is gained by opting for: >> SELECT * FROM get_user_info(1234, ''that_users_password'') > I mean if webserver account can execute this query why ''SELECT * FROM > whatever'' is it more difficult?Because the webserver does not have SELECT access on the user_info table. It has only execute access on that function, which verifies the password. The function itself is declared SECURITY DEFINER; it''s effectively ''setuid'' and so the function can access the table, even though the database user calling it cannot. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA