Open Source database management systems

From the The Free On-line Dictionary of Computing , data is numbers, characters, images, or other method of recording, in a form which can be assessed by a human or (especially) input into a computer, stored and processed there, or transmitted on some digital channel. Computers nearly always represent data in binary. Data on its own has no meaning, only when interpreted by some kind of data processing system does it take on meaning and become information.  People or computers can find patterns in data to perceive information, and information can be used to enhance knowledge.  Since knowledge is prerequisite to wisdom, we always want more data and information.  But, as modern  societies verge on information overload, we especially need better ways to find patterns.

 

 

1234567.89 is data.
“Your bank balance has jumped 8087% to $1234567.89” is information.
“Nobody owes me that much money” is knowledge.
“I’d better talk to the bank before I spend it, because of what has happened to other people” is wisdom.

A database management system (DBMS) is suite of programs which typically manage large structured sets of persistent data, offering ad hoc query facilities to many users. They are widely used in business applications. A database management system (DBMS) can be an extremely complex set of software programs that controls the organization, storage and retrieval of data (fields, records and files) in a database.  It also controls the security and integrity of the database. Data security prevents unauthorized users from viewing or updating the database.

Query languages and report writers allow users to interactively interrogate the database and analyze its data. SQL – Structured Query Language, is a popular database query language.

A business information system is made up of subjects (customers, employees, vendors, etc.) and activities (orders, payments, purchases, etc.).  Database design is the process of deciding how to organize this data into record types and how the record types will relate to each other.  The DBMS should mirror the organization’s data structure and process transactions efficiently.

An intelligent database is a database management system which performs data validation and processing traditionally done by application programs.  Most DBMSs provide some data validation, e.g. rejecting invalid dates or alphabetic data entered into money fields, but often most processing is done by application programs.  There is however no limit to the amount of processing that can be done by an intelligent database as long as the process is a standard function for that data. Examples of techniques used to implement intelligent databases are constraints, triggers and stored procedures. Moving processing to the database aids data integrity because it is guaranteed to be consistent across all uses of the data. 

Some of the popular SQL DBMS are

  • Oracle
  • Sybase
  • IBM DB2
  • Informix
  • MS SQL Server
  • MS Access
  • mSQL
  • MySQL
  • Postgresql

One often comes across a lot of discussion between commercial and open source DBMS. Such discussions are out of scope of this article where the focus would primarily be three popular open source databases, namely, MySQL, PostgreSQL and MaxDB. No comparisons on “which is a better system” would be discussed here. There are several ways of measuring open source software such as features, performance, reliability, support, and the license model. 

PostgreSQL
– PostgreSQL is an enhancement of the POSTGRES database management system (and is still sometimes refereed to as simply “Postgres”), a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, it replaces the PostQuel query language with an extended subset of SQL. PostgreSQL is free and the complete source is available. PostgreSQL was first developed at the University of California at Berkeley in 1986 as a research prototype.

PostgreSQL is Fully ACID compliant and ANSI SQL compliant. It has most features present in large commercial DBMSs, like transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. It also has certain unique features such as user-defined types, inheritance, rules, and multi-version concurrency control to reduce lock contention.
In terms of performance PostgreSQL is at par with most commercial and open source DBMS. Each release of PostgreSQL has atleast one month of beta testing before it is released as a production release. They are generally stable and reliable.
Support is available via mailing lists.  They also have a commercial per-incident support available.  But generally the support options available are weaker compared to other DBMS.
The license model of PostgreSQL is the best as it follows the BSD license. The BSD license is similar to the LGPL where the user can do anything with the code, even call it his own code and sell it. There is no obligation whatsoever.
PostgreSQL is available on Linux and all unix flavors (34 platforms with the latest stable release), and Windows compatibility is available via the Cygwin framework. Native Windows compatibility is also available from several commercial companies.

