PostgreSQL vs. Microsoft SQL server

On Sat, May 1, 2010 at 12:47 PM, Thomas Løcke <thomas.granvej6@gmail.com> wrote:

Anybody know of any recent comparisons made between the two?

I'm in the process of buying a new telephony related software suite,
and I'm getting mixed advice. Some say that MSSQL is _much_
better/faster than PostgreSQL, and others say the opposite.

The vendor is more or less indifferent, with a small plus to the
Microsoft solution because, well, they are a Microsoft shop. The
sales-people all bang on about MSSQL being the superior choice, and
PostgreSQL being a "toy compared to the Microsoft RDBMS". The tech
people though are divided into three groups: One group says the two
systems are more or less equal, another group who says the Microsoft
database is superior and finally a group who speaks highly of
PostgreSQL.

I've not been able to convince them to send me some actual benchmark
numbers, which actually should turn on quite a few alarms, come to
think about it.  :o)

Maybe you guys are aware of some recent generic tests/comparisons
between the two systems?

It's probably much more important what you're running each on than the
db itself for most of these situations.

Anyone who thinks pgsql is a toy is an idiot. or a sales person.
Wait, that might be redundant here.

Anyone who judges one poorly while using only the other is a
cheerleader. I like cheerleaders, when they're in a uniform dancing
around on the gym floor. I don't tend to look to them for advice on
which dbms to use.

I've only used MSSQL enough to know I don't really care for it that
much, and I've converted a dozen or so MSSQL users to PostgreSQL just
by letting them connect to my db instead of theirs (think corporate
intranet). But that could have just been because my server was better
run and configured than theirs.

Anyway, both are reasonably good servers. Don't listen to a sales
person for goodness sake, they're in it to sell you stuff, and a
bigger price tag is better for them. Test it for yourself.

On Sat, 1 May 2010, Thomas Lï¿œcke wrote:

Anybody know of any recent comparisons made between the two?

A Google search will turn up a lot of comparisons.

I'm in the process of buying a new telephony related software suite, and
I'm getting mixed advice. Some say that MSSQL is _much_ better/faster than
PostgreSQL, and others say the opposite.

This opens a world of potential flames. The first thing you should ask is
on what basis the comparisons are being made. Initial price? Licensing fees?
Support? While MS-SQL requires a Microsoft OS underneath it, postgres can be
installed on that OS or on linux or the *BSDs. No charge for the underlying
OS, either.

What is the basis for "fast?" How does the dbms relate to the telephone
software? Do you need real-time transaction processing or as a lookup for
phone numbers?

I've been using postgres for more than a dozen years, and know that there
are a wide range of applications where it's the back end. Some applications
require fast read/write capability (which it has), others capability to
store hundreds of millions of rows per table, and it handles these, too.

Ask more specific questions of your proposed vendor and have them back up
the answers with meaningful support, not marketing fluff from Redmond.

Rich

On Sat, May 1, 2010 at 2:47 PM, Thomas Løcke <thomas.granvej6@gmail.com> wrote:

Anybody know of any recent comparisons made between the two?

I'm in the process of buying a new telephony related software suite,
and I'm getting mixed advice. Some say that MSSQL is _much_
better/faster than PostgreSQL, and others say the opposite.

The vendor is more or less indifferent, with a small plus to the
Microsoft solution because, well, they are a Microsoft shop. The
sales-people all bang on about MSSQL being the superior choice, and
PostgreSQL being a "toy compared to the Microsoft RDBMS". The tech
people though are divided into three groups: One group says the two
systems are more or less equal, another group who says the Microsoft
database is superior and finally a group who speaks highly of
PostgreSQL.

I've not been able to convince them to send me some actual benchmark
numbers, which actually should turn on quite a few alarms, come to
think about it.  :o)

Maybe you guys are aware of some recent generic tests/comparisons
between the two systems?

most comparisons you see are going to be feature checklists of no real
value. what really matters is how you are going to access the
database and what you are going to do with it. if you are going to
primarily use microsoft tools (c#/visual studio/reporting services)
you are probably going to be better off with sql server. these
technologies integrate better with sql server.

if you are writing your client applications in jdbc/php/etc, your
database choice is a wash in terms of connectivity. postgresql with
its bsd license and strong open source pedigree has a lot of
advantages, so lean postgres but keep ms in mind if you have all
microsoft servers and/or ms administrators.

if you are writing stuff in C/C++, doing significant coding INSIDE the
database (pl/pgsql, C, etc) and/or think of the database as a self
contained development environment, then postgresql is definitely the
right choice. our C interface (both libpq and internal) is excellent,
variety of server side programming options is second to none, and
postgres's flexible type system is fantastic and capable of doing all
kinds of things that bend the rules of what a database is 'supposed to
do'.

also, spend some time reviewing the postgresql mailing lists, and note
the quality and quantity of responses you get to difficult questions.
we are helpful bunch :-)

