I have used MySQL and SQL server all of my professional career.
The company I will be working for uses PostgreSQL.
Can someone who was/is in a similar situation please give me some insight on what the biggest differences are and what types of client tools I would use to connect to and manage the database?
Thanks
In terms of support, there are some very basic differences you should be prepared for that have nothing to do with the databases themselves, but how they are supported. PostgreSQL support comes from two, maybe three places. For comprehensive support by the people who write the code, look to the pgsql-general mailing list. Use the postgresql wiki (like this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions) and the docs to figure out the basics, and then post a question with as much relavent info as you can. You'll generally get an answer within a few hours to a day, depending on how obscure your problem is. The faster channel is the IRC channel (google it) which has a few folks on from around the world all the time. Not as comprehensive an answer usually, but much faster. Finally you can pay for commercial support and have someone you pay on the other end of the phone line to do anything up to and including log into your servers to fix problems. While bulletin boards are a common place for MySQL support, they aren't for PostgreSQL so don't expect to find that to be the case.
In terms of code maturity and quality PostgreSQL is pretty high up there, and you will seldom if ever see crashes or data corruption that is PostgreSQL's fault. It's generally more strict on interpretation of the SQL specification than either MSSQL or MySQL, so if you find a data type from one of those two dbs not working, it's likely not a legit SQL type but something created by those dbs (i.e. datetime or unsigned ints in MySQL). If you have a query that works in MySQL and not in PgSQL, it's likely that MySQL is more permissive than the SQL spec actually allows, and PgSQL is following the spec.
The majority of PostgreSQL users who need high performance machines run some flavor of unix, i.e. Solaris, BSD, Linux etc. The Windows port is ok, but is and always will be, a bit of a red-headed step-child in terms of performance and support. There just aren't as many users of high performance Windows / PgSQL machines out there, and likely never will be. It's stable on Windows, and a decent performer, but if you want 10k TPS you'd best look to Unix.
The GUI tools built just for pgsql are primitive compared to other dbs. Most PostgreSQL DBAs use whiteboards or commercial applications like TOAD to do data modelling. OTOH, PostgreSQL has the absolute best command line interface of any database I have ever used. Learn the two commands \h and \? and from there you're set. Complete syntax diagrams for all SQL statements are stored in the psql command line interface, and it has tab completion that is very very good. The psql user interface is very much like a unix command shell, fast and powerful, but a bit cryptic for a beginner. Learning it is well worth the effort though.
In terms of replication the latest versions are easily a match for MSSQL server and mostly a match for Oracle. Note that there isn't just ONE replication solution for postgresql. There's built in streaming replication, simple PITR as well. Then there's Londiste from the folks at skype and Slony paid for originally by the folks at Afilias, who happen to run the .info, and .org domains on pgsql with slony replication. Slony is still in many ways the most mature, but also the most complex, and the least forgiving. It's not the absolute fastest, but it is the most flexible. Expect to spend a few weeks learning it if you need to use it.
PostgreSQL releases on a 12 to 18 month release cycle, and the cycle has been getting officially shorter of late, trying to maintain a one year release. New releases are officially supported for 5 years. Each release is often much faster or more capable or both than the last version, and the development is pretty transparent, assuming you're willing to pay attention to the pgsql-hackers mailing list.
One of the beauties of PostgreSQL is the speed with which bugs get patched. I have, on two occasions, reported a bug to the pgsql-general list, and had a patch within 24 hours. Both times it took longer to test it, schedule production downtime, and apply the patch than it took to get it. A well described bug or problem gets almost immediate responses on the pgsql-general mailing list. A whinging, vague complaint about a poorly defined problem often gets deafening silence.
Two books I'd recommend are Performance PostgreSQL 9.0 by Greg Smith, and the PostgreSQL Admin Cookbook from the same publisher Pakit (or something like that). both are worth every penny to a pgsql admin / dev / dba type.
I had to make the change a few years ago, but was able to change back to MSSQL after a job change. You'll probably start out using pgAdmin as the replacement for SSMS. I'm not aware of a direct replacement for Profiler, but this question may help. Getting used to the different installation / configuration / maintenance procedures will be a challenge if you don't have experience with *nix systems.
The biggest difference is that Postgres has MVCC (like Oracle) and MySQL does not (and MS SQL has only gotten it in 2005 and it isn't yet widely used). This means readers don't block writers and writers don't block readers, which in turn means a different style of database design and tuning is needed.
Another big difference is no hints in Postgres. This means if you get a bad plan, you will probably have to delve deep into the internals of the optimizer to fix it. Not a problem in MS SQL (which has a rich set of hints, like Oracle) not in MySQL (which has such a rudimentary query optimizer that it's barely worth it).
Relative to MS SQL, Postgres replication is weaker, as are its tools for backup and recovery, I would place them at about the level of Oracle 8i (and MySQL's I'd place around the level of Oracle 6), whereas in this area, SQL Server 2008 is easily as good as Oracle 11gR1.
