Re: Is Postgres comparable to MSSQL

Andy Hester wrote:

>Hello,
> I am a network/security guy not a dba. A friend is a developer/dba type. We
>often debate whether open source software is viable for the enterprise or our
>50 person company. He doesn't know much about Linux or opensource yet is
>very dogmatic.
>
>
Most people are....

> One argument he makes repeatedly is that there is no database that is
>enterprise worthy for a cost even comparable to MSSQL.
>
On the advantages, MS SQL has at least a commercial attempt at
multimaster replication (both async and sync). I suspect you can
architect similar solutions with PGPool and/or Slony-I but these may be
more complex to set up initially than MS SQL... Also it is mostly
self-tuning on Windows. Finally, you have OLAP capabilities that are
not included out of the box on PostgreSQL.

On the disadvantages.... PostgreSQL is extremely pedantic about data
integrity, an generally pedantic (aside from case folding) about
standards-compliance in general. It is probably inferior to PostgreSQL
in basic technical functions unless you have to run your RDBMS on
Windows (then the process-based nature of PostgreSQL may give you some
headaches when using a large number of concurrent users and small
queries, but you may want to see how it builds on SFU if this is the
case). For example, I am not really happy with the fact that what MS
SQL calls "triggers" are really our rule system by another name and that
they have no real trigger system of the sort that we, DB2, or Oracle
have. Yes, it is sufficient for 80% of your cases, but it leaves some
reasonable gaps in functionality.

> He says that
>Postegres is not even in the same class as MSSQL, DB2, Oracle.
>

If I were to rank them for enterprise capabilities:
1) Oracle and DB2
2) PostgreSQL
3) MS SQL Server

Areas that Oracle and DB2 have us beat include the ability to do
business intelligence on very large data sets with reasonable
performance (via parallel queries running on partial data sets on
different nodes (this alleviates a certain class of I/O bottlenecks when
working on BI queries against, say, 2-4 TB of data with arbitrary
constraints). I believe that Bizgres is working on this issue :-) But
in most other areas, we are comparable, though they may have more mature
replication solutions available.

I used to work at Microsoft's Product Support Services. I was not
impressed by MS SQL after hearing dealing with many support calls.

> He often
>points to the db benchmarks which of course, pg is not involved in because
>you have to pay be involved.
>
>
Well, let me point you to something else. I have a customer running an
accounting application on PostgreSQL. After a while of using it for
Point of Sale stuff, they started having some odd performance issues.
So I turned on debugging, analyzed queries, submitted an analysis to the
list and got a good answer from Tom Lane about what was going wrong and
a few suggestions on how to fix it. I am not aware of any of the
RDBMS's that you mention that has the ability to submit performance
questions to the core developers. In essence there is no enterprise
RDBMS which has better support than PostgreSQL (except perhaps
FirebirdSQL and is sorely lacking in documentation).

Now, regarding benchmarks (the TPC competitions), these are usually done
by software/hardware vendors interested in promoting their projects.
These are often multisystem clusters requiring multimaster replication,
so drawing any real parallels between your 50 person company and the
question of the TPC benchmarks in hundreds of transactions per second is
likely to be misleading at best. Indeed I am sure that the DB2 cluster
that was at the top a few years ago was probably using parallel
queries. Finally, because performance is very query-specific and very
data-set specific, one benchmark doesn't tell you very much about what
performance your company is likely to get.

To do this, what you will need to do is generate what is expected to be
representative data and load, and test it on both SQL Server (you can
download an eval version from Microsoft's web site), and PostgreSQL
(ideally on Linux). Then run your own benchmarks based on your expected
load, data, and query structures. Ask for help if you need performance
tuning advice :-)

> I thought that pg was on par with these, that is to say that in all but the
>most demanding applications, you would never see the difference between good
>implementations of each one.
>
> Please let me know if I am wrong. I would love to have some serious examples
>to show that opensource is not a "toy" and can be used effectively in the
>enterprise with all of the regular enterprise features.
>
Note that the .ORG TLD is mostly managed via PostgreSQL. .INFO too (but
.ORG is much bigger and more impressive)? Also take a look at the case
studies on http://www.postgresql.org/about/casestudies/

> I am sure everyone
>is tired of comparison questions, but comparisons would be ok to.
>
> He probably still won't admit to it, but at least we will both know. :)
>
>
Hope this helps.
Chris Travers
Metatron Technology Consulting