Discovering PostgreSQL Strengths

This article is about PostgreSQL Foreign Data Wrappers and other features that MySQL does not have.

I discovered PostgreSQL 9.4's foreign data wrapper functionality when I was reading its basic documentation on data definition language (DDL).

Reading the fine manual. RFTM. What a concept!

That reading was part of research into the features of PostgreSQL that would make it a superior RDBMS to MySQL. That research was part of a project to improve the data architecture of a large online job search website serving eastern Canada. That site used MySQL, among other data management tools, and was experiencing severe growing pains. It needed a lot of change.

It turns out that even elementary features of PostgreSQL, found in that early chapter of its manual, provide plenty of reason to avoid MySQL and use PostgreSQL. Some examples:

• Check constraints allow checking data values, and not merely data types, for reasonableness before they are added to a table. This is analogous to having assertions in code. MySQL does not support check constraints at all. Indeed, the MySQL community has ignored this feature request for more than 10 years.
• Schema support permits the logical grouping of tables, views, and other database artifacts (like sequences, data types, and functions). This can be used to avoid name conflicts within the same database or, more significantly, to assign permissions to a group of artifacts. In MySQL, 'CREATE SCHEMA' is synonymous with 'CREATE DATABASE'. In other words, MySQL does not have schema support.
• Table inheritance allows the creation of a table which includes the column definitions, data, check constraints, and not-null constraints of another table, plus new columns, data, and constraints. Single inheritance can be used to remove the necessity to duplicate data between tables via triggers or other methods. It can be used to ensure that several tables each have the same schema. Multiple inheritance is also supported. This enables even more powerful data models. For example, a table that inherits from many other tables effectively contains the union of the data in those tables; so, inheritance is useful for implementing various table partitioning schemes. MySQL does not support table inheritance in any way.
• Table partitioning with constraint exclusion. Table partitioning is a data structuring technique that addresses performance issues by using some criteria to decide into which table a given row is to be inserted. Then, a parent table (using single inheritance) is used to union the partitioned tables. The parent table can then be queried as if it contains all the data - as if the partition tables do not exist - creating a useful abstraction. Subsequently, constraint exclusion can be used as a query optimization technique; the query optimizer of PostgreSQL tries to find a subset of the partition tables to scan by using properties of the query to exclude as many of the partition tables as possible from the scan.
• Dependency tracking helps with consistency in data management. It is the practice of tracking the relationships between database objects, and then enabling reasonable actions when deleting those objects. For example, when dropping a view, the RDBMS provides warning that other views depend on the one being dropped. A 'CASCADE' option will allow the dependent views to be dropped automatically in that case. MySQL does not honour the CASCADE modifier on DROP VIEW, so does not support dependency tracking, generally.

Foreign Data Wrappers

In that same early chapter of the PostgreSQL manual, I found out about foreign data wrappers. A foreign data wrapper is a library that abstracts the details of connecting to a data source outside the instance of PostgreSQL. The foreign data wrapper takes care of the details of obtaining data from that source; it makes it seem like the data is in a database table which can be queried and updated as per usual.

This is especially significant because it provides an excellent way to perform a stepwise migration from MySQL to PostgreSQL. Such a stepwise migration can be performed by beginning by configuring a PostgreSQL server with a foreign server connection to the MySQL instance(s). Then, old code can continue accessing the MySQL instance if need be. New or changed code can access the PostgreSQL instance, possibly via the foreign tables. New or changed code can take advantage of newly added tables or views that wrap the functionality of the old tables in MySQL.

There are a variety of already-written foreign data wrappers. The PostgreSQL Extension Network (PGXN) points to a useful variety of foreign data wrappers. There are wrappers for MySQL (Github repo), MongoDB, CouchDB, Redis, AWS S3, Twitter, and more. Also see the PostgreSQL wiki page for foreign data wrappers.

Standard for Foreign Data Wrappers

Foreign data wrappers in PostgreSQL are an implementation of the SQL/MED standard for federated data management systems.

Leaky Abstraction

Using foreign data wrappers is not a replacement for data migration. It is a replacement for the application code that would be required to query data outside of PostgreSQL. Due to performance and concurrency issues (at least) the abstraction that the external data is inside of PostgreSQL leaks. This is because foreign data wrappers simply cause PostgreSQL to become a client of some server. FDWs do, however, make it very easy to create a materialized view inside of the PostgreSQL instance - a materialized view that depends on the external data.

Provided that data developers are aware that the abstraction leaks, foreign data wrappers can be an incredibly useful tool. They can save huge amount of error prone application-code writing.

Experience Reports

Some bloggers have documented their experiences with PostgreSQL foreign data wrappers.

• End Point (with the MySQL wrapper)
• Kartoza (with the MySQL wrapper)
• Dimitri Fontaine (migration to PostgreSQL from MySQL but found MySQL wrapper not useful)
• EnterpriseDB (with the MongoDB wrapper)
• Paul Tagliamonte (with the Docker FDW)
• Relational IO (entire company is based on PostgreSQL foreign data wrappers)

More Sources Of Information

WikiVS has a PostgreSQL versus MySQL comparison that has received 576,445 views at the time of this writing. I find it a balanced comparison.