MS SQL vs PostgreSQL

[Dominik Flejter]

Hello :)

I'm working on an internet application with MS.Net client program, which is
supposed to communicate with database through PHP or ASP-based webservice.

The database is to hold up to 20-30 mln records in less then hundred
tables; most of the tables (holding up to 80% of all data) will be updated
quite rarly (e.g. once a week several record will be added or changed), the
others will be updated nightly (with big amount of new data replacing old)
when the service is to be down; then all reindexing and vacuuming will be
also performed.

Queries - concerning almost only numeric data (but some of them also with
strings research) - will be quite complex (involving up to 20 tables with a
lot of JOINs, GROUP BYs, ORDER BYs,) and will return up to 2000-3000 record
containing up 50-70 fields each. Numer of users is estimated to 200-300,
numer of queries per hour - to 1000-10000.

I'm considering two software configurations to accomplish this tasks:

1) Windows-based: with ASP and MS-SQL Server;
2) Linux-based: with PHP and PostgreSQL.

In case of Linux-based configuration I'll be able to invest a little bit
more into hardware (as I save a nice sum of money not buying MS products).

What are the pros and cons of each of the configurations? Do you know
business application examples for these configurations? What are the
deferences between the two database servers that can become important in
this case? How important are performance differences? What about
security/stability/backup creation? Any other suggestions / valuable
readings?

I'll appreciate any opionions / information; if needed, I'll try to provide
more details.

TIA, best regards

Dominik Flejter

[Todor Todorov]

Hi :)
I have used postgresql RDBMS in bussines environment for last 4 years.
I'm shure that this database will exceed all of your expectations. I
have used it
mainly with C/C++ and php programs and I am perfectly satisfied from
stability performance and posibilities.

Dominik Flejter <ga*************@bijSPAMERA.poczta.onet.pl> wrote in message news:<4j****************************@40tude.net>.. .

Hello :)

I'm working on an internet application with MS.Net client program, which is
supposed to communicate with database through PHP or ASP-based webservice.

The database is to hold up to 20-30 mln records in less then hundred
tables; most of the tables (holding up to 80% of all data) will be updated
quite rarly (e.g. once a week several record will be added or changed), the
others will be updated nightly (with big amount of new data replacing old)
when the service is to be down; then all reindexing and vacuuming will be
also performed.

Queries - concerning almost only numeric data (but some of them also with
strings research) - will be quite complex (involving up to 20 tables with a
lot of JOINs, GROUP BYs, ORDER BYs,) and will return up to 2000-3000 record
containing up 50-70 fields each. Numer of users is estimated to 200-300,
numer of queries per hour - to 1000-10000.

I'm considering two software configurations to accomplish this tasks:

1) Windows-based: with ASP and MS-SQL Server;
2) Linux-based: with PHP and PostgreSQL.

In case of Linux-based configuration I'll be able to invest a little bit
more into hardware (as I save a nice sum of money not buying MS products).

What are the pros and cons of each of the configurations? Do you know
business application examples for these configurations? What are the
deferences between the two database servers that can become important in
this case? How important are performance differences? What about
security/stability/backup creation? Any other suggestions / valuable
readings?

I'll appreciate any opionions / information; if needed, I'll try to provide
more details.

TIA, best regards

Dominik Flejter

[Some Internet Dude]

In a recent class I took at school about databases, the professor had
us use a remote instance of SQL server to do our projects. It had to
be remote because our campus had totally closed off the port which SQL
server uses by default because of some of the common attacks which
crackers commonly use against it. Then one day our remote SQL server
also came under attack and had to be shut down for several days.
During those several days, I took my scripts to create my database,
database tables, insert statements, user defined domains, etc and
modified them to work (much more SQL standards compliant I might add)
with PostgreSQL. I got everything loaded into Postgres and was able
to continue work my project while other students were probably left
waiting for the attack on the remore SQL server to end. I love
PostgreSQL. :)
w.k.

On Wed, 14 Apr 2004 14:52:57 +0100, Dominik Flejter
<ga*************@bijSPAMERA.poczta.onet.pl> wrote:

Hello :)

I'm working on an internet application with MS.Net client program, which is
supposed to communicate with database through PHP or ASP-based webservice.

The database is to hold up to 20-30 mln records in less then hundred
tables; most of the tables (holding up to 80% of all data) will be updated
quite rarly (e.g. once a week several record will be added or changed), the
others will be updated nightly (with big amount of new data replacing old)
when the service is to be down; then all reindexing and vacuuming will be
also performed.

Queries - concerning almost only numeric data (but some of them also with
strings research) - will be quite complex (involving up to 20 tables with a
lot of JOINs, GROUP BYs, ORDER BYs,) and will return up to 2000-3000 record
containing up 50-70 fields each. Numer of users is estimated to 200-300,
numer of queries per hour - to 1000-10000.

I'm considering two software configurations to accomplish this tasks:

1) Windows-based: with ASP and MS-SQL Server;
2) Linux-based: with PHP and PostgreSQL.

In case of Linux-based configuration I'll be able to invest a little bit
more into hardware (as I save a nice sum of money not buying MS products).

What are the pros and cons of each of the configurations? Do you know
business application examples for these configurations? What are the
deferences between the two database servers that can become important in
this case? How important are performance differences? What about
security/stability/backup creation? Any other suggestions / valuable
readings?

I'll appreciate any opionions / information; if needed, I'll try to provide
more details.

TIA, best regards

Dominik Flejter