Slashdot Mirror
PostgreSQL 7.3 Released
rtaylor writes "Nearly a year's worth of work is out. The new tricks include schema support, prepared queries, dependency tracking, improved privileges, table (record) based functions, improved internationalization support, and a whole slew of other new features, fixes, and performance improvements. Release Email - Download Here - Mirror FTP sites (at bottom)."
Did they do anything to improve/add replication support? That seems to be the only real thing that was holding it back from replacing Oracle, as far as I can tell. I know several projects for such a thing were in the works, but they appeared to be very beta.
Software piracy is victimless theft.
WOOHOO!
DROP COLUMN [column] FROM TABLE [table];
This up-until-now lacking feature has been the bane of my existence. I HATE cruft being left lying around.
(btw, I don't know if that is the correct syntax, just a guess)
My
Limekiller
It's an Oracle killer based on the fact that 80% of Oracle installations out there are overkill.
Seriously, look at the amount of Oracle installations out there. Now how many of them _need_ any of those features? Is it worth the extra cost of Oracle? More than likely it's all some marketing crap that eveyrone is led to believe than "only Oracle can do this".
My PHB is like this. He insists we use Oracle because we need an "industrial strength database", for a db with 40,000 records. Argh! Oracle is freaking expensive, we got Larry Ellison crusing around in some damn yacht race on our bill. In our case, Postgre would be an Oracle-killer, it's just getting people past that fact that Oracle is unnecessary for alot of applications.
That article's very much out of date, as is stated in an addendum at the top and as is made clear in the later posts in the discussion of the piece.
...
I'm the OpenACS project manager so want to make sure that people understand that the piece (and much of the commentary) was accurate when written, but that it was written many moons ago. However, nowadays MySQL has the InnoBase backend which provides full transaction support, and has seen other major improvements.
Our project only supports Oracle and PostgreSQL, and I still feel MySQL is lacking in many areas, but it has improved greatly in the last couple of years.
So has PostgreSQL, of course! We love it
Of course, he uses it on a Thinkpad, but you have to spend money to make money.
You are not alone. This is not normal. None of this is normal.
Well, if you insist... :-)
It's better for certain things (most things, actually). PostgreSQL is a bit more feature-complete as a SQL database than MySQL is. MySQL is improving, certainly: it now has transactions and such. But PostgreSQL has quite a bit more: triggers, rules, stored procedures, and views, for instance.
In terms of speed, MySQL is faster for certain specific operations but that speed comes at the price of database integrity: the lack of rules and triggers means that it is not possible for the database to enforce consistency between tables. One must thus trust applications to do the right thing, which is generally not wise.
It's like the difference between an OS with memory protection and one without. The one without may be faster for certain things, since the OS doesn't have to worry about messing with page tables and dealing with page faults of various kinds, but the price is that you now have to trust the applications running under the OS to do the right thing and not touch memory that doesn't belong to them.
As I said, MySQL is faster for certain things. But PostgreSQL is reportedly better at handling lots of concurrent transactions than MySQL. It's not clear, then, that MySQL is much better than PostgreSQL, if at all, under high load situations. And if it isn't, then there's really little reason to go with it over PostgreSQL.
Finally, even if MySQL is faster, it's not likely to be so much faster that it is the difference between success and failure. And I can tell you this: experience shows that the initial requirements of a project are often vastly different, and usually much less demanding, than the final requirements for the same project. So it makes more sense to go with the most capable database backend you can lay your hands on, as long as it remains within your budget (your real budget: remember that you're likely to spend a lot more money than you expected, if only because the requirements will change over time). That means going with PostgreSQL over MySQL, if given the choice. You have to make the decision early because changing your database engine mid-project is extremely difficult, especially if your code was written to work around the limitations of the database engine, as it almost certainly will if you're using MySQL.
These days I don't think the question should be whether you should go with PostgreSQL instead of MySQL. It should be whether you should go with MySQL instead of PostgreSQL. PostgreSQL should be the default choice these days, because it is so much more capable at the same price.
Use 'slashdot stuff' in the subject line in any email you send me if you want to get past the spam filter.
If mysql is 'borking' at anything over 8000 rows, I'd take a real hard look at my design if I were you. I did a conceptual demo for a client once, where we sucked up a raw data file from their mainframe - 65000 rows, five tables, and the largest table had about 18 columns. The import took all of 45 seconds, and there was absolutely no performance problems of any kind. This was being assessed to determine how much faster this would have been compared to a RAD-based solution they were currently using. But 65,000 rows is small potatoes.
No, it hasn't. A summary of the list of missing features:
Of course, they give crap rationalizations for each, and/or that "it's planned for [distant version of MySQL]". Of these features, only the last might be considered trivial, and even that is quite a pain if you're trying to write some portable SQL.
The others, particularly the lack of triggers and foreign keys, make this a data integrity nightmare for anything nontrivial.
Sure, sure, "but you can do it all in code": typical response. You know, that was their response to lack of transactions, too. "Too slow", "you don't need those". Right. You could just write a whole database in your code, too. The point of using a RDBMS (and, lacking relations of any sort, makes MySQL just a DBMS) is reliability so you don't have to constantly worry about these things.
PostgreSQL has all of the above features, and quite a few more. It's an OORDBMS. (Yes, this is very cool, and lets you do some very nifty things.) It's got better-than-row-level-locking (MVCC; MySQL does table locking only.) And all the other things people have mentioned here.
MySQL is a toy database, and should be treated as such. Not just for transactions: for all the things that make a robust RDBMS.
Don't think of it as a flame---it's more like an argument that does 3d6 fire damage
...mySQL had proper, full support for constraints Slashdot wouldn't post so damn many duplicate articles...
HAR HAR HAR...just jokin' around...pleeeeze don't kill me.....
Let's go down the list:
...and a year after the bug was fixed, do we see a benchmark update? Nope.
...whatever.)
What the MySQL developers conveniently fail to mention is that if you use transaction-aware table types, performance drops dramatically. Under load with multiple concurrent connections, PostgreSQL is pretty close to the speed of MySQL or faster by default and blows MySQL's doors off when MySQL is transaction safe.
Regarding foreign key constraints, see note regarding transactions. And if you are really concerned about fk speed, you don't have to use them in PostgreSQL either.
Record locks hunh? It may surprise you to know that as a user, you don't need to explicitly tell PostgreSQL to lock tables with your queries. Ever. This a relational design issue. This should be handled by your database architect when they layout the table structure, rules, foreign keys, views, and triggers. You do have someone that designs the table structure ahead of time right? Sure you do. But why don't you have to explicitly lock the tables is PostgreSQL?
Maybe it's because PostgreSQL is smart enough to know when you need them without your help.
Transactions? Aren't those only for banks and e-commerce? Nope. Let's say you want to update all the users in Slashdot to give all of those loyal geeks one extra karma point. So you select on all users, grab their current karma, add one, and update the record. This has two problems: concurrency and completion. What happens if the user is moderated up or down in between the moment that the record is selected and the moment it's updated? Looks like the user has accidentally been given either (a) an extra point or (b) had a point taken away. Also look at what happens if the database goes down while doing the work (someone kicked a cord), who got the extra point and who didn't? Darn. Wish I had transactions...
So you use the transaction-aware MySQL tables. Wow! Performance has sure dropped out and we have to think about implementation details like locks. I sure wish there was a way to avoid stupid programming mistakes like forgetting a lock. Well...you could use just about any other database out there (including PostgreSQL).
As for stored procedures and triggers, you need not talk about features that aren't here *yet*. Version 4 isn't out of beta yet and you're hanging on a possible v5 feature? While we're at it, let's talk about how multi-master replication will appear in PostgreSQL by then. And didn't you hear? Microsoft's IIS will have its security holes patched up in two years too. Vaporware is vaporware. Believe it when you can download it.
------
Now then, on to personal gripes about MySQL above and beyond the ones I have listed above.
Benchmarks: On MySQL's benchmark page comparing PostgreSQL, they complain that no utilities are available for benchmarking but their own. This is not strictly true. No benchmark can be made because the syntax to the different RDBMSs are so dissimilar that none can be made currently without a strong bias. Stored procedure support, for example, would definitely skew results away from MySQL. But that wouldn't be fair for a benchmarking tool since MySQL doesn't support stored procedures. The same is true of triggers, rules, views, and other such "unimportant" features.
Of course MySQL's benchmark shows MySQL in a good light. They use only the feature set of MySQL to perform the benchmark.
They also mention on the page that they've contacted the PostgreSQL developers for tuning information and methods of improving the benchmark tool. I cannot express loudly enough that THIS IS A LIE! The PostgreSQL mailing list has many instances of developers reporting that they (a) never heard about this "contact" until someone pointed out the MySQL page, and (b) they have been ignored when they've tried to submit tuning techniques and other optimizations. Sounds like some people don't want their benchmarks to give the "wrong" results. Heaven forbid!
As it stands now, the benchmark is a year and a half old. MySQL is on its 53rd patch revision and PostgreSQL is two minor version releases later since this benchmark was released. Weren't you saying something about posting stale information? They still have a page complaining about vacuum bugs and the desire for a newer version of PostgreSQL that fixes the problem.
Feature comparisons: another source of info, it talks about the query speed on mostly read only data. Did someone forget to mention that flat files are even faster for mostly read only data?
It states that since MySQL has more users, it must be better than PostgreSQL. Funny how that logic doesn't seem to work for Windows. They use the same logic with the number of books. It wouldn't surprise me if there were more books on DOS than Linux. Does that make DOS better? Does that say anything about the relative quality of those books? No.
MySQL supports more APIs and languages. This is correct unless you want to count stored procedure languages. Oh wait, MySQL doesn't support stored procedures. (Yet! They'll be there in two or three years or so.
It then touts MySQL's fine replication facilities. Hello people! How often has slashdot gone down due to database issues? Hardly a poster child for stability or reliability.
According to mysql.com, PostgreSQL doesn't have a unit test/regression test. It makes one wonder if they've even used PostgreSQL.
PostgreSQL is said to be deficient with ODBC. Too bad they couldn't provide any specifics.
I'll relax about the statement that MySQL had more functionality with ALTER TABLE. FYI for readers, that has just changed with PostgreSQL 7.3.
They are correct that PostgreSQL doesn't have MERGE. Instead, they use the SQL92 standard UNION. Does the same thing. And let's not forget about views. What was that!? MySQL has extensive use of non-standard syntax? Any queries you write in MySQL will only have a prayer of working on MySQL? Say it ain't so!
PostgreSQL has had full text search for a while as part of contrib.
I don't even want to start with "MySQL Server is coded from the start to be multi-threaded, while PostgreSQL uses processes." Aside from databases on Windows, this helps whom significantly? A clue folks: Apache HTTPd also uses processes extensively. Pure thread support was only really added for the sake of Windows. This is one of those times where stability and consistency are more important than raw speed. This is your data!
And on and on...
As a final note, I would like everyone to take a trip down memory lane with me and recall that the MySQL dev team didn't see a need for MySQL to have transactions or any other of those "fancy" things at all until a couple of years ago -- when everyone started to realize that MySQL wasn't really twice as fast as PostgreSQL even though MySQL was crippled feature-wise.
The MySQL has so much misleading information (apart from the items that are outright false) on the web site, I wouldn't recommend it to anyone who wants correct information, "current" or otherwise.
- I don't need to go outside, my CRT tan'll do me just fine.
The thing that makes Postgresql completely different from MySQL is that it is an *active* RDBMS. By active, I mean that you can set it up so if it gets certain kinds of data, it can operate on that data to create new records, delete records, update other tables etc.
Postgresql has the *intellegence* built in. You can write all sorts of georgous functions to do stuff, especially if, like us, your shop uses several languages... PHP, Perl, Java, Python, C++, etc. Why replicate your business logic everywhere?
Transaction support and file/record locking are the least of your problems. If you do serious database stuff, at some point, you are *going* to want VIEWS, TRIGGERS, RULES, and STORED PROCEDURES (functions). Having this functionality in the database engine, instead of in your code makes a heck of a lot of difference when the time comes to scale.
Coming from a MySQL backgroud in a multi-language shop, we clearly saw the limitations, and decided to switch the entire database platform over to Postgresql a year ago.
We haven't looked back since.
Newsfollow.com
Too bad. When Internet burned tons of startup money, they hired lots of "so-called programmers" to do web-development stuff. No wonder that MySQL and PHP (and Linux!) was typically a choice. Who cares about transactions? Who cares about aspect separation? Just show the first home page to the boss!
The positive outcome: big bosses heard about Linux. Could Linux be where it is now without those so-called programmers? I doubt so. Professional Services from IBM and Microsoft would decide for you what technology to use after your boss has decided what partnership contract to sign.
But that wasn't the only way to "educate" big bosses about Linux: startup boom sparked Linux marketing boom creating OSDN, and others, including Slashdot. As a result, Linux is not self-selling itself: everyone loves Linux therefore Linux is protecting your investments. Crowd effect.
Could it be possible would Linux be really bad? No. Why it didn't happened to PostgreSQL? I think b/c PostgreSQL-based few companies didn't care about marketing. Or cared wrong. Or didn't have money to care. Compared to what? To Linux. Try to find some subject about Linux using google - besides mail-lists you've got many official documents, FAQs, HOWTOs, learning courses, support companies. Try to do it for PostgreSQL - mostly mail-lists and few official docs.
With improved better marketing PostgreSQL may become in one or two years as Linux today. Without good marketing only PostgreSQL developers, few enthusiasts and some Slashdot readers will know that not all open-source databases are so bad.
Less is more !