Why Postgres?

[artacus]

Postgres, the "other" open-source database. If you're like me, when you started learning PHP, the books and tutorials all covered PHP and MySQL web development. And that is what you learned and what you stuck with. And because you ran in the open-source crowd, you've heard your friends ranting about  Postgres and how great it was. But MySQL seemed to do everything you needed reasonably well, so why bother?

 

I'm a professional Oracle developer and I've worked on over a dozen DBMS's over the last 15 years. But hands down, Postgres is my favorite to work with. Now there are a lot of good databases out there. Oracle can do almost anything under the sun. MySQL always impressed me with its speed. MS SQL, well, that one I just hate. Sorry, but everything on SQL Server is harder than it needs to be and at the end of the day, I just feel "dirty".

 

So what makes Postgres so special, even beating out Oracle and MySQL? Postgres is just fun to work with. I originally started using Postgres a few years ago when 8.3 was released. We needed a database that could understand XML and our client couldn't afford Oracle (who can?).  We were  able to take our Oracle expertise and directly apply it to Postgres. In fact Oracle and Postgres are probably the closest of any two databases out there.

• It has transactions uses MVCC to manage concurrency instead of locking
  
• a powerful procedural language with a syntax similar to Oracle and DB2's
  
• the option to add other procedural languages like PHP, Python, Java and Ruby
  
• XML support
  
• spacial and temporal extensions
  
• an extensible type system
  
• fast, performs as well as mysql with the innodb engine
  
• probably the most standards compliant database out there
  

 

And to that impressive list, version 8.4 released this week adds Common Table Expressions including recursive, and analytic functions along with several hundred new features.

 

Those of you coming from MySQL will have a higher learning curve than Oracle users as MySQL diverges from the SQL standards more often than most databases (save MS SQL). So you'll have an initial investment learning what to do in places MySQL would cheat for you. But the investment will pay big dividends down the road as you learn to harness the power of the worlds most advanced open source database.

 

The postgres web site is here

and I blog about Postgres and Flex here.

[btherl]

"It has transactions uses MVCC to manage concurrency instead of locking" isn't really accurate or fair .. postgres does use locking, it just uses them better than the default mysql table type.  And InnoDB uses MVCC.

 

And from a DBA's point of view, one of the biggest weaknesses of postgresql currently - no query plan hinting.  That's possibly the biggest way in which postgres wastes our time.  Hopefully those new query monitoring tools promised in 8.4 will help

[artacus]

Hmmm, lets see, what is more important to me? Being able to have transactions and full text search in the same table, a robust programming environment, full XML support, and gis extensions... or optimizer hints?

 

I've only ever needed optimizer hints a handful of times and then only on Oracle. But maybe now that Oracle owns both the InnoDB engine and MySQL, optimizer hints will play a larger role in opensource db's.

[btherl]

If you've got complex queries and the postgres optimizer doesn't understand them, then you're pretty much on your own.  We once struggled for months trying to work out why this was happening:

 

create table tab (
   a text,
   b text,
   c text,
   data text
);
create index tab_ab_idx on tab (a, b);
create index tab_abc_idx on tab (a, b, c);
select * from tab where a = 'foo' and b = 'bar' and c = 'baz';

 

Now which is the correct index to do the lookup with?  The index on ab or abc?  Postgres invariably chose the ab index, because it thought that that would restrict the result down to one row.  In reality columns a and b were highly correlated, so this index would produce many rows, and all the optimizer's calculations from that point on would be wrong.

 

So the solution is simple right - make postgres use the abc index.  But you can't!  The only solution is to use "de facto hinting", such as rewriting the index such that the optimizer will use it, eg:

 

create index tab_abc_idx_i_hate_postgres on tab (a || b || c);
select * from tab where a || b || c = 'foobarbaz';

 

This is a hidden ugliness of the postgres approach.

 

Apart from that though I love it   I can put up with this annoyance for some of the other nice things postgres has.

[artacus]

create index tab_abc_idx_i_hate_postgres on tab (a || b || c);

 

LOL. Point taken.

 

But why not just drop index tab_ab_idx since it would be covered by tab_abc_idx? Compound indexes work left to right so it could use tab_abc_idx to filter on 'a', 'a,b' and 'a,b,c' but not 'b,c' or 'c'. (I know you know that but others reading this may not).

 

Hey! Did I mention that Postgres also has functional indexes 

[btherl]

I like partial indexes .. very useful for some situations.

 

I don't remember now what the actual situation was .. there may have been another solution like that one you suggested, but the problem of not being able to control the optimizer is still the same.

 

We had similar issues recently where our nightly aggregation of clickstream data would occasionally take forever.  It would have been great if we could have said "We want THIS query plan .. it may not always be optimal, but it will at least terminate within our lifetime".  In this situation we were able to add extra indexes and now it runs fast enough regardless of which query plan it decides to use on any particular day.