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

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

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

> (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. MS SQL doesn't seem to be able to cope with
limitedRAM, 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
haslots 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.

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.

-Josh Berkus