Thread: RE: [GENERAL] Benchmarking PGSQL against Microsoft SQL 7.0?

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

> -----Original Message-----
> Please forgive my newbieness but I am extremely new to both
> Postgresql AND
> Linux. I've been a consultant for 6 years working mostly on Microsoft
> platforms (NT, MSSQL). I have developed many data-driven
> websites using
> WinNT/IIS/ASP/MSSQL. Now, getting sick of MS and being
> enamored with the
> whole Open Source movement, I wish to learn (and develop a
> project or two)
> on Linux/Apache/PHP/PGSQL.
>
> I was wondering if anyone has done any benchmarking (even roughly),
> comparing PGSQL to Microsoft SQL Server 7.0 or even the older
> MSSQL 6.5.
> Specifically I just want the reassurance that the two
> products are in the
> same ballpark as far as performance goes. Has anyone done
> this recently?
>
> Thank you,
> Bryan
>
>

On Fri, 8 Jan 1999, Jackson, DeJuan wrote:

> 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

  There appear to be significant benchmarking hurdles with MS SQL 7.0
Hasn't everybody seen Oracle's million dollar challenge to Microsoft?  It
is somewhere on www.oracle.com  Basically, if you can publish benchmark
results that show Oracle less than X times (was it 90 times?) faster than
MS SQL, Oracle would give you a million bucks.  The trick here is that the
licence agreements for many DBMS prohibit the publishing of benchmarks!
Either that, or Oracle really is 90 times faster than MS SQL!


Tom

> 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

Thanks to all for the reference to the earlier discussion about comparing
Microsoft SQL Server to PGSQL.

re: Oracle - it's always been clear to me that Oracle far outpaces MSSQL; no
need to go into detail there. I was simply hoping for a comparison to MSSQL
because that's the tool I have been using (and my clients generally prefer).

However, re: the long message that someone quoted, with a feature-by-feature
comparison: I appreciate the laundry list, and it had a few eye-openers for
me (was surprised that PGSQL doesn't yet support RI although I know it's
just around the corner). But what it lacked was the real meat of my
question, which is a basic benchmark comparing the two. Again, I'm not
looking for 10 significant digits of precision or anything, I just want to
know whether or not the two products are in the same ballpark in terms of
raw performance. Assuming they are, then I can feel comfortable recommending
using PGSQL to my clients for future projects (I build data-driven web
sites). I am anxious to get off the Microsoft wagon and onto the Open Source
wagon, from a professional standpoint. I know that Linux can meet
tit-for-tat Windows NT on performance, as can Apache meet IIS, as can PHP
meet ASP (I think, not sure about that). The last piece is, can PGSQL meet
MSSQL, or at least be in the same ballpark.

I only briefly considered MySQL before turning to PGSQL in my new platform
research; I saw that MySQL lacked a lot of advanced SQL features (such as
transactions), and that although it was blazingly fast, it seemed kind of a
hack. Also, it's sort of commercial in a weird way that I can't quite figure
out. So here I am in the PGSQL camp checking things out.

Thanks,
Bryan

> The last piece is, can PGSQL meet
> MSSQL, or at least be in the same ballpark.

Yes. Depending on the size of your database and the loading on the web
server, I would expect that performance is acceptable now. If you can
tell us the approximate size of your intended databases then it is
likely others can tell you they are already running something that size. 

We have a major improvement in table locking for concurrent access
coming in the next release (v6.5) which we expect will offer significant
improvements in speed in a multi-client environment. afaik we don't have
any numbers yet, but I think there are a few folks out there with the
right setup to simulate a large number of clients. When Vadim sez "go",
we should try running some benchmark comparisons against v6.4.

I ran some row inserting benchmarks a year or two ago and (I recall)
found that I saw ~50InsertsPerSecond on both my Alpha/DUnix/Ingres
installation and on my i686/Linux/Postgres box. These were basically
single-client tests. There may be some more info in our mailing list
archives.

Another performance consideration is that linux takes fewer resources
from the machine than does WindowsXX, so you can donate the extra to
Postgres ;)
                  - Tom

On Sat, 9 Jan 1999, Thomas G. Lockhart wrote:

