PostgreSQL and MySQL are making significant inroads into businesses' software portfolios, say the authors of Apress' Beginning Databases with PostgreSQL. When the costs of licensing and maintenance are compared to that of
big name vendors, the savings become significant.
In this tip, Neil Matthew and Richard Stones explain where PostgreSQL is best put to use and why businesses more likely to choose a big name product. They also describe administration tools available for PostgreSQL.
When should PostgreSQL be used in business settings?
Neil Matthew: In most companies of a significant size, there will be a need for a number of different database products. For example, in some applications you may need a low-cost implementation with a small memory footprint. That sort of implementation may be installed on mobile users' laptops.
You might also need excellent performance scalability, for example, in building a data warehouse. The diversity of database applications and attitude to risk naturally leads to tactical "mix-and-match" solutions from a range of vendors.
Richard Stones: For high-end, mission-critical applications, businesses will most likely choose a big name product from a large established vendor if they can afford it. It makes for comfortable support -- they have someone with large financial resources to 'blame' -- should the product not behave as expected.
The reality is that database products work pretty much as specified. Nevertheless, the comfort factor for executives in large organizations, when dealing with a "Tier 1" vendor for important systems, is not to be underestimated.
What are some areas where PostgreSQL might be deployed?
Stones: Things are a little different when you need to deploy a large number of databases, and the failure of any individual database isn't threatening to the company's overall operations or financial results. One example would be a point-of-sale (POS) application that needs to be deployed in a large number of retail outlets.
The bottom line is that the costs of licensing and maintenance become much more significant when set against the comfort factor of big name vendor support. This is one area in which PostgreSQL and MySQL are rightly making significant inroads into businesses' software portfolios.
Matthew: A second example can found in small, tactical applications with a marginal financial justification. Anything that can be done to reduce cost, such as using a free database, may help make the IT investment case.
The launch of Oracle Database 10g Express Edition and Microsoft SQL Server 2005 Express Edition, plus Oracle's acquisition of Innobase and Sleepycat, are a clear indication that the big vendors are under pressure from open source database offerings. The hope for PostgreSQL is that after using it as a tactical, lower-end solution, companies will realize that it is a very capable, scalable and stable product, and they really can safely use it in a much wider context than their initial deployments.
How common is it for IT managers to know enough about PostgreSQL to use it? What are the training options?
Matthew: In my experience, IT managers know nothing at all about PostgreSQL. Typically, it is enthusiastic developers that introduce PostgreSQL into an organization. Perhaps a quick fix project will use PostgreSQL. Then, when it is seen as successful, it will lead to PostgreSQL being considered for more mainstream applications.
Stones: PostgreSQL is a standards-compliant database, and because the quality of available documentation is so high, we have never needed to use external training resources.
What are some tools that are available for administration of PostgreSQL?
Stones: The best known administration tool is probably pgAdmin. This is a very capable and easy-to-use GUI tool for managing PostgreSQL installations. For normal, day-to-day use, pgAdmin has it covered.
One thing it doesn't provide is a Web interface. If you need Web-based administration take a look at phpPgAdmin.
How strong is the documentation for PostgreSQL? What sort of support options exist?
Matthew: The standard documentation is very impressive, particularly for an open source product. It's well written and comprehensive. There are also a good range of books - including our own, Beginning Databases with PostgreSQL, Second Edition. (Apress)
If you really get stuck, there is always the ultimate low-level documentation available; the source code.
Stones: There are also various FAQs and the PostgreSQL mailing lists, which are archived on the PostgreSQL site and contain the answers to a huge number of questions.
If you want commercial support, then the PostgreSQL site has a list at of commercial support companies. At the time of writing, North America alone had around 30 companies offering support, and even more are listed for Europe, so there are plenty of choices.
Can you suggest a workaround for the lack of support for inheritance in PostgreSQL?
Stones: Generally, the data and usually its database implementation, long outlive any application written to use it. Consequently, the database designs tend to be conventional, normalized databases that could be implemented in any of a variety of SQL92-compliant databases including PostgreSQL.
Therefore, I don't find that the table inheritance feature is useful, and its limitations have no real impact.
Do you think PostgreSQL can compete with SQL Server or Oracle? Tell us why or why not in an email.
This was first published in October 2006