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



Wed, 26 Jun 2002 12:01:18 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: Democracy and organisation : let's make a

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




Wed, 26 Jun 2002 13:22:00 -0700
Josh Berkus <josh@agliodbs.com>
Re: Democracy and organisation : let's make a
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




Wed, 26 Jun 2002 18:36:58 -0400
Andrew Sullivan <andrew@libertyrms.info>
Support (was: Democracy and organisation)
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 all light.  --XTC




Thu, 27 Jun 2002 13:13:03 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Democracy and organisation : let's make a
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 all light.  --XTC




Thu, 27 Jun 2002 13:14:12 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Democracy and organisation : let's make a