Postgresql 9.2 will have boat-loads of performance enhancements

You can look at PL/Proxy that makes some things related to partitioning across multiple machines easier.

The big limitation is that your DB structure has to be hierarchical. I think a lower-level solution at the WAL level would be better.

For instance, you could keep track of the configuration history - when adding a shard, you create a new version. And when trying to fetch, you could try the latest version of your configuration (which says, for example, that it's on server 3) and if not found you could try the previous version (which says that it's on server 1). You can also add the possibility of removing a shard, however this requires that for every write you'll have to do a secondary write to another server, for redundancy.

In the background you could have a healer that whenever a new version gets pushed, it would go through all entries and move them accordingly. And at the end it would declare such a version to be "healed".

However, what I described above works in case you want to introduce a new server in production immediately. Like say, in the case of something like Twitter, you want to write to the new shard right away and the most important messages are the most recent ones. But you may also want to create a configuration such that a new server isn't hit by the front-end until the healing script hasn't finished migrating.

The thing is, a layer like this gets to be very optimized for your needs and there's no one size fits all.

In general, I would say you should stay on MSSQL if your server environments are Windows and only look at the OSS alternatives if you're willing (and able) to make a full stack switch to Unix.

It's a really reliable database, more reliable than MySQL. It has all the features you expect a real RDBMS to have.

It has good performance too. There's this myth going around, with people saying that it doesn't have good performance. However it is just a myth, started because the default configuration is optimized for poor servers without much memory. Make sure to optimize your configuration (i.e. specifying how much memory can PostgreSQL use for certain tasks - you can find guides for that online).

Postgres has very good reliability (for the longest time, reliability and completeness were the only two driving goals of the project). It also has acceptable-to-good performances (on unices anyway) but the default configuration is completely untuned.

This default is meant to run on very weak machines, so the db "works" everywhere OOTB, many people don't configure their postgres (and it's not been trivial historically, it's gotten a lot better) and end up with miserable performances.

This issue is most visible with "the other open-source database" (MySQL) as it has 1. a much more aggressive default configuration and 2. MyISAM default (I think it might have been switched now, but that's pretty recent), which has very fast reads (and nothing else)

An other issue with postgres is probably the lack of postgres DBAs (let alone good ones) compared to Oracle and MSSQL, though any good and open-minded DBA should be able to get up-to-speed quick on it. The last issue would be tooling, I believe MS provides lots of administration and GUI tools for MSSQL, there's a dearth of those for postgres.

In pg_hba.conf, let unix users authenticate as matching PostgreSQL users:

    local all all peer

    sudo -u postgres createuser -SRD webappuser
    sudo -u postgres createdb webappuser --owner=webappuser

    sudo -u webappuser psql

What doesn't make sense about it? It opens a postgres shell to the `template1` database, that's all...

And there should be very little reason to connect to `template1`: it is the default template for creating a database (it's copied to the new db file basically), so the only point in connecting to it is e.g. creating a few common tables or enabling extensions so that all the databases you create have them by default.

I've yet to ever need that.