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.
For "simple" stuff, MySQL is (supposedly) quicker than PostgreSQL but you might want to check the following link for just why MySQL sucks for non-toy apps
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.
PostgreSQL now supports the ALTER TABLE
HURRAY! this has been my biggest annoyance with postgresql since I've started using it. there are workarounds for older versions, but they become arduous when you have a lot of existing data.
this is a *very welcomed* implementation.
There aint no pancake so thin it doesn't have two sides.
You are linking to something that counts security advisories for Linux and compares them with security advisories for other OS'es. You seem to have misunderstood that security advisories for a certain OS are something entirely different from security advisories for a RDBMS. The 2 are completely unrelated.
p orted-platforms.html
In fact, if you want to you can run PostgreSQL on Windows if receiving less security advisories is more important than the time-to-fix. Or if you feel really paranoid, just run PostgreSQL on OpenBSD. That gives you an excellent safety record, with only 1 remotely exploitable security problem in nearly 6 year.
For a complete list of supported platforms see http://developer.postgresql.org/docs/postgres/sup
Dancing Girls
The PostgreSQL now includes a number of beautiful dancing girls
I can't tell you how long I've been waiting for this feature! Now I can get rid of Oracle for GOOD!!
Kudos to the PostgreSQL team!
Do you want to live in a world where things like the GUI, 3D graphics, wordprocessing, webserving, and other commercial products were never developed?
With the exception of wordprocessing all of the innovations you cite were developed by academic R&D teams, NOT by commercial software vendors.
This one exception, wordprocessing was NOT an innovation arising from the computer revolution - it's roots go back to IBM Selectric Magcard typewriters.
All the commercial vendors have done is copy, copy and copy.
Citations:
GUI - Stanford Research Institute Augmentation Center
Wordprocessing - IBM Typewriter Division
3D graphics - Evans and Sutherland, UofUtah and Harvard
Webserving - T. Berners Lee, CERN
The fact is that if the closed source behemouths were to dry up and blow away, it would probably enhance innovation by reducing the barriers to entry in the marketplace imposed by the likes of Microsoft.
After all, what VC is going to fund a new wordprocessor these days? VC's know all to well what Microsoft would do to any new market entrant that starts to gain traction. They have the horrible example of Netscape to look back on.
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.
Enterprise noun def: an organization so large that the people who buy systems don't know how they work and so have to hire someone who does.
PostgreSQL can handle the 50 million rows provided the data structures are well-designed, and according to their press release they can handle the replication (it's always dynamic). The query rate of 2000/sec is more a question of the server hardware, server configuration, and network infrastructure than the database software per se. I don't know about "complex 3D boolean queries" but I know for a fact that PortgreSQL can process big, ugly, inefficient queries pretty well.
What makes M$SQLServer and Oracle more "suitable" for enterprises is their add-on tools (both have loads, though of varying quality) and the fact they maintain service centers for when the DBA throws up his hands and gives up. From my perspective though, the reduced licensing costs (even for large installations, we're talking about hundreds of $'s times thousands of users!) could pay for a lot of up-front hardware and ongoing code review. If I were starting a largish e-commerce project today, I'd start on Open Source just because or the reduced up front cost. You don't become among the richest people in the world by giving anyone a good deal.
Everything I've ever learned the hard way was based on a statistically invalid sample.
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
Main feature I've been waiting for replication.
:)
As of a couple of months ago none of the replication options for postgres were any good. Most were unreliable, offered very small features or very hard to set up.
Some looked like they had promise, but were not there.
Please, please, please, add replication to the next release
I also wish performance for simple case dbs was faster. eg key value dbs compared to the performance of sleepy cats berkley db.
I'm sure there would be a *lot* of money to be had if someone were to make a good replication system. Possibly releasing it blender styles? Or offering to implement replication for businesses for a fee?
Perhaps one of the postgres groups could ask for donations from some of us users so some developers could work on it full time. I know I could easily convince my boss to cough up for it. Almost any business that relies on postgres could be convinced to chip in I think.
Now, everyone joins the VP-MKTG's bandwagon and wants their new reports compiled and summarized that way. In MySQL, without support for views, every query ends up having to be constructed again, including the tortured logic involved in having no sub-selects or unions. With view support, all we need to do is toss that awful query into a view, and select out of the view. It's not gorgeous, but it works. And you can even hand off the view to the other developers, so they don't get stuck in the quicksand of recreating the logic from scratch.
And now, because PostgreSQL supports functions that return datasets, we can toss all that logic into a function, and call that instead.
So, in answer to the question of what can PostgreSQL do that MySQL can't do: unions, subselects, views, functions. All are time savers. Lacking them, we can devise work arounds, but having them is very, very nice.
Everything I've ever learned the hard way was based on a statistically invalid sample.
Hmmm... I guess it is all a matter of perspective. For an RDBMS to handle 50,000 records, particularly on todays hardware, is actually quite trivial. I've regularly run into apps that size (web based and otherwise) using (shudder) MS Access 2000. While Access is a real dog, it can handle it and "never have problem one" size wise.
In my experience with database applications, anything under 100,000 records is considered a small database. I have to admit in the last year or two my firsthand experience with mySQL has been limited, but from what I see in the current feature set there are situations where PostgreSQL is the definite superior choice.
Sure, MySQL can handle a lot of records and is quite stable and lightweight, however the lack of such basic features as foreign key constraints and restrictions such as table-level-only locking limit its usefulness as an "Oracle killer".
Yes, MySQL can do SELECTs from million-plus record tables, do lots of inserts per second etc etc in all the benchmarks, but what happens when you have 100,000s to millions of records and dozens to hunderds (maybe thousands?) of concurrent users who all frequently write to and delete from the database in addition to doing SELECTs. mySQL falls apart because all the users have to wait in line to write to a given table because the whole damn thing gets locked out until the first user to open a write transaction hasn't finished yet.
A large database also adds challenges to maintaining referential integrity. If foreign keys were so expendable that they could be eliminated (functionality put in the application) to boost performance, why does such functionality remain in Oracle MSSQL and others? Maybe because if it is done in the database (and done right--solid and stable) you don't have to trust your data integrity to application code. You don't want to deal with orphaned records when you have 45 tables and 2.5 million records to manage, just because some flunky Java programmer didn't cover all the bases in a class buried somewhere in a big enterprise web app. Foreign keys allow for an extra layer of protection for important data--if a database constraint is violated by an application bug it'll sure get noticed faster.
These are but two examples where PostgreSQL has the edge (even if it can't top out some arcane benchmark). Given that, mySQL is still very useful--and it doesn't have to be limited to "toy apps". Slashdot can use mySQL quite well, thank you very much. It is certainly NOT a "toy app", and mySQL's performance can handle all the slashdotting thrown at it, as far as retrieving data goes. Keep in mind though, that Slashdot data is fairly static. Its database is read far more often than it is updated/inserted/deleted (even given how many comments and articles are posted and with moderation). The relationships between tables are simple enough that referential integrity is manageable through application code, and above all, the data is not THAT mission critical (if a comment or 12 disappear from Slashdot, the lights will stay on and planes will not fall out of the sky). Speed and compactness are primary for Slashdot, the rest is secondary, and mySQL can fit the bill. If I need something to handle payroll, or health records or whatever--I thing PostgreSQL has reached the point where I find it the more trustworthy of the two with data of that nature.
While PostgreSQL vs mySQL can ignite a virtual Jihad, it should come down to this--pick the right tool for the right job (not just features and performance, but also personal preferences--what tools are you most comfortable with, etc etc).
...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
on the concurrancy issue. But once again, what if the update fails halfway through? How do you know which records have been updated and which still need updating?
Going to add and drop a temporary column?
With PostgreSQL (and any other ACID database), that same SQL you wrote is atomic. It either works completely or not at all. No special keywords. No extra steps. It just works.
When it's that easy in PostgreSQL, why would you use MySQL? Note that this is a write operation; Don't assume that MySQL is faster.
- I don't need to go outside, my CRT tan'll do me just fine.
Comment removed based on user account deletion
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 !
Prior to 7.3, I used to do most of my prototyping in MySQL. Then I would convert the database over, and test it, then I would dump, add triggers, etc. and restore.
;)
;)
There are two scripts that come with PostgreSQL to take a database dump from MySQL and turn it into something you can use with PostgreSQL. So the switch is painless.
3 cautions, though
1) PostgreSQL timestams are time-zone independent, and the database manager will correct for timezone if set. So if your timestamps are off by a certain factor, that is probably why.
2) Timestamp format is different, so you may have to rewrite any time-stamp parsers.
3) Limit clauses in MySQL are non-standard.
Coming from someone who supports both
LedgerSMB: Open source Accounting/ERP