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)."
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.
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
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.
Instead of "telling" your PHB, why not try "showing" your PHB. Reimplement your db in postgres, take your PHB by the hand, and demonstrate the effectiveness of your alternative.
I've always found "doing" achieves far better results than "talking".
Mike
-- Karma whore? You betcha. --
I think that's the sort of thing that as soon as that feature is filled in, people will say it's "just" something else that's missing. There are a bunch of features I can think of that would be nice and PostgreSQL doesn't have. And probably there's someone who considers each one to be vital:
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).
Given it lacking ACID compliancy til very recently... well... you're an idiot for using it in "critical places".
Well the fact of the matter is MySQL still has no stored procedures and no triggers. Two very important things IMHO.
Zoot!
yes it lacks features, but guess what, who really gives a hoot.
I do. I really dislike having to repeatedly write code to get around the lack of features in MySQL. MySQL developers have often pointed out that there are workarounds for the missing features, however they don't talk about the impact this has on application performance, quality and development time.
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.
MySQL supporters are like people who believe that the moon landings were a hoax. No matter what contrary information they're confronted with, they still consider MySQL to be the best. It's all just an anti-MySQL conspiracy apparently...
:(
Rational and complete arguments like yours be damned.
This is your data. If it's important enough to store, it's important enough to protect. ACID is not optional.
- I don't need to go outside, my CRT tan'll do me just fine.
With all due respect, your business logic should stay in the middle tier, not embedded in the data layer. (I'm assuming you're talking about n-tier enterprise development) If your BL is all in stored procedures, then you've got your system IO bound, (standard db queries) as well as CPU bound ( business logic calculations ) which is just a painful situation to be in if you want to ever scale up.
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 !
As far as strictly data engine features go, Oracle has:
materialized views (which can increase performance)
point-in-time recovery
data partitioning
more flexible backup and restore options
the ability to use Win NT passwords and security (I know, that's not a big issue for most)
PostgreSQL does have some support from third-party software. phpPgAdmin for example, is a great tool and gets better with each release. pgAccess is a tool that comes with PostgreSQL, but I haven't used it much in the last year or so, and I remember it feeling like it was not quite ready for prime time. But it's a Windoze front-end for the PostgreSQL server and that's a big deal! There are also a couple of books about it (with more on the way, I hope), one of which, Practical PostgreSQL, published by O'Reilly is very good and available online.
The biggest thing you get for all the money you spend on Oracle is a "known" product. There are hundreds of books on Oracle (many are awful--caveat emptor) as well as classes and trainers and consultants and DBA's everywhere in the world (repeat caution above).
I love PostgreSQL, enjoy working with it, and am delighted with the new functionality the developers chose to include in version 7.3 (almost like they read my mind). Face it, for most medium-sized projects, we create a connection to the database (I often use ODBC) and start firing off queries. It doesn't matter to our program which database is behind the connection. We want speed, efficiency, and safety for our data. Anything more is window dressing (or comfort for the suits). Long live PostgreSQL!
Oops, I'm going to get down-modded for editorializing... *sigh*
Everything I've ever learned the hard way was based on a statistically invalid sample.