It is very easy to find hosting services sites that offer in their plans the Systems Managers of the Bank of Database (SGDB) MySQL and PostgreSQL although this is more common with the first one. As these DBMS are not only used in the Internet, perhaps it is your interest to use them on your projects of the software, since each one is endowed with interesting advantages, as free of use. To help you choose the best for your application, this article introduces the main features of both, starting with MySQL. For both, it is recommended that you have some knowledge about the concepts of databases data.
Some of the terms
Even those people who are already working with the database can getting “lost” in the midst of so many names of resources. Thus, to facilitate understanding, the following is a list with a brief explanation about the most important features:
– Referential integrity: also known as “integrity referential”, this feature consists of restrictions or the existing rules for a correct insertion of data, by example, to prevent that a table be filled without this happening in another;
– Schemas: feature that allows you to cross-reference information in the same database, but in different structures;
– SQL: acronym for Structured Query Language, is a language used in relational databases;
– SSL: acronym for Secure Sockets Layer, is a protocol for the secure exchange of information;
– Stored procedures: this resource consists of SQL commands “saved” on the server to, for example, perform repetitive tasks, avoiding a customer has to run them constantly;
– Transactions: also known as transactions, the transactions are executed statements in a block designated by parameters that indicate its beginning and its end;
– Triggers: also called triggers, the triggers are features that allow the firing of a sequence of commands then immediately or soon after an event;
– the Views: the views consist in a kind of virtual table formed by fields extracted from a table “true”, facilitating the control over the data accessed.
The MySQL database
The MySQL is one of the systems of database management more popular than it exists and be optimized for applications Web, is widely used on the Internet (including here in the AbbreviationFinder). It is very common to find hosting services web sites they offer the MySQL and PHP language, precisely because they both work very well together.
Another factor that helps in the popularity of MySQL is its availability for virtually any operating system such as Linux, FreeBSD (and other Unix-based systems), Windows and Mac OS X. in addition,MySQL is a free software (licensed under GPL), which means that anyone can study it or change it according to the need.
Among the technical features of the DBMS MySQL, are:
– High compatibility with languages such as PHP, Java, Python, C#, Ruby and C/C++;
– Low processing requirement (in comparison as other DBMS);
– Multiple data storage systems (Database engine), such as MyISAM, MySQL Cluster, CSV, Merge, InnoDB, etc.;
– Features such as transactions (transaction), connectivity secure indexing of text fields, replication,etc.;
– Instructions in SQL, as shown in the name.
Until the time that this article was written, MySQL was in the version 5.0 (more precisely, 5.0.26). In relation to version 4.0, there has been addition of many features and important improvements, such as:
– Triggers;
– Stored procedures;
– Sub-selects;
– Full support to Unicode;
– INFORMATION_SCHEMA (for storage of the data dictionary);
– Server side cursors;
– SSL support;
– Improvement in error handling.
MySQL has emerged in Sweden by the hands of three classmates: Allan Larsson, David Axmark and Michael Monty Widenius. Working with database, they felt the need to make certain connections between tables and used mSQL for this. However, it didn’t take to realize that this tool is not them met as the needed and went to work on a solution own. Came then the MySQL, whose first version it was released in the year 1996.
An important fact to be noted about MySQL is that this DBMS it also has a commercial license, this is paid. In this case, it is possible to obtain differential support of the developers.
It’s also worth mentioning that, in February of 2008, MySQL was purchased by Sun Microsystems,who has paid the amount of 1 billion dollars for the acquisition. More information about this transaction in this link (in English).
The PostgreSQL database
The system manager database PostgreSQL had its beginning at the University of California, Berkeley, in 1986. At the time, a programmer called Michael Stonebraker led a project for the creation a server relational database called Postgres, from another project from the same institution named Ingres. This technology was then bought out by Illustra, a company later acquired by Informix. However, even in the face of this, two students Berkeley (Jolly Chen and Andrew Yu) compatibilizaram Postgres to language SQL. This project received the name of Postgres95.
In 1996, when the project was stable, the database received the name of PostgreSQL. However, while it still had the name Postgres95, the database has had several changes. Your the code was completely revised and the SQL language was defined as default.
Technically speaking, PostgreSQL is a database relational database and object-oriented. One of its attractions is have common features with the database of a large size, which leaves able to work, including, with operations of mission critical. In addition, it is a database versatile, secure, free and open-source (available under a license BSD).
Among its features, we have:
– Compatibility multi-platform, that is, runs in various operating system, such as Windows, Mac OS X, Linux and other variants Unix;
– Compatibility with various languages, among them Java, PHP, Python, Ruby, and C/C++;
– The database size unlimited;
– Tables with size up to 32 TB;
– Amount of lines of up to 1.6 TB unlimited;
– Fields of up to 1 GB;
– Support for features like triggers, views, stored procedures, SSL, MVCC, schemas, transactions, savepoints, referential integrity, and expressions regular;
– Instructions in SQL, as the name implies.
At the time this article was written, the PostgreSQL was at version 8.1.
MySQL x PostgreSQL
MySQL or PostgreSQL, which one to use? Both are very good and not make ugly the face of the paid alternatives. In addition, they have resources and the advantages in common, which means that, for most applications, both can be used. In fact, the correct way is not to try to find out which is best, but in that situation a or another must be used.
PostgreSQL is optimized for complex applications, that is, that involve large volumes of data or that deal with critical information. Thus, for a system of trade electronic medium/high, for example, the PostGreSQL it is more interesting, since this DBMS is able to to deal satisfactorily with the volume of data generated by the query operations and sales.
MySQL, for its part, is focused on agility. Thus, if your application requires quick returns, and does not involve operations complex, the MySQL is the most suitable option, since it is optimized to provide rapid processing of the data and short time of response without requiring a lot of hardware. If you need, for example, a database to store the content your site, your forum, or you need to keep a register of users a portal, the MySQL “serves as a glove”, because such applications do not need the advanced features that PostgreSQL offers.
To choose one of these two DBMS, try to understand well what resources your application need. Try to estimate the volume of data, evaluate the available hardware, make sure of the features necessary, and, subsequently, search for information more detailed MySQL and PostGreSQL. If your application it is simple – especially if it is something that is connected to the Internet -, not you need to think about a lot: MySQL is a choice satisfactory because it is easily found in web hosting services.
However, there are situations in which both the MySQL and PostgreSQL will be good choices. In these cases, it is recommended do tests with both and assess which offers more advantages to application.
Ending
A database can be the difference between having and not have a business, be it of any size. Therefore, the choice must be done well and aspects such as performance, features, documentation and support should be considered. In all these points the MySQL and the PostgreSQL are excellent, and therefore, the choice between one of them just depends on of your application.