merlin

Thomas Løcke wrote:

Anybody know of any recent comparisons made between the two?

for purely SQL, I prefer Postgres by a wide margin. But, MS SQL
Server comes with a whole infrastructure that includes a lot of powerful
tools, like replication, data extraction and translation, active
directory integrated single sign-on, and so forth. If you're working
in a pure MS world, with .NOT -er- .NET, etc etc, its pretty hard to
get away from MS SQL as there are so many tools

the bottom line tends to be driven by whatever the applications
support. I have one MS SQL database in my lab hosting a program which
only supports MS SQL. Luckily, the demands on this database are low
enough that i can use the free SQL Express.

Merlin Moncure <mmoncure@gmail.com> writes:

On Sat, May 1, 2010 at 2:47 PM, Thomas Løcke <thomas.granvej6@gmail.com> wrote:

Anybody know of any recent comparisons made between the two?

I'm in the process of buying a new telephony related software suite,

if you are writing stuff in C/C++, doing significant coding INSIDE the
database (pl/pgsql, C, etc) and/or think of the database as a self
contained development environment, then postgresql is definitely the
right choice. our C interface (both libpq and internal) is excellent,
variety of server side programming options is second to none, and
postgres's flexible type system is fantastic and capable of doing all
kinds of things that bend the rules of what a database is 'supposed to
do'.

An specific example here would be the prefix_range data type that handle
indexing telephony routing queries. See it here:

http://prefix.projects.postgresql.org/

Keep in mind you'll find other side projects along with PostgreSQL to
help you answer to your business needs. It's not just about which is
faster on some random benchmark but which helps you best doing your
work.

I know nothing about MSSQL, but another axe of analysis that I tend to
follow is the error management. PostgreSQL is very good on this point,
you really can trust your data to it.

Regards,
--
dim

On May 1, 2010, at 12:47 PM, Thomas Løcke wrote:

The
sales-people all bang on about MSSQL being the superior choice, and
PostgreSQL being a "toy compared to the Microsoft RDBMS".

This is complete bullshit.

I say that as someone who spent years using MS SQL Server, and who very much enjoyed using it, and who still likes it. (Though I haven't used it much lately.)

The high-level overview is that from a technical standpoint they are both excellent products, and I consider them very much comparable for the databases I've done. They have some different specific strengths & weaknesses for sure. PG's locking scheme, MVCC, basically precludes certain specific optimizations that means a small number of very specific queries don't perform as well, while at the same time it means that throughput with multiple simultaneous connections scales extremely well with multiple processors.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Scott Ribe wrote:

PG's locking scheme, MVCC, basically precludes certain specific optimizations that means a small number of very specific queries don't perform as well, while at the same time it means that throughput with multiple simultaneous connections scales extremely well with multiple processors.

SQL Server uses MVCC too as of their 2005 release, implemented with row
versioning similarly to Postgres. The main non-MVCC holdout at this
point is DB2.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us

On May 1, 2010, at 5:16 PM, Greg Smith wrote:

SQL Server uses MVCC too as of their 2005 release, implemented with row versioning similarly to Postgres. The main non-MVCC holdout at this point is DB2.

Funny, I've ported to even later versions than that, but missed the change. Well, OK, I'm sure I read it in the release notes at the time, but it made no important different to the app I was working on at the time.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Sat, 2010-05-01 at 20:47 +0200, Thomas Løcke wrote:

-->Anybody know of any recent comparisons made between the two?

I'm in the process of buying a new telephony related software suite,
and I'm getting mixed advice. Some say that MSSQL is _much_
better/faster than PostgreSQL, and others say the opposite.
-->
-->No shocker there. Their salesmen out for a sale, having no under standing how to compare DB's .
-->

sales-people all bang on about MSSQL being the superior choice, and
PostgreSQL being a "toy compared to the Microsoft RDBMS".
-->I've been using MSSQL from 7.0 to MSSQL 2008. Been using Postgresql as of 8.2 and think very highly of it.

The administrative overhead of PG IMHO is far less, hardware requirements are lower, easier to develop in and against.

MSSQL has lots of MS tools you can use which all cost lots of money. Hardware requirements far steeper, primary because the OS hardware requirements are higher.

My first choice is PG
-->
-->

The tech
people though are divided into three groups: One group says the two
systems are more or less equal, another group who says the Microsoft
database is superior and finally a group who speaks highly of
PostgreSQL.
-->
-->To say PG is superior to MSSQL and vice versus is a loaded argument. What is the bases of the comparison.
-->
-->Comparing the two strictly as DB to DB they are equal. Both support large section of SQL standard, they both have excellent track records not corrupting data, both do WAL, both scale up to thousands of transactions per second. both are ACID
-->
-->But the equality between the 2 stops there and each product has pluses and Minus.
-->
-->wiki has some comparison info
-->http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
-->http://en.wikipedia.org/wiki/Comparison_of_database_tools
-->
-->

I've not been able to convince them to send me some actual benchmark numbers, which actually should turn on quite a few alarms, come to think about it. :o) -->
-->Benchmarking is an important piece but should not be a deciding factor.
-->
-->Benchmarks on DB's are miss leading, because the way each may execute a given set of queries can and will result in drastically different numbers.
-->
-->Tweaking a DB and the queries is a time consuming process and normally results in rewriting the queries, add indexes, changing configurations or even changing table layouts.
-->
-->My experience shows both are very fast if properly configured and the developers understands how a specific DB works to properly write queries. Seen more than once a DB taken to its knees because poorly written SQL statements or design . Performance and General mailing list are packed with such examples.
-->

Maybe you guys are aware of some recent generic tests/comparisons between the two systems? -->
-->General tests and comparisons are worthless.

This list is packed with examples as are other DB mailing list where users pick apart benchmarks because the Tester missed some setting, or some other arcane trick that is Database specific.

-->
-->

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

On 02/05/10 02:47, Thomas L�cke wrote:

I've not been able to convince them to send me some actual benchmark
numbers, which actually should turn on quite a few alarms, come to
think about it. :o)