> We have a major improvement in table locking for concurrent access
> coming in the next release (v6.5) which we expect will offer significant
> improvements in speed in a multi-client environment.

I'd like to jump in here with two comments:

1) Yay for fine-grained locking!

2) Will PostgreSQL _ever_ have support for foreign keys?  That is the
single biggest gripe around here at MindSpring.  Lots of people use psql
and love it, myself included, but the lack of support for foreign keys
is so annoying that it boggles the mind.  I know that you can simulate
the behaviour using triggers, and so the question comes, why can't
someone just hack the command syntax to translate the "foreign keys"
directive into the appropriate trigger insertion?  This has been on the
TODO list for as long as I have been watching PostgreSQL, and at this
point I despair that it will ever get done.  I would be happier if the
README just said "PostgreSQL does not and will never support the SQL
'FOREIGN KEYS' directive."

This is intended to be user feedback, not whiny complaining.  I'm very
happy with what PostgreSQL gives me.  Thanks for the good work.

-- 
Todd Graham Lewis       tlewis@mindspring.net      (800) 719-4664, x2804

"It's still ludicrous that nobody's ever made a run at us by making UNIXa popular platform on PCs.  It's almost too
latenow."  -- Steve Balmer
 
"It is too late."   -- Bill Gates             _Newsweek_, 6/23/97, p. 82

> 2) Will PostgreSQL _ever_ have support for foreign keys?

Yes.

> That is the
> single biggest gripe around here at MindSpring. Lots of people use 
> psql and love it, myself included, but the lack of support for foreign 
> keys is so annoying that it boggles the mind.

Well, if it were *that* annoying someone would probably have fixed it up
by now. That's supposed to be one of the advantages of open source
software; you can "scratch your own itch" rather than having to wait for
others.

I used an Ingres database for years, which had neither foreign keys nor
outer joins. Did OK with it too. I had a choice of what to work on, and
I'm poking at outer joins (don't know if they will make the next release
yet though). Foreign keys weren't as interesting to me, so ...

> I know that you can simulate
> the behaviour using triggers, and so the question comes, why can't
> someone just hack the command syntax to translate the "foreign keys"
> directive into the appropriate trigger insertion?

That might be a good way to do it. Go fur it dude!

> I would be happier if the
> README just said "PostgreSQL does not and will never support the SQL
> 'FOREIGN KEYS' directive."

Liar :))
                    - Tom

On Sat, 9 Jan 1999, Thomas G. Lockhart wrote:

> > I know that you can simulate
> > the behaviour using triggers, and so the question comes, why can't
> > someone just hack the command syntax to translate the "foreign keys"
> > directive into the appropriate trigger insertion?
> 
> That might be a good way to do it. Go fur it dude!

I'll see whom I can pester into doing it...

> > I would be happier if the
> > README just said "PostgreSQL does not and will never support the SQL
> > 'FOREIGN KEYS' directive."
> 
> Liar :))

Ok, maybe "happier" was an overstatement...

-- 
Todd Graham Lewis       tlewis@mindspring.net      (800) 719-4664, x2804

"It's still ludicrous that nobody's ever made a run at us by making UNIXa popular platform on PCs.  It's almost too
latenow."  -- Steve Balmer
 
"It is too late."   -- Bill Gates             _Newsweek_, 6/23/97, p. 82

On Sat, 9 Jan 1999, Bryan Field-Elliot wrote:

...
> question, which is a basic benchmark comparing the two. Again, I'm not
> looking for 10 significant digits of precision or anything, I just want to
> know whether or not the two products are in the same ballpark in terms of
> raw performance. Assuming they are, then I can feel comfortable recommending
...
 I don't if it possible to get such information, as I would think it be
illegal to tell anybody (publish) the results if Postgresql and MSQL were
benchmarked together.  This is the whole point of the Oracle challege.
The only MSSQL benchmarks available are those produced by Microsoft,
because no body else is allowed to publish results.  The only thing that
can be done, is to try to build an identical test system that Microsoft
used, and run the postgresql tests on it.


Tom

