Postgresql And Mysql: Comparing Licensing, Functionality, And Reliability

PostgreSQL and MySQL: Comparing Licensing, Functionality, and Reliability Steven C Jackson

Table of Contents Title............................................................................................................................................................3 Abstract......................................................................................................................................................3 1. Introduction............................................................................................................................................3 2. Licensing................................................................................................................................................3 MySQL..................................................................................................................................................4 PostgreSQL...........................................................................................................................................4 3. Features..................................................................................................................................................5 MySQL..................................................................................................................................................5 PostgreSQL...........................................................................................................................................5 4. Scalability and Reliability......................................................................................................................6 MySQL..................................................................................................................................................6 PostgreSQL...........................................................................................................................................7 5. Conclusion.............................................................................................................................................7 References..................................................................................................................................................9

Title Comparing Licensing, Functionality, and Reliability of PostgreSQL

Abstract This paper is intended to compare the two most popular open-source licensed Database Management Systems (DBMS) in the areas of Functionality, Reliability, and their respective licensing terms.

1. Introduction There is much debate about which open source database management systems (DBMS) is the best to use. For this paper, I will attempt to objectively compare the benefits of MySQL and PostgreSQL which are arguably two of the most widely adopted open source DBMS's. The scope of this paper is to compare the licensing, features, scalability, and reliability of the two DBMS's. While this paper is not intended to provide a complete analysis of all things MySQL and PostgreSQL, I will provide enough analysis of each DBMS to inform the reader of the particular advantages of using each system. Each DBMS will be given and overall proportional amount of analysis in order to strike and objective balance but some sections may be lengthier than others if a particular topic is more well documented than another. In general, I have noticed that more data and conversations can be found concerning MySQL than there is for PostgreSQL but that is in no way a testament to its worth.

2. Licensing Both MySQL and PostgreSQL are offered with open source licenses. MySQL is released under the GNU General Public License (GPL) and PostgreSQL under the Berkley Software Distribution (BSD) license. Each holds its own advantage and in this section I will detail what each license offers the user as well as any other licenses the DBMS may be release under.

MySQL As mentioned above, MySQL is released under the GPL. The GPL guarantees the software publisher that their creations may be freely adapted and distributed by all those who use the licensed software. Freedom to use and modify the software as the user sees fit is a great advantage of using the GPL and by extension, MySQL. In its licensing, MySQL gives any developer who uses the DBMS the freedom to distribute the DBMS with the developer's own GPL-based software. This allows developers to be able to rely on a robust, open source database upon which they can develop rich applications while retaining the freedom to distribute it along with their own software. In addition to offering MySQL with a GPL license, MySQL also licenses it under a commercial license for those companies who choose to a more restrictive, closed license and/or offer their software only in binary format. While this license is available for closed-source developers, it also comes with a price tag. Developers who do not release their source code are required to pay MySQL in order to distribute MySQL with their product. MySQL may be contacted for OEM licensing terms and prices.

PostgreSQL The BSD license is an open source license which, like the GPL, allows the user to distribute, copy, modify, or use the software as the user sees fit. This license, however, does not require that and derivative or reliant works release their own source code under the same license. This is especially useful for companies or individuals who wish to use a free and open source DBMS while choosing to not distribute their own source code as free and open source software. Because of the nature of the BSD license, PostgreSQL does not need to offer an open source license along with a closed source license in order to cover all uses. This provides users with the advantage of not needing to pay a fee if they choose to write closed source software but it also contains the disadvantage of not requiring that the users adhere to the same code of programming ethics as the DBMS author.

3. Features Each DBMS provides features that one would expect in a modern database system but each also provides its own distinct advantages and disadvantages wen compared to each other or to other open source or proprietary DBMS's in general. Each DBMS is relational and provides features such as table locking and primary keys but each has its own set of features that sets it apart from the other. This section breaks down the relative advantages of each DBMS. Of course, the greatest feature of each of these databases is that they are robust, open source database management systems.