Is performance really your #1 criterion anyway?

I'd be looking at management, reliability, backup, integration into the
rest of the infrastructure, product longevity, support, etc. Performance
you can always throw hardware at.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

On Sat, May 1, 2010 at 8:12 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

I'd be looking at management, reliability, backup, integration into the
rest of the infrastructure, product longevity, support, etc. Performance
you can always throw hardware at.

And given the relatively high costs of a MSSQL installation, you can
throw a LOT of hardware at a PostgreSQL server.

Greg Smith wrote on 02.05.2010 01:16:

Scott Ribe wrote:

PG's locking scheme, MVCC, basically precludes certain specific
optimizations that means a small number of very specific queries don't
perform as well, while at the same time it means that throughput with
multiple simultaneous connections scales extremely well with multiple
processors.

SQL Server uses MVCC too as of their 2005 release, implemented with row
versioning similarly to Postgres. The main non-MVCC holdout at this
point is DB2.

AFAIK even in a fresh install of SQL Server 2008 the row versioning is turned off by default (at least this is true for 2005)
I don't know if this is for compatibility reason or because of the performance penalty that comes with it

And DB2 9.7 introduced MVCC as part of their Oracle compatibility.

Thomas

Skype, perhaps the largest telephony app in the world, uses Postgresql.

Here's some info on their postgresql usage:

http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper

--- On Sat, 5/1/10, Thomas Løcke <thomas.granvej6@gmail.com> wrote:

On Sun, May 2, 2010 at 7:57 PM, Andy <angelflow@yahoo.com> wrote:

Skype, perhaps the largest telephony app in the world, uses Postgresql.

Here's some info on their postgresql usage:

http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper

Thank you very much for those two links. Very interesting reading indeed.

And also thanks to all the other replies in this thread. You've all
given me something to think about.

:o)
Thomas

On Mon, May 3, 2010 at 12:04 AM, Thomas Løcke <thomas.granvej6@gmail.com> wrote:

On Sun, May 2, 2010 at 7:57 PM, Andy <angelflow@yahoo.com> wrote:

Skype, perhaps the largest telephony app in the world, uses Postgresql.

Here's some info on their postgresql usage:

http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
https://developer.skype.com/SkypeGarage/DbProjects/SkypePostgresqlWhitepaper

Thank you very much for those two links. Very interesting reading indeed.

And also thanks to all the other replies in this thread. You've all
given me something to think about.

Also keep in mind that people don't have to report if they're using
PostgreSQL, so there are a LOT of companies that use it and you never
will know because it's hidden away.