Slashdot Mirror
Comparing MySQL and PostgreSQL 2
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?
SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.
I have seen many url comparing the two.
But haven't seen any recent study.
Is mySQL still the most popular one?
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.
MySQL definitely has a lot more mindshare. Therefore it's probably still more popular.
Developers: We can use your help.
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?
Religion for nerds. Stuff that really matters
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.
SCO employee? Check out the bounty
I met david axmark on a Linux Lunacy cruise, where he was talking about how great mysql was. When pressed about things like transaction support ( this was a couple of years ago ) and stored procedures he responded "yes, well, it's the most popular database on the planet".
He didn't seem to be at all bothered that this the main argument people give for using windows.
Sitting Walrus Blog
I'm using both, but mainly Postgres. From what I can tell:
... well, bad)
Postgres 7.5
Pros:
- Supports stored procedures
- Supports triggers
- Supports schemas
Cons:
- Heavy on resources
MySQL 4.0
Pros:
- Fast
- Easier to find PHP scripts that use it
Cons:
- Bad relational support (and yes, I know about InnoDB, but even then, it's a bit
- No stored procedures/triggers
- Easily corrupted by crashes.
I sense much beer in you. Beer leads to intoxication, intoxication leads to hangover. Hangover leads to sobering.
Comment removed based on user account deletion
I have been wondering, which is better, vi or emacs?
I am TheRaven on Soylent News
...I'm starting to feel more and more drawn to check Postgres out. I've been mostly happy with MySQL though; I just want to see what I'm missing (VACUUM scares the shit out of me - but is it still there?)
There'll be under 400 posts. It's a boring subject except for a selected few. Plus it is a vacation day as you correctly pointed out.
You are such a noob :-)
http://www.geocities.com.nyud.net:8090/mailsoftwar e42/db/
It was updated March '05.
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 (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS, 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.
Friends don't let friends misuse the subjunctive.
there is a short (decent) comparison at this url. ;-)
From my point of view (web application developer, Ozone 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
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.
:%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.
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.
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 and Postgres. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.
Get your own free personal location tracker
So are you :)
MySQL still seems to get an unreasonable amount of attention. Like firefox or OOo, it's not that much better than the alternatives but has become something of a mascot of the open source movement (or rather the dual-licensing variant therof). However, it has caught up a lot in terms of functionality, just as PG has in speed, and generally the two have grown much closer together as their respective shortcomings are sorted. There is far less to choose between them than there once was.
I am trolling
Is that a typo? Cos I would always have said "lego is".
Where are you from.... Just wondering....
Get your own free personal location tracker
My mother's friend's son says it is.
The signal / noise ratio was also MUCH, MUCH higher.
Eureka Science News - automatically updated
It would be a great question if newer benchmarks were out and linked in the article.
Who cares about something 6 years old? How about now?
The question can not have an answer if all the data linked is 6 years old.
http://saveie6.com/
why not just ask the frod chevy question while you are at it and then link to microsoft and linux for good measure..
how did this make it to the front page and why are people still talking about mysql as if they were purchased by SCO, from what I read SCO bought a license to use MYSQL..
-anyone: "hey guys, SCO bought some acme widget"
-slashdot: "acme is evil!!! we will boycott acme!! death to SCO"
see how the above is not helping out anything..
anime+manga together at last.. in real time.
because I started buidling a proprietary project for my company and without really looking into the MySQL licensing decided to go with MySQL (I know I am stupid). Anyways, I also purchased SQLyog (A GUI for working with MySQL) to speed up development.
Now I am stuck. I have to either a) switch to postgres, which does not seem too bad an option, or b) pay for MySQL (and prices are not even listed online *shudder*). What is the learning curve for postgres? Are there any good books? What about tools like SQLyog?
GPL - Got punk'd lately?
I have a zero digit ID, I win!
What's a 4 digit ID again?
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.
Except for big iron (where DB2 dominates) and Micorosft environments (where SQL server dominates), Oracle is the dominant player.
I recently moved my deployments from Oracle to MySQL because:
1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.
2. MySQL's new administration tools are significantly better than Oracle's out of the box tools (This is why a year ago I refused to use MySQL for production, and now I've switched everything).
3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).
5. MySQL's supposed gotchas pale in to comparison to Oracle's. When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)
I think that anybody deploying Oracle for non-Oracle applications is going to have to very seriously consider MySQL if for no other reason than all the DBA salaries you can get rid of.
If you want to buld a $1M cluser, stick with Oracle (for now). If you want to run application specifically designed by (or for) Oracle, stick with Oracle. Otherwise, switch at the first opportunity.
MySQL will still be popular until most standard (cPanel/Ensim/Plesk/DirectAdmin/etc) hosts start supporting PgSQL by default. Most of them only support MySQL, and if someone is going to write a script, chances are it will be written for the majority. I mean, it's no excuse not to write a decent database abstraction layer, but regardless.
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.
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.
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.
mysql has 5 letters
postgresql has 10 letters
so clearly, postgresql has twice the letters, a clear victory if you ask me
as bad a name as joomla.
The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.
For a mom and pop ISP with only 3 or 4 employees this is significant. Is it feature filled? No
Its just included in the default user account which is difficult if not impossible with posgresql unless you manually install it for each account.
Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.
This is why msql is so popular. Its what ISP's prefer.
http://saveie6.com/
MySQL has partnered with SCO MySQL must be evil. OK EVERYONE STOP WHAT YOU ARE DOING RIGHT NOW!! and Re-port all your application into PostgreSQL!!!. Seriously partnerships are not alliances, IBM is Partnered with Microsoft and IBM is one of the largest Linux supporters out there. All that a partner ship does is allow company A to buy company B's product or servies at a bulk price. with the agreement that so many products will be sold or so much will be paid a month.
If something is so important that you feel the need to post it on the internet... It probably isn't that important.
only old comparisons matter...
(had to do it)
how long until
With over 3 million rows in the largest table in our database, Posgres is starting to show its weaknesses.
Anytime that I see a VACUUM VERBOSE ANALYZE in the process list, I know that my queries won't complete for 3 hours or so. But if we don't vacuum the tables, the entire database grinds to a halt (load average of 15 during the busy times).
darn VACUUM
No. You can make a derivate database engine from MySQL, but you aren't forced to release it under the GPL, and are free to release it under a propriety license if you want, and sell it even if you think people will buy it.
This is why people are protesting dual-licensing schemes that are half GPL, half propriety. They'd rather they be full GPL and the derivate of any GPL software be open-source'd, for anybody to learn and/or improve upon. Sure, dual-licensing can be the best of both worlds, but some people would rather all incarnations of MySQL be open to the public.
Would a Kroger executive talk enthusiastically about your new "partnership" with them?
... but it changes when another company is quoted as saying anything more than "we sold them a license and we'll sell you one too!"
Usually, companies don't want to be seen publicly supporting nutcases who try to make a news story about buying some toothpaste.
SCO can have the press conferences it wants and tell everyone whatever they want
So, if I try to insert, say, a string of 10 chars into a varchar(9) field, what will it do? Will the magic version 5 reject it, as ever real database does, or will it truncate it silently, just as Toy databases (ala MySql 4.x) are wont to do?
What about the whole not-null thing? You know, if a field is set to NOT NULL and you don't populate it when you insert a row, a real database will reject it, where as a Toy database will accept it (MySql 4.x again!) and populate it with ... some other value.
Yeah, right.
I've used both databases for years and they both have their place. One thing that is rarely mentioned about Postgresql is the Table inheritance. I have yet to find this feature in another RDMS. Anyone know of a database out there that supports this feature?
It allows you to create a table that inherits the fields of a parent table. Each time you insert data into the child table the parent also gets a record. You can use regular expressions to select from all child tables. Very cool and useful.
why should it matter what the server wants? The client can be as user-friendly as it needs to be, and can silently send '\q' to the server whenever you write 'exit'. In fact, many database servers won't even accept ascii, so you will need a 'smart' client to do things like split your query into batches (e.g. isql splits your script into batches on every line equalling 'go', even though 'go' has no special meaning under the dbms). Don't confuse simple client-side interpretation from server-side query parsing. Oh, and SQL*Plus (Oracle's text-based sql client) will send the appropriate exit instruction on any batch (delimited by '/') containing 'exit' by itself.
You are correct (IANAL, but you're right). However, this is *not* what the MySQL people claim -- take a look at their commercial license webpage. The existence of this webpage is one major reason why I prefer to avoid MySQL. The people behind MySQL not have a Do No Evil stance. At least the postgres people aren't assholes.
Any program relying on (nontrivial) preemptive multithreading will be buggy.
I am outraged with MySQL AB after I learnt they do business with SCO. As a result, I plan to migrate my website's databases from MySQL 4.1 to PostgreSQL 8.0/8.1, and I concentrate on PostgreSQL support in my software projects. MySQL is wrong if they believe they can have the support of the free/libre open-source software communities and have relations with SCO at the same time. PostgreSQL is an advanced object-relational database management system which offers great programmability through its native programming language PL/PgSQL, as well as through several other language bindings (for PHP, Java, Perl, even sh). PostgreSQL is BSD-licensed and supported by a community of free software developers, while MySQL is supported by a corporation, MySQL AB, which sells proprietary licenses.
Minor detail indeed.
I assume you are talking about the psql client. Note that this is a client and not PostgreSQL. There are other clients you can use with a PostgreSQL server some of which are GUIs. phpPgAdmin is one of them:
http://phppgadmin.sourceforge.net/
burnin
"Since then both databases have evolved to wherever they are today." Thank you for that profound insight..
As are you. :-p
How do you think I feel?
Xenu loves you!
PostgreSQL is best opensource database for serious, enterprise size applications, so MySQL is no match here. However, PostgreSQL had/still has serious issues about support - MySQL is much better supported in various apps, software, systems, etc. For example, I do programming now in Java/WebObjects, and I had problems to go with Postgres as database, using EnterpriseObjects. For now I stick with MySQL and still hope I can migrate to PostgreSQL later.
However, MySQL is superior is small and medium PHP based sites, because it is "good enough". They also working constantly on bringing new features and bug fixes, so for sake of both them I can say - competition is good in this case.
user@ubuntubox:~$ stfu This server is going down for shutdown NOW!
Where-as you're a long-timer? ;-)
Get your facts straight coward:
_ 948.html
"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
burnin
Version 4.1 throws all the data truncation warnings you could wish for. Someone else has already commented on the use of NULL rather than NOT IN. I don't think there is any particular merit or demerit in different syntactical variations between database engines, other than being a pain in the butt to port. I just try and use MySQL where appropriate, and for some things it's very appropriate. It's just that, if you have to work with several different engines, you can get more error prone owing to minor variations. It's also often erroneous to compare databases on the basis of problems with certain queries, because the optimisations might be such that a variant query would give the reverse result. It's better (IMHO) to stick with what you got, and use indexing or temporary tables intelligently to optimise complex queries.
Panurge has posted for the last time. Thanks for the positive moderations.
PostgreSQL will hopefully some day have Postgres-R integrated into it; a distributed lazy replication update scheme. (Multicast to ensure ordered group communication to derive seriazability, v. 2pc) This should allow it to scale out to a couple hundred if not couple thousand boxen quite easily with rather stunning performance.
Some more info here
Way of the future kiddies, look sharp.
Myren
consider this a mod point i'd have given had I had one
True, but then again it also depends on who is bearing the news as well.
_ 948.html
Considering you are nobody your press release wont really mean much. And I would also go so far as to say The SCO Group are nobody as well and their press releases don't mean much.
However, if Kroger made a big deal about your toothpaste purchase in the news section of their website it may actual be something to consider.
When SCO made their press release I didn't pay much attention because I have become very skeptical of any messages that come out of their organization. But it is disconcerting when MySQL considers the partnership to be news worthy as well:
http://www.mysql.com/news-and-events/news/article
burnin
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'll ever gain much momentum.
Dewey, what part of this looks like authorities should be involved?
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.
http://www.huihoo.com/postgresql/mysql-vs-pgsql.ht ml
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). Data integrity in a database should not be an optional feature.
- I don't need to go outside, my CRT tan'll do me just fine.
Anything that's not already deployed is going to get a chance to see how PostgreSQL and Firebird perform under similar circumstances.
Probably won't make any difference in MySQL's bottom line but I don't care. You don't partner with SCO.
That's our life, the big wheel of shit. - The Fat Man, Blue Tango Salvage
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.
At what price learning? At what cost wisdom? The price is a man's peace of mind, and the cost is his life.
...does this count as a dupe? Or is there a statute of limitations thing going on here?
~~~~~ BigLig2? You mean there's another one of me?
One thing I have always hated about Postgres is how it handles case sensitivity. If you're going to be case sensitive, compare it to exactly what I gave you. The user shouldn't have to take extra steps to maintain case. Even better, look at MS-SQL. When you turn off case sensitivity, it actually turns it off.
Or at the very least, if you're going to upper/lower case the SQL statement, do the same thing with the schema and data.
It is amazing what you can accomplish if you do not care who gets the credit. -- Harry Truman
For both I just use Alt+Ctl+Escape and then click. But I only do it that way because I think that goth scull-and-crossbones cursor is so totally cool.
OMFG. Who gives the slightest crap about how the included command line client behaves? Even further, making a case that MySQL is somehow more "user-friendly" from that is quite an accomplishment. Idiot.
Comparing MySQL and PostgreSQL 2
Is that the only way that MySQL can look favorable? By comparing to version 2 of Postgres?
fnord
God Fucking Damnit
this is where you usualy go for perfomrance benchmarks on databases
http://tpc.org/
You will notice that neither mysql or postgres are in there. Not sure why. Oracle RAC on Linux is showing up prominately...
I once asked on slashdot about why people use MySQL and how does it compares to PostGreSQL. Got a bunch of interesting responses.
Prescriptive grammar:linguistics
MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
In other words, you're not actually running an enterprise-scale software system (which would require significant $$$, professional DBAs, and tools no matter WHAT database product you're using). You're running a single-man shop.
This is a great troll -- all of your points are sufficiently broad as to be impossible to prove or disprove either way. They're all obvious personal opinions but phrased as facts. Who's to say that any arbitrary enterprise software system can be satisfied with MySQL's features, or another's isn't? Or that you find MySQL's administration tools better than Oracle's (which I find to be therichest out there). Or that any "gotchas" you've had with BLOBs and CLOBs seem purely anecdotal.
The number of features that Oracle has over MySQL is simply staggering, as is its ability to robustly handle enormous concurrent loads. Its clustering support, backup & recovery abilities, and query optimizer are second-to-none. I'm glad you've found a cheap and better alternative, but I hardly think it's applicable to all.
-Stu
Ohm...
All hail three digit user!
Bows down before your lordship.
There isn't one from MySQL AB.
Actually there is a press release on MySQL's website:
SCO Partners With MySQL AB to Lower Costs and Increase the Power & Scalability of Modern Database Solutions
FalconShould there be a Law?
is MySQL going to stop distributing its product under GPL ? Should those users for whom GPL and the freedoms it gives are important start preparing for a switch to something else ?
I already use, er have installed and am learning to use, another OS database, Firebird.
FalconShould there be a Law?
Sorry billysara, I must have beat you by a few minutes. :)
MySQL Administrator
MySQL Administrator is a powerful visual administration console that enables you to easily administer your MySQL environment and gain significantly better visibility into how your databases are operating. MySQL Administrator now integrates database management and maintenance into a single, seamless environment, with a clear and intuitive graphical user interface. By using MySQL Administrator you will be able to:
* Achieve higher database availability through improved management
* Reduce errors through visual database administration
* Lower database administration costs through improved productivity
* Deliver a more secure environment through easier privilege management
MySQL Administrator enables developers and DBAs to easily perform all the command line operations visually including configuring servers, administering users, and dynamically monitoring database health. Other common administrative tasks such as monitoring replication status, backup and restore, and viewing logs can also be performed through the MySQL Administrator graphical console.
"Who are in control, they are not in control of anything - they don't even control themselves!" - Glen Beck
Damn you! ;-)
I originally tried to convert an application from Oracle to MySQL 5.0. Very simple app, though it had some views in it. The views performed much slower than the straight SQL did - microseconds vs 10seconds plus. I then converted to PostgreSQL 8.0 and everything worked fine. As some one with years of database experience with Oracle, SQLServer, and others, I found Postgresql, at face value, to rank up with both of them in many respects, while I found MySQL to be somewhat limiting. MySQL's flat out speed is of no value if it can't provide the basic requirements of a relational database. While I don't have heavy-duty performance requirements, everything else about PostgreSQL seems top-rank. Documention, stored procedures, SQL adherence, advanced storage mechanisms, etc. were all there. And when the next, great open source database comes along, the standardization that it provides will make it all that much simpler to port yet again!
This is different from a working day, how?
As a rock-in-roll Physicist once said, No matter where you go, there you are.
Imagine this:
90 servers sending ~500,000 inserts/updates per day per server to 4 (four) myisam tables, the other 10 machines continiously read buffers and transform data filter and write something like 45% of incomming records passing to processed tables and on the end having other clients that read processed tables intensively and doung statistical queries every here and there.
The only possible choice to make this happened is mysql. Have you ever seen 21,700 inserts per secound continiously 24h? Do you want a screenshot?
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.
Above all things... this is one of my favorite features of PostgreSQL. MySQL has nothing of the sort. Procedural Languages... yum. I recently showed on my blog how I could interact with an instance of DRb from inside of PostgreSQL. How cool is that? MySQL has nothing like this. I'd also guess that a majority of the applications that use MySQL could use SQLite instead and as soon as that becomes more popular, we'll see less MySQL usage.
Robby Russell
PLANET ARGON
Robby on Rails
http://www.commandprompt.com/images/mammoth_versus _dolphin_500.jpg
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.
fifth sigma, inc.
Al Gore is really bitter.
The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.
Not really. PostgreSQL had MySQL beat there. You can assign permissions to groups. With large numbers of accounts, this becomes problematic with MySQL.
PostgreSQL 8.1 beta further improves this by supporting the concept of roles. A role is like a user or group except that they can be nested. This allows even easier administration of databases with hundreds or thousands of user accounts.
Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.
Commercial as in what? A mom-and-pop web-store? I would call that commercial and because MySQL will *truncate* numbers to fit fields if they are too big, I would say PostgreSQL is needed for that.
You are right in that MySQL is quite adequate for content management provided (of course) that you don't need to integrate it with other buisness apps. If you do, you can either go to PostgreSQL (and take advantage of schemas) or use PostgreSQL with DBI-Link to pull the data from MySQL. DBI-Link is easily one of the coolest new projects for PostgreSQL as it allows any data source reachable via Perl's DBI interface to be seen as a table from within PostgreSQL.
LedgerSMB: Open source Accounting/ERP
If you had bothered to look at the first link, you'd see that it linked to a previous slashdot article, way back in '99, that compared the two then.
That was part one, this is part two of that comparison. Neither really linked to any real comparison, they just tried to spur some discussion of the two, and let the comments tell the real story.
You can do a join, and add a "having isnull(wheee.blib)".
Really, I'm not trying to be clever with my signature.
As far as I know Postgres-R is dead..? Slony-II is under development, and will provide synchronous / multi-master replication for PostgreSQL. (Slony-I is already available, but is asynchronous / single-master.)
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.
LedgerSMB: Open source Accounting/ERP
Exactly. This isn't just about buying a license. MySQL is outright promoting that they are working together with SCO, who is a soon-to-be bankrupt and known enemy of the open source community.
MySQL issues have come up in the past (see PHP5 licensing issue for example)... their motivations for the future seem to be forgetting who made them who they are.
Every Linux user who has installed a distribution has probably install MySQL. It's been supported by the open source community and they would not be where they are without that community.
If you have a group of dedicated friends, do you join a pact with their enemy? It only seems to validate that MySQL approves of the business practices of SCO. Is this true? Where is the ethics? Selling SCO a license is one thing, joining a pact is another.
This concerns me.
My question for the PR department at MySQL... does MySQL support the ethical business practices of SCO?
That is how I see this announcement.
Robby Russell
PLANET ARGON
Robby on Rails
Don't know about comparing mysql postgresql, but reading the slashdot article from 6 years ago made me realise how slashdot has gone downhill!
All digit people are newbies. When I first joined slashdot in the late seventies we still used letters. I was the letter Q. At some point they switched to numbers and everybody was supposed to sign up for a number. Well I never got around to it so that's why I'm posting as AC today.
Old. :^)
Slashdot's first reaction to VMware
What does the ANSI standard say this means?
SELECT ('Hello ' || 'World!') as greeting FROM my_table;
What does it mean in MySQL?
Yes, every RDBMS has extensions to the standard and this is important but MySQL *breaks* standard behavior in many profound ways.
Another example: What does the ANSI standard say should happen here:
CREATE TABLE my_table (
myfield CHAR(4)
);
INSERT INTO my_table (myfield) values ("Hello World!");
What does MySQL do?
MySQL does the same with numbers.
These are areas where standard behavior is actually broken by the RDBMS.
LedgerSMB: Open source Accounting/ERP
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.
There is a new company, called EnterpriseDB which offers additional Oracle compatibility capabilities on PostgreSQL. Not FOSS, though quite affordable.
They are also donating a lot of code back the community and have announced that they will be working on having SQL99-compliant PSM support in PostgreSQL 8.2 (possibly also able to support Oracle, MySQL, and PostgreSQL stored procedures in addition to the SQL99-compliant stuff).
LedgerSMB: Open source Accounting/ERP
For an enterprise system,you also need:
1) Views
2) Triggers
3) Integrity Enforcement (i.e. if you try to insert 1000000 into a numeric(4,2) column of your enterprise accounting app you should get an error and not have something inserted).
As your system gets large you may also want:
1) Table partitioning
2) Functional Indexes, i.e. create index on table foo (md5(bar))
3) Partial indexes (i.e. create index on table foo (bar) where open IS TRUE)
MySQL hardly offers all of these capabilities.
PostgreSQL 8.1 will offer all of them in usable forms.
BTW, for those interested, my site has a whitepapers section which has a MySQL to PostgreSQL migration guide.
LedgerSMB: Open source Accounting/ERP
Nitpick: Firebird (Interbase) started off on, what, an Apollo DN320? Not exactly designed for Windows. It got the Windows-oriented feel from being owned by Borland at a time when Borland focused on Windows only. (Note that they're now working on Delphi/Builder for Linux, so that's changing.) The Firebird project dropped support (last year?) for several very old machines that simply don't exist anymore. It worked, but nobody needed it anymore. I'd say it was more designed for a Unix environment, and happens to install easily on Windows (and Linux. Our production firebird server runs Slackware.)
I don't know the details about the internal workings of either one, but they both bug me.
./configure
For years I've hated MySQL's install process. Call me old fashioned but I just want to
make
make install
It makes upgrading and maintaining my servers really nice. MySQL is just an annoyance.
Today I tried PostgresSQL, and I was happy to see a typical install process. Then I finished and I was blown away that it requires a shell in order to run. I understand that daemons need user accounts, but it just doesn't seem secure to give a daemon a shell.
IMO MySQL is a pain, and PostgresSQL is a joke.
There's more to uptime than the box(es) being up. Netcraft doesn't count when Slashdot has returned 500 status codes because the back-end (read: database) fell over. While it speaks volumes about the reliability of Apache over long periods of time, Netcraft uptimes mean didley in this case for MySQL.
Don't get me wrong. Slashdot's reliability has been steadily improving over the years. Perhaps this is an indication of the stability of MySQL improving over the same interval.
----
Also remember that older articles are archived -- removed from active database queries/updates and rendered to flat text files. Once again, it speaks highly of Apache rather than MySQL.
- I don't need to go outside, my CRT tan'll do me just fine.
empty strings and nulls are seen as equivalent
To an extent, I can understand that - they are both an absence of information. I think it's something that depends on your needs - if you need a string, and an empty string conveys something important, then the null should stand for "no information provided", in case there should be a default. Then again, in most cases, there's no difference between "specifically no data" and "no data provided" - for example, someone's middle name. Whether they just don't have one or never gave it, is there really a difference that matters?
I guess that by "nested loops" you really mean deep joins, e.g.
....
select books.title, publishers.address, cities.timezone
from books
join publishers using(publisher_id)
join cities on(address_city=city.name);
PostgreSQL - and presumably most of the other DBs - will guess how many rows will match based on a precomputed statistical analysis of the tables and choose one of several different join algorithms, as appropriate. One of the possible algorithms is nested loops, i.e.
for b in books
for p in publishers
for c in cities
if b.publisher_id = p.publisher_id
and p.address_city = c.name
This is obviously very inefficient in the cases where the data sets are large; you'll get much better results using other techniques using e.g. hashing, or a merge-sort type approach. What indexes are present is also important.
PostgreSQL has the "EXPLAIN ANALYSE" command that you can use to find out what strategy it has chosen for your query. Just about the most common FAQ on the PostgreSQL mailing lists is "Why is PostgreSQL using an inefficient method and ignoring my indexes?". The answer is nearly always "its statistics have got out of date, run a periodic cron to update them" or "it is confused about the relative cost of reading from disk and sorting in memory, tweak something". Less often the answer is "add another index" or "re-write the query".
By poking around a bit you can nearly always get it to do the right thing. In your case, if PostgreSQL really is taking 18 hours vs. 5 minutes (200x slower), then do go and ask on the lists. Someone is sure to help if you can show an "EXPLAIN ANALYSE" output. It could be that MS_SQL is faster, but I find it inconcievable that the difference is that great.
Proper security is not determining the bad things and preventing them. It's determining what is acceptable and rejecting everything else.
This is true for programs just as it is for programs, including but not limited to database engines. In a sufficiently complex environment, very few people can wrap their heads around every possible combination of variables and logic -- especially when coding with a group. Better to code to what you know is right and reject the rest. It's the only way to be sure.
- I don't need to go outside, my CRT tan'll do me just fine.
Your points about MySQL are spot-on. If you set 'allow nulls' on an InnoDB database table fiend to be off, and fail to populate that field in an INSERT statement, MySQL puts in an empty string in that field. Logically, it should throw an error, I would assume.
...
...
I wouldn't mind a mode where I could choose how to handle non-nullable fields where no data is present (or write some sort of SQL error handling Stored Procedure).
Also missing are proper quoted / non-quoted syntax checking, so I don't try and stick an int value into a varchar field, etc. Heck, up until 4.1, you couldn't even do a
SELECT
UNION
SELECT
with either of those above SELECT's containing a subquery. Wasn't documented, either, and I had to pour through forums to find out why it didn't work.
However, some of the features I mentioned above are actually why people prefer mysql, they would prefer the database didn't bark at them, it allows for more rapid prototyping of web applications if you don't have to validate all those fields against empty string conditions.
Reason, free market capitalism, and individualism
In the forthcoming version 8.1, Autovacuuming is part of the backend and should "just work" without much user intervention. In current versions a separate process autovacuums at dynamically-adjusted intervals. Alternatively you can run it manually from a cron job. If you install from a package like Debian's it will all just work.
For the curious who don't know what we're on about: PostgreSQL transactions can be isolated from each other to some greater or lesser extent, depending on what your application needs. So if you have many concurrent transactions, some will be seeing the newest state of the data and others will be seeing older data from the point in time when they started, while the disk will also contain data that has just been added and not yet committed. So when data is deleted it cannot be immediately removed from the disk as some old transactions may still be referring to it. Vacuuming is the process of reclaiming the disk blocks used by old deleted data. These reclaimed blocks can either be reused for new data (VACUUM) or returned to the operating system (VACUUM FULL).
It's the difference between an empty bucket and no bucket. In some cases, an empty bucket is as useless as no bucket, but there is still a significant difference.
Spine World
And Friendster was also slower than tar for a very long time. In addition, when you make changes in Friendster, you may have to wait for those changes to become visible to others. They have some very aggressive caching.
On second thought, who knows? Maybe it used to. That would explain the constant downtime and speed problems.
- I don't need to go outside, my CRT tan'll do me just fine.
Ummm... no. MySQL does not have user-defined data types, object-relational extensions, full support for the CHECK constraint (a big one IMHO), views in a stable release, updatable views, rules, stored procedures in a stable release, synonyms, support for more than one autoincrement column per table, automatic conversion of code pages between client and server, nested transactions, complete trigger support, access privilege grouping, access to multiple databases in one session, multi-master replication, gateways to other DBMSs, XML data and transformation tools, and better tools for recovery from failures.
Actually, as of somewhere between 4.0.x and 5.1, MySQL(tm) does gain "enough" functionality to be able to (in some sense) support the operation of THE "enterprise system," namely SAP R/3.
It was interesting to watch the presentation by the MySQL guys at OSCON last month; I think that at some point they actually did mention that they had introduced all of the functionality actually required by SAP R/3.
Note that SAP R/3 does not require:
R/3 does all of those sorts of things (as much as it does them) in the application, that is, in the several gigabytes of ABAP/4 code.
In principle, I don't think there's anything that was needed to support "enterprise software" like R/3 aside from the introduction of the InnoDB transaction manager.
You may want to object and say, "Oh, but I mean for developing NEW enterprise software, not stuff written for IMS back in the 1970s."
To which my response would come in two parts:
A year ago, I was puzzling over the MaxDB deal; the only scenario that made any sense about it was for this to represent an opportunity for MySQL AB to, by supporting MaxDB for a while, to figure out the exact minimum set of features that they need to add to their own product in order to attract SAP AB's interest the next time they feel the need to rattle sabers at Oracle over licensing fees.
Considering that this is a route into playing high level games with some of the biggest "totally proprietary" vendors out there, that should cause concern for anyone in the free software community...
If you're not part of the solution, you're part of the precipitate.
> 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
The case-insensitive comparison is done like this:
select * from T1, T2 where lower(x)=lower(y);
If you have normal indexes on x and y, this won't use them and you'll get a sequential table scan. To be useful for this query, you need indexes on lower(x) and lower(y), e.g.
CREATE INDEX idx1 ON T1 (lower(x));
CREATE INDEX idx2 ON T2 (lower(y));
This is described on the reference page for CREATE INDEX.
I suggest that you ask on the mailing list next time you find a "showstopper" problem - people really are using PostgreSQL for serious applications, and an obvious thing like this would have been fixed long ago.
That's the primary reason, as far as I can see.
If you're not part of the solution, you're part of the precipitate.
I should know, I offer both mysql and postgresql for all our hosting customers. The only difference is postgresql allows more fine grained permissions and is easier to administer.
Postgresql lets you add users just fine, you don't need to install it for each system user, that's just crazy. You do the same as you do with mysql, add a user (and a database for them is a good idea too).
And the reason to use postgresql isn't because its "heavy", whatever that means. Its because mysql intentionally corrupts data on you. Set a column to not null, then don't insert anything in that column. Instead of getting an error like you should, it will put a 0 or a "" or something similar depending on the column type. Try inserting an int too big to fit in a column, sorry no useful errors from mysql, it will just chop the number down to fit. People who want their database to store the data they put in it, instead of changing it on them should use postgresql. Or anything but mysql more accurately.
...why increase the risk of your development process by switching back and forth? Oracle is available under a free development license. You don't need to buy additional licenses to develop applications on Oracle, only to deploy them. You can have a completely separate Oracle install as a test base and you won't have to worry about switching back and forth from PL/SQL (or "recompiling," though it seems foolish to hard-code your SQL queries in C).
Breakfast served all day!
I only did a very quick search but truncation of data fields doesn't appear to be in the Bugs database. Have you submitted it, please give the #.
If you have submitted, I apologise, otherwise cut some slack and help the cause.
As someone that's done a lot of code on top of both MySQL and PostgreSQL, I can tell you, the difference is like night and day. MySQL can handle crappy web apps that barely need a real database at all, but the gap between that and everything else is like the grand canyon. It lacks almost all features that are absolutely required for non-trivial apps. E.g., it's just recently that it even had stored procedures! That's sad.
PostgreSQL has all sorts of really important stuff like stored procedures, triggers, nested queries, GIS/GiST indexes (rtree/PostGIS), asynchronous notifications, etc.. It also doesn't force your app to be GPLed. I think 99% of people currently using MySQL don't realize they changed the client library license, even for the newer 3.23 series.
The reason people used to use MySQL for web stuff was that MySQL was really easy to set up, could run on Windows, and let you write sloppy SQL. The same people probably write HTML that doesn't validate. PostgreSQL is now easy to set up and can run on Windows. It still doesn't let you write sloppy SQL, and that's a good thing.
As someone who's been using PgSQL in production environments for years at several companies, I can sum up the biggest drawback with Postgres in a single word:
VACUUM
rooooar
That, and watch out for AUTO_INCREMENT. That's one really useful extension in MySQL that many other databases don't implement (they don't need it, because they have a more generic system). If you use AUTO_INCREMENT columns and insert NULLs in them, you'll have to rewrite that code, as it won't do what you want in postgres.
Please correct me if I got my facts wrong.
MS SQL Server: ... Troubled security history.
Nice and objective. Not.
At least Microsoft deals with it's security issues promptly. Unlike, say, Oracle.
Your MS SQL bit seems a bit harsh:
tightly integrated with Windows in terms of memory management.
Um, what specifically does it do that the others don't? There are plenty of apps that'll reserve a chunk of memory from the OS (CorelDraw springs to mind) and there are open APIs to do this.
Troubled security history.
ONLY if you left the TCP/IP administration port open to the internet. (Which is a really stupid thing to do.) Firewall that off or restrict your server to named pipes and there've been no problems.
While it's true that MySQL 4 and 5 support "clustering" out of the box, it's actually a somewhat misleading statement. MySQL's clustering support is actually an entirely different database engine from MyISAM or InnoDB, much in the same way that MaxDB is. Cluster is actually something called NDB (Network DataBase), which Mysql AB acquired from a european telco (I think Vodaphone, but don't quote me on that) and is slowly integrating into the MySQL codebase.
NDB has some interesting design features, but it is not a plug-and-play HA solution for MySQL. It imposes some very serious limitations on queries and datatypes -- differences that basically make it a worst-case-scenario subset of the limitations of MyISAM and InnoDB. Additionally, running NDB requires running a number of specialized daemons that are administered in a substantially different fashion than the rest of mysqld, and which are, to put it charitably, a bit on the undocumented and flaky side.
Mysql AB appears to be working pretty hard to integrate NDB into their codebase, and I expect that in another 2 or 3 years it will be a very compelling solution. But for now, it's an interesting toy and nothing more.
News for Nerds. Stuff that Matters? Like hell.
Breakfast served all day!
Um... btree index on lower(field)?
Novell to Offer MySQL Network - 9 Aug 2005. From here:
"SAN FRANCISCO (LinuxWorld Conference & Expo) - Novell and MySQL AB today announced an agreement to deliver enhanced, combined support for key components of the popular open source LAMP infrastructure stack. Under the reseller and joint-support agreement, the only accord of its kind between a Linux* vendor and MySQL AB, Novell will now offer subscriptions to the MySQL Network commercial database service directly to its customers. As a result, customers can now deploy a true enterprise-class open source foundation for their IT infrastructure with confidence."
Of course MySQL AB must have made Novell aware of this before it happened, SCO vs Novell is fairly well known in the industry... Maybe MySQL AB are just getting ready for Novell's takeover of SCO? After all SCO owe Novell quite some money and maybe MySQL wants to be on the SCO "I owe you" list too. Investing in debts anyone, may that be done?
To tell you the truth, my RDBMS's are in order of preference:
PostgreSQL, FirebirdSQL, DB2, MS SQL, Oracle, MySQL.
Oracle is a nightmare largely because it tries to replace too much of the OS's function, so tuning it is extremely complex. This means that you can expect the time (and hence cost) required to develop and tune a database in Oracle to be much higher. With MySQL, this cost is almost nothing, but the cost of having invalid data in your enterprise database might be even higher.
LedgerSMB: Open source Accounting/ERP
Troubled security history.
Back that up. Other than MSDE being installed without an SA password (which is really just a problem with uneducated admins) what exactly is this "troubled security history" ?
Because I say you're full of it.
To an extent, I can understand that - they are both an absence of information.
Not really. NULL reprensents the value of "unknown." If you follow the empty string IS NULL analogy, then maybe a 0 in an INT field should evaluate as NULL too. But it doesn't for good reason.
For example, lets say that I am evaluating damage on houses in Mississippi following Hurricane Katrina. There is a huge difference between an absense of damage (0, empty string) and an unknown quantity of damage (NULL).
LedgerSMB: Open source Accounting/ERP
No other RDBMS has had the number or severity of viruses target it as MS SQL Server. Need I remind people of the SQL-Slammer worm? And this was not about blank SA passwords (unlike previous worms).
SQL-Server holds the dubious honor of being the host for the virus which spread around the world fastest.
LedgerSMB: Open source Accounting/ERP
You mean a Microsoft installer file named something like postgresql-8.0.msi? A single, redistributable file like that?
Looks like there's a file just like that in the Win32 zip download for PostgreSQL. Just bundle up the appropriate install file with your app and you're set.
Available via FTP and bittorrent for your convenience. Admittedly, it wouldn't have helped you two years ago, but it's been there for at least the last six months or so.
What was that MSDE advantage again?
- I don't need to go outside, my CRT tan'll do me just fine.
I haven't seen anybody mention this yet, but MySQL has one major downside that the other databases don't: its developers.
The MySQL "gotchas" are widely known and cited here. Sometimes somebody will reply that it's fixed in a beta version, or you can use an option to fix it in a beta version, or it's fixed in the latest release version.
I don't care.
I simply cannot conceive of any developer who is remotely qualified to work on a database who would make the choices that have led to these gotchas. The gotchas themselves are bad. But they are merely a symptom of a bigger problem - the incompetence of the developers. These are guys that confuse 0 and NULL, for fuck's sake!
Until new developers take over, or the existing developers say something like "yeah, I don't know what we were thinking, that was a fucking moronic thing for us to do", I simply won't be able to trust MySQL. Who knows what other gotchas are lurking in the wings?
I realise it sounds like FUD, but I'd really like a MySQL fan to explain how it is that they can trust developers that make so many basic errors.
MySQL is great for some things, but we use PostgreSQL at my job because it supports CIDR:
http://www.faqs.org/rfcs/rfc1519.html
If you have a table in your database full of IP addresses, this is invaluable because you can query blocks of IP addresses using CIDR notation.
If anybody here has actually studied database theory, you know that the relational model allows you to do a lot of cool things. For instance:
#1 updateable views (these are like subroutines calls in programming language: they allow you to abstract and refactor)
#2 arbitrary database contraints (these let you say, for instance, that tuple X refers to a tuple in relation A OR relation B, but not both. or that the string in attribute Z must not contain spaces).
#3 real type constraints: can't store integers into a string attribute, and vice-versa
#4 user-defined types: need to store IP addresses, JPEGs, XML data, etc, and declare constraints.
#5 arbitrary relational expressions. for example, I'd like to join two tables and then project the result, then join the result with something else. Compare with arithmatic: most languages let you nest and group expressions arbitrarily: A + ((B*C+2) * D)
Once I design my database using relational principles, I have to find an actual physical database to implement it with. Since there are no relational databases today (except maybe Dataphor), I have to settle for an SQL database. Now. Which should I choose? Open source is usually more flexible, let's see how MySQL and PostgreSQL fare:
MySQL
#1: no updateable views. In fact, no views at all in any release version.
#2: No triggers to implement these.
#3: ugh, no
#4: no
#5: yes, finally they have sub-selects, which is a wordy way of writing out relational expressions, but that's the best we can do with SQL.
Let's see how PostgreSQL meets my needs:
#1: no updateable views, but you can intercept inserts and updates and send them to your own code.. good enough.
#2: simulated with triggers, we can do it.
#3: yes
#4: yes
#5: yes, sub-selects here too, and Postgres had them longer
So, if the relational model is a "10", and MySQL is a "2", then Postgres is at least a "3" or "4". I.e., MySQL is a SUBSET of PostgreSQL. Maybe if you're hacking together a blog or LiveJournal or something, you can away without having this stuff (heck, you can probably just use flat files). But for any real database app that involves money or requires accuracy, I'll stick PostgreSQL.
Remember folks, the purpose of a database is DATA INTEGRITY, not data storage. Once you figure this out, like Codd did in the 70's, you'll be a much better programmer, and you'll laugh at stuff like MySQL (just like Lisp programmers laugh at BASIC programmers).
I'm not sure why I don't see this mentioned yet, but this wikipedia article has a great grid that compares major database systems.
Well, there is a *big* difference: different middle names apply to different persons. For instance, if the police is looking for a "John Wayne Holmes" and look into a database, then there is a vital difference in knowing if a certain "John Holmes" just doesn't have a middle name or omitted to put a "Wayne" in there.
You are working off an old sheet.
Oracle 10G is as easy if not easier the MS Sql. It also costs the same feature for feature. Oracle has now matched MS on price on every level.
evil is as evil does
So what exactly is the difference between the MySQL-SCO relationship and the PostgreSQL-SCO realtionship that were announced 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 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?
There's a very interesting and details comparision of the SQL support of several databases here.
Learn what functional indexes are, dumbass.
> as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.
Yeah, dipshit: the solution is store your fucking character data in UTF-8.
But people ASK for it because it's what they've been offered all over the place, it's what they've used at other hosts, and it's what everyone else is using. It's a circular issue. If people *WANTED* postgres support in all their hosting accounts, ISPs would deliver it. They just might find it a bit harder to set up. But then more people would know about it, and then ask for those pg apps by name in the future.
I tried to use postgresql years ago and it had a (ridiculous) 8k per row limit. There were many things I couldn't do with that, mostly involving community/forum type sites. Look at where PHP/MySQL exploded - hosted community/forum sites (phpbb, etc.) Not saying the code is good or bad, but it met a need, people used it, and it grew from there. Theoretical standards don't stand a chance in the way of people getting their needs met. And I now fear it'll be years before anyone can mount a serious challenge to MySQL on the lower end now. It's not just about introducing a new system, it's about branding and mindshare now, something which was up for grabs 7-8 years ago. It's MySQL's game to lose now.
creation science book
It's NOT fine, neither on Windows, nor on Linux. I've tried both just two weeks ago. Try it yourself.
One feature I'm aware of that is otherwise uncommon among most Windows programs is that MS-SQL tries very tightly to match a specific amount of physical memory in use, and tries very hard to trim its belt if some of its text or data segments end up in the swapfile. Most Windows programs don't go through the trouble, and a port of MySQL or PostgreSQL to Windows likely doesn't do this, making this feature a win for MS SQL.
"The Devil does not know a lot because He's the Devil, He knows a lot because he's old." -- unknown
Try functional indexes with Unicode strings, then come back and we'll talk. Until then, shut the fuck up.
I (and probably any other DBA reading Slashdot) don't follow what you're talking about. Please post an actual example of this SQL loop you speak of. Do you mean correlated subqueries?
Part of the definition of NULL is that it is not equal to anything, not even itself. Try this on a compliant database sometime (like postgres): 'select 1 where null=null'. You should get nothing back. This is what the 'is' operator is for, testing to see if something is null. 'select 1 where null is null' will give you a row.
Empty strings on the other hand are equivalent to other empty strings. They also have a type and a length, NULL doesn't.
> I agree that the US economy isn't *completely* a free market economy, but it is *principally* one.
> And what I meant by my original comment is that I believe that market forces should for the
> most part be allowed to operate unimpeded.
Sure, you guys are all in favor of the free market - unless the government decides to compete. Even if a majority of people (screwed by HMOs and high-priced health care) voted for a single-payer system, you'd probably scream "socialized medicine!" at the top of your lungs.
If you want to see how free markets can screw people, learn about how the rural U.S. was provided with electricity by co-ops because the power companies wouldn't do it. That was the turning point for American agriculture that brought it out of the 19th century and enabled us to enter the post-industrial/information/service economy. But the power companies were too short-sighted to see what a boom in productivity it would create - they just saw no short-term profits and said no thanks.
Really?
How about dual core CPU pricing? How much EE cost per CPU? I can continue.
It only can be matched if you are big enough and beg long enough to get a hefty discount.
Please understand I have the utmost regard for craftsmanship and specialized professionals... in part because I am a specialized professional with a craft, myself. DBAs... in-house programmers... honestly, these professionals are very, very expensive. If using MySQL means I can save (between the DBA and programmer) well over $100,000 a year, I'm afraid market realities make my choice an easy one.
It might help to think of the matter in non-IT terms: some companies have lawyers on payroll; the majority of companies don't. The same is true of DBAs and Programmers... these are tasks that are largely outsourced to consultants.
Also consider that advancements in technology (including software) generally wind up obsolescing (to varying degrees) the roles of specialized professionals.
Where's your neighborhood printer? In the corner, at the other end of a USB cable. In industrial design shops, 3D printers are starting to do the same to sculptors.
Returning to the actual software in quesiton, MySQL is nice. It's not amazing, but it doesn't by any means suck. If using MySQL can save you some bucks, time, and effort, go for it. Postgres is fine, too... especially now that VACUUM() operations are configurable in the db's conf files... still waiting for it to be 99.999% transparent issue, however (eg, when someone that uses postgres might actually find themselves asking at an odd moment, "vacuum? what's that good for?")... it'll be for postgres' own good, I assure you!
Last time I looked into this, MySQL didn't support spatial with InnoDB which means that you couldn't use spatial with transactions. What about PgSQL?
Twelve-and-three-quarter inches. Unyielding. This wand belonged to Bellatrix Lestrange.
One feature I'm aware of that is otherwise uncommon among most Windows programs is that MS-SQL tries very tightly to match a specific amount of physical memory in use, and tries very hard to trim its belt if some of its text or data segments end up in the swapfile. Most Windows programs don't go through the trouble, and a port of MySQL or PostgreSQL to Windows likely doesn't do this, making this feature a win for MS SQL.
With PostgreSQL you shoudl be able to tell it how much memory should be available for various types of operations. But you are right. Having part of a working set in a swap file is a very Bad Thing(tm) for an RDBMS.
LedgerSMB: Open source Accounting/ERP
1: ANSI is "Hello World" MySQL is '0'
2: ANSI is error, and abort the inserting transaction. MySQL inserts 'Hell'.
Another case in point:
mysql> create table test (
-> test numeric(4,2));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (test) values (10000000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+--------+
| test |
+--------+
| 999.99 |
+--------+
1 row in set (0.00 sec)
So if this number was important and meant something (which it would in production) you just entered bad data into your database!
LedgerSMB: Open source Accounting/ERP
page 8, 2.5 Maintenance Differences
The first word in the paragraph should read 'PostgrSQL' i believe.
Interesting guide, thanks.
"Our interests are to see if we can't scale it up to something more exciting," he said.
Now what does it matter if Mysql partnered up with SCO
When you partner with a company who considers the license you distribute your software under invalid, you've got problems.
When you partner with a company who is actively trying to ruin the livelihoods of your customers, you're going to have problems.
My God, it's Full of Source!
OUTSIDE_IP=$(dig +short my.ip @outsideip.net)
A critical responsiblity of the dbms is to ensure data integrity independently, that is: the dbms should have the native capability to never have to rely on and/or trust the accessing client to sanitize or validate input data. This means that it is more desirable for a query to fail than for the dbms to try and guess intention of or worse, "correct" the query if the query will result in a constraint violation. There should be no so-called "undefined" behaviors. If an outcome of a query won't be deterministic, it should fail (excluding things using an auxillary prng for obvious reasons), or barring that, at least emitting a warning. The speed of the query should always secondary to the resulting integrity of the data.
Several MySQL gotchas have been documented on stable versions. Many of these are egregious pitfalls plaguing this dbms. Some of these are just plain violations of SQL (i.e. playing with the definition of NULL or column types behaviors like VARCHAR), to outright altering input.
Finally, the reliability is atrocious. Since there is no "repair table" option for innodb, I have to fetch a backup copy of the db because it gets corrupted so easily. The concept of supporting atomic transactions is made irrelevant if the physical portion of the engine still gets trashed in a way requiring major manual intervention.
PostgreSQL:
chris=# select date '99999-02-01';
date
-------------
99999-02-01
(1 row)
chris=# select date '2005-02-29';
ERROR: date/time field value out of range: "2005-02-29"
I.e. there is a Feb 1, 99999, but not a Feb 29, 2005.
For MySQL:
mysql> create table test ( test date );
mysql> insert into test (test) values ('0000-00-01');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (test) values ('2005-02-29');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (test) values ('99999-02-01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------------+
| test |
+------------+
| 0000-00-01 |
| 2005-02-29 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
Note that MySQL suffers from a Y10k bug. Not a big deal for normal work but I can see scientific uses where this might be an issue. Furthermore, it allows days, months, or years to be zero in inserted dates. Let me just schedule an appointment for January 0, 2007..... Or Feb 31, 2006.
LedgerSMB: Open source Accounting/ERP
For quite some time you couldn't use a table type which supported both referential integrity AND transactions at the same time in MySQL.
How have MySQL and PostgreSQL changed over the years? Are the points from 6 years ago still valid?
PostgreSQL has matured greatly over the last 6 years. Six years ago it was a PITA to do any prototyping on because you could not drop a column from a table. You could not change the size limit of the variable, etc. There were few easy to use admin tools. And more.
Today, PostgreSQL is probably the most flexible open source RDBMS, is *rock solid* (only errors I have gotten in the last four years were either bugs in extremely obscure corner cases which were already fixed by the time I found them or were the result of hardware failures). I have used PostgreSQL for moderate OLTP databases and for prototyping a few OLAP projects. There are a number of good GUI tools (PGAdmin) and good web-based tools (PHPPgSQL). But I still prefer the command line.
MySQL has also evolved in important ways. The attitude of the company has changed from "You don't really need these features" to "Would you trust your data to a program progressing as rapidly as PostgreSQL?" to "Ok, we want to impliment the entire ANSI SQL standard." 4.1 was a step in the right direction with UNION and subselects supported (albeit not in ways that perform well). 5.0 should be a further improvement, especially with strict mode, but they have a long ways to go to impliment a system which is as enterprise-capable as PostgreSQL was six years ago.
At the same time, PostgreSQL is moving into even newer territory. Some projects such as DBI-Link are bringing at least part of the SQL/MED (Management of External Data) standard to PostgreSQL. 8.1 will have two-phase commit for distributed transactions, and better data warehousing capabilities. Projects such as Bizgres are promising to bring parallel execution of queries to PostgreSQL also, perhaps allowing it to compete with Oracle 10g in the areas of business intelligence etc. And companies such as Affilias have developed open source replication systems (which could be used in multimaster configurations with a sufficiently small number of masters though these configurations are largely undocumented). And with PostgreSQL 8.1, the backends will vacuum the database automatically. In short none of the arguments against PostgreSQL from six years ago will hold true after this next release (expected within the next few of months).
There are a few rough spots still in PostgreSQL. If you use off-the-shelf software and never put any information in any tables, the planner can get tricked into doing a nested loop join against an empty table. This can kill performance. If the database doesn't get vacuumed every few billion transactions, the transaction counter wraps around which is a Bad Thing(tm) because transactions in the future are now hidden by MVCC. In practice this rarely happens becuase if any reasonable percentage of your queries are writing updates to the database, you will get slow performance after a fraction of this number. Bit if it happens, you are in Trouble(tm). Even with the new release it will be possible to set this up so that this can happen, but it should not be easy.
LedgerSMB: Open source Accounting/ERP
Once again, feature for feature oracle costs the same. EE has more features then SQL server enterprise edition and costs about the same even though it has more features and supports more processors.
evil is as evil does
Subselects, views, triggers, table inheritance, stored procedures, tablespace management.
ahem.
I have used MySQL behind a multi-million dollar website over the last 2 years, with projected growth it's hard to say weather or not mysql will work well in the future. However with everything I've seen, Postgres and most of the other commercial databases don't really solve the problem of scaling in a write-heavy environment. We've had to turn to creating separate clusters of master-slaves since our writes pretty much consume the slaves. This will eventually grow to a point where adding slaves won't make a difference. I fail to see how clustering or replication is going to help out in this instance. There are a few downfalls to MySQL. I.e. where is the support for master failover without some sort of half assed setup? In my eyes (web developer) data consistancy and roll-back support doesn't matter as much as the ability to scale and failover. In most cases you store critical data outside of your MySQL db, though it is pretty safe to do so.
"exit" is not in the SQL specification, therefore it shouldn't be accepted by an SQL shell. QED.
psql uses "\" to denote psql commands, everything else is SQL.
MySQL is best used when you need speed, compatability, and reliabilty.
MySQL supports load balancing, PostgreSQL doesn't.
MySQL supports multiple table formats, PostgreSQL doesn't.
MySQL stays faster on larger databases than PostgreSQL.
PostgreSQL is best used when you want more features at the expense of everything listed above...
The choice is really a case-by-case question.
These are confirmed by this table.
With MySQL 5.0 close to a "stable" release, all of this will go out the window as MySQL will gain many of the features of PostgreSQL.
are a moron
God Fucking Damnit
I don't really have a favourite among the two. I use a DB mostly through EJB containers. So all those nifty features are of no use because the DB layer won't use them. They don't do much more than the standard SQL.
Getting mysql to use indexes is an art. Just try random things and eventually you'll get it right. I need to use postgres more but it seems like that's not a problem w/ postgres.
I eventually figured out that mysql doesn't perform well(ie has a hard time using indices) when joining 4 or more tables.
2 years and no mod points. Join reddit. Because openness is good.
PostgreSQL - PostGIS spatial database extention, 300+ functions plus indices.
.... with all the tasty spatial analysis functions you'd expect - intersections, crosses, buffers, overlays, ... over 300 spatial functions with spatial indices.
... FREE.
... ESRI, with ArcSDE installed on Oracle which is their preferred way of doing things, you are looking at a cost of $50,000 in software, plus $18,000 per year in license maintenance fees. These are canadian numbers, which I priced out in January 2005.
MySQL - wimpy generic spatial text format, very limited functions
PostgreSQL can use the PostGIS extention to hold spatial data and give you full spatial database capabilities
And in the last year or so, PostgreSQL has been available using an windows installer, with PostGIS as an option, making all its power available to those who are not so hot with linux or similar OS's, and dont want to spend 2 weeks figuring out how to compile said software with all features working correctly thanks to the instructions that bare no resemblance to reality.
MySQL, like many other 'enterprise' databases can hold spatial data in WKT / WKB formats that can be fed to programs that can use it, but it has very limited spatial functions and I dont think it can generate spatial indices. Someone can fill us in on this point, I understand that a spatial extention may be in the works.
The HUGE benefit of PostgreSQL with PostGIS is that it gives you a full powered spatial database engine which works with OSS GIS softwares and web mapping programs such as MapServer, Grass, QGIS, JumP, etc
If on the other hand you chose to use the name brand software since MySQL can't do the job,
I'd rather spend that money developing a system, instead of just buying the damn software.
George Bush + Linux = "I will not let information get in the way of the fight against Windows"
Well, MySQL at least *has* some collation support. The bad thing is how it is used in SQL:
SELECT X FROM T ORDER BY X COLLATE collation_name;
This seems nice at first. The catch is the collation names. Imagine you write an application for international customers. A person from Paris would like a french collation, while a person from Israel definitely needs a different collation. So the collation to use strongly depends on the locale of the person who is logged on. Usually people use the ISO country and language codes (en-US, fr-CA etc.) in their applications to define a locale. Those codes are also used in Java's Locale class for instance. Now the MySQL collation names are like this: latin1_german1_ci, latin1_spanish_ci, etc.
This does not exactly match with anything of the above. And it makes it harder than necessary for developers to select the right collation for a certain locale. You need some kind of mapping table. Moreover you can not be sure that every MySQL installation has support for all the collations you use, because they can be selected at compile time.
When you compare this with the way Oracle handles that MySQL looks pretty bad. For me Oracle is the only DB that handles collation well-enough from a developer's point of view.
And hey, what about time zones? When I store a time and date in a DB, it must be well-defined in what TZ the data is returned. Oracle for instance does quite a bad job regarding that.
>The one good thing I have to say about mysql is that its multi->user friendly for hundreds of accounts.
We started out as a "mom and pop ISP" 10 years ago and mysql administration was always a pain in the ass to me. Maybe I don't get it, but I really think a debian box with postgresql installation is a breeze to administer.
$ createuser mom
$ dropuser pop
$ createdb -U mom
And psql is your friend. It really suffices for most database work.
At least on debian postgresql is a very good out of the box experience.
In my job at a large govt agency, I run MySQL in a moderately-sized configuration (1-2M records avg) in a 24/7 environment. We are *constantly* having to rebuild the database every few months because the @$#(*&^ thing keeps corrupting itself. We've examined every facet of the system, and have optimized the crap out of it. Now the whole database is running on high-end solid-state disk drives (backed-up, etc). Same problem. When it DOES work, its really, really slow. It can hardly get out of its own way. Our application requires a lot of subselects, and table joins. MySQL doesn't do very well with either of those. As a result, the application is required to do a lot more work for the database, and there's a lot more database traffic overhead than there should be. MYSQL BLOWS CHUNKS MySQL is good for some kinds of web applications where it doesn't have to do anything except get user data. But get it the hell away from you if you have REAL WORK to do. YOU'VE BEEN WARNED
...in particular, they are very useful for a process that programmatically transforms many input records into few output, that can't be done in declarative SQL. Otherwise, you'd have to pipe those many input over whatever network to the handler program. A database procedural language can operate on them in-line and without ever constructing or transporting huge data structures - then merely send you the results.
Our update server gets about 14 updates per second. We've never had MySQL corruption on the box.
Both of our query servers get between 200 and 300 queries per second, in addition to mirroring the update server, and have never had MySQL corruption.
Our data is small (about 12 Gbyte), so maybe we're just lucky, but I think heavy activity is probably not the problem.
We *did* get some corruption once, about 4 years ago. At the time we were paying for MySQL support, pushed our files to the developers, and MySQL was fixed.
Are you sure it's not a bug in the application?
I think Oracle beats both OS databases hands down, more complex the task or more data, better Oracle performs in comparison.
Spatial data, terabyte level datawarehouses , and very complex nested joins in optimizer makes OS stuff say quack. And then there is maintainability, real-time clusters, backup-recovery scenarios (single transaction cannot get lost in disaster recovery in stuff like banking) while database is online, haven't seen stuff like RMAN's block-level recovery in OS products.
Oracle just kick's ass in competition. Yes, it has lots of bugs when you look at the list at metalink, but oldest releases are already starting to be very solid stuff (like 9.2.0.6 for example for 9iR2 series)
In comparison, if PostgreSQL and mySQL compared, Postgre is just lightyears away. Last time I checked mySQL it lacked triggers, sequences, procedures, partioning tables and all the basic stuff.
How about not using Eclipse because IBM's business with the freakin Nazis ??!?!? Yeah it's all on CNET too:
http://news.com.com/2009-1082-269157.html
A couple of months ago I spent a good ammount of time tuning MySQL/InnoDB for a JBoss/J2EE app. It wasn't fun but I couldn't port the data to PostgreSQL.
I run quite a big forum, most active in its kind in Finland. One day in June the site just didn't let anyone sign in or show threads or messages. So I login:
[x@x forum]$ mysql -p x
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Didn't find any fields in table 'backup_yabbse_instant_messages'
Didn't find any fields in table 'backup_yabbse_log_activity'
Didn't find any fields in table 'backup_yabbse_log_errors'
Didn't find any fields in table 'yabbse_members'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 168027 to server version: 4.1.11-Debian_2woody1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from yabbse_members;
ERROR 1016 (HY000): Can't open file: 'yabbse_members.MYI' (errno: 145)
mysql>
After that I repaired my members table, losing all the rows. Luckily backups were recent enough, losing only 7 latest registered members (of some thousands).
After that, I occasionally get MySQL error messages from the e-mail warning system, but repair table helped and didn't lose data.
?SYNTAX ERROR
Everything is made for data integrity in PostgreSQL. It is made simple for the user: Do your stuff, we protect your data.
I've been long time working with Postgres, MySQL and Oracle and I really think both 3 are in different "market" sectors: - MySQL is the best in it's zone, it's fast, easy to use and reliable but IMHO it's not (still) a full RBDMS, what it's not a bad thing at all. It does the work it is supposed to do and does it better than nobody. - PostgreSQL is a wonderful RBDMS but not intended for OLTP-only-non critical data as MySQL is nor intended for the big markets. If there a real good point in Postgres (aside from being a free RDBMS) is that it's rock-solid even more than Oracle (from my own experience). The bad point of postgres is performance with big databases and/or big boxes. - Oracle is not for gaming, not intended for small or medium DBs or boxes, hard to admin and not easy to tune, also expensive but when the big boxes come and the big dbs are there it clearly outperforms postgres.
Someone that actually knows something.
Danke.
Everybody talks about MySQL 5.0 as if it's just a upgrade to MySQL 4.1. Isn't it a completely different database? Previously known as MaxDB and before that as SAP DB.
What genius decided that the Slashdot date format should be day and month only?
If you use en_US.UTF-8, en_GB.UTF-8, and probably any other en_XX.UTF-8 as a locale on Linux, you'll have Unicode trouble in a lot of apps: not just PostgreSQL. This is due to weirdness in the locale tables on glibc.
This problem does not affect other European languages, whose tables are coded correctly.
For a demonstration, look at this file. It shows the erroneous output of strxfrm under en_XX.UTF-8 locales, compared to other locales. Note, for example, how all Japanese kana evaluate to the same value, giving totally broken results for comparisons.
If your comment title says 'Re: Foo', I'm not likely to read it.
Indeed. I did some benchmarking of MySQL verus Microsoft SQL Server Express 2005 recently.
MSSQL works about 4x slower if you do not prepare queries properly. Even so, MySQL is significantly faster for the queries that matter in our application (but MSSQL is faster for certain other queries).
MySQL 4.1 and newer support prepared statements, if you really want them. (I'll benchmark that tonight, now that I think of it...)
Now that Ingres is open source, surely this is worthy of consideration when deciding which low cost DB to use.
MySQL MaxDB (formerly known as SAPDB) is comparable with Postgresql in terms of features. It suffers a similar lack of recognition with hosting companies and php developers. Ingres is another "enterprise" ready db that is available open source.
Who supports XA Transactions?
MySQL - Not yet (planned in 5.0.12?)
SAPDB/MAXDB - Yes (limitations?)
Postgresql - No - in the works (8.1/8.2?)
Firebird - Yes
Berkeley DB - Yes
Ingres - Yes (limitations?)
Pricing vs Proprietary
Oracle: $58K CDN per CPU + 10% maint
DB2: $55K CDN per CPU + 10% for maint
MS SQL Server: $3K CDN per year per server (enterprise edition)
MySQL MAX DB: $1,800 CDN per CPU + 10% maint
Postgresql: Free + Support
Ingres: Free + support
Be careful when making comparisons between Oracle and MySQL or PostgreSQL; Oracle is much more than a database. It's more accurately viewed as an information storage and retrieval system when you consider all of the other technologies that come with Oracle; eg. LDAP, JVM, textual search, media repository, geo-location service, etc.
Most databases don't support case-insensitive string comparisons in a speedy fashion. You either use "lc(col) = xxx" or "col like '[Xx][Xx][Xx]'" - both of which mean you are not using any indexes on col.
I know this is true in Sybase and Oracle at a minimum. Oracle may have recently changed in this regard but I doubt it (it's been a few years since I've used Oracle).
Sigh. No, MySQL is not like Microsoft, you ridiculous troll.
Seriously, this is one of my pet peeves when people go on about "this dbms doesn't implement this part of the standard" or "you should have been coding to SQL99, not the MySQL documentation". Do you know what the difference is?
Like my DBMS of choice, the MySQL documentation is FREE (and excellent, btw). Where do I go to get a copy of SQL99 for the same price? (At ansi.org all I see is a link that says "purchase standards", which is a rather sickening contradiction in terms in my mind.)
Sure, big companies can probably afford to license it for their developers. Web design contractors probably cannot. Besides, no matter what your intentions, you will be *actually* running your code on one of the many available choices; the MySQL manual does a pretty decent job of pointing out where the implementation differs from the spec, and at least gives you the knowledge about "non-standard" features that might help to simplify your life.. whether you use them is up to you.. portability is always an issue, but adhering to a standard that you aren't allowed to look at shouldn't be.
(Please note, I'm not really arguing for/against MySQL here, just want to point out that referring to the SQL "standard" is a bit like referring to GWB's "complete military service record". Give me a copy before you ask me to read from it.)
That's analogous to string variables in many programming languages. While PERL will (usually) be happy to tell you that a variable that contains an empty string and an uninitialized variable are equal, under C that will create an agregious, and sometimes elusive, bug.
sigs, as if you care.
As both a programmer and DBA, I am often disgusted by the atrocious way that most programmers treat the database. Joining tables by scan-and-select, naive use of features, bad assumptions, "it's all just rows and columns" thinking, etc. Modern RDBMSs are very sophisticated tools, with many ways to either shoot yourself in the foot or to provide serious value to the application, depending on the sophistication of the programmers.
In some cases, yes, you should keep all or most of your logic in your applications. In others, it makes sense to push some of the logic closer to the data. Like everything in this business, it depends on your goals and constraints.
As for "control-freak DBAs" ... If you got blamed every time some idiot screwed up a WHERE clause, you'd be a bit of a control freak, too.
I had forgotten how much cooler teenagers look when they are smoking. Oh, wait
since I'm on various lists, apparently the newest version of mysql will no longer be gpl'ed and in addition to upgrade, I'll have to pay $699 for any linux licenses in my shop before I can get mysql updates.
Not without warning you about your sloppy programming first:
$ perl -w
use strict;
my $uninit;
if( '' eq $uninit ) {
print "equal\n";
}
^D
Use of uninitialized value in string eq at - line 5.
equal
C has no concept of NULL in the same sense that perl and SQL do. In the later, NULL is a distinct entity from 0 or ''. In C, the "NULL" constant is the same thing as 0. That's why it shows up as a wierd bug, because there is no such thing as an uninitialized/undefined/null value in C in that sense. A variable always has a value, just not what you expect.
This concept is known as ternary logic.
If you use -Tw as I usually do, then yes, you get the warnings. The point is, however, that to PERL the two are equal, even though it knows an "undef" from an empty string. It's a feature.
C has no concept of NULL ...
True, even though C programmers do. As you say, an uninitialized char pointer may have the value 0 or some other randomish number you can't anticipate. The value "NULL" is usually a macro for "(void * (0))"), if I can split a hair, but there's nothing special about that value to the language or a compiler; you may init some pointer to the value 45 and probably not get a warning.
sigs, as if you care.
Let's recap: The only comparison in this slashdot article is a reference to a six year old comparison referenced in Slashdot. That's it. There is no comparison going on here.
Here's my comparison: starting somewhere in version 4.x, mySQL moved much closer to the SQL standard and other SQL implementations (MS, Sybase, Oracle et al.) than PostgreSQL, particularly in respect to stored procedures, and particularly when being programmed in SQL. (ironic, eh?)
I have recently checked out the latest versions of mySQL and I am impressed. As for PostgreSQL, it certainly has its strengths, but portable stored procedure support is not among them. Do a quick search of the table of contents for "stored" in the PostgreSQL documentation. Yes, it's buried there somewhere in the documentation but that in itself is telling. The earlier version of PostgreSQL was just plain different. My brief perusal of the current documentation suggests that it hasn't changed in this respect.
Reading a bit more, I see it hasn't. Input variables for PostgreSQL don't have names; they are numbered: $1 for the first argument, $2 for the second, etc. The only other database language I've seen this is 4th Dimension but that's for procedural scripts, not (the equivelant) of SELECT statements.
As an SQL programmer, trying to work with PostgreSQL was sheer hell. As for scripting in other languages, I wouldn't know. It seems to me that the PostgreSQL designers care little for common SQL coding.
I took a job porting a non-SQL database to PostgreSQL. The existing small IT staff chose PostgreSQL. I was hired as an SQL guy because none of them had any real experience with SQL. You should be able to see where this is going. Without going into details, the results were devastating for me.
Can anyone recommend any postgres development tools for windows? So far I've come across this:
m anager
EMS PostgreSQL Manger
http://www.sqlmanager.net/en/products/postgresql/
In PostgreSQL, NULL's have types.
If you try to do a SELECT NULL::text::bool you will see what I mean (you will get an error, that you cannot get a bool from a text value). A NULL doesn't mean you don't know its type (this can still be inferred from the column), just that you don't know its value.
Therefore in PostgreSQL you cannot compare NULL::BOOL NULL::TEXT simply because that is like trying to compare an unknown orange to an unknown apple. It doesn't work. You get an error rather than a NULL.
I don't know how NULL casts are handled in other RDBMS's (whether you can cast a NULL text as integer or make other arbitrary and invalid comparisons between NULLs). Though I believe that MS SQL assumes that NULLs are untyped.
LedgerSMB: Open source Accounting/ERP
I offer this as a hook near the top for opinions and discussions about PostgreSQL and MySQL, since the soap opera fans seem to have completely flooded the thread with discussions about SCO.
Have MySQL sorted out their data corruption stuff yet? Is there clustering support for Postgres yet? Does anyone have anything more useful to contribute than more ranting and conspiracy theories about SCO?
-1 Uncomfortable Truth
The server does not care about "\q" at all. The server only accepts SQL queries.
"\q" is a command for the "psql" program (which is the command-line client that postgres admins usually use). When psql sees "\q" it terminates itself, which is just the client software. The server of course keeps running.
Social scientists are inspired by theories; scientists are humbled by facts.
MSDE (yes its Microsoft) is free enough (via MSDN) and works fine for me... at least is the equivalent to an enterprise product.
e .mspx
. aspx
Isn't MySQL kinda like MS Access? SQL Express runs rings around both MySQL and PostgreSQL in my humble opinion.
SQL MSDE http://www.microsoft.com/sql/msde/howtobuy/msdeus
SQL Express (beta): http://lab.msdn.microsoft.com/express/sql/default
Latex, ASCII, Minix, and Pascal.
:P
PDF alert?
It may not be pretty, but it sure runs fast.
Actually I would have thought it would be the other way around. That it might not be fast, but the resulting documents would be pretty.
LedgerSMB: Open source Accounting/ERP
Under certain circumstances, MySQL will create a MyISAM table instead of an InnoDB table. Hence transactions are still broken wrt MySQL in my book. For some apps this is not a big deal, however.
.Net framework is coming soon too (which should make it really easy to port from MS SQL).
It is also certainly not as bad as truncating numbers........
As for Subselects. They don't perform too well yet. table inheritance is not planned (but is *really* nifty for things like data warehousing and table partitioning for massive databases).
Stored procedures? MySQL has those even in production releases (if you write them in C). The beta version only supports Perl.
PostgreSQL supportes them (out of the box) in SQL, PLPGSQL, Perl, Python, and TCL. You can also add PHP, Java, SH, R, and other languages if you like. I hear the
How about:
Tuple-level replication (MySQL's replication is statement-level which could cause problems in some cases)
XA or Two Phase Commit
Table Partitioning
SQL/MED (in PostgreSQL via DBI-Link)
User-defined types
Honestly, there is just so much more you can do with PostgreSQL that it is hardly worth comparing them.
LedgerSMB: Open source Accounting/ERP
1) Yes, I meant 'vacuum analyze'. Yeah, i've heard about something that does this for me, but gezus, I shouldn't have to worry about installing/configuring something else to get something as basic as this to happen automatically. Not to mention, I don't want this scheduled at certain times of the day, it should just "happen" as changes to the DB happen. Ie. it shouldn't be a batch job operation.
:(
2) Yeah, I'm familar with phppgadmin. It sucks. It's ugly as sin and doesn't allow me to do ER diagrams like phpmyadmin does (which honestly isn't the easiest thing in the world, but hey it works). The interface isn't nearly as intutive as phpmyadmin and it seems like everything takes 3x as many clicks. For now, using vim is actually easier for my 40+ table DB.
3) Mostly, I find the docs on mysql.com better organized and easier to find what I'm looking for. Honestly, Pg is very well documented IMHO for being such a large application (OSS or otherwise). The one thing that could be improved IMHO was performance tuning which is well documented, but is still seems very much like voodoo.
4) I've optimized the hell out of the INSERT's under Pg. Tuning postgresql.conf, doing transactions, etc. After talking to the guru's on #postgresql, the best I could get my batch job to run was 28hours (yes I said *hours*). MySQL does it in 8 (still too long, but I could live with it for a while). Dropping my FK's and indexes and doing some really creative coding by loading large (1GB+) portions of my dataset into RAM I've got it down to about 2hours (of course, I can't run any queries during those 2 hours since there aren't any indexes).
Don't get me wrong, I like Pg. It's definately a lot more powerful then MySQL/InnoDB, I just wish it was easier to use and allowed DBA's to make decisions to be able to trade off performance for reliability (turning off WAL for example, being able to disable FK checks without having to drop/create them, etc).
As far as C strings are concerned, you can have:
/* basically, 0 */
char *str;
str = "";
in which str points to a byte in memory that is 0.
char *str;
str = NULL;
In this case, str will point to the memory address 0, which is invalid. If you dereference that, your process will terminate. There is a difference between a null string and an empty string, even in C.
While that can be annoying, to be sure, why do people rely on the database to do their data validation?
Uh, so you really want to rely on some sweat shop programmers in Kuala Lumpur to get all your validation straight? You're really sure that the consultant that came in for a week to fix a legacy Cobol program understood all idiosyncrasies and properties of your corporate data? You guarantee - putting your job, professional reputation and career on the line - that no brain dead clerk in accounting hacks together an Access "application", which corrupts your database? See, I thought not.
That should be done in the application code long before you ever run an insert or update.
No, it sure as hell should not. As a matter of fact it's probably virtually impossible to guarantee that you caught any and all mistakes in the application. The complexity goes up exponentientally with the complexity and age of the application. I've interviewed a significant amount of applicants for dba jobs. If any of them would have come up with such a braindead concept the interview would have been concluded at once. There would be no more need to continue.
If you're trying to insert invlaid data, you're the only one to blame.
You ever heard of data type properties, unique indexes, declarative constraints, triggers and stored procedures? Either not, or you seem a bit unclear on the concept. Those are exactly the objects that support you in guaranteeing that this really cheaply produced piece of software as well as the illicite front ends from the accounting department rumaging through your corporate data are prevented on the lowest possible level to corrupt your data. Sure, it can still happen, the database engine can have bugs, you can have fundamentally flawed logic in your database code, etc. But the feasibilty for this to happening is exponentiality larger when you rely on the application to guarantee correct data.
I suggest that you educate yourself before turning yourself into the laughing stock of millions of readers.
HTH HAND
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
Why do you perceive the TCO higher with Postgresql then with MS-SQL Server?
I'm really curious here.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
This is exactly the kind of story I like to see on Slashdot as it generally encourages intelligent(??) discussion on technical issues.
And sure folks from both the MySQL and PG camps might get a bit hot headed; but hey ho it makes great reading!!
I always figured Oracle to be a badly assorted mass of a lot of files preferrably scattered around on a lot of disks. I think the architecture is a mess and that installation, administration and tuning is like a root canal while you get all four wisdom teeth extracted at the same time without narcosis. But...
If you have a look at this list of MySQL idiosyncrasies you will see why MySQL is simply unusable in an enterprise environment, or even in an environment that relies on the validity of your database transactions.
That doesn't mean that MySQL doesn't have a legit place, but it's certainly not in the billing environment of a major Telco, or in any billing environment for that matter.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
Not really. C doesn't have a string type like the languages we're comparing it to. In this example you just have one single variable of type 'char *' with a value of 0. *str isn't a different variable, it is just the same variable with an operator (*) applied. That still isn't the same thing as a null/undefined in perl/python/etc where any given single variable can be 0, some other value, or in the totally separate state of being undefined.
Actually that ability to compare undef and a value should never have been put into the language. It is just bad programming practice. That's why practically every perl program out there runs with -w and use strict nowadays. I bet perl6 won't even allow that type of stuff, at least not by default.
But it's true that the null pointer is not the same as a pointer to a zero-length string. The null pointer can't be dereferenced, so it's meaningless to try to determine its length.
(I'm talking about 2000 heren not "Yukon"/2005 which I admit looks quite promising)
.NET language though). You cannot create user-defined AGGREGATE functions in MSSQL.
* MSSQL is too damned expensive for what it does. PostgreSQL has more features and is free.
* MSSQL's scheme for locking and handling contention is primitive. I've had INSERT and UPDATE queries put on table-level locks at the oddest times. PgSQL NEVER does this, it has quite a sophisticated method of handling concurrent transactions on overlapping datasets (MVCC). If you have many concurrent connections performing a lot of INSERTs and UPDATEs it can be frustrating to manage all the locking in MSSQL.
* MSSQL is not as standards-compliant as PgSQL. The latter has non-standard extensions but their developers place much more effort on ANSI compliance.
* MSSQL is not multi-platform. All the others you mentioned will work on Linux, various UNIX flavours and Windows.
* MSSQL is feature-rich in many ways but lacks other features most of the competition has had for ages. For example, in MSSQL you can return the "TOP x" rows of a set but you cannot do an "offset"--for example, MSSQL cannot return rows 101 to 200 of a set! So, of you are doing a web interface that pages through results you have to resort to clunky hacks. Also you can only do stored procs in TRANSACT-SQL (I eagerly await Yukon's ability do do them in any
* MSSQL might be "easy to tune" but one might say that is because there isn't much that can be tuned. If the query plan seems wonky sometimes you just have to accept it or rewrite your query. There ARE more tuning parameters than one might be aware of, however you have to scour the web for a knowledgebase tip or an undocumented/unsupported hack. MSSQL is the antithesis of Oracle in that respect--the latter is extremely tuneable but trying to understand and manage it could make your brain explode.