Popularity of Open source databases
Open source databases come with the advantage of being free, available for understanding, modification, and improvement as per needs. There are three ways of using an open source database - do-it-yourself, third party, and outsourcing.
Published on: Mar 4, 2016
Transcripts - Popularity of Open source databases
Opensource Databases: A Comparative Analysis -Amit Kumar
GlobalLogic The Leader in Global Product DevelopmentOverviewOpen source databases come with the advantage of being free, available forunderstanding, modification, and improvement as per needs.There are three ways of using an open source database - do-it-yourself, third-party, and outsourcing. The choice of an open source database depends onfactors, such as the intended usage, duration of use, and the cost involved as themain factor. Often the best comparison is obtained by first-hand evaluation.However, the views of the user community regarding the most suitable opensource database are worth observing. The points of comparison are players withlicense, popularity, features, and selling points.This paper is an effort to provide a background to help you choose an opensource database that suits your needs.
GlobalLogic The Leader in Global Product DevelopmentPlayers The top three vendors in the database • PostgreSQL - has hundreds of companies listed as Users. Themarket are: Oracle, IBM (DB2 and Informix), & Microsoft (SQL community is as old as Aug-96. It is much-adored amongServer). In the open source market, there are six important technical jig wigs for its huge support of features.players (on the basis of acceptance in the software community EnterpriseDB is a product built over it. Last few releases haveat large): been version 7.4 in Nov-03, current version 8.0 in Jan-05, and 1. MySQL - GNU (General Public License) as well as the new release 8.1 in Nov-05. commercial 2. PostgreSQL - BSD (Berkeley Software Distribution) • Firebird - about 0.1 million deployments as of Sep-05. This is a license very old community since Sep-84 and has a huge fan 3. Firebird - Initial Developers Public License (IDPL) following for the number of good and bad times it has been 4. Ingres - GNU General Public Licensev2 through. The versions out are version 1.0 in Mar-02, current 5. MaxDB (earlier known as SAP DB, it is being v1.5 in Feb-04 and 2.0 alpha is out there. developed in alliance with MySQL especially for SAP environments) - GPL • Ingres - about 5000 existing customers. After changing many 6. HSQLDB - BSD license hands, the product has been made available to open source since ‘04, though it has been in existence along withPopularity PostgreSQL. The current release - r3 is out since Aug-04 and r4 is much-awaited for its support to materialized views. • MaxDB - about 6000 customer installations. This has been specific to SAP applications earlier known as SAPDB. StartedThe popularity of an open source database seems like a trivial since Oct-00. It was available in version 7.5 in Nov-03 andconcern to the technical community, but the fact stands tall that version 7.6 in Nov-05a product’s success is based on other reasons in addition to • HSQLDB - 0.3 million downloads. The project is finding hard tothe technical features. Some of these are: get contributions and is slower than others. It is the youngest a) Support available from community as well as tool in the group, formed since 01. It was out with version 1.7 in vendors Feb-05 and version 1.8 July-05 (It released 6 versions since b) Momentum of the product being released in and open Apr-01). source arenaThese are the two most important business concerns for Platform Support also speaks about the popularity andchoosing an open source product. The former ensures integrity acceptance. Regarding the OS, we have major players aswith various other technologies and the latter ensures that open Linux, UNIX, Apple, and Windows. Besides, we also havesource is not going to die or fade out easily. Novell with Netware and BSD. • MySQL & PostgreSQL fare at the top of the group as theyThe six open source databases that we selected, fare support all the listed platformsdifferently on the popularity aspect. • Firebird & Ingres lack support to Novell OS. • MaxDB favors the market with Linux, UNIX, and Windows• MySQL - most popular open source database boasting more platforms only.than 8 million active installations. Starting since 95 (internally), • HSQLDB is a java-based database, and hence, is more ofthe MySQL community is the largest and has frequent releases; JVM-dependent than platform-independent.version 4.1 came out in Apr-03, current version 5.0 in Aug-04,and the much-talked and awaited future release version 5.1 isout as beta since Nov-05.
GlobalLogic The Leader in Global Product DevelopmentOpen Source Database MySQLPoints in favor -• This is the most widely accepted open source database inFeatures In general, most database features are use. This simple reason is quite compelling to select among various options.supported by all databases with minor deviations. To name a • It claims to be comparatively easier to learn and use.few, we can categorize them as follows: • The popularity provides for a lot of options in Administrative• SQL Compliance tools.• ACID compliance, Locking and concurrency support, Foreign • The architecture provides for plugging storage engines.Key constraint & Transaction support Many options exist in the market to cater to various needs e.g.• Functional features: InnoDB. — Views, schemas, sub-select, stored procedure, triggers, • Commit grouping, gathering multiple transactions from xml support multiple connections together to increase the number of — Tablespace feature commits per second. — UNICODE support • It is available on Novell Netware OS also besides the — API support generic lot of Win, UNIX, and Mac family. Points against it -•• Non-functional factors: Even the current version lacks many SQL RDBMS features. — Performace & Availability For instance, its handling of dates allows storing a date with a — Stability & Flexibility day beyond the last day of a month with less than 31 days, — Web and Data-warehousing strengths and arithmetic operations are vulnerable to either integer — Security, Authorization, and SSL support overflow or floating point truncation. Though these can be — Replication, Load-balancing, and Clustering support taken care of by running special SQL modes. There are many — Ease of installation, configuration, management, and such features that have been criticized and are slowly being administration taken care of in newer releases. Version 5.0, for example, — Hot backups supports views, stored procedures and cursors, and version — Admin and migration tools/scripts 5.1 will support triggers. — Easy to learn & use • Other criticisms include its divergence from the SQL — Good documentation standard on the subject of treatment of NULL values and — Distribution License & Support default values.It would be a continuous task to figure out what features • It does not support Roles.distinguish one database from others. To begin with, here are aComparison offew pointersSelling Points MySQL PostgreSQL EnterpriseDB FireBird Ingres MaxDB HSQLDBTemporary Table P P P P P PMaterialized Views 1 2 3 P P PIndexing 4 P5 P PDomain P P P P P PCursor P P P P P PPartitioning 6 P P P1. Emulates Views using Stored Procedures and Triggers 4. Limited R tree and hash indexing support2. Emulates Views using Stored Procedures and Triggers 5. Expression indexing support from r43. Support from r3 6. Partitioning supported from MySQL 5.1
GlobalLogic The Leader in Global Product DevelopmentWe can conclude that even though MySQL lacks compliance queries.with the SQL standards, it provides for comparable • PostgerSQL use a multiple row data storage strategy calledperformance. It serves the purpose for users who are willing to MVCC to make it extremely responsive in high volumeaccept the programs limitations (which decrease with every environments. The leading proprietary database vendor usesmajor revision) in exchange for speed, simplicity, and rapid this technology for the same reasons.development. Points against it -It is often included as a default component in low-end • It is Considerably slower than MySQLcommercial web hosting plans. For application developers • It does not support the entire ANSI SQL 92 standard, much(mostly using PHP and Perl), MySQL is the only DBMS choice less the ANSI SQL 99 standard.unless they want to operate their own web hosts. • PostgreSQL isnt hierarchical. Databases hold only tables, not other databases.Ingres Firebird• The database is technically at par with big databases as Points in favor -Oracle. Besides having an open source advantage, business • Mature Windows support. It has been supported on Windowsand other peripheral factors can only justify the use of Ingres. for a very long time and it is well-tested.MaxDB • Mature ADO.NET provider. Npqsql (PostgreSQL ADO.NETPoints in favor - provider) is still in beta version. Firebird ADO.NET provider• The database is specifically made for SAP and performs best supports the embedded Firebird, services API (backup, restore,with it. It covers most of the shortcomings in terms of features statistics, batch SQL execution, etc).over its sister MySQL. • Embedded version (with 2 MB runtime and easy switching toPoints against it - a standalone server) seems to be one of the biggest• It is quite complicated to install & has poor documentation. advantages of Firebird.HSQLDB • It is reliable, stable, fast, and low-cost, and with a smallPoints in favor - footprint. And not so hard to learn!• It offers a fast, small (less than 100k in one version) databaseengine which offers both in-memory and disk-based tables.Embedded and server modes are available.• It is best known for its small size, ability to execute completelyin memory, and speed. It can also run on free Java runtimessuch as, Kaffe.• It is very fast for simple queries, e.g. HSQLDB takes less than4 seconds to INSERT 50,000 rows, whilst MySQL takes over 20mins!Points against it -• It is not a good choice if the DB size is large. It not only suckssystem resources, but degrades DB startup performance as well.• If you benchmark complex queries (joins with sub-selects, INclauses, etc), youll find that its a weak spot is its queryoptimizer.• HSQLDB requires Java, which could be a sufficientconstraint not to use it.PostgreSQLPoints in favor -• PostgreSQL supports a richer SQL dialect and SQL sub-
GlobalLogic The Leader in Global Product DevelopmentPoints against it -• Stored procedures are difficult to implement in Firebird.• Most RDBMSs stores column names lower case. Firebirdstores them in upper case.• Firebird OAT (oldest active transaction) & OIT (oldestinteresting transaction) needs constant monitoring. If the OATgets stuck, the database starts to get huge as transaction datais stored in the database file itself. Firebird does not have theconcept of a separate transaction log. Amit Kumar is a graduate from the Bengal Engineering• PostgreSQL supports temp tables and tons of built-in College, Shibpur and specializes in software productfunctions, which is missing from Firebird. Firebird also has 3 engineering with Java/J2EE.SQL dialects - SQL PSQL, DSQL, etc and you cant use one Amit Kumar works for GlobalLogic Inc, a premier softwaredialect from the other dialect. engineering vendor with marquee customers like Gemalto,ConclusionIt is a question of what you desire from a database Avolent, Mantas, and Nextone to name a few. He has deepthat matters.For small and web-based retrieval applications, the understanding in areas related to open source technology andin-built pre-configured MySQL in many application servers is has a wealth of experience interacting with customers, andserved just right.The moment we need more from our understanding their vision so that the same can bedatabases, be it features or performance, or the lack of skilled implemented at the Global Deliver Centers at GlobalLogic.resources, we start looking the other way.We all know how user/developer-community in general isreluctant to dispense with a setup database if it serves thebasic purpose. The reluctance can be explained from the factthat everything would come at a cost be it installation,configuration, timely upgrades (maintenance), administration,or even training the resources for the new database server.Hence, for anything playful or level-I, its MySQL. Anythingserious and level-II, it is usually replaced by PostgreSQL orIngres or Firebird. If the application is SAP-based - the defaultchoice is MaxDB. More often than not we tend to fall on theproven and built-for products. If the user is Java-savvy andhas a love for fast but basic database, not to mention thefascination of such a low memory print, the usual option isHSQLDB. Some tools claim it to be 20 times faster, which is alot!As I mentioned, that there are positives as well as negatives forPostgreSQL, Ingres, and Firebird. All the three have similarfeatures. Its really difficult to justify the best among the three.Its a particular feature in the wish list or inclination with (out)reasons (like the support community which is usually positive,but an opinion clash could always be there) - which impacts inthe choice. All the three have their limitations and have alsoproved their worth as a part of successful enterprise solutions.