| technology | system admin - Graeme Messina
MySQL vs Postgres
Whether you are starting a new project, looking to store data, or you are experimenting with databases, then you are probably curious about the current state of open source databases.
Most of these different SQL types use very similar syntaxes and commands, although there are differences between them. Postgres and MySQL both have similarities between them, so we thought we would look into why you might choose one over another.
In production environments it is not unusual for open source databases to be used. A company usually chooses a database management system based on their requirements, although enterprise support is usually a must have service for live environments.
This is because bugs and system issues can crop up from time to time that need vendor intervention.
What is a Database?
A world without databases is almost unthinkable. Database technologies have helped to form the way that the web works, and the way that online business is conducted. As these technologies have scaled, so too has the need for data storage.
Databases are collections of structured information that are usually stored electronically and can be organized by different fields. Database management systems (DBMS) control most databases including Postgres and MySQL.
Managing, modifying, updating, controlling, and organizing data becomes easier with a graphical interface, which is essentially what a DBMS is in relation to databases.
You can also perform queries from the command line or CLI when interacting with databases, which is why scripting for databases is possible from outside of a DBMS.
Applications, able to translate data into various formats, are created with the CRUD functionality (Create, Read, Update and Delete). Database technologies are provided by companies such as IBM, Oracle and Microsoft.
Why use a Database?
In addition to providing scalable and secure data storage, databases also allow for easy data access. Organizing data makes it much easier for users to interact with it and make changes to it. You can order records in a database by their fields such as name, id and date as a simple example.
The use of databases makes finding records easier, and statements can be used to call on data fields from various tables to create a single report. More advanced features such as join statements allow you to connect tables from different databases together where they share common values.
Databases really shine in information rich environments because they are so good at processing and sorting large datasets.
In organizations where data segmentation is so vital, security features make it possible to control information, avoiding duplication, unauthorized access and preventing data loss.
Uses for database systems
- Data and data search capabilities (tools) are provided for specific records in a set of data.
- They contain special information that is used for data management.
- They can provide a solution to users who wish to access (and possibly edit) the same data entry.
The historical performance of MySQL has been characterized by fast execution of read-intensive workloads, but this has come at the price of concurrency in write-intensive workloads.Rich, powerful features are built into this flexible and scalable system so it is not surprising that it has seen such wide adoption for open source projects.
Anecdotally, Postgres was said to be faster for writing large data sets, but it was slower at reading but this is largely based on how you have configured your system and how much data you have.
Performance disparities between Postgres and MySQL have nearly disappeared with recent versions of both databases.
If you are considering MySQL or PostgreSQL for regular applications, performance is an important consideration, however, either will work well, even when factoring in expected future growth.
Both platforms can be replicated and many cloud providers offer managed, scalable database hosting in the cloud.
Postgres vs MySQL: Advantages
In terms of object-relational database management systems (ORDBMS ), PostgreSQL pioneered many concepts that were not readily available in commercial systems until a relatively short time ago.
Object-relational databases are not different to relational databases, but object-relational databases can be thought of as enhanced visions of relational databases.
Postgres is very similar to MySQL but is built upon object-relational database principles, while MySQL is a relational database. These are similar to one another, but object-relational databases offer more advanced features.
An example is Postgres’ inherited table structure is the ability to overload and inherit functions, which is required for some applications.
Postgres adheres to the SQL standards more closely than MySQL does, which means that MySQL has less of a learning curve if users already have some SQL knowledge when getting started.
Does this really matter for the average person wanting to learn either Postgres or MySQL? Probably not, but it is worth considering when you plan on implementing a database standard, especially if you are new to SQL in general.
But having said that, Postgres is better at handling concurrency than MySQL for a number of reasons, namely that MVCC (Multiversion Concurrency Control) allows parallel queries to be executed.
A non-blocking index may be created in Postgres (using the CREATE INDEX CONCURRENTLY syntax) as well as a partial index (for example, an index can be created to ignore deleted rows if you are using a soft delete model).
Some Comparisons Between Postgres and MySQL
Postgres offers a lot of extensibility. Advanced data types are supported that are not present in MySQL (geometry, geographic/GIS, network address types, JSONBs that can be indexed, timezone-aware dates and times). Additional types, operators, and indexes may also be created by you when using Postgres.
Licensing has not been a problem with Postgres, but with MySQL. MySQL AB was acquired by Sun Microsystems in 2008, which was itself acquired by Oracle in 2010. MySQL has an open source and a proprietary version available.
There has been speculation about MySQL and if it will remain an open source product, but that is still not clear in the long term. For now, it is available in both commercial and open source licensing forms.
As of now, open source forks of MySQL are in active development and can also be used such as MariaDB and Percona. These are 'drop-in replacements' for MySQL as an alternative DBMS.
MySQL is often seen as a simpler kind of database that is easy to use and has basic features that are ok for using in applications like websites. MySQL is easy to install and configure, and comes with a graphical interface that makes managing your data very easy.
Postgres also offers graphical configuration tools, but also has advanced features that make it more suitable for big datasets with more records. There is a lot that you can do with Postgres right out of the box, but takes some getting used to if you start delving into some of the more advanced features that it offers.
When to Use MySQL
If you have an open source project, a small database or if you want to learn how to administer databases then MySQL is a great option to learn.
Examples such as the aforementioned website installation come to mind. Other kinds of applications that use MySQL include basic Point of Sale applications, some accounting software, and personal projects.
If you don't need advanced features and complicated queries with multiple parameters then MySQL is a trusty workhorse that offers stability and simplicity for everyday use.
MySQL syntax teaches you concepts that translate to other database types that will be beneficial for anyone that is trying to learn how they work.
When to Use Postgres
The PostgreSQL database supports all of the additional data types required by modern systems, including documents, primitives, geometry, and structures.
Postgres enforces your data integrity through constraints and rules, and orphan or invalid records aren’t a problem in PostgreSQL.
Postgres is able to handle large databases with complex queries and statements for accessing data and displaying customized results.
With Postgres it is possible to parallelize read queries, it has powerful indexing, and it has the ability to manage concurrency in multiple versions.
PostgreSQL supports a wide range of features for improving its performance and it places a high priority on data integrity. Using its advanced replication capabilities, you are guaranteed to have your data completely protected.
There are benefits to using either MySQL or PostgreSQL, depending on what you are trying to do with your data. The deciding factors depend on what you want to accomplish with your project or goal. If you want to create applications that require advanced database functionality, then PostgreSQL might be the right choice for you. This is because it:
- Is highly scalable installations
- Is compliant with the SQL standard, mostly
- Has JSON support
- Has advanced functions like triggers and functions
- Offers multi-platform support
This makes it quite versatile for people that need these features. That is not to say that MySQL isn't a capable database system though. There are many reasons why MySQL might appeal to you, with some examples being:
- Roll back support
- Has advanced functions like triggers and functions
- Scalable enough to handle 50 million rows
- Multi-platform support
- Easy to learn and use
The main takeaway from all of this is that both systems are useful in certain scenarios, and whichever one you choose should be based on what your needs are, what your comfort level is with each database, and what you plan on doing with your project.
MySQL is designed to be easy to use and understand, while offering features that are both capable and useful for every day database operations. Postgres is more advanced, and has features that are useful in environments where performance and speed are more important than user friendliness.