MySQL MySQL provides many features that are available in proprietary and costly DBMS's. MySQL allows the user to choose from different engines such as MyISAM and InnoDB – the former being a great engine for a table on which a lot of selects are performed and the latter for tables with lots of inserts. Another advantage of using InnoDB is the allowance of foreign key constraints but, unfortunately, InnoDB is not the default engine in MySQL and this engine must be manually set when creating the table. A great feature of MySQL is that it can be used in low-memory environments such as embedded devices as well as in rich and robust industrial grade applications. MySQL's wonderful flexibility without significant performance costs is indeed a feature that sets it apart from other DBMS's. Even with all this flexibility, MySQL does not sacrifice any worth and adheres to ANSI-SQL standards.

PostgreSQL PostgreSQL is a standards compliant DBMS that has complete support for sub queries. One of my favorite out-of-the box features is that it has primary and foreign key constraints that the DBA can rely on and use to perform cascading insert, update, and delete statements. Like MySQL, it has support for auto-incrementing columns which is great for auto generating some ID's for your data. One of the best features that PostgreSQL offers is its Generalized Search Tree (GiST) which contains many sorting and

search algorithms. Stored procedures are a great advantage available in PostgreSQL and it is not limited by using its own single language. It has support for several languages including Perl, Python, Java, and C++ among others. It is also well supported in other languages and is easy to connect to using C++, Java, Ruby, PHP and man others. PostgreSQL is a DBMS chock full of features of which any user from novice to expert can take advantage. In addition to these features, PostgreSQL boasts impressive limits of: unlimited database size, 32 TB table size, 1.6 TB row size, and between 250 and 1600 columns per table depending on the4 data type. This is indeed an impressive features that is sure to impress the modern and rich media-centric internet world.

4. Scalability and Reliability When deploying small applications bundled with a DBMS, scalability is of little concern but now that there are large, enterprise sized solutions that rely on open source database management systems (Drupal immediately comes to mind) and these open solutions are increasingly adopted by corporations, it is imperative that the DBMS is scalable. MySQL and PostgreSQL each offer scalability that is rivaled only by much larger, proprietary DBMS's. Reliability goes hand-in-hand with scalability which is why I've combined these two sections. A scalable database does little good if it is not also reliable (and vice-versa). Databases need to be able to handle a heavy load or crash and be reliable enough to quickly and reliably recover from the problem. In the cases of scalability and reliability, both MySQL and PostgreSQL perform admirably.

MySQL Because of the trend of servers containing more CPUs rather than focusing on faster CPUs, MySQL has taken advantage of symmetric multiprocessing (SMP) in order to distribute load across the CPUs to

use them in in the most efficient manner possible. This allows the running process to be independent of whatever processor it is running on. Due to MySQL's focus on taking advantage of hardware trends, they greatly increase their scalability and reliability. In addition to providing scalability through its use of multiple processors, MySQL also provides a query analyzer in order view query bottlenecks and problems that allows you to more efficiently write queries. However, this service is not free but comes with the enterprise edition of MySQL. PostgreSQL

PostgreSQL PostgreSQL provides multiple services that ensure reliability and scalability. Its services include pointin-time recovery, asynchronous replication, online backups, and write-ahead logging. Each of these services are integral systems that provide fault tolerance that is on par with and perhaps better than expensive and proprietary DBMS's. Anecdotally, yet a wonderful example, PostgreSQL has shown its scalability by way of Skype. This Voice and Video Over IP service is utilizing PostgreSQL in a manner that will allow Skype to serve one billion users. By using stored procedures and interesting server layers and distribution, Skype is well on its way to making this a reality.

