The relational data model organizes data in tables of rows and columns and dominates in database management tools. Today, there are other data models including NoSQL and NewSQL but relational database management systems (RDBMSs) still dominant for storing and managing data.
This article compares and contrasts two widely implemented open-source RDBMSs: PostgreSQL and MySQL. We'll focus on exploring the data types that each use, their advantages and disadvantages, and situations where they are best used.
PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. MySQL is an open-source relational database management system (RDBMS). Both are supported by all major cloud service providers, including Amazon, Google, & Microsoft.
MySQL is perceived to be much quicker but offers fewer features. PostgreSQL is believed to have a deeper feature set. Some programmers think of PostgreSQL as being similar to Oracle, and it is often favored by those who know Oracle products.
MySQL has a strong following that likes its speed and ability to create new projects quickly. The truth is that the current releases of both products, MySQL 5.6 and PostgreSQL 9.2 (depending on the distribution version), have plenty of speed, power and dynamic new features. Let's take a closer look.
What is a database management system (DBMS)?
Databases are models of clusters of information or data. A database management system is a program that interacts with a database. A DBMS controls access to a database, writes data, runs queries and performs other tasks related to database management. One common misconception is that databases and database management systems are the same thing.
The difference between a database management system and a database are as follows:
- A database management system is software that interacts with a database.
- Whereas a database is a collection of data and doesn't necessarily have to be stored on a computer.
Database systems have underlying models that structure how data is stored and accessed. Relational database management systems employ a relational data model. This means the data is organized into tables, formally known as relations. A relation is a set of tuples (rows) in a table and each tuple shares a set of attributes (columns).
Each attribute (column) is assigned a data type that dictates the kind of entries allowed in the attribute. Different RDBMSs have different data types that aren't always interchangeable.
Structured query language (SQL) is a popular way for relational databases to manage and query their data. That said, many RDBMSs have their own dialect of SQL which can have certain limitations and/or extensions. Extensions are generally built to allow users to perform complex queries that would be difficult with standard SQL. Common data types that you may be familiar with from programming languages include dates, strings, integers and booleans.
In some DBMSs, numeric data like integers are signed meaning they can represent positive and negative numbers and others are unsigned meaning they only represent positive numbers.
What is a database constraint?
A database administrator may contraint what values can be entered into an attribute (column) or the entire tuple (row). Common SQL constraints are:
- UNIQUE: No two entries in a column can be the same
- NOT NULL: Column cannot have null values
- PRIMARY KEY: Every entry in a column is not null and is unique
- FOREIGN KEY: Refers to the primary key in another table
- CHECK: Constrains the range of values that can be entered in a column
- DEFAULT: Provides a default value for a given column
- INDEX: Enables faster retrieval of data
PostgreSQL (or Postgres) is said to be the most advanced open-source relational database, maintained by the PostgreSQL Global Development Group and its prolific community support. Its goal is to be highly extensible and standards compliant. Being standards complaint makes it very easy to switch to another type of SQL server. It has features like table inheritance and function overloading often associated with object databases. Companies that use PostgreSQL include Apple, BioPharm, Etsy, IMDB, Macworld, Debian, Fujitsu, Red Hat, Sun Microsystem, Cisco and Skype.
PostgreSQL has developed strong features to boost performance. They include:
- Several indexing functions including functional, index-only scans, partial, functional and multiple-index-counting.
- Asynchronous commit.
- Cost-based optimizer.
- Asynchronous as well as synchronous replication.
Recent releases have added more than 70 new performance enhancements. They are the direct result of a commitment to improve performance by taking out identifiable problem areas. The program can compress and decompress the information as it runs to optimize disk space. Another advanced feature developed recently is the ability to set asynchronous commits for the whole system, or per user or transaction. This lets different kinds of transactions run together without the need to choose in advance which storage engine type to use for each table.
Postgres is very efficient at concurrency and does not have read locks thanks to Multiversion Concurrency Control (MVCC) that enables atomicity, consistency, isolation and durability of transactions (ACID compliance)
PostgreSQL is very strict in coding--MySQL not so much. While PostgreSQL was created as an academic project made to mimic a huge database, MySQL started out small and has evolved to be big enough to handle large websites. Leading cloud vendors have PostgreSQL support on their platforms. There is also a REST API for any Postgres database.
PostgreSQL also executes security protocols based on the Access Control Lists (ACL) for user operations such as connections and queries.
MySQL databases are the world's most widely used databases. It is easy to set up and requires minimal fine tuning to achieve excellent performance levels. The name is derived from a girl name My, a daughter of one of the cofounders, Michael Widenius. The source code is available under the GNU GPL (General Public License). The project is now owned and administered by Oracle Corporation. Previous to Oracle's involvement, it was run by a Swedish company named MySQL AB. Companies that use MySQL include GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube and Spotify.
MySQL is widely used as part of the LAMP (Linux, Apache, MySQL, PHP) stack of open source programs that form the backbone of many websites on the Internet including:
- Google (excluding Search)
The core MySQL program does not have any Graphic User Interface tools to operate the software or create and maintain databases. There are several front-end programs available to make MySQL easier to use. The official program for this purpose is MySQL Workbench, owned and developed by Oracle but free to use for everyone.
MySQL works on many Windows, Linux and Mac platforms including:
- MAC OS X
- Novell Netware
- SCO Unixware
- Microsoft Windows
MySQL features include cross-platform support, updatable views, SSL support, stored procedures, information schema, query caching, partitioned tables and full text indexing through the MyISAM program. MySQL was written in C and C++. MySQL supports programming languages such as C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D and Erlang. MySQL also supports hash indexes and the InnoDB engine uses inverted lists for FULLTEXT indexes. It is highly flexible and reasonably flexible, making it a popular choice for web applications.
Should I use MySQL or PostgreSQL?
For new projects, determine if you are going to port to closed software later on. In that case, PostgreSQL is closer to Oracle, and any code made for PostgreSQL will port to Oracle easily. MySQL has moved away from the SQL standard somewhat. PostgreSQL is more reliable because it is ACID (Atomicity, Consistency, Isolation, and Durability) compliant which means queries will maintain data integrity, and return the same output without error. Both support multiple operating systems including Unix and non-unix operating systems.
MySQL is less reliable and not ACID compliant The way it handles foreign key references, auditing and transactions make it less reliable. It is also does not support CHECK constraints. MySQL is good if you are thinking you may use code from other open source projects. Since it is widely used in smaller websites, there are plenty of add-ons, plugins and modules to optimize MySQL from popular software like Wordpress, Drupal and Joomla.