RE: [GENERAL] Benchmarking PGSQL against Microsoft SQL 7 - Mailing list pgsql-hackers

> There has been a discussion in the HACKERS list (check the archives on
> the website
> [http:\\www.postgresql.org\]) about PostgreSQL vs MS SQL 6.5 for the
> last 2 days or so.
> I think these ideas must come in threes, so where is that other person?
>
> I can't really give a feature comparison of MS SQL 7.0 as I've never
> used it. as for a benchmarking, although it is out of date, check
> http://www.tcx.se/benchmark.html.  There is no direct comparison of
> PostgreSQL and MS SQL but it might give you some idea of something (hey
> I can't make heads or tails of it all, but remember a smaller bar is
> better).
> Oh well, hope this helps somebody somehow,
>     -DEJ

This was hard to find, given the subject line, so here it is:


---------------------------------------------------------------------------


RE: [HACKERS] ["G. Jayson Stangel" <jayson@biztown.com>] Questions?
     _________________________________________________________________

     * From: "Jackson, DeJuan" <djackson@cpsgroup.com>
     * To: Magnus Hagander <mha@sollentuna.net>, "'Hal Snyder'"
       <hal@enteract.com>, hackers@postgreSQL.org, "'jayson@biztown.com'"
       <jayson@biztown.com>
     * Subject: RE: [HACKERS] ["G. Jayson Stangel" <jayson@biztown.com>]
       Questions?
     * Date: Thu, 7 Jan 1999 15:46:02 -0600
     _________________________________________________________________

My experience with MS SQL 6.5(my first database):
  wrote an application or managing risk in natural-gas commodities
[PowerBuilder] (~1 yr.)
  wrote several shopping cart and invoicing applications [ASP] (~6
months)

My experience with PostgreSQL v6.3 - v6.4.2(my favorite database):
  wrote several web sites for the advertising firm I now work at [PHP]
(~1 yr.)

I also have no formal database training.  Therefore everything that I've
learned was because I had to (you always learn those best).

What MS SQL 6.5 server has over PostgreSQL v6.4.2:
1. row-level-locking promotable to page and table
  (therefor faster concurrent transactions)
2. referential integrity(RI)/foreign key implementation
  (not including triggers [Postgres has those too])
3. stored procedures that can return multiple rows
  (a design flaw in earlier versions of Postgres that hasn't yet been
corrected.)
4. fully implemented dynamic precision datatypes ie NUMERIC, DECIMAL
5. a fuller implementation of the SQL92 standard
  (only slightly)
6. multiple database access from any query
  (to tell the truth I haven't missed this one at all)
7. server replication
8. automagically clustered tables
  (this could as easily be considered a drawback)
9. service-pack patches of the server
  (don't let the fact that it's larger than the software it's patching
bother you)
10. all those nifty Microsoft interface programs
  (yeah right)

What PostgreSQL v6.4.2 server has over MS SQL 6.5
1. PRICE!!!!!
  (this includes unlimited licenses)
2. stability
  (anything in Windows is subject to crashes)
3. support
  (the Postgres mailing lists outdo Microsoft support by a factor of
1000)
4. You have the code
  (if you want something added you can do it)
4. clustering on any index
5. multiple PL languages for functions (a.k.a. stored procedures)
6. type extensibility with full operators and aggregates
  (I've never even seen a MS SQL 6.5 extended type)
7. a larger and more versatile base-type set
  (have a look at interval)
8. database size is only as big as it needs to be
  (you don't need to set up data/log blocks in Postgres)
9. regular expression(regex) matching of strings
  (I hope you know what regex is)
10. functional indexes
  (you can index upper(my_col), think about it)
11. unlimited tables in a query (limited only by your ram)
  (MS SQL 6.5 is limited to 16 including those in self joins and
subselects)
12. multiple index methods
  (one method might work better for certain data distributions and
types)
13. a rule system that by itself could make up for the lack of RI
14. a developement/release cycle that is faster than Microsoft patches
and contain more feature improvements than Microsoft releases.
  (no joke)
15. cascading triggers and rules
  (try triggering another tables trigger from a trigger in MS SQL 6.5,
it won't happen)
16. 8k row limit (settable at compile time)
  (MS SQL 6.5 has a 2k row limit, just try it and see)
I could go on but I have to get to the dentist.

In PostgreSQL v6.5, scheduled to beta in February 1999, the locking
system will be better then MS SQL 6.5 (already implemented in CSV
tree[see the web site] if you want to compare speed), therefore
concurrent transactions should far outstrip MS SQL 6.5.  PostgreSQL v6.5
will also have a fuller set of the SQL92 standard (including NUMERIC AND
DECIMAL) then MS SQL 6.5.  RI should be fully implemented by then (no
promises, I'm not the one doing the work).  I'm hoping Jan would have
rewritten the function system to allow for multiple row return as well.

I replicate a PostgreSQL server using a copy.  And there are so many
cool PostgreSQL interfaces written by many people out there I'll let you
review them for yourself.

Oh well, off to the dentist now (pray for me),
        -DEJ

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026