MySQL vs PostgreSQL

Twogreatopensourcedatabases:acomparisonJoshBerkusPostgreSQLCoreTeamHPTuxTalks,June26,2008WhoisJosh?●PostgreSQLCoreTeam–10yearsinvolvementwiththeproject–Largedatabaseperformance,tuning,projectpress&corporaterelations,usergroups●Databasegeek–15yearsdatabaseapplicationdevelopment–MSSQL,MySQL,Oracle,others●OpenSourceguru–OpenOffice.org,LedgerSMB,Bricolage,OpenBRR,OSCON,moreTopics●SoundBite●History●MostCommonUses●Features●Performance●SummaryTopics●SoundBite●History●MostCommonUses●Features●Performance●SummarymostlyaboutPostgreSQLSoundBiteThemostpopularopensourcedatabaseThewebdatabaseTheworld'smostadvancedopensourcedatabaseTheopensourceOracleHistoryofMySQL●MySQLServerdevelopmentstartedin1994,marketedbyTCXDataKonsultAB●MySQLABfoundedin1995byMichael“Monty”Widenius,DavidAxmarkandAllanLarsson●Serverdevelopmentbasedonrequirementsforpracticalproductionuse:fewfeatures,butfastandstable●Frequentreleaseswithsmallchanges●Easytoinstallanduse(15-minuterule)HistoryofPostgreSQL•1986:POSTGRESattheUniversityofCalifornia,BerkeleyMichaelStonebrakerprojectSuccessortoINGRES•1994:firstcommecializedasIllustra(latermergedintoInformix)•1995:open-sourcedPortedtoSQLPostgreSQLGlobalDevelopmentGroupformed•1997:portedtoJapanese,supportedinJapan•1999:firstfull-timedevelopers&corporatesupport•2004:nativeWindowssupport•2006:supportedbySunDevelopmentHistoryDesignedby/forApplicationDevelopersDesignedby/forDatabaseAdministratorsDevelopmentPriorities(historically)PostgreSQL1.Dataintegrity2.Security3.Reliability4.Standards5.DBFeatures6.Performance7.Ease-of-use8.ProgrammerFeaturesMySQL1.Ease-of-use2.Performance3.ProgrammerFeatures4.Reliability5.DBFeatures6.Dataintegrity7.Security8.StandardsDevelopmentDirection(asimplification)Simple,EasytoUse,FastFeatures,Security,StandardsMySQLPostgreSQLCommunityOwnedbyonecompanywithusercommunityCommunity-ownedwithmanycompaniesinvolved●CoreMySQLis100%ownedbySun/MySQL●90%ofMySQLdevelopersworkforSun–exceptforthemanystorageengines●MySQLhasalargeusercommunity–manythousandsactiveworldwide–manypartnersinotheropensourcegroups●Sun/MySQLcontributestootherOSSprojects–PHPespecially●PostgreSQLhasalargedistributeddeveloperandusercommunity●Notownedbyanyonecompany–dozensofcompaniesandindividualscontributecode–est.over200developersin14timezones●CommunityOwned–supportedby5differentnon-profitsPostgreSQLCommunityMapHackersProjectsCompaniesCoreCoreAdvocacyCommittersFoundationsUserGroupsandNationalGroupsMostCommonUses●Websites●CRM●Logging●OEMapplications●Telecom(cluster)●Networktools●DataWarehouse●ERP●DataWarehouse●Geograpic●WebSites●OEMapplications●Networktools●CRMReleases●Feature-basedreleases–newfeaturesinminorreleases–every1-3years●3.23:2000●4.0:2003●4.1:2004●5.0:2005●5.1:2008?●Time-basedreleases–nonewfeaturesinminorreleases–everyyear●7.4:2003●8.0:2004●8.1:2005●8.2:2006●8.3:2008FeaturesStorageEngines●PluggableStorageEnginesallowMySQLtobehavelikeavarietyofdifferentdatabases–TelecomDB:MySQLCluster–Non-transactional:MyISAM–Transactional:InnoDB–Compressed:Archive–In-Memory:Memory–Write-only:BlackholeProgrammerFeatures●Excellentdriversforalllanguages–includingJDBC4●PHP–high-performancedrivers&specialsyntax–Nativedriver●MySQLProxy3rdPartySupport●MostopensourcewebprojectsdefaulttoMySQL–manyuseonlyMySQL–primaryrelationaldatabaseformosttop25websites●HundredsofvendorssupportMySQL–morethan50%ofmulti-databaseproducts–manyMySQLPartnersMySQLScale-Out●SimpleReplicationmakesit(relatively)simpletoscaleout–usedbyGoogle,Yahoo–load-balancereadsonslaves–beingsupplantedbymemcachedWritesLoadBalancerMasterSlaveSlaveReadsReadsRequestsSimplicity●Easytosetup–15minuterule–everythingincluded●Easytoadministrate–programmer-administered–mostinstallationsdon'tneedtuning●EasyReplication–verysimplemaster-slave&multimasterreplicationFeaturesMigrateability●ClosesttoproprietaryenterpriseDBs●AutomaticmigrationfromInformix–Informixis50%PostgreSQL●RelativelyeasymigrationfromOracle–easiestofanyOSSdatabase–putsmigrationcostwithinaffordablerange–toolsfordataintegration●SQLServer,DB2harder–buteasierthatMySQLSecurity...bydefault,PostgreSQListhemostsecurity-awaredatabaseavailable...DatabaseHacker'sHandbook(basedonacomparisonofPostgreSQL,MySQL,Oracle,DB2andSQLServer)Security●Authentication–multiplemethods:login,SSL,Kerberos,more–host-basedauthentication●Logging–logoutputishighlyconfigurableandsupportsuserauditing●Permissionsmodel–SQLROLESsupported,includingnestedroles–multiplesettablepermissionsonalldatabaseobjectsSecurity●Cleancode–onlyonesecuritypatchpertwomonths–communitypatchesusuallyoutinless72hours–onlyoneexploitinthefieldinthelastfouryears●DBAuditing–PostgreSQLsupportshighlyconfigurabletriggersandotherDBautomation–No“auditingtoolkit”outyetTransactionSupport●BulletproofACIDthankstoMVCC–possiblybestofanyRDBMS●TransactionalDDL–applyschemachangesinatransaction–greatforchangemanagement●includingagiledevelopment●Savepoints–spec-compliantsubtransactionsBI/DWFeatures●Largedatabasemanagementfeatures–tablespaces,tablepartitioning–automaticlargefield/rowcompression●Powerfulquerypl