WIXLIB

PostgreSQL vs. MySQLA Comparison of Enterprise SuitabilityAn EnterpriseDB White Paperfor DBAs, Application Developers, Enterprise Architects, and IT ManagersJune, 2009http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability2Executive SummaryMost IT professionals generally recognize that PostgreSQL is better suitedfor enterprise database responsibilities than MySQL.Fundamental and more sophisticated features and performance thatcharacterize enterprise capable databases are contrasted betweenPostgreSQL and MySQL.Topics addressed include: Database performance, Query optimization, ACID Transaction support, Data durability, Referential integrity, Support for procedural languages and triggers, and Support for industry standard authentication methodsBecause many of MySQL’s limitations only become apparent postdeployment when a full re-architecting of an organization’s datainfrastructure is difficult, this comparison will be of particular interest tothose who have little or no experience with MySQL in deployment.An in-depth feature comparison chart is provided detailing the differencesbetween MySQL and Postgres Plus Advanced Server.An in depth discussion targeted specifically to your organization’sconsideration of MySQL or PostgreSQL can be scheduled with enterprisedb.com. EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability3PerformanceMySQL is considered by some to be a high-performance database, andthis can be true for certain classes of read-mostly, Web-basedapplications. However, acceptable performance is generally considered tobe only available from MySQL’s default storage engine, MyISAM, whichhas several important limitations that make it unsuitable for enterprisedeployment.MyISAM is based on IBM’s Indexed Sequential Access Method (ISAM) fordata storage, which was designed primarily for extremely fast retrieval ofkeyed data. In this context, MyISAM is considered to provide adequatespeed. However, MySQL’s implementation of ISAM is known for causingroutine and somewhat antiquated database problems, such as:Data CorruptionOnce committed to an enterprise database, data should remainusable and intact. However, MyISAM’s data corruption problemsare so infamously common that the myisamchk utility, which is usedto find corruption in MySQL data files, is scheduled as a dailyoperation on many production MySQL systems. Further, in caseswhere catalog corruption occurs, it is difficult, if not impossible, torecover successfully.Lock ContentionRow-level locking is well understood as a foundational requirementof enterprise database operation. MyISAM lacks this feature, andinstead locks rows at the less granular table level. This simpler andless-granular approach causes significant lock contention in multiuser environments.Offline ManagementBecause MyISAM does not support multi-versioning, many routineadministrative tasks, like adding a column to a table, becomeimpossible to perform during normal use. This lack of multiversioning often requires the DBA to take down the database just toperform simple changes.Some of the problems with MyISAM may be avoided on a table-by-tablebasis using alternative storage engines, such as InnoDB. However, theMySQL catalog only operates on MyISAM. Because of this limitation,catalog corruption and administrative tasks are still problematic. MySQLdeveloper and co-founder Michael “Monty” Widenius has acknowledgedthis as a severe limitation, with a partial fix scheduled for MySQL 6.1. EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability4In contrast, PostgreSQL is very differently architected and presents noneof these problems. All PostgreSQL operations are multi-versioned usingMulti-Version Concurrency Control (MVCC). As a result, commonoperations such as re-indexing, adding or dropping columns, andrecreating views can be performed online and without excessive locking,allowing a DBA to perform routine maintenance transparently to thedatabase’s users and to the applications running against the database.Multiple Storage Engines and Query OptimizationPostgreSQL supported multiple storage engines in the late 1980’s andover time this functionality was removed learning the lesson that it is betterto concentrate efforts on a single storage engine. It wasn’t until justrecently that MySQL implemented similar functionality via their newlydeveloped Pluggable Storage Engine API. However, MySQL’s API is notrobust enough to allow for accurate query optimization and makes queryplanning and tuning difficult, if not impossible, to perform.MySQL’s API presents only two optimizer-related function calls to storageengine developers. These calls, if implemented, can assist in costingplans for the query optimizer. However, because these calls do notpresent the context of the query to the storage engines, the enginesthemselves cannot accurately return a proper estimate. Often, this resultsin the generation of slow query plans.Transaction SupportEnterprise-class databases must include transactional support. Indatabase terms, a transaction is a single unit of work, which may includetwo or more operations. For example, in a simple debit/credit operation,two operations must be performed. First, an amount is debited from anaccount. Second, the same amount is credited to another account. What ifthe first operation succeeded but the second failed? In a database withouttransaction support, the application would be responsible to notice thefailure and correct it.Conversely, in a database that supports transactions, the database wouldproperly undo the debit; this is called atomicity. The following properties,which are referred to by the acronym “ACID”, are generally understood tobe required for the reliable handling of transactions: Atomicity: guarantees that either all or none of the tasks within atransaction are performed. EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability5 Consistency: ensures that, irrespective of the success or failure of atransaction, the database will remain in a consistent state. Isolation: makes operations in a transaction appear isolated from allother operations. Durability: guarantees that when a commit succeeds, the transactionwill persist, not be undone, and can be recovered.Simply put, PostgreSQL is ACID-compliant, but MyISAM is not, either withrespect to the data in the database or with respect to the databasemetadata. As a result, PostgreSQL reliably handles transactions, butMyISAM does not.Referential IntegrityReferential integrity, the guaranteed data consistency between coupledtables, is another requirement for enterprise class database operations.An example of referential integrity may be found in an employee databasein which employees are linked to their departments using a field. In theemployee table, this field would be declared as a foreign key to thedepartment table, which contains a list of all company departments.If referential integrity were not enforced, a department could be droppedfrom the department table, leaving employees working for a non-existentdepartment. PostgreSQL maintains referential integrity. In contrast, veryfew MySQL storage engines support referential integrity, and, becauseMySQL will quietly accept the syntax for creating referential integrity ruleswithout actually enforcing them, administrators are often forced to doublecheck their changes.Procedural Language SupportFrom business processes to utility functions, procedural languages allowdevelopers and DBAs to implement programmatic logic within thedatabase, speeding up access and response times by reducing networkround-trips and by executing more closely to the data.While procedural language support was just recently added to MySQL,PostgreSQL has supported procedural languages for both Tcl and aPL/SQL-like dialects since version 6.3 in 1998.Because PostgreSQL is an extensible database, developers can writetheir own procedural language handlers. As a result, PostgreSQL hasstable implementations of procedural language handlers for many EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability6common programming languages, such as Perl, Tcl, Python, Ruby, andPHP.Support for TriggersSupport for triggers was also only recently added to MySQL.Unfortunately, MySQL’s triggers were only implemented per-row, thuslacking the ability to execute per-statement. This is a significant omission,as statement-level triggers are the commonly implemented. Statementlevel triggers have been supported in PostgreSQL since version 6.3 wasintroduced in 1998.Supported Authentication MethodsPostgreSQL offers a wide variety of well-known, industry-standardmethods to authenticate database users, including trust, password,GSSAPI, SSPI, Kerberos, Ident, LDAP, and PAM. MySQL only supportsits own, non-standard, non-pluggable, internal authentication system. Thismakes enterprise use difficult, because database accounts cannot becentrally provisioned or managed.ConclusionThe idea that PostgreSQL is better suited for enterprise deployment thanMySQL is rooted in concrete differences between the two databases’features, maturity, functionality, and performance. MySQL is widelydeployed, but its legitimate uses are limited to a narrow range ofapplications that can tolerate MySQL’s inherent limitations.Many enterprise IT departments have optimistically selected MySQLbecause of its popularity and then “hit the wall” once the database is inproduction. The following section titled ‘PostgreSQL vs. MySQL FeatureComparison’ presents an overview of enterprise attributes of PostgreSQLthat are absent or limited in MySQL.For more information comparing MySQL to PostgreSQL in the context ofyour organizations usage, please visit or sional services.dosales@enterprisedb.com EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com

PostgreSQL vs. MySQL, A Comparison of Enterprise Suitability7PostgreSQL vs. MySQLFeature DB, Data Warehousing, Business IntelligenceBulk Data LoaderYYDirect Path LoadNYFunction-based IndexesNYOptimizer StatisticsYYManagementPipelined Table FunctionsNYPartitioningYYParallel DatabaseParallel QueryNYHigh AvailabilityPhysical Standby DatabaseOnline OperationsOnline BackupOnline ReorganizationNNY*NYYYYContent ManagementText Data Support / AccessGeo-Spatial Data SupportY*Y*YYYYYYYYYYInformation IntegrationCapture / Consumedata /Transactions / EventsNetworkingConnection ManagerMulti-Protocol ConnectivityConnection PoolingCommentsEDB*LoaderUse SETOF functionGridSQL provides parallel query* Dependent on storage engine* InnoDB does not support text* InnoDB cannot indexThrough JMS EnterpriseDB Corporation, 2009 All rights reserved.EnterpriseDB and Postgres Plus are trademarks of EnterpriseDB Corporation. Other names may be trademarks of their respective owners.http://www.enterprisedb.com