Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Comparing MySQL and PostgreSQL 2

ScuttleMonkey posted more than 8 years ago | from the backend-makes-the-frontend-go dept.

Databases 902

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

cancel ×

902 comments

Get off it ScuttleMonkey (4, Insightful)

Anonymous Coward | more than 8 years ago | (#13484567)

SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.

Re:Get off it ScuttleMonkey (0)

Anonymous Coward | more than 8 years ago | (#13484634)

Mod parent "-1 Wrong". The partnership [mysql.com] is more then that.

Re:Get off it ScuttleMonkey (0)

Anonymous Coward | more than 8 years ago | (#13484674)

Did you actually read it? All they did was buy a license of MySQL that's not under GPL for their OS, and now they plan to offer certificiations for it. What next? "SCO and McDonalds announce deal" after some SCO employees eat a couple Big Macs?

Re:Get off it ScuttleMonkey (0, Troll)

rubycodez (864176) | more than 8 years ago | (#13484746)

yeah, I read it: "As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs" What are you, McBride's lap boy? You may soon be out of a job when McBride gets his 24K steel jewerly and new 350 lbs. body building homosexual rapist roomate for stock fraud.

Re:Get off it ScuttleMonkey (3, Interesting)

ray-auch (454705) | more than 8 years ago | (#13484644)

SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company

No, it's like MySQL _sold_ them something.

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

It would be interesting to know why MySQL did business with SCO, maybe on principle they turn no customer away, or maybe a need for money overrode. The latter case might be a legitimate concern for the community.

Not exactly ... (4, Insightful)

khasim (1285) | more than 8 years ago | (#13484727)

If I go to the store and buy a copy of MSOffice, that's one thing.

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

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

SCO and MySQL AB did the press release thing. That's not the same as SCO buying a license to distribute.

Re:Not exactly ... (4, Insightful)

Quarters (18322) | more than 8 years ago | (#13484805)

Press releases have no bearing whatsoever on the level of business relationships. Press releases happen if someone (or some-company) has enough money (~$300US) to do a wire release and has something to say. They're just an advertising medium. Nothing more, nothing less.

I could do a press release about how I just bought a tube of toothpaste at the local Kroger. The wire service(s) would happily take my money and put the story on their distribution network(s). Big whoop.

But would Kroger co-release it? (2, Interesting)

khasim (1285) | more than 8 years ago | (#13484871)

Would a Kroger executive talk enthusiastically about your new "partnership" with them?

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 ... 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!"

Re:Not exactly ... (0)

Anonymous Coward | more than 8 years ago | (#13484836)

SCO issued a press release. There isn't one from MySQL AB. They are a private company and they don't have a need to spew PR over every sale they make.

Re:Get off it ScuttleMonkey (0, Redundant)

jellomizer (103300) | more than 8 years ago | (#13484761)

Well that is the real problem with the zealot GNU People they are just as bad as the religious right. SCO is against the GNU especially Linux, MySQL is a business who produces GNU products. SCO wanted to give MySQL money for products and services, MySQL being a company who has to pay for their expenses and employees and make a profit. MySQL doesn't need to agree with all of SCOs policies and to them SCO's Tiff with IBM and Linux are none of their business. It is just like the argument between evolution. Just because there is a topic that people don't want to agree with, Random vs. Divine intervention they will decide to toss out everything and classify it as evil. I think if you people want to argue about all the problems with religion you should also see how you are handling issues like GNU usage, and making lines on who are the bad guy and the good guy.

Re:Get off it ScuttleMonkey (1)

toddbu (748790) | more than 8 years ago | (#13484816)

As a zealot GNU person who is a member of the religious right and believes in free market economics, I'm just not sure what to think of your comments. :-)

Many things (1)

btk667 (722104) | more than 8 years ago | (#13484572)

I have seen many url comparing the two.
But haven't seen any recent study.

Is mySQL still the most popular one?

Popularity (1)

truthsearch (249536) | more than 8 years ago | (#13484585)

MySQL definitely has a lot more mindshare. Therefore it's probably still more popular.

Re:Popularity (4, Insightful)

mellon (7048) | more than 8 years ago | (#13484648)

MySQL is like Microsoft. It's not entirely compatible with the standard, but everybody is using it, so if you want to use their software, you have to use it too. I have a copy of PostgreSQL and a copy of MySQL on my server, because Wikipedia doesn't work with PostgreSQL. I presume this is because the developers started working with MySQL back in the bad old days when it was _really_ incompatible, and their code now contains dependencies on MySQL.

I don't really know what to say about all of this - these incompatibilities are really frustrating as an end-user of this software, but I understand that it's hard to make things work with both MySQL and PostgreSQL, and resources are limited. What frustrates me is that these incompatibilities create a form of lock-in - once you've based your app on MySQL, you are stuck with it.

I suspect that if you were to start now, and to use the SQL spec rather than the MySQL documentation as a reference while doing your development, you would wind up with something that was a lot more portable, so this isn't actually an argument against using MySQL. It's more an argument towards sticking to standards when using whatever db you choose, so that when the time comes to use a different DB backend, you aren't faced with a monumental refactoring job.

Re:Popularity (3, Insightful)

TheMMaster (527904) | more than 8 years ago | (#13484726)

I hate to burst your bubble here, but ALL dbms's are slightly different from eachother, this is largly because the SQL spec leaves room for these kind of things.
It's not like there is a HUGE difference, as long as no dbms specific procedural language was used (think plsql (oracle)) it's pretty trivial to port an application from one dbms to the other
The biggest differences are usually pretty subtile and indeed rather frustrating, but by no means hard to solve.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.

Mysql is very isp friendly (4, Insightful)

Billly Gates (198444) | more than 8 years ago | (#13484798)

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.

Re:Popularity (1)

Billly Gates (198444) | more than 8 years ago | (#13484814)

And I forgot to say this is why Microsoft took over since we are comparing mysql to Microsoft. :-)

Sure DOS sucked but hey the tools were for DOS and it was very user friendly since it came on a pc and you could develop quick apps on it. (proprietary though)

Re:Popularity (1)

Infernal Device (865066) | more than 8 years ago | (#13484875)

The other possibility is to use some sort of intermediate layer such as ADODB to relieve some of the problems associated with specific platform issues. These have their own sets of problems, but we don't live in a perfect world.

popularity (1)

ignorant_newbie (104175) | more than 8 years ago | (#13484592)

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.

Re:popularity (3, Insightful)

Jerry (6400) | more than 8 years ago | (#13484651)

He didn't seem to be at all bothered that this the main argument people give for using windows.


That's probably because those features are part of PostgreSQL and is the main argument for why people believe that PostgreSQL is overtaking MySQL. Also the fact the PostgreSQL can run PL/SQL with only some modifications, and visa-versa.

Re:popularity (1)

winkydink (650484) | more than 8 years ago | (#13484652)

he responded "yes, well, it's the most popular database on the planet".

If you don't need transactionm support or stored procedures; two things a lot of large-scale, high transaction databases cannot live without.

It's like saying legos are the most popular building material in the world.

OT: Re:popularity (1)

caluml (551744) | more than 8 years ago | (#13484679)

legos are

Is that a typo? Cos I would always have said "lego is".
Where are you from.... Just wondering....

Re:OT: Re:popularity (0)

Anonymous Coward | more than 8 years ago | (#13484699)

in the U.S., "legos" is the plural of "lego", where "lego" is a noun referring to an individual lego brick.

Re:OT: Re:popularity (1)

caluml (551744) | more than 8 years ago | (#13484724)

Thanks for that.
I wonder if it's to do with the way the UK and the US talk about companies.
UK: The BBC is (as though it were an entire entity)
US: The BBC are

Re:OT: Re:popularity (1)

Wereon (842163) | more than 8 years ago | (#13484785)

Wrong way round. Although the BBC itself does say "The BBC is".

Re:popularity (4, Informative)

Bloater (12932) | more than 8 years ago | (#13484663)

MySQL's biggest problem is that if you try to update or insert with invalid data, in many cases it successfully inserts wrong data. PostgreSQL doesn't do that.

PostgreSQL has this nice Object-Relational model where tables can be derived from each other, but there are some nasty bugs that mean I think those features are still best avoided.

Overall, though, I think PostgreSQL is by far the better RDBMS.

Comparing MySQL and PostgreSQL 2 (-1, Flamebait)

Anonymous Coward | more than 8 years ago | (#13484574)

Well that's easy, one's a powerful database, and the other ain't.

Yawn.... (3, Insightful)

truckaxle (883149) | more than 8 years ago | (#13484583)

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.

Yawn indeed (0)

Anonymous Coward | more than 8 years ago | (#13484615)

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.

Re:Yawn indeed (1)

truckaxle (883149) | more than 8 years ago | (#13484808)

Plus it is a vacation day as you correctly pointed out

Are you suggesting the average slashdot reader is enjoying this long weekend like normal folk do?

Right now the majority of denizens of slash are peering into monitors in the mothers basement, studio apartments, wifi in the coffee shop, upstairs in the overheated spare bedroom, or dorm rooms. Sitting motionless except for the sporaidic deft fingers clicking on the keyboard of slow arc movement of the mouse. Sad but true. Wait my mom is yelling for me upstairs I gotta go....

Another question (4, Insightful)

Eightyford (893696) | more than 8 years ago | (#13484589)

Sure it's slashdot and we all love free software, but how do these two compare with oracle, sql server and other non-free db's?

Re:Another question (1)

Orkie (899576) | more than 8 years ago | (#13484600)

Well, I don't like MS MSQL Server but that is just me (nothing to do with it being Microsoft of non-free, it just doesn't suit me).

Re:Another question (2, Informative)

Anonymous Coward | more than 8 years ago | (#13484628)

Not really. Oracle has the 'non benchmarking' clause that prevents you from doing that. If people were able to compare it to, say, PostgreSQL, and publish the results you'd see a lot pgsql boxes replacing expensive Oracle licenses. MS's SQL server is a total joke.

Re:Another question (4, Insightful)

Dogtanian (588974) | more than 8 years ago | (#13484693)

Oracle has the 'non benchmarking' clause that prevents you from doing that.

Then arrange to have the benchmarking done in a country which won't uphold anti-competitive bullshit clauses (and when Oracle protest that the license lets them sue the guy in the jurisdiction of Buttfuck, Illinois, will tell them where they can stick their extradition request).

Although I reckon such a case (brought by Oracle) might still get thrown out in a US court, I wouldn't bet my life savings on that, and the US legal system means you're unlikely to get fees paid if Oracle lose (does this *ever* happen in the US?); a great way for the large company to effectively win by attrition if the benchmarkers don't have that much money.

Re:Another question (2, Insightful)

Trigulus (781481) | more than 8 years ago | (#13484767)

A joke in what way? I have run several MS-SQL servers for many years and the only two things that continualy bother me is the lack of being able to include an auto-numbered column in results without using something expensive like functions and the lack of paged (limit) result sets. I am however in the process of migrating to PostgreSQL.

Re:Another question (4, Interesting)

Jerry (6400) | more than 8 years ago | (#13484686)

PostgreSQL compares very well to Oracle.

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

If I had my way I'd use PostgreSQL as the primary database, but some folks believe you've gotta pay money or the app isn't any good. As long as it's their money and not mine.

MySQL vs. Oracle (2, Informative)

solman (121604) | more than 8 years ago | (#13484731)

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.

Re:Another question (5, Informative)

ron_ivi (607351) | more than 8 years ago | (#13484766)

This page [arvin.dk] is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.

Helllooo?? Editors?? (4, Informative)

wfberg (24378) | more than 8 years ago | (#13484590)

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

This sort of whoring-for-comments article should be a poll.

Re:Helllooo?? Editors?? (1, Flamebait)

geoffspear (692508) | more than 8 years ago | (#13484606)

You haven't heard of Ask Slashdot?

You must be new here. How'd you get a user ID in the 24000s? Are they recycling old ones now?

Re:Helllooo?? Editors?? (1)

Dot.Com.CEO (624226) | more than 8 years ago | (#13484692)

This is an ask slashdot template: "I have a problem, can the slashdot collective help me?". This is stupidity: "Is Gnome better than KDE? What's your opinion?"

Re:Helllooo?? Editors?? (1)

Tablizer (95088) | more than 8 years ago | (#13484796)

If you don't like slashdot, than cancel your subscription.

My point of view (2, Informative)

TruthSeeker (461299) | more than 8 years ago | (#13484601)

I'm using both, but mainly Postgres. From what I can tell:

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 ... well, bad)
- No stored procedures/triggers
- Easily corrupted by crashes.

Re:My point of view (4, Informative)

einhe1t (900548) | more than 8 years ago | (#13484712)

Parent is comparing non-current versions, and making up false "cons" for mysql, out of thin air...

Mysql 4.1 is the current stable version, and 5.0 is nearing release.

4,1 has excellent relational support, it is damn near impossible to corrupt if db design is correct, and innodb is great. IIRC ./ has been running on mysql + innodb for years. It also support clustering "out of the box".

5.0 has views, triggers, stored procedures etc, and it's still amazingly fast.

Note: I base my mysql 4.1 comments on the linux version. I have heard that there is a version of mysql for windoze, but I can't vouch for it, and for all I know, it could be a disaster, but I don't really have anything definitive to say about it. Who knows, maybe original poster is talking about mysql on windoze (shrug)

Re:My point of view (0)

Anonymous Coward | more than 8 years ago | (#13484799)

MySql is the Windows 95 of the DBMS world.

Once the 5.x branch is regarded as production quality and the issues raised at http://sql-info.de/mysql/gotchas.html [sql-info.de] have been addressed then I'll consider it as something more than a toy. Until then, I'll stick with Postgresql.

Re:My point of view (0)

Anonymous Coward | more than 8 years ago | (#13484820)

Postgres isn't a speed slouch either, if you're judging by the "butt dyno" with terms like "amazingly fast."

I like to use PostgreSQL because it adheres to the standards better. Plus, I don't have to deal with this list (infamous, for certain)

http://sql-info.de/mysql/gotchas.html [sql-info.de]

There's also a quote out there about one of the primary devs not knowing the difference between a PRIMARY KEY and a UNIQUE field. That is not the kind of leadership I want in a database.

Haw haw (4, Informative)

Safety Cap (253500) | more than 8 years ago | (#13484874)

Parent is comparing non-current versions, and making up false "cons" for mysql, out of thin air...

5.0 has views, triggers, stored procedures etc, and it's still amazingly fast.

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.

Re:My point of view (4, Informative)

scrutty (24640) | more than 8 years ago | (#13484754)


There is no postgresql release 7.5. The last 7.x release was 7.4 , the current stable is 8.0 with 8.1 in beta.

Re:My point of view (1)

TruthSeeker (461299) | more than 8 years ago | (#13484784)

True, must be something specific to the Debian package ... Don't know why they did that tho.

Re:My point of view (1)

sbenj (843008) | more than 8 years ago | (#13484774)

I've also used both, and agree with you with a few caveats.

-I haven't had crash issues, everything's pretty much worked as advertised.
-I'd read that the biggest issues) for me with MySQL, the limitations in using subqueries, were in the process of being fixed. That'd make the 2 basically equivalent for me, at least in functional terms (coming from a developer's perspective here, not a DBA)

I'd like to see benchmarks, but other than that both systems are fabulous, I really appreciate the work that's gone into them. And they're much easier to work with than oracle (where you often get the impression that they intentionally obfuscate things to justify the cost).

Re:My point of view (2, Insightful)

Tablizer (95088) | more than 8 years ago | (#13484853)

And they're much easier to work with than oracle (where you often get the impression that they intentionally obfuscate things to justify the cost).

To be fair, Oracle targets the really high-end apps or performance where hiring a dedicated Oracle expert(s) is worth it.

I doubt even Postgre can match the performance, reliability, and scalability of Oracle. Only IBM DB2 can come close so far. Postre may compete with Sybase and SQL-Server's level.

Thus, you are comparing apples to oranges.

Although for read-only speed, some RDBMS do seem faster than Oracle. Oracle seems optimized for a fairly even mix of reads and writes and reliability. Thus, if you sacrafice one of these, you perhaps may find something a bit faster (or at least easier to tune for such.)

(And please find a better name for Postgre. I keep thinking "post nasal green drip" when I see the name.)
   

The most interesting part of the old article... (-1, Offtopic)

Ann Elk (668880) | more than 8 years ago | (#13484602)

...is the abundance of four-digit user IDs. I feel like such a n00b.

Re:The most interesting part of the old article... (2, Funny)

adamwood (5089) | more than 8 years ago | (#13484627)

You are such a noob :-)

Re:The most interesting part of the old article... (3, Funny)

DAldredge (2353) | more than 8 years ago | (#13484673)

So are you :)

Re:The most interesting part of the old article... (0)

Anonymous Coward | more than 8 years ago | (#13484721)

I have a zero digit ID, I win!

Re:The most interesting part of the old article... (1)

FiReaNGeL (312636) | more than 8 years ago | (#13484694)

The signal / noise ratio was also MUCH, MUCH higher.

Re:The most interesting part of the old article... (1)

alba (7098) | more than 8 years ago | (#13484725)

What's a 4 digit ID again?

Minor Detail (1, Redundant)

cloudkj (685320) | more than 8 years ago | (#13484607)

One thing I noticed when using PostgreSQL in my databases class was that someone mentioned that MySQL is the only dbms that actually lets you exit the application when you type "exit". I believe in Postgres, you have to type "\q". Maybe MySQL is more user-friendly?

Also (4, Funny)

TheRaven64 (641858) | more than 8 years ago | (#13484608)

I have been wondering, which is better, vi or emacs?

Re:Also (0)

Anonymous Coward | more than 8 years ago | (#13484666)

I am so sad that I wasted my mod points on things other than modding this +1 funny.

Re:Also (1)

DataPath (1111) | more than 8 years ago | (#13484667)

Neither. Use ed [gnu.org] !

Re:Also (1)

BoomerSooner (308737) | more than 8 years ago | (#13484698)

I like Textpad [textpad.com] or in a worse case scenario VIM but that is worse case only.

Re:Also (1)

McGiraf (196030) | more than 8 years ago | (#13484680)

that is an easy one:

joe

And is Windows really better than Linux? (0)

Anonymous Coward | more than 8 years ago | (#13484684)

My mother's friend's son says it is.

Re:Also (0)

Anonymous Coward | more than 8 years ago | (#13484844)

vi, of course!

Don't listen to those emacs/joe/M$ Word _l_users.

Been using MySQL, but... (1, Interesting)

imbaczek (690596) | more than 8 years ago | (#13484611)

...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?)

Re:Been using MySQL, but... (1)

MemoryDragon (544441) | more than 8 years ago | (#13484633)

I would be more scared by having corrupted data on mysql (which happens more often than you can think) thank having a task trigger from time to time which does vacuum on the db repo without damaging anything. Yes vacuum still is there, almost any db has it, it helps to keep the db fast and yes there are facilities which let you do the task automatically.

Re:Been using MySQL, but... (3, Informative)

Anonymous Coward | more than 8 years ago | (#13484635)

In recent versions of PostgreSQL, VACUUM still exists but is non-locking (i.e. it will not block concurrent database activity). There are tools available to run VACUUMs automatically as the database is in use, and recent versions of Postgres have been tuned so that the I/O performed by VACUUM should have less of an effect on the rest of the system.

Re:Been using MySQL, but... (1)

kanazir (704999) | more than 8 years ago | (#13484740)

Put /usr/local/pgsql/bin/vacuumdb -U pgsql -a -z in your crontab and forget about it...

Found one! :) (2, Informative)

Spy der Mann (805235) | more than 8 years ago | (#13484649)

Re:Found one! :) (2, Informative)

llefler (184847) | more than 8 years ago | (#13484819)

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

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

Spatial databases (4, Informative)

MostlyHarmless (75501) | more than 8 years ago | (#13484662)

I don't have any thoughts about the more general question, but PostgreSQL is much better at storing spatial data than MySQL. MySQL has spatial functions built in, but it only supports a subset of the OpenGIS functions [mysql.com] (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS [refractions.net] , which supports the full OpenGIS specification and a bunch of other extension functions besides. I've used MySQL by default simply because it is more familiar to me, but I've switched to PostgreSQL for my current project simply because of the spatial data module.

PostgreSQL is supreme A LOT (4, Interesting)

michalf (849657) | more than 8 years ago | (#13484664)

there is a short (decent) comparison at this url [poznan.pl] .
From my point of view (web application developer, Ozone [ozoneframework.org] framework author and the author of a few rich-content websites I can say for sure: I am more than happy to discover PostgreSQL. Why? More Oracle-like, transactions, nested transactions, views, sql-schema... I doubt MySQL 5.0 will come even close to the standard of PosgtreSQL.
Some can say MySQL is fast. No, it is not. When you run more than 100 users at once PostgreSQL is faster. MySQL has stupid table-locking mechanism that decreases performance significantly under high load.
I would say: PostgreSQL seems to be slower, is not perfectly optimized, but much better goals in its design were used. And one of the goals ic SQL conformance. MySQL is FAR from the SQL standard.
If you want to migrate from MySQL to e.g. Oracle - it is a pain. But PG is much closer to it.
IMHO PostgreSQL is an industry-standard database and we use it for almost every project now. We have used MySQL some time ago and believe me - the difference is huuuuuge. PG is a real database. MySQL seems like a table-managing-application ;-)

best regards - michal

Re:PostgreSQL is supreme A LOT (1)

ashpool7 (18172) | more than 8 years ago | (#13484872)

Postgres DOES NOT do nested transactions. I like Postgres, but that's simply not true. 8.0 has something called "savepoints" in transactions where you can abort part of a transaction, but it doesn't do nested transactions.

I used to like MySQL (5, Insightful)

caluml (551744) | more than 8 years ago | (#13484665)

The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.

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

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

Re:I used to like MySQL (2, Informative)

croddy (659025) | more than 8 years ago | (#13484770)

mysql> select count(*) from users where id not in (1, 2, 3, 4, 5);
+----------+
| count(*) |
+----------+
|       93 |
+----------+
1 row in set (0.12 sec)

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

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

mysql>

Re:I used to like MySQL (0)

Anonymous Coward | more than 8 years ago | (#13484818)

Indeed.

SELECT *
        FROM foo
        LEFT OUTER JOIN wheee
                ON foo.bar = wheee.blib
        WHERE wheee.blib IS NULL

NOT IN is evil.

AJO

Re:I used to like MySQL (2, Informative)

DrXym (126579) | more than 8 years ago | (#13484868)

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


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


Sadly though, there's no incentive since MSDE is "free". Basically MSDE is a cut down SQL server which is no bad thing. But I'd love to see Postgres bundled in a similar form on XP - just the engine but nothing else. I reckon lots of apps need a database engine and Postgres fits the bill quite nicely. It would even be a smaller redistributable than MSDE too.

Still the same, but less so (1)

m50d (797211) | more than 8 years ago | (#13484677)

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.

Simple (-1, Troll)

drsquare (530038) | more than 8 years ago | (#13484681)

PostgreSQL = Incredible
MySQL = Ridiculous.

Discuss.

Re:Simple (-1, Offtopic)

Anonymous Coward | more than 8 years ago | (#13484742)

Discuss.

Oh that is fucking annoying. It's worse than when people end their sentences by spelling out the word PERIOD. It's almost as bad as the MOD PARENT UP posts too... and those are mighty fucking annoying.

What a terrible question? (1)

Billly Gates (198444) | more than 8 years ago | (#13484697)

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.

ford chevy (1)

joeldg (518249) | more than 8 years ago | (#13484701)

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..

I have a small problem... (1, Informative)

Anonymous Coward | more than 8 years ago | (#13484716)

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?

Re:I have a small problem... (1)

RPoet (20693) | more than 8 years ago | (#13484745)

You can use MySQL as a database engine in proprietary projects. However, if you want to make a derivate database engine from MySQL, you have to release that part under the GPL. There is a huge difference.

Re:I have a small problem... (1, Interesting)

Anonymous Coward | more than 8 years ago | (#13484845)

From my understanding of the below statement...
If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library.

The simple fact that I access MySQL via OBDC requires me to obtain a license before I may provide a commercial service.

Like before, It seems that I am still stuck.

No. (0)

Anonymous Coward | more than 8 years ago | (#13484849)

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.

Re:I have a small problem... (0)

Anonymous Coward | more than 8 years ago | (#13484827)

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).

This is a problem... why?

MySQL will still be.. (1)

dotdan (902253) | more than 8 years ago | (#13484741)

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.

Don't forget Interbase/Firebird (3, Informative)

ThinkThis (912378) | more than 8 years ago | (#13484750)

Our company has developed an app used at several hundred sites on the Interbase/Firebird platform. (Firebird is now the only open source version). It is stable, quick, low maintenance with support for transactions, triggers, row level lockinge, etc. I would consider MySQL for web development because it comes preinstalled on many hosts and because of the number of tools available.

Web Tools (2, Insightful)

mikeboone (163222) | more than 8 years ago | (#13484759)

So far I've stuck with MySQL for most of my projects since phpMyAdmin is so much better than phpPgAdmin. I can almost always get a web-based database tool running on the platform I'm developing for.

If there's a better web interface for Postgres than phpPgAdmin, let me know so I can try it.

Heavy (5, Informative)

HadenT (816717) | more than 8 years ago | (#13484776)

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

* - it's unlikely but possible my configs are to blame.

here's a comparision (0)

El_Muerte_TDS (592157) | more than 8 years ago | (#13484778)

mysql has 5 letters
postgresql has 10 letters

so clearly, postgresql has twice the letters, a clear victory if you ask me

What does postgres mean? (0)

Anonymous Coward | more than 8 years ago | (#13484783)

as bad a name as joomla.

Re:What does postgres mean? (1)

Cmdr TECO (579177) | more than 8 years ago | (#13484861)

POSTGRES came after INGRES. [wikipedia.org]

My GOD! (1)

jellomizer (103300) | more than 8 years ago | (#13484800)

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.

Re:My GOD! (1)

iggymanz (596061) | more than 8 years ago | (#13484829)

"As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs" Sounds like more than typical product selling partnership to me

BitcH (-1, Offtopic)

Anonymous Coward | more than 8 years ago | (#13484801)

don't be afraid ThINKING ABOUT IT. obsessed - give recent article put

In South Korea... (1, Funny)

JamesP (688957) | more than 8 years ago | (#13484837)

only old comparisons matter...

(had to do it)

Save me from the VACUUM! (0)

Anonymous Coward | more than 8 years ago | (#13484841)


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

Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...