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

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

Thanks for the info!!! I hope the dentist went well!!!

It went well, thank you for asking. I got a new dental assistant, and
she was gentle and sweet (a far improvement). She said that I have good
teeth, and that I must have been taking good care of them because there
wasn't much for her to do.

I have only one question more question(I need to sell the
Microsoft guy in the office on this.) I go to Microsoft and
they say 20,433 transactions per minute on a quad xenon 450
bla, bla, bla. Is there a place where this statistic can be
found for Postgre.

I don't know of any such statistics, but maybe some of the other guys
would.
-DEJ

I think one of the questions that, and perhaps you have, needs to be asked
is:
1) what kind of transaction test produced those numbers?
2) what kind of user load, sim. transactions did the server have?
3) were the transactions non-conflicting?
4) were the transactions read only, read-update, insert?
5) and a most important question is over what time period was the test
taken. This is where various logging, and checkpointing issues
start
to surface. Additionally, some folks do not run the test for any
extended
period of time because of memory leakage issues, and other stability
issues.

Its very easy to design a test that produces high numbers. Its the
definition of what they mean that causes the problem. Ask the MS
guy to give you the structure of the test to get those numbers. If MS
doesn't release it then they are useless.

As for the TPC tests, they should have the code available. A, and B
are next to useless.

Jordan

----- Original Message -----
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: G. Jayson Stangel <jayson@biztown.com>
Cc: PGSQL Hackers <pgsql-hackers@hub.org>
Sent: Friday, January 08, 1999 1:20 PM
Subject: RE: [HACKERS] ["G. Jayson Stangel" <jayson@biztown.com>] Question
s?