Re: Democracy and organisation : let's make a

> -----Original Message-----
> From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
> Sent: Wednesday, June 26, 2002 9:18 AM
> To: Curt Sampson; pgsql-hackers(at)postgresql(dot)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.