PostgreSQL versus Other RDBMSs

I l @ ve RuBoard

One of the first questions asked by new users to RDBMSs is, "Which one is best?" That question is nearly impossible to answer without a full understanding of the database's required functionality.

Comparing databases is like comparing vehicles. Each type of vehicle is suited for a particular task; motorcycles would be preferable over pickup trucks in some situations and would be disastrous in others. Likewise, the required functionality of a RDBMS must be understood before the right fit can be established.

In lieu of trying to compare apples to oranges, the following sections will give a brief listing of the popular RDBMSs currently available and will list their strong and weak points as well as their typical uses.

PostgreSQL

Pros:

  • Large support and development community.

  • Many commercial support options (Great Bridge, Red Hat, and others).

  • Fully open sourced.

  • No royalty or license fees.

  • Has a very robust feature set.

  • Supports a large number of internal procedural languages that can create stored procedures, triggers, rules, and functions.

  • Has a wide array of API access solutions, including ODBC, JDBC, C, Perl, PHP, and Python.

  • Fully transactional.

  • Scales very well with additional users.

  • Supports database versioning (MVCC) as a concurrency control mechanism.

  • Fully ACID-compliant database (Atomicity, Concurrency, I, and D).

  • Many web tools exist for interfacing with PostgreSQL.

  • Supports foreign keys.

  • Online backup and restores possible.

Cons:

  • No replication in current version (7.1).

  • Not multithreaded ( problematic in NT environments).

  • Only a handful of GUI administration tools currently available.

Typical uses:

  • When you examine the features, development tools, reliability, and performance of PostgreSQL, it's hard to find fault with it. As a midrange database, PostgreSQL is impossible to beat in many situations. Although it is still an open-source project, a number of commercial entities now offer professional support.

  • The only possible concern that exists when evaluating PostgreSQL is the specific environment where it will operate . Although an NT version of the database is available, it tends to run better in a UNIX-style environment.

MySQL

Pros:

  • Large support and development community.

  • Very prevalent as a web back end.

  • Open sourced and therefore can be customized.

  • No royalty or license fees.

  • Performs basic SELECT s very quickly.

  • Small footprint needed to run.

  • Runs on many different operating systems.

  • Interfaces with many popular web-development tools.

Cons:

  • Doesn't perform complex joins or subselects.

  • Doesn't natively support transactions.

  • No atomicity with regard to locking.

  • No support for stored procedures.

  • No triggers.

  • No foreign keys.

Typical uses:

  • MySQL makes an excellent choice for serving dynamic web pages, particularly if there is no need for transactions or complex queries. Additionally, MySQL is very straightforward to install, configure, and administrate.

  • Because MySQL is so lean, it can outperform many other RDBMSs in terms of raw SELECT , INSERT , or UPDATE speed. However, because MySQL lacks real transactional support and row-level locking, this speed will degenerate if multiple INSERT s or UPDATE s are performed simultaneously .

  • Generally, MySQL is ideally suited to serve as an engine to small to mid- size databases, particularly if a high number of INSERT and UPDATE queries will not be concurrently utilized.

Microsoft SQL Server

Pros:

  • GUI interface makes installation and administration easy.

  • Lots of training and support options available.

  • Has good support for transactions and atomicity.

  • Integrates extremely well with other Microsoft Office applications.

  • Has support for replication and clustering.

Cons:

  • Only able to run on Microsoft operating systems.

  • Proprietary software ”cannot be customized.

  • Cost for large installations can be a factor.

Typical uses:

  • Overall, MS-SQL can be an effective database engine for small to mid-size solutions. It is particularly useful for sites constrained to use only Microsoft operating systems and applications. If a high degree of customization or heterogeneous support is needed, other options will be more effective.

Interbase

Pros:

  • Newly open sourced.

  • Supports many features: transactions, triggers, user -defined functions, and so on.

  • No royalty or license fees.

  • Runs on Windows, Linux, and Solaris.

  • GUI interface makes installation and configuration easy on Windows platforms.

  • Interfaces well with Delphi and PowerBuilder.

  • Commercial support from Borland and others.

  • Some impressive enterprise features.

Cons:

  • Newly open sourced.

  • Lacks some of the more advanced SQL statements (such as CASE , NULLIF , and COALESCE ).

  • Functions can only be written in C.

  • Linux version fairly new.

  • Somewhat fractured development community.

Typical uses:

  • Interbase is a full-featured RDBMS that used to be a proprietary product and that has now been open sourced. Although this is seen as a plus with regard to furthering development and tightening security, there are disadvantages. A coherent project has yet to fully develop around the newly opened code. As a result, the development community is slightly fractured.

  • Interbase interfaces well with many popular development languages. Additionally, it has been in development for a long time and consequently possesses almost all of the desired features in a modern RDBMS.

DB2

Pros:

  • Very scalable, including clusters.

  • Possesses very advanced replication features.

  • Support for multiterabyte databases.

  • Support for all features in a modern RDBMS.

  • Years of product refinement.

  • Supports multiple platforms, including small embedded-style environments.

  • 724 support options available.

  • Plethora of training and user groups available.

Cons:

  • Proprietary software.

  • License cost can be significant.

  • Configuration can be overly complex for simple jobs.

Typical uses:

  • Used by enterprises for large, complex, involved projects that need a full-featured RDBMS.

  • Proper installation and configuration can be complex. As a result, DB2 may not make much sense for small to mid-range database solutions. However, as a back end to a massive database system, DB2 cannot be beat.

Oracle

Pros:

  • Very scalable, including clusters.

  • Support for massively parallel architectures.

  • Support for multiterabyte databases.

  • Support for all features in a modern RDBMS.

  • Years of product refinement.

  • Supports multiple platforms, including small embedded-style environments.

  • 7x24 support options available.

  • Plethora of training and user groups available.

Cons:

  • Proprietary software.

  • License cost can be significant.

  • Configuration can be overly complex for simple jobs.

Typical uses:

  • Oracle is often considered to be the flagship commercial RDBMS available. Oracle has spent a tremendous amount of time and money in developing a product that can almost guarantee 100% uptime reliability. Unfortunately, those advanced features cost a significant amount of money to implement correctly.

  • Typically, proper installation and configuration can be complex. As a result, Oracle may not make much sense for small to midrange database solutions. However, as a back end to a massive database system, Oracle cannot be beat.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net