5. Conclusion I came into this paper expecting to find a clear winner in the MySQL versus PostgreSQL debate and I have to admit that I was leaning towards MySQL as being the clear winner since I have a bias towards it from my use of Drupal (which, while it supports both MySQL and PostgreSQL, it is set to MySQL by default) and MySQL in real-world applications and have only used PostgreSQL in academic settings but I must admit that, after this research and Oracle's acquisition of Sun (and thus MySQL's uncertain future) PostgreSQL has greatly piqued my interest. In each area covered in this paper, both MySQL an PostgreSQL prove to be worthy competitors in the

DBMS market. Whether you are drawn to MySQL's open source DBMS marketplace dominance, distribution of tasks across multiple CPUs, and its flexibility in such wide-ranging applications or to PostgreSQL's BSD licensing, it's wonderful search and sorting algorithms, or the data integrity ensured through foreign key constraints, you can be assured that either system will be a great, truly open source addition to your database-driven application. Despite PostgreSQL lagging behind in adoption as compared to MySQL, it may soon make headway given Oracle's acquisition of Sun but that is a debate for an entirely different paper. Flame bait aside, MySQL's and PostgreSQL's impressive scalability and reliability coupled with their bountiful features and exceptional licensing will ensure that these Database Management Solutions are going to continue being effective competitors against their open and closed source counterparts for years to come.

References 1. PostgreSQL Global Development Group. (2009). “PostgreSQL: Licence”. Retrieved December 7, 2009 from the World Wide Web: http://www.postgresql.org/about/licence 2. PostgreSQL Global Development Group. (2009). “PostgreSQL: About”. Retrieved December 7, 2009 from the World Wide Web: http://www.postgresql.org/about/ 3. PostgreSQL Global Development Group. (2009). “PostgreSQL 8.3 Feature List”. Retrieved December 7, 2009 from the World Wide Web: http://www.postgresql.org/about/press/features83 4. Sun MicroSystems, Inc. (2009). “MySQL :: MySQL Licensing Policy”. Retrieved December 7, 2009 from the World Wide Web: http://www.mysql.com/about/legal/licensing/index.html 5. Sun MicroSystems, Inc. (2009). “MySQL :: High Availability and Scalability”. Retrieved December 7, 2009 from the World Wide Web: http://dev.mysql.com/doc/refman/5.0/en/haoverview.html 6. Sun MicroSystems, Inc. (2009). “MySQL :: What Is New in MySQL 5.4”. Retrieved December 7, 2009 from the World Wide Web: http://dev.mysql.com/doc/refman/5.4/en/mysqlnutshell.html 7. Sun MicroSystems, Inc. (2009). “MySQL :: The Main Features of MySQL”. Retrieved December 7, 2009 from the World Wide Web: http://dev.mysql.com/doc/refman/5.0/en/features.html 8. Sun MicroSystems, Inc. (2009). “MySQL :: Features & Benefits”. Retrieved December 7, 2009 from the World Wide Web: http://www.sun.com/software/products/mysql/features.jsp 9. Linux Magazine. (2009). “High Availability and Scalability for PostgreSQL”. Retrieved December 7, 2009 from the World Wide Web: http://www.linux-mag.com/id/2896

10. Possibility Outpost. (2009). “Skype Plans for PostgreSQL to Scale to 1 Billion Users”. Retrieved December 7, 2009 from the World Wide Web: http://highscalability.com/skype-planspostgresql-scale-1-billion-users 11. QuinStreet Inc. (2009). “PostgreSQL vs MySQL: Which is better?”. Retrieved December 7, 2009 from the World Wide Web: http://www.databasejournal.com/features/mysql/article.php/3288951/PostgreSQL-vs-MySQLWhich-is-better.htm 12. Angsuman Chakraborty. (2009). “PostgreSQL vs. MySQL: A Comparative Review”. Retrieved December 7, 2009 from the World Wide Web: http://blog.taragana.com/index.php/archive/postgresql-vs-mysql-comparative-review/ 13. FermiLab. (2009). “PostgreSQL or MySQL?”. Retrieved December 7, 2009 from the World Wide Web: http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html