can postgresql scale to the likes of sql server? is it easy to tune?

Question

hoping someone has experience with both sql server and postgresql.

Between the two db's, which one is easier to scale? Is creating a read only db that mirrors the main db easier/harder than sql server?

Seeing as sql server can get $$, I really want to look into postgresql.

Also, are the db access libraries written well for an asp.net application?

(please no comments on: do you need the scale, worry about scaling later, and don't optimize until you have scaling issues...I just want to learn from a theoretical standpoint thanks!)

Answer 1

Currently, setting up a read-only replica is probably easier with SQL Server. There's a lot of work going on to get hot standby and streaming replication part of the next release, though.

Regarding scaling, people are using PostgreSQL with massive databases. Skype uses PostgreSQL, and Yahoo has something based on PostgreSQL with several petabyte in it.

Answer 2

I've used Postgresql with C# and ASP.Net 2.0 and used the db provider from devart:

http://www.devart.com/dotconnect/postgresql/

The visual designer has a few teething difficulties but the connectivity was fine.

Answer 3

I have only used SQL Server and not much PostgreSQL, so I can only answer for SQL Server.

When scaling out SQL Server you have a couple of options. You can use peer to peer replication between databases, or you can have secondary read-only DB(s) as you mention. The last option is relatively straight-forward to set up using database mirroring or log shipping. Database mirroring also gives you the benefit of automatic switchover on primary DB failure. For an overview, look here:

• http://www.microsoft.com/sql/howtobuy/passive-server-failover-support.mspx
• http://technet.microsoft.com/en-us/library/cc917680.aspx
• http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-database-mirroring.aspx

As for licensing, you only need a license for the standby server if it is actively used for serving queries - you do not need one for a pure standby server.

If you are serious, you can set up a failover cluster with a SAN for storage, but that is not really a load balancing setup in itself.

Here are some links on the general scale up/out topic:

• http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx
• http://msdn.microsoft.com/en-us/library/aa479364.aspx

ASP.NET Libraries are obviously very well written for SQL Server, but I would believe there exists good alternatives for PostgreSQL as well.