PostgreSQL vs. MySQL - comparison, features and reviews

Although there are many similarities and overlaps between the two PostgreSQL vs MySQL databases, there are also very clear differences between them. The developer needs to clearly distinguish between them in order to determine which database is most suitable for a particular use case.

PostgreSQL vs MySQL

PostgreSQL vs MySQL are two popular open source databases. They support all the basic SQL operations the user needs. When it comes to more advanced tasks, you need to take advantage of certain features supported by each database, such as materialized views or partial indexes. For example, PostgreSQL supports materialized views, but MySQL does not.

A summary of the similarities and differences of the most frequently evaluated functions and capabilities between PostgreSQL vs MySQL looks like this:

If a developer wants to create a consumer-oriented application whose goal is to scale with over a million active users, MySQL is the best choice. Otherwise, PostgreSQL is better.

In addition, you can consider the following aspects of the comparison of PostgreSQL vs MySQL.

In terms of reliability:

1. PostgreSQL 10 has added logical replication, which makes it equivalent to MySQL. Historically, replication was one of the reasons for choosing the second database, but now, by this criterion, the first is equal to it.
2. Both databases have parameters that allow you to correlate the performance and durability of MySQL vs PostgreSQL for web applications.

In terms of speed:

1. For applications with long SELECT analytics runtimes, PostgreSQL performs better due to the parallel query capability.
2. For small SELECTs covering a simple and clustered index, MySQL works fine.
3. For applications with a lot of small MySQL is better suited.
4. For heavy UPDATE applications, MySQL works much better.
5. For applications with heavy DELETE on ephemeral data, both support a partition, both work well if the user carefully uses this function.

From the point of view of scalability and comparison of Postgres vs MySQL DBMS:

1. Both databases scale pretty well (up and down).
2. PostgreSQL is more modest. If the user has 1000 connections, she needs more than 10 GB of additional memory.

Some interesting differences between PostgreSQL and MySQL:

1. MySQL 8 now supports CTE and window functions. Therefore, both databases are practically comparable, with the exception of custom types on PostgreSQL.
2. In practice, PostgreSQL is “safer” by default, especially for beginners. For example, MySQL has weird defaults for character encoding and sorting.
3. You could say that MySQL is great for bulk OLTP, and PostgreSQL for everything else, especially for analytics.

Open source

Open source software has unique advantages - cost, flexibility, freedom, security and accountability, i.e. unrivaled proprietary software solutions. Open source open source software can be redistributed and modified by anyone.

Open source software has long-term viability and always has a place in advanced technology. It is created and supported by the global community of organizations and individual developers.

PostgreSQL was developed by a group of several companies and members. This is free open source software with a PostgreSQL license similar to BSD or MIT.

The MySQL development project has made source code available. Now it is owned by Oracle Corporation and offers a variety of paid publications for private use.

ACID Compliance

ACID (Atomicity, Consistency, Isolation, Durability) is a set of database transaction properties. ACID compliance ensures that no data is lost or deleted in the system in the event of a failure, even if multiple changes occur during the same transaction.

PostgreSQL is compatible with ACID and ensures compliance with all requirements. MySQL only works with ACID when using the InnoDB and NDB Cluster Storage engines.

SQL compliance is a standard in which a database must comply with all structured recommendations and query language standards. This is very important when companies want to work with heterogeneous databases for the application.

The presence of SQL compliance makes it very easy to move the desired values ​​from one SQL-compatible database to another, for example, Oracle on PostgreSQL or SQL Server. In this case, you need to consider before deciding which database to choose, MySQL vs PostgreSQL l.

PostgreSQL supports most of the basic SQL functions. Of the nearly 180 functions required for Core compliance, PostgreSQL performs at least 160. Currently, none of the existing versions of the database management system claims to be fully compliant.

MySQL is partially compatible with some versions, for example, it does not support CHECK restrictions.

