Re: Democracy and organisation : let's make a - Mailing list pgsql-hackers

On Wed, 26 Jun 2002, Josh Berkus wrote:

> As a professional MS SQL Server 7.0 manager....

Well, I wouldn't call myself a professional at managing SQL Server, but
I did do about two years of work on an application (database design,
programming and day-to-day running of the production system) that ran on
SQL Server 7.0 and gave it a pretty good workout. I've used 2000 a bit,
but most of my comments apply to my experience with 7.0.

> > 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!)

Really? I've dealt with 85 millon row tables in SQL Server without
difficulty, and the machine was not that much larger that then one you
describe. (2-way 800 MHz Xeon, 4 GB RAM, Clarion 12-disk RAID array.)

> And clustered indexes are only really useful on tables that don't see
> much write activity.

I've not found that to be true. If the write activity is *really*
heavy you've got a problem, but if it's moderate, but not really low,
clustered indexes can be really helpful.

To give you an idea of what clustering can do for a query in some
circumstances, clustering a 500 million row table under postgres on
the appropriate column reduced one of my queries from 70 seconds to
0.6 seconds. The problem with postgres is having to re-cluster it on a
regular basis....

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

While you're right that there have been problems with restores on SQL
server from time to time, I've done a *lot* of large (120 GB database)
backups and restores (copying a production system to a development
server), and for large tables, I've found SQL Server's binary backups to
be faster to restore than postgres' "re-create the database from COPY
statements" system.

> >ability to do transaction log shipping,
>
> Well, we don't have a transaction log in the SQL Server sense, so this
> isn't relevant.

It is completely relevant, because log shipping allows fast, easy and
reliable replication. Not to mention another good method of backup.

> > access rights,
>
> We have these, especially with 7.3's new DB permissions.

7.2 has extremely poor access permissions. 7.3 is not out yet.

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

No. Run CLUSTER on the table, or drop an index and re-create it, or just
expand the table so that it moves into yet another 1 GB file, and watch
the table, or part of it, move to a different disk. (The last situation
can be handled by pre-creating symlinks, but ugh!)

> And, unlike MS SQL, we can move the table without corrupting the
database.

You can do that in MS SQL as well, just not by moving files around     .
Letting the database deal with this is a Good Thing, IMHO              .

> Once again, all we need is a good admin interface.

Now, this I don't understand so well. PGAdminII seems pretty much as
good as Enterprise Manager to me, though I admit that I've looked at it
only briefly.

> Now, let me mention a few of MS SQL's defects that you've missed:
> Poor/nonexistant network security (the port 1433 hole, hey?)

Hm? You'll have to explain this one to me.

> huge resource consumption

I've just not found that to be so. Specifics?

> a byzantine authentication structure that frequently requires hours of
> troubleshooting by an NT security expert,

Easy solution: don't use NT security. Ever. It's a nightmare.

> 8k data pages

You mean like postgresql? Though the row size limit can be a bit
annoying, I'll agree. But because of SQL Server's extent management, the
page size is not a big problem.

And look at some of the advantages, too. Much less row overhead, for
example.

> no configuration of memory usage,

I've always been able to tell it how much memory to use. There's not
much you can do beyond that, but what did you want to do beyond that?
It's not like you're stuck with postgres's double-buffering (postgres
and OS) system, or limits on connections based on how much of a certain
special type of memory you allocate, or things like that. (I love the
way that SQL server can deal with five thousand connections without even
blinking.)

> and those stupid, stupid READ locks that make many complex updates
> deadlock.

I'll admit that this is one area that I usually like much better about
postgres. Although the locking system, though harder to use, has
its advantages. For example, with postgresql the application *must*
be prepared to retry a transaction if it fails during a serialized
transaction. Application writers don't need to do this in SQL server.
(It just deadlocks instead, and then it's the DBA's problem. :-))

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