

Postgresql 9.2 will have boat-loads of performance enhancements - xyzzyrz
http://momjian.us/main/blogs/pgblog/2011.html#November_29_2011

======
FooBarWidget
These are fantastic improvements. But I wish there is auto-sharding support
for PostgreSQL, or at least an auto-sharding layer. For some data sets I
really sharding and building things manually just takes too much time.
Unfortunately sharding is not as easy as 'insert into server number object_id
% NUMBER_OF_SERVERS'; a proper auto-sharding application should also handle
moving data from one shard to another.

~~~
bad_user
Adding or removing shards can get complicated and it is better to handle this
at _your_ application layer.

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.

------
chrislomax
I like all the talk surrounding Postgresql recently, does anyone have any
experience with moving from MSSQL to Postgresql?

I am not asking if one is better than the other because it depends what I am
using it for but what about performance / reliability?

~~~
hkarthik
Such comparisons are often difficult since MySQL and Postgres typical perform
significantly better on Unix based systems than they do on Windows. Also using
the Windows/.NET libraries for connections tend to perform worse even if the
database server itself is hosted a Unix box.

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.

~~~
durzagott
At the company I work for we are currently in the process of migrating from
MSSQL to PostgreSQL. However, we are trickling to a full stack switch to
Linux. In the meantime, the Postgres databases have performed extremely well
and on balance we are happy with it.

Unfortunately I would argue that at an enterprise level MSSQL is better than
Postgres (pure conjecture here). If nothing else, it is way easier to employ
MSSQL experts where I live and next to impossible to find Postgres talent.
This pretty much means that we are learning by trial and error what the best
practices are.

~~~
nodata
If you need remote Postgres talent, go to the source: www.EnterpriseDB.com

~~~
wulczer
Actually, there's quite a few companies that offer professional PG support,
there's a list on the official PostgreSQL page:

<http://www.postgresql.org/support/professional_support/>

------
whalesalad
I know a lot of this will be up to the architecture and specific nature of
each app... but has anyone recently compared a modern MySQL and Postgresql
installation on a few baseline metrics? I'd love to see the differences. I am
starting to use it more and more, being a Django developer surrounded by
Postgres users. But, so far the core differences are scaring me away. psql
template1? It doesn't make as much sense to me.

~~~
ryanbraganza
What's the equivalent of template1 in mysql?

~~~
masklinn
I don't think there is one, mysql only creates empty databases it does not
have a concept of db templates.

------
bostonaholic
Wait, is a boat-load more or less than a butt-load?

~~~
DennisP
Boats are bigger than butts.

