Despite all of the hype about NoSQL databases, MySQL and PostgreSQL are two database management systems that have truly stood the test of time. These systems were developed back in the mid 90’s, and they still consistently rank among the top 5 most popular databases. So why are they so widely used and how do they compare? Read on to find out!
What is MySQL?
The first version of MySQL was released in 1995, and it continues to be the standard choice for both beginner and advanced developers. It is a traditional relational database and uses tables as its core structure. MySQL is known for its performance in read-heavy workloads, though it fizzes out during concurrent write operations.
What is PostgreSQL?
Although Postgres is just a year older than MySQL, it’s advertised as “the most advanced open-source relational database in the world.” It is an object-relational database, which makes it easier to work with in modern web frameworks. In addition, its heavy emphasis on safety and security inspired unique features such as transaction snapshots, asynchronous notifications, and triggers.
Key Differences between MySQL and PostgreSQL
In most real-world scenarios, MySQL and PostgreSQL have comparable performance, though in applications that are more read-heavy, MySQL outperforms PostgreSQL by a wide margin. As a general rule of thumb, MySQL is better for website and transactional traffic, while PostgreSQL is great for analytical and concurrent processes. These differences stem from the ways in which they were originally built. Read on for a deep-dive into the critical distinctions between them.
|
MySQL |
PostgreSQL Database Architecture |
Relational Database Management System |
Object-Relational Database Management System License |
Open Source (maintained by Oracle) |
Open Source (maintained by volunteers) Performance |
Great at read-heavy processes |
Great for concurrent write operations NoSQL support |
Has recently released support for NoSQL |
Has lots of support for NoSQL and more data types Replication |
One-way synchronous replication |
Synchronous replication Security |
TLS support |
Native SSL support |
A brief note on licensing and ownership:
MariaDB is a popular alternative to MySQL. It was built by the original developers of MySQL and is guaranteed to stay open source (unlike MySQL, which is maintained by Oracle although it is open source). It’s used by the likes of Wikipedia, Google, NASA, and Tesla! Since MariaDB is a fork of MySQL, you do not have to make any major changes, and you can easily port over your existing sources and data.
PostgreSQL and MySQL Performance
Postgres inherently performs better due to its concurrent support for write operations without the need for read/write locks. It is also fully ACID compliant and implements transaction isolation and snapshots. On the other hand, MySQL tries to achieve concurrency with the use of write locks. This essentially reduces the number of concurrent operations per process. In order for MySQL to scale in production environments, you will have to add a considerable amount of resources.
One area where MySQL excels, however, is in read-heavy operations. Since most applications only read and display information from a database, there have been many times when a simple MySQL implementation far outperformed a PostgreSQL implementation. This is because Postgres allocates a significant amount of memory (about 10MB) when it forks a new process for each connection. This causes bloated memory usage and effectively eats away at speed. Thus, it sacrifices speed for data integrity and standards compliance. For a simple implementation, then, Postgres would be a poor choice!
PostgreSQL vs MySQL Database Structure
MySQL is a relational database management system (RDBMS), whereas Postgres is an object-relational database management system. This allows Postgres to define objects and inherit tables, paving the way for more complex structures. This comes at a cost, however, because it requires additional computation to build object oriented, or OO-based, structures.
PostgreSQL vs MySQL Tables
MySQL Table Inheritance
It’s difficult to reuse or build upon tables in a MySQL database. Relationships are forged loosely with the help of foreign and primary keys.
PostgreSQL Table Inheritance
In a PostgreSQL environment, however, tables can be inherited and extended. Explain what we mean by inherited/extended which should move you into what a child/parent table is.
When a child table is created in Postgres, the parent tables are merged in name and type. They are also bound together so that future modifications to parent tables cascade to all of the child tables. However, if the child table has a column with the same name and type, then the operation will fail. This bond also extends to querying: when a table is queried, all of its child tables are queried as well.
MySQL Show Tables
With MySQL it’s easy to display all tables. You can simply use the “SHOW TABLES” statement to quickly display all tables in a specific database/
PostgreSQL Show Tables
With Posgres, you can show tables in two different ways. One method is to use the “SELECT” function and query data with the below:
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != ‘pg_catalog’ AND
schemaname != ‘information_schema’;
Another example, is to:
-
Connect using the psql tool
-
Then use the postgres user to login and the enter the password to connect
-
Next, you’ll want to connect to a database
-
Then, you’ll want to use dt command to show all tables in the database you selected.
PostgreSQL vs MySQL Indexes
Relational databases use indexes to speed up queries when dealing with large amounts of data. This allows databases to find a particular row without the need to cycle through all of the available data. Indexes essentially function as lookup tables.
Indexes add considerable overhead, however, and they should be used sensibly.
Traditionally, indexes perform reverse lookups and search tables against one or multiple columns.
MySQL Indexes
In MySQL, indexes are stored in B-Trees such as INDEX, FULLTEXT, PRIMARY KEY, and UNIQUE.
PostgreSQL Indexes
With PostgreSQL, you can create an expression index. This is an index that works on the result of a function or expression rather than just the value of a column. It also supports partial indexing, where only a part of the table is indexed.
PostgreSQL vs MySQL Syntax
Despite all of the differences between MySQL and PostgreSQL, their syntax is refreshingly similar – they’re both based on the SQL standard, after all! In fact, it’s so similar that I could just copy the MySQL query to a Postgres environment at times – with no changes.
Selecting Records in Postgres and MySQL
PostgreSQL query:
SELECT * FROM users;
MySQL query:
SELECT * FROM users;
Inserting Records in both PostgreSQL and MySQL
PostgreSQL query:
INSERT INTO users(id, name, email) VALUES (1, ‘Swaathi’, ‘swaathi@example.com’);
MySQL query:
INSERT INTO users(id, name, email) VALUES (1, ‘Swaathi’, ‘swaathi@example.com’);
Updating Records in Postgres and MySQL
PostgreSQL query:
UPDATE users SET segment = “main” WHERE age > 18;
MySQL query:
UPDATE users SET segment = “main” WHERE age > 18;
Looks the same, right? It does – but there are a few differences.
Difference Between PostgreSQL and MySQL Case Sensitivity
MySQL is not case sensitive, but Postgres is; therefore, you don’t need to capitalize column names as they appear in the database when writing MySQL queries, but if you don’t capitalize them in Postgres, it will cause a failure.
Default Character Sets and Strings
One of the most annoying things about MySQL is it’s collation and encoding properties. If they are not set appropriately, it could cause data inconsistencies. In certain versions of MySQL, it is necessary to convert strings to UTF-8 encoding before being parsed.
However, you don’t need to worry about it in Postgres!
Replication and Clustering
Replication enables you to have multiple copies of the data copied automatically from ‘master’ to ‘slave’ databases.
Clustering refers to combining multiple copies of databases together and providing a single IP address for the client to read. When a read or write request comes in, the nodes in the cluster decide among themselves who can optimally serve the client.
PostgreSQL Replication
PostgreSQL supports “synchronous replication,” where two database instances run simultaneously and the master database is synchronized with a slave database at the same time. Due to its concurrent nature, read and write operations can happen simultaneously. With synchronous replication, however, each write waits for confirmation to be received from both master and slave.
MySQL Replication
In MySQL, replication is “one-way asynchronous.” Here, one server acts as a master while the others are slaves. In this case, the goal is not to ensure continuity of the data, but rather, the entire database, including selected tables or even selected columns within tables.
PostgreSQL and MySQL Performance Monitoring
Despite all of the great things about both MySQL and PostgreSQL, neither of them have a useful log monitoring system. It’s quite painful to extract actionable information out of the built-in logs. Health data about your database performance is crucial for scaling up. This is where SumoLogic can help you out.
SumoLogic’s log monitoring system works on both MySQL and PostgreSQL to extract valuable information about performance and efficiency. It enables you to identify slow performing queries, search by error types in the logs, and collect metrics.
Try it out today!
Conclusion: PostgreSQL vs MySQL
At the end of the day, MySQL and PostgreSQL are both great options. Depending on the purpose of your application, however, one may be preferable to the other:
Do you need to build complex queries and relationships that need to be frequently updated and consistently maintained? PostgreSQL might be the better option.
Do you just need a simple and reliable database that gets the job done? MySQL could be the one for you.
Another factor to consider is the availability of community support. Despite being created around the same time, MySQL has skyrocketed in usage and has become the default tool for building applications. PostgreSQL is still catching up to MySQL’s popularity, which means that fewer developers are working with it and fewer third-party applications are available. However, with the increasing need for highly concurrent services that are always available, Postgres might soon overtake MySQL as the most popular database tool.
If you’re still not sure which one to choose, here is a final piece of advice: “Nobody was ever fired for buying IBM!”