MySQL – When it was originally developed, the idea was to make a fast, light and stable database server which was not very heavy on features.  This made MySQL very popular as it was a disruptive technology. It had just the features required to make customers use it. It was not a complete system then. All the small users who generally use a database for just “insert, select, delete” where very happy with MySQL. Slowly MySQL started adding features and is now a full fledged system. However, it still carries the reputation of being a Toy DBMS even today. It is no longer as fast as it used to be. However, today it can boast of having most of the features that any commercial or open source database system offers.

MySQL support a broad subset of the ANSI SQL 99 syntax. MySQL database server’s unique independent storage engines let you choose the type of database storage that is most appropriate for your particular needs. If you need row-level locking and transaction support, you can use the InnoDB storage engine. If your application doesn’t require transactions, you can use the MyISAM storage engine for maximum performance. In terms of performance and features it is at par with most DBMS available today.

MySQL is available on various platforms including Linux, Microsoft Windows, FreeBSD, Sun Solaris, IBM’s AIX, Mac OS X, HP-UX, AIX, QNX, Novell NetWare, SCO OpenUnix, SGI Irix, and Dec OSF.

MySQL offer community based support as well as professional support. Support is also available via mysql’s consulting partners.

MySQL offer a unique licensing system where the MySQL database server is available under the MySQL AB “dual licensing” model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the “GPL”) or under a commercial license. One must note here that GPL is not as flexible as the BSD license. There are four versions of the database server available:

  • MySQL Standard includes the standard MySQL storage engines and the InnoDB storage engine. InnoDB is a transaction-safe, ACID-compliant storage engine with commit, rollback, crash recovery and row-level locking capabilities. This version is for users who want the high-performance MySQL database with full transaction support. MySQL Standard is licensed under the GPL. MySQL Pro is the commercially-licensed version of the server with the same feature-set.

  • MySQL Max is for the user who wants early access to new features. This version includes the standard MySQL storage engines, the InnoDB storage engine, and other extras like the Berkeley database (BDB) storage engine, and support for splitting tables across multiple files to avoid operating system file size limitations. In future releases, MySQL Max will include more cutting-edge features.

  • MySQL Pro is the commercially licensed version of the MySQL Standard database server, including InnoDB support.

  • MySQL Classic only includes the standard MySQL storage engines, differing from MySQL Pro and MySQL Standard only by the omission of the InnoDB storage engine. It is only available under a commercial license.

The mysql site has a good feature comparison system where one can compare various DBMS.

To summarize the two one would say that if one is looking for good features and a flexible licensing system that PostgreSQL would be the way to go. However, if the priority is speed then the option would be MySQL. In terms of the number of installations MySQL would surely beat PostgreSQL. MySQL is one of the most popular databases for web based applications. On the other hand, PostgreSQL is more popular with commercial applications such as ERP’s, Accounting software etc. One would always wish to have the best of both PostgreSQL and MySQL. Enter….. MaxDB.

MaxDB – Originally called SAPDB, this database system was developed by SAP AG and then purchased by MySQL. It was originally made for the SAP ERP and hence is very feature rich and can beat any DBMS in terms of performance.

Some of its features as taken from their site are –

  • Easy configuration and low administration
  • Elaborate backup and restore capabilities
  • Continuous operation, no scheduled downtimes required
  • Designed for large number of users and high workloads
  • Scales to database sizes in the terabytes
  • High availability through cluster and hot-standby support
  • Synchronization Manager to control enterprise-wide data replication
  • Easy-to-use graphical database tools
  • Available for all enterprise HW/OS platforms.

MaxDB comes under the dual licensing system similar to MySQL i.e. both GPL as well as commercial.
Support and training is also available via various channels including a community based support system as well as a commercial support system.

MaxDB surely would be a good contender to be the most popular database system in the near future. 

Note: The contents of this article is copied form the following sites

1) http://www.postgresql.org/
2) http://www.mysql.com/products/mysql/
3) http://www.mysql.com/products/maxdb/

The introduction is taken from The Free On-line Dictionary
of Computing

 

Leave a Reply