Replication for fast WAL processing

This means performing online copying of data from the database on one PC or using loaded MySQL vs PostgreSQL vs SQLite server systems, so that users have fresh information, gaining access to data for tasks.

PostgreSQL supports Master-Standby replication and makes significant improvements by creating extremely fast WAL processing, resulting in near real-time replication and hot spare capabilities.

Replication offered by PostgreSQL:

1. Single wizard in one standby mode.
2. Single wizard for multiple backups.
3. Hot standby / streaming replication.
4. Bidirectional replication.
5. Replication of the flow of logical logs.
6. Cascading replication

Replication offered by MySQL:

1. Single wizard in one standby mode.
2. Single wizard for multiple backups.
3. Single wizard in one standby mode for one or more backup numbers.
4. Cyclic replication from A to B to C and back to A.
5. Master of mastering.

Speed ​​boost apps

Performance is an area that can only be assessed by comparing the performance of potential scenarios, since it depends on the pure requirements of a particular user and the nature of the application to increase the reading speed of PostgreSQL vs MySQL.

PostgreSQL is widely used in huge systems where speed is critical and data must be correct. It supports various options for optimizing performance, for example, Oracle, SQL Server and decently works in OLTP / OLAP, when speed and detailed data analysis are needed. It also works well with Business Intelligence applications, but is better suited for Data Warehousing and data analysis applications requiring fast read / write performance Performance PostgreSQL vs MySQL.

MySQL is widely used in web projects that need a database for simple transactions. MySQL, when overloaded with heavy loads or when trying to execute complex queries, works well in OLAP / OLTP systems that require read speed. In general, MySQL is fairly reliable, works well with high concurrency scenarios and with Business Intelligence applications.

Security and Cloud Hosting

Database security refers to collective measures used to protect it from malicious threats and attacks. This is a broad term that includes many processes, tools and methodologies that ensure security in a database environment. Let's make a brief comparison of SQLite MySQL and PostgreSQL security.

PostgreSQL has ROLES and legacy roles for setting and maintaining permissions. It also has built-in SSL support for connections and encryption of client / server messages and line-level security. PostgreSQL has the SE-PostgreSQL extension, which provides additional access controls based on SELinux security policies.

MySQL implements security based on access control (ACL) for any operations that the user may try to perform.

Cloud hosting provides the flexibility of servers, allowing you to quickly expand or reduce their capabilities. It also provides an opportunity to reduce downtime while managing peak workloads and is supported by all major cloud service providers, including Amazon, Google and Microsoft.

Community Concurrency Support

PostgreSQL has a very strong and active community that constantly improves existing functions, and new innovative developments strive to keep it at the forefront of the database.

MySQL has a large community of developers who, after acquiring it by Oracle, focus mainly on maintaining existing functions. New ones are added only occasionally.

Concurrency support means that multiple users can access data at the same time. This is one of the main functions for comparing MySQL and PostgreSQL, which are considered with respect to a similar criterion. Such a system increases the ability of many people to simultaneously access the database and use it in several places.

NoSQL and JSON features

NoSQL and JSON are very popular, and NoSQL databases are becoming more common. JSON is a simple data format that allows programmers to store and share sets of values, lists, and key value mappings on different systems.

PostgreSQL supports JSON and other NoSQL features, such as native XML support and key-value pairs with HSTORE. It also supports indexing JSON data for faster access.

MySQL has support for the JSON data type, but there is no other NoSQL function. It does not support indexing for JSON.

A materialized view is a database object containing the results of a query that can be updated as needed from the original base table. It can be considered as a cache for databases.

A temporary table stores data that is not required to be stored outside the session that creates it. The main way that it differs from a materialized view is that the latter provides the ability to periodically update data, which leads to increased efficiency for this use case.

PostgreSQL supports materialized views and temporary tables. MySQL supports temporary tables but does not support materialized views

Geospatial data