> 2) Will PostgreSQL _ever_ have support for foreign keys?  That is the
> single biggest gripe around here at MindSpring.  Lots of people use psql
> and love it, myself included, but the lack of support for foreign keys
> is so annoying that it boggles the mind.  I know that you can simulate
> the behaviour using triggers, and so the question comes, why can't
> someone just hack the command syntax to translate the "foreign keys"
> directive into the appropriate trigger insertion?  This has been on the
> TODO list for as long as I have been watching PostgreSQL, and at this
> point I despair that it will ever get done.  I would be happier if the
> README just said "PostgreSQL does not and will never support the SQL
> 'FOREIGN KEYS' directive."

I was going to soapbox this item, row reuse without vacuum, and buffered
logging as things at we really need.  I am thinking about row reuse now,
and want to post something about buffered logging this week to Vadim.

That leaves foreign keys.  We really need to address this, because it
comes up so often.  Can we hash through the issues again, and see if
someone takes on the project.

--  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,
Pennsylvania19026
 

Bruce Momjian wrote:
> 
> > 2) Will PostgreSQL _ever_ have support for foreign keys?  That is the
> > single biggest gripe around here at MindSpring.  Lots of people use psql
> > and love it, myself included, but the lack of support for foreign keys
> > is so annoying that it boggles the mind.  I know that you can simulate
> > the behaviour using triggers, and so the question comes, why can't
> > someone just hack the command syntax to translate the "foreign keys"
> > directive into the appropriate trigger insertion?  This has been on the
> > TODO list for as long as I have been watching PostgreSQL, and at this
> > point I despair that it will ever get done.  I would be happier if the
> > README just said "PostgreSQL does not and will never support the SQL
> > 'FOREIGN KEYS' directive."
> 
> I was going to soapbox this item, row reuse without vacuum, and buffered
> logging as things at we really need.  I am thinking about row reuse now,
> and want to post something about buffered logging this week to Vadim.
> 
> That leaves foreign keys.  We really need to address this, because it
> comes up so often.  Can we hash through the issues again, and see if
> someone takes on the project.

If my memory servers me right:

Vadim _almost_ did implement FOREIGN KEY syntax just before 6.4 (or
6.4.1 ?)
using the trigger version in contrib.

But he stopped the work as he was unhappy with the way the contrib stuff 
worked  i.e. the keys can't break the rules even _during_ the statement.

I wholly agree with this being broken, but it still would not hurt very 
because the same is unfortunately true for current primary keys/unique 
indexes, try for example

hannu=> create table test(
hannu-> k int primary key,
hannu-> d int
hannu-> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index test_pkey
for table test
CREATE
hannu=> insert into test values(1,1);
INSERT 18285 1
hannu=> insert into test values(2,2);
INSERT 18286 1
hannu=> update test set k=k-1;
UPDATE 2
hannu=> update test set k=k+1;
ERROR:  Cannot insert a duplicate key into a unique index

IMHO the right behaviour would be to allow this kind of updates, and I 
suspect that Oracle does allow it.

At least the current behaviour is non-intuitive.

---------------Hannu Krosing

Hannu Krosing wrote:
> 
> Bruce Momjian wrote:
> >
> > That leaves foreign keys.  We really need to address this, because it
> > comes up so often.  Can we hash through the issues again, and see if
> > someone takes on the project.
> 
> If my memory servers me right:
> 
> Vadim _almost_ did implement FOREIGN KEY syntax just before 6.4 (or

I did nothing for syntax.

> 6.4.1 ?)
> using the trigger version in contrib.
> 
> But he stopped the work as he was unhappy with the way the contrib stuff
> worked  i.e. the keys can't break the rules even _during_ the statement.

I stopped just after starting -:)

> I wholly agree with this being broken, but it still would not hurt very
> because the same is unfortunately true for current primary keys/unique
> indexes, try for example

Yes, this would be nice, but I have no time/wish to do this - 
I would like to do "right" implementation someday. 
And for UNIQUE keys too.

> hannu=> update test set k=k+1;
> ERROR:  Cannot insert a duplicate key into a unique index
> 
> IMHO the right behaviour would be to allow this kind of updates, and I
> suspect that Oracle does allow it.

Sure.

Vadim