Tuesday, February 12. 2008
PostgreSQL 8.3 is out
As many have said - PostgreSQL 8.3 was released on February 4th, 2008 and has numerous enhancements. Listing of features can be found at PostgreSQL 8.3 release notes, and has been mentioned ad-nauseum by several Postgres bloggers. Robert Treat has provided a nice round-up of blog entries that demonstrate various 8.3 enhancements in his PostgreSQL Blog's 8.3 Feature Round-Up. As a side note, the new EnterpriseDb funded Stack Builder feature for windows provides a nice complement for getting add-ons to PostgreSQL.
Horizon of PostgreSQL
Many PostgreSQL contributors are very proud of the fact that PostgreSQL is an open source project and therefore can not be bought like MySQL which is an open source product made by a commercial company. I'm not sure general PostgreSQL users really care that much about this. I suspect that many think
To many, the fact that PostgreSQL can't be bought and that its BSD licensed leaves some nagging questions?
In the end I think what most users care about are 3 things.
How high the project ranks in each of these areas is the most important and will drive users to support the project and everything else is just debateable catalyst. When you think about PHP, .NET, Java, FireFox, Apache, do you even think about whether they can or can not be bought. I know we don't much. The success of those tools I think demonstrates that the little guy, even if he knows little about IT really does matter. Microsoft realized that a long time ago, MySQL realized that, which is why they have been so successful. If it does what we need it to do, is easy to use, and doesn't cost an arm and a leg, that's how we pick our tools. Everything else is just philosophical snobbery. MySQL still has an advantage in the hosting arena since pretty much any Linux host has it preinstalled. Its just there, low hanging fruit (aside from the confusing licensing), so just use it.
In the past, I think PostgreSQL ranked pretty poorly in these areas - e.g. it didn't work natively on Windows, had to compile yourself, wasn't available on many shared hosts (still is case, but the story is getting better), and it had some pretty annoying user-friendly problems (it still does to some extent which we shall go into later, but is much better than when we started using it), and it had extremely limited funding which slowed its movement. Because it had so few users and marketing behind it - it was basically a geek-only friend and therefore few used it and cared to fund it. Today I would say PostgreSQL's pace of achievement is faster than any database (open source or commercial) and the more people relying on it for mission critical applications, the faster that accelaration will become. That key fact faster acceleration, future growth is perhaps the number one reason why we started to use it for projects where we had a choice of database over our past favorites. It may not do everything we need it to do today (heck none of the databases we have come across do everything we need or would like them to do), but it is getting there quicker than anything else.
Why is BSD License good for PostgreSQL?
I don't think the BSD license works for all projects, but for large commodity type projects such as PostgreSQL, I think it works well. The reason: When you have a large complex project with lots of contributors, and is so bread and butter to everything that anybody does, its a pain to fork and then try to reintegrate all the goodies other people have added in later versions especially if you are not in the DBMS building business. Its much easier to work right on the main path so you know when you get back the new version - its got all your enhancements and everyone else's enhancements in it and no need to cut in your changes each and every time. The BSD also means that if you choose to keep some piece of the pie for yourself (which is great for Integrators and DBMS builders) - you don't need to worry about the GPL police coming after you. For small projects in niche markets with few updates - the incentive to fork is much higher.
Other incentive to cooperate
PostgreSQL is beginning to garner quite a few high-profile company/org use - Skype, Sun, Affilias, SourceForge, Sony Games, and Federal Governements to name a few and these entities are not in the DBMS building business. This means their incentive to pool thier money and invest in missing parts of the PostgreSQL core, instead of handing over millions of licensing dollars to the likes of IBM, Microsoft, and Oracle is high. These companies provide a great marketing tool that no money can buy. The more features PostgreSQL piles on, the more of a no-brainer this decision is. This trickles down to consulting service money, training support, ISP and Integrator support, confidence in users It is alive and building a whole ecosystem of applications that work with it, funding for missing parts and the whole thing just snowballs into a huge feedback loop that grows with each new user as more people rely on this piece of software. The fact that it is BSD licensed is attractive to businesses and research institutions alike. One can see this in the numerous side projects going on that are built on top of the PostgreSQL core.
What is the deal with DBMS providers contributing?
As many have noticed, there are a couple of DBMS providers that contribute to the PostgreSQL group - e.g. GreenPlum BizGres, and EnterpriseDB are perhaps two of the most glaring contributers. They build a DBMS that is not a forked version, but one built ontop of the main core, but with added features. What is in it for them to contribute? Two thoughts come to mind for us
What does PostgreSQL 8.4 and future versions have in store?
One of the great things about PostgreSQL, like many other Open source projects/products - is that you don't need to wait 3-5 years to see changes and then have this gigantic thing that you need to engulf as you do with the likes of Microsoft, Oracle and IBM commercial databases. That is perhaps the most attractive thing about it. Nevertheless, there are still some annoying facets about it today that make it more difficult to use in certain cases than Microsoft SQL Server or MySQL for example.
Hubert Lubaczewski had a similar rant a couple of months ago in What should be changed in PostgreSQL and while we didn't agree with half the things he said and were on the Thank god PostgreSQL doesn't support that misfeature side, we do feel his pain in other areas.
Check out the PostgreSQL 8.4 wishlist for items that are on the brains of Postgres developers.
How to convert a table column to another data type
As Robert Treat pointed out in our PostgreSQL 8.3 is out and the Project Moves On, one of the features that was introduced in PostgreSQL 8.0 was the syntax of ALTER TABLE sometable ALTER COLUMN somecolumn TYPE new_data_type USING some_function_c
Weblog: Postgres OnLine Journal
Tracked: Feb 12, 17:56
Display comments as (Linear | Threaded)
"While you can now change data types of columns in tables - you are somewhat limited as to what you can change them to"
Actually I am quite certain that PostgreSQL offers more flexibility in this regard than any other database (commercial or floss) given that you are not limited to casting to change data types, you can use user defined functions as well. And when you think about the idea that you can make those functions in any language (perl, java, python, php, tcl and on and on) really the capabilities are close to limitless if you really need it.
Now, what would be a nice improvement in this area would be an optimization to allow transparent data type changes to occur solely at the catalog level, rather than requiring a table rewrite, but that's a different rant all-together. :-)
Indeed you are right. This is one of those duh moments. I guess we can take that off the list of rants and put it in our Q & A section. So I guess the
ALTER TABLE table ALTER COLUMN col TYPE new_data_type USING CAST(col as new_data_type);
was introduced in 8.0 or at least that's when the docs first mention it?
Thanks for the tip.
#1.1 Leo and Regina on 2008-02-12 16:36
I'll repeat it ad nauseam!
#2 Vincenzo Romano on 2008-02-12 13:49
Windowing Functions are very important in the ETL area. LAG / LEAD functions in Oracle are f.e. essential to calculate a valid_to out of a valid_from date.
I hope that basic windowing functions will make it into 8.4.
PS: MS SQL, Oracle, DB2 and Terradata support windowing
#3 Jan Ischebeck on 2008-02-12 17:04
I make heavy use of windowing and analytic functions in Oracle, to the point where I hardly know how I lived without them. In fact, it's the one thing keeping me from moving to PostgreSQL, and I'd love to see them supported in 8.4. BTW, MS SQL's support in woefully incomplete. Oracle's is much more flexible (don't know about DB2 or Teradata).
#3.1 Anonymouse on 2008-03-11 13:59
Quote: One of the great things about PostgreSQL, like many other Open source projects/products - is that you don't need to wait 3-5 years to see changes and then have this gigantic thing that you need to engulf as you do with the likes of Microsoft, Oracle and IBM commercial databases. That is perhaps the most attractive thing about it. :End Quote
From an enterprise perspective, that is not really attractive. Large companies usually have many databases and upgrades are time consuming and expensive. Postgres can be a painful upgrade and rather than frequent patches, it has frequent major upgrades. It's kind of like saying Fedora is better than Redhat because it is updated frequently.
True - but from experience upgrading from something like SQL Server 2000 to SQL Server 2005 was much more painful than the PostgreSQL pseudo major upgrades (in terms of cost of fixing applications broken by it, switching DTS packages to SSIS etc - though you could run in downward compatibility mode, but then in downward mode you couldn't take advantage of a lot of new features). Granted lots of work needs to be put in to make this much less painful to make PostgreSQL more attractive since ,gasp - a lot of ISPs that claim to support PostgreSQL are running only 7.4 or 8.0. What's the fun in that.
Sure the sum of each pseudo major upgrade may be more than the pain of one super upgrade. If you are lacking some key nice feature you want you don't want to wait 5 years to get it. It is not a choice one would or should always take advantage of, but I think the fact the choice is there is good. Technology is moving way too quickly and competition as well to have to always wait for 3-5 years.
#4.1 Regina on 2008-02-13 14:55
Syndicate This Blog
Show tagged entries