Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
This might be interesting: facility9.com/2011/12/… –  a_horse_with_no_name Dec 7 '11 at 8:36
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
It's possible to get the impression from what Scott wrote that the Windows version doesn't perform well because it's not being used/tuned in as many high performance installations. It's actually because there are architectural issues with how PostgreSQL runs on Windows that are at fault there. –  Greg Smith Dec 20 '11 at 1:58
    
Honestly it's kind of a chicken and egg thing. If PostgreSQL attracted millions of users and developers for windows, then architecturally it would change to be faster on Windows and so on. But yeah, either way, on Windows it's a workstation or workgroup class database, while on Unix / Linux it's an enterprise class database. –  Scott Marlowe Jan 9 '12 at 17:47
add comment

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.

share|improve this answer
    
Actually MySQL's InnoDB table handler IS MVCC. Also saying you'll have to delve deep into the internals of the planner to solve a bad query plan is a bit of a misdirection. You'll likely not have to dissect the source code or anything. What you will have to do is figure out WHY the query planner is making a bad decision. A good place to start is wiki.postgresql.org/wiki/SlowQueryQuestions. –  Scott Marlowe Dec 7 '11 at 7:05
    
You can somewhat simulate hints using e.g. set seqscan off. –  a_horse_with_no_name Dec 7 '11 at 8:33
    
@ScottMarlowe Anything beyond a hint counts as "deep" when you have a bad plan at 3am for a batch job that needs to complete before business opens, or a bad plan on a transaction that's about to be executed a million times in trading hours as soon as business opens. Hints are in grown-up databases because you might rarely need them if you write good SQL, keep stats up to date etc, but when you do need them you really need them. –  Gaius Dec 7 '11 at 9:03
    
Sorry but hints aren't the answer, they're the problem. If you have to hint things, then when the data set changes, and a better query plan comes along, suddenly you've got another non-optimal query to fix yet again. 90% or more of the time, a bad query can be fixed with something as simple as raising stats target for a particular column. The other 10% of the time it's adjustments to random_page_cost and seq_page_cost to fix it. Note the subtle slam of "grown up" databases there. PostgreSQL is quite mature. Hints aren't in the offing. –  Scott Marlowe Dec 7 '11 at 15:57
    
a_horse_with_no_name: Well, that's more like hitting the planner in the brain with a big mallet, but sometimes it works. Note that you can make such a setting by user, db, connection, etc. It doesn't have to be global to work. I have once or twice, in the past, thrown a set seqscan=off or nestloop=off right ahead of a query on a reporting server to get good performance, but haven't had to do that since pg 7.4 roamed the land. –  Scott Marlowe Dec 7 '11 at 15:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.