This is all geographical data that is stored in the database and can provide analysis. This is information about a physical object that can be represented by numerical values ​​in a geographic coordinate system.

PostgreSQL supports geospatial data through the PostGIS extension.

MySQL has built-in support for geospatial data and offers only about 80 functions related to spatial values, of which only about 30 functions perform any real analysis. These include operations such as Buffer, Intersection, and Union.

MSSQL and SQL Anywhere can offer more than 80 and 100 spatial operations, respectively. Both include analytic functions such as Union, Difference, calculation of lengths, distances and areas.

Regarding the three-dimensional aspect of geodata, MSSQL and SQL Anywhere offer one or two functions to check if the geometry is 3D. If necessary, they recommend adding the Z coordinate.

With PostgreSQL and Oracle, which offer about 300 functions, including 2D, 3D, MSSQL and SQL Anywhere, there are no significant geodata problems, and MySQL is only getting closer to competitors in this area.

Using programming languages

This feature helps a wide range of developers to complete several tasks in their native language.

PostgreSQL supports a wide range of programming languages, including C / C ++, Java, JavaScript, Net, R, Perl, Python, and Ruby. It is even possible to run custom code in separate processes, that is, work as background applications.

MySQL has support for server-side programming in one language, which is not extensible.

PostgreSQL has several features designed for extensibility. You can add new functions and index types. MySQL does not have extensibility support.

Administration and Monitoring Tools

Because databases are important software components, there are dozens of tools available for administering, monitoring, and troubleshooting, starting with command-line applications:

• mysql (MySQL, MariaDB).
• psql (PostgreSQL).

They are great for basic administration tasks because they are built-in with their servers and are always available. No additional installation required. Both of them have an available command history, so you can re-run previously executed queries and commands. They have a set of bult-in commands that can facilitate interaction with the database.

For example, psql has a d command to list all databases and mysql has a status command to get information, such as server version and uptime. The official MySQL 8 vs PostgreSQL graphical tools are also available:

• MySQL Workbench (MySQL, MariaDB).
• pgAdmin4 (PostgreSQL).
• For MySQL Workbench.

PostgreSQL seems to have more graphical tools available. If it is more convenient to use tools with graphical interfaces, instead of command-line applications, then you need to keep this in mind when making decisions.

User reviews

Experienced programmers believe that MySQL and PostgreSQL are two of the most popular open source RDMS programs on the market. For many years, they have successfully competed with commercial closed-source database programs. Each of them has gained a certain reputation, having its own strengths and weaknesses. Therefore, it is difficult for beginners to make independent choices, in this case it is better to use the advice of experienced developers. Experts write that both databases can be used with success, but you need to know for what cases to choose one or the other.

Here are some recommendations that developers share in their reviews:

• MySQL is perceived much faster, but offers fewer features. PostgreSQL is believed to have a deeper feature set.
• Some programmers find PostgreSQL similar to Oracle.
• The current releases of both products (MySQL 5.6 and PostgreSQL 9.2) have a large number of high-speed, powerful and dynamic functions.
• PostgreSQL has developed powerful features to improve performance. In recent releases, more than 70 new enhancements have been added.
• PostgreSQL is very strict in coding.
• MySQL is widely used as part of the LAMP stack of open source programs that form the backbone of many websites on the Internet.
• The main MySQL program does not have GUI tools for managing software or creating and maintaining databases.
• MySQL runs on many Windows, Linux, and Mac platforms.
• MySQL is less reliable. Since it is widely used on small sites, there are many add-ons, plug-ins, and modules for optimizing MySQL from popular programs such as Wordpress, Drupal, and Joomla.

conclusions

Surprisingly, it turns out that MySQL is best suited for online transactions, and PostgreSQL - for adding only analytic processes, such as a data warehouse.

The following is a summary table comparing MySQL and PostgreSQL:

, Postgres . Postgres, , . , MySQL " " Postgres, .