Thread: Re: Democracy and organisation : let's make a
> -----Original Message----- > From: Josh Berkus [mailto:josh@agliodbs.com] > Sent: Wednesday, June 26, 2002 9:18 AM > To: Curt Sampson; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Democracy and organisation : let's make a > > > Curt, > > You do point out some good areas in which PostgreSQL needs to improve > if we're going to go after the MS SQL market. The rest of this > e-mail, though, is a refutation of your comparison. > > As a professional MS SQL Server 7.0 manager, I have to disagree. > However, I have not used MS SQL 2000 extensively, so it's possible > that some of these issues have been dealt with by MS in the version > upgrade. > > > Uh..."no way." I've found MS SQL Server is consistently faster when > > it > > comes to the crunch, due to things like writing a heck of a lot less > > to the log files, significantly less table overhead, having > clustered > > indexes, and so on. > > Up to about a million records. For some reason, when MS SQL > Server 7.0 > reaches the 1,000,000 point, it slows down to a crawl > regardless of how > much RAM and processor power you throw at it (such as a Proliant 7000 > with dual processors, 2 gigs of RAM and Raid-5 ... and still only one > person at a time can do summaries on the 3,000,000 record timecard > table. Bleah!) Totally false: http://www.microsoft.com/sql/evaluation/compare/benchmarks.asp > And clustered indexes are only really useful on tables that don't see > much write activity. False again. There is a problem if the clusted objects are added always to the end of the file, or are constantly hitting the same data page. This is often solved by creation of a hashed index that is clustered. Then, the new writes are going to different pages. > > (Probably more efficient buffer management also > > helps a bit.) > > Also not in my experience. I've had quite a few occasions > where MS SQL > keeps chewing up RAM until it runs out of available RAM ... and then > keeps going, locking up the NT server and forcing an emergency reboot. That's a configuration error. > MS SQL doesn't seem to be able to cope with limited RAM, even when > that limit is 1gb. > > > Other areas where postgres can't compare is backup and > > restore, > > Hmmm .... MS SQL has nice GUI tools including tape management, and > supports incremental backup and Point-in-time recovery. On the other > hand, MS SQL backup takes approximately 3x as long for a similar sized > database as PostgreSQL, the backup files are binary and can't > be viewed > or edited, sometimes the restore just fails for no good reason > corrupting your database and shutting down the system, restore to a > database with different security setup is sheer hell, and the database > files can't be moved on the disk without destroying them. > > I'd say we're at a draw with MS SQL as far as backup/restore goes. > Ours is more reliable, portable, and faster. Theirs has > lots of nice > admin tools and features. > > >ability to do transaction log shipping, > > Well, we don't have a transaction log in the SQL Server sense, so this > isn't relevant. > > >replication, > > This is a missing piece for Postgres that's been much > discussed on this > list. > > > access > > rights, > > We have these, especially with 7.3's new DB permissions. > > disk allocation (i.e., being able to determine on which disk > > you're going to put a given table), > > This is possible with Postgres, just rather manual. And, unlike MS > SQL, we can move the table without corrupting the database. Once > again, all we need is a good admin interface. > > > and so on. SQL Server's optimizer > > also seems to me to be better, though I could be wrong there. > > Having ported applications: You are wrong. There are a few things > SQL server does faster (straight selects with lots (>40) of JOINs is > the only one I've proven) but on anything complex, it bogs down. > Particularly things like nested subselects. Can you provide an example of a complex query containing subselects where PostgreSQL will outperform SQL Server 2000? I would like to see it. > Now, let me mention a few of MS SQL's defects that you've missed: > Poor/nonexistant network security (the port 1433 hole, hey?), huge > resource consumption, a byzantine authentication structure that > frequently requires hours of troubleshooting by an NT security expert, > weak implementation of the SQL standard with lots of proprietary > extensions, 8k data pages, no configuration of memory usage, and those > stupid, stupid READ locks that make many complex updates deadlock. It's still a great product with better features than PostgreSQL. However, PostgreSQL is definitely catching up and could easily pass MS SQL Server. DB/2 is another story. I have worked as an MS SQL Server DBA (also database designer and programmer along with just about anything else that could be done with it) and am aware of the difficulties associated with SQL Server. It's a very good product. Customer support is also a big issue comparing free database systems with commercial ones. I know that there are a couple groups that do this, but that genre of businesses do not have a good track record of staying in business. MS, Oracle, and IBM will be there five years down the road to help. One area where there is a monumental difference is in license fees. For a single corporation, it does not matter. But for someone who writes database applications that will be delivered to thousands of customers, it is an enormous advantage.
Dann, > Totally false: > http://www.microsoft.com/sql/evaluation/compare/benchmarks.asp The microsoft benchmarks aren't worth the screen space they take up. I don't consider these "evidence". I'm basing this on real experience of working with real production databases, not some idealized benchmark database directly admined by the SQL Server developers in Redmond. > False again. There is a problem if the clusted objects are added always > to the end of the file, or are constantly hitting the same data page. > This is often solved by creation of a hashed index that is clustered. > Then, the new writes are going to different pages. Depends on your level of write activity, and the size of the records. Clustered indexes work nicely for some tables. Not for others. > That's a configuration error. Yes? And you're going to tell me how to fix it? I've tinkered with the memory alloction in the SQL server config; the best I seem to be able to do is make SQL server crash instead of NT. > It's still a great product with better features than PostgreSQL. Once again, I disagree. It has *different* features, and if your focus is GUI tools or Win32 tool integration, you might say it's "better" than Postgres. But you'd have to admit that Postgres has some features and options that MS SQL can't match, such as SQL standard compliance, TOAST, hackable system tables, etc. Also, as I said, I've not worked much with SQL Server 2000. MS may have improved the product's reliability since 7.0. > I have worked as an MS SQL Server DBA (also database designer and > programmer along with just about anything else that could be done with > it) and am aware of the difficulties associated with SQL Server. It's a > very good product. Until it crashes. Unrecoverably. Don't scoff. I've had it happen, and the only thing that saved me was triplicate backup of the database. > Customer support is also a big issue comparing free database systems > with commercial ones. I know that there are a couple groups that do > this, but that genre of businesses do not have a good track record of > staying in business. MS, Oracle, and IBM will be there five years down > the road to help. If you can afford the fees. Personally, I've received more help from the PGSQL-SQL list than I ever got out of my $3000/year MSDN subscription. Also, PostgreSQL Inc. offers some great support. I've used it. > One area where there is a monumental difference is in license fees. For > a single corporation, it does not matter. But for someone who writes > database applications that will be delivered to thousands of customers, > it is an enormous advantage. Yup. -- -Josh Berkus
Followup set to -advocacy On Wed, Jun 26, 2002 at 12:01:18PM -0700, Dann Corbit wrote: > Customer support is also a big issue comparing free database systems > with commercial ones. I know that there are a couple groups that do > this, but that genre of businesses do not have a good track record of > staying in business. MS, Oracle, and IBM will be there five years down > the road to help. I normally wouldn't get involved in this one, since it's the sort of thing that turns into a flamefest. And anyway, I'm not sure -hackers is the place for it (hence the followup). But as a lowly user, I cannot let such a comment go unanswered. I've used several commercial products of different kinds. I've supported various kinds of databases. I've worked (and, in fact, currently work) in shops with all kinds of different support agreements, including the magic-high-availability, we'll have it in 4 hours ones. I've had contracts for support that were up for renewal, and ones that had been freshly signed with a six-month trial. But I have never, _never_ had the sort of support that I get from the PostgreSQL community and developers. And it has been this way ever since I started playing with PostgreSQL some time ago, when I didn't even know how SQL worked. I like to have commercial support, and to be able to call on it -- we use the services of PostgreSQL, Inc. But you cannot beat the PostgreSQL lists, nor the support directly from the developers and other users. Everyone is unvarnished in their assessments of flaws and their plans for what is actually going to get programmed in. And they tell you when you're doing things wrong, and what they are. You cannot, from _any_ commercial enterprise, no matter how much you are willing to pay, buy that kind of service. People find major, showstopper bugs in the offerings of the companies you mention, and are brushed off until some time later, when the company is good and ready. (I had one rep of a company I won't mention actually tell me, "Oh, so you found that bug, eh?" The way I found it was by discovering a hole in my network so big that Hannibal and his elephants could have walked through. But the company in question did not think it necessary to mention this little bug until people found it. And our NDA prevented us from mentioning it.) Additionally, I would counsel anyone who thinks they are protected by a large company to consider the fate of the poor Informix users these days. Informix was once a power-house. It was a Safe Choice. But if I were an Informix user today, I'd be spending much of my days trying to learn DB2, or whatever. Because I would know that, sooner or later, IBM is going to pull out the dreaded "EOL" stamp. And I'd have to change my platform. The "company supported" argument might make some people in suits comfortable, but I don't believe that they have any justification for that comfort. I'd rather talk to the guy who wrote the code. A -- ---- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M6K 3E3 +1 416 646 3304 x110
On Wed, 26 Jun 2002, Dann Corbit wrote: > I have worked as an MS SQL Server DBA (also database designer and > programmer along with just about anything else that could be done with > it) and am aware of the difficulties associated with SQL Server. It's a > very good product. Yeah, I agree. Maybe it's good because it was originally built by Sybase, not MS. :-) > Customer support is also a big issue comparing free database systems > with commercial ones. Ha ha ha ha ha! I've dealt with MS customer support. If you like spending your first day or two trying to escalate your trouble ticket past complete losers who don't know half what you do about SQL Server, their support is OK. In the end, there's really no substitute for an extremely competent DBA. And having source code and direct access to the developers is a godsend. > One area where there is a monumental difference is in license fees. For > a single corporation, it does not matter. Even for a single corporation, it can matter. Deploying, say, ten smallish Oracle servers is not exactly cheap. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Wed, 26 Jun 2002, Josh Berkus wrote: > Depends on your level of write activity, and the size of the records. > Clustered indexes work nicely for some tables. Not for others. Well, I'm sure everyone would agree with that. The point is that SQL Server gives you the option, posgres doesn't. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC