I still remember when I heard PostgreSQL for the first time. It was around 2001, when my former room mate mentioned that he was a maintainer for PostgreSQL on Debian. At that time, I was still studying electrical engineering, so database systems were no concern to me. But I knew something was there and even used PostgreSQL for some projects, professional and private. Today I held a presentation for my fellow developers at wer denkt was, comparing PostgreSQL with MySQL.
Over the last years, both systems have added lots of functionality and became much closer with respect to features. The Dumper Blog did a comparison of MySQL 8 and PostgreSQL 10 in 2018. While MySQL added common table expressions and window functions and improved JSON support and geospatial features, PostgreSQL introduced logical replication, semi-synchronous replication and declarative partitioning.
In this blog, I will highlight the remaining differences between the two most widely used open-source SQL implementations.
One thing only PostgreSQL can do is table inheritance, like in object-oriented programming languages.
-- normal city CREATE TABLE city ( id SERIAL PRIMARY KEY, name text, population float, area float ); -- capital, inherits from city table CREATE TABLE capital ( country_id REFERENCES country(id) ) INHERITS (city);
A view in SQL is a saved query which can be used like a table.
-- normal view CREATE VIEW all_city_view AS SELECT * FROM city; -- materialized view CREATE MATERIALIZED VIEW all_city_matview AS SELECT * FROM city; -- refresh materialized view REFRESH MATERIALIZED VIEW all_city_matview;
Full outer joins
A full outer join can also be useful for some special kind of queries. MySQL does not support this, while PostgreSQL does.
-- first table CREATE TABLE departments ( department_id serial PRIMARY KEY, department_name varchar(255) NOT NULL ); -- second table CREATE TABLE employees ( employee_id serial PRIMARY KEY, employee_name varchar(255), department_id integer ); -- add data to tables INSERT INTO departments (department_name) VALUES ('Sales'), ('Marketing'), ('HR'), ('IT'), ('Production'); INSERT INTO employees (employee_name, department_id) VALUES ('Bette Nicholson', 1), ('Christian Gable', 1), ('Joe Swank', 2), ('Fred Costner', 3), ('Sandra Kilmer', 4), ('Julia Mcqueen', NULL); -- perform full outer join SELECT employee_name, department_name FROM employees e FULL OUTER JOIN departments d ON d.department_id = e.department_id;
One major difference is the naming of data types. To convert safely, refer to MySQL to PostgreSQL Types Mapping. This is a major obstacle when porting SQL scripts, better use a tool like pgloader which has built-in conversion logic for a variety of input formats.
This is a crucial functionality for the AppJobber application, which allows people to see all available micro jobs in their selected map range. So this runs down to selecting entries from a large table within a bounding box. While MySQL has built-in support for geospatial data types, PostgreSQL uses the PostGIS extension. The Boston Geographic Information Systems did a detailed comparison of geospatial features between MS SQL Server, MySQL and PostgreSQL/PostGIS, which seems a bit outdated.
So I was running a sample bounding-box query on all available jobs, using a bounding box around Darmstadt. It took MySQL roughly a second to complete the query, PostgreSQL did it in less then 400 ms. However, the two queries were a bit different, as MySQL had location data readily stored as blob and PostgreSQL used a converted geometric point type.
There is ample discussion about performance, most favoring MySQL for read-heavy loads and PostgreSQL for read/write-heavy applications. So this depends very much on the application in question.
After all, I hoped to show that PostgreSQL is a viable alternative to MySQL with more advanced features.