PostgreSQL has quite a few compelling advantages, some of which are not found in any other database management system. This article will outline the ones that are most interesting to the Oracle developer seeking to do a migration or investigate alternatives for new development.
To prevent this article from becoming another list of PostgreSQL features, it will stick to the features that differentiate it from Oracle specifically, and the advantages that those differences create.
Federation vs. Foreign Data Wrappers
Oracle has a feature called Federation that allows tables from other databases to be treated as local data. PostgreSQL kicks this up to an 11 with foreign data wrappers. It is fantastically superior to the federation system, can be hooked to anything that remotely resembles data, and costs nothing in fees. This makes PostgreSQL a great ETL platform, even if all you use it for is the data ingestion.
plSQL vs. everything else
Oracle has a built-in programming language called plSQL. PostgreSQL has somewhere around 80 or so. Really. The extension system of PostgreSQL is used to create procedural languages as plug-ins, and there are bindings for just about any language you can think of. And if there isn’t, you can always follow along with Mark Wong for how to make a wrapper for your personal favorite.
Application programming
Oracle provides an application API to communicate with the database. PostgreSQL also provides an API for convenience and trusted languages. However, the product is open source, so there are no second class development citizens. You can access anything that PostgreSQL has to offer by including the header file in your project. Do whatever you want with it.
Internationalization and Localization
Oracle provides a globalization toolkit. PostgreSQL is built from the ground up to rely completely on well-known and widely compatible system services for localization. It can use any character encoding, collation and code page that the operating system provides.
Web Development
Oracle acknowledges the existence of HTML through HTML DB. PostgreSQL natively supports JSON, XML and plugs in Javascript as a back end coding language, which you may use at the same time as Java or any other front end language of your choice.
Authentication
Oracle has a built-in authentication system (a nod here to Proxy Auth, which is a bit more flexible). PostgreSQL relies on the Host Based Authentication and SASL protocols to plug in any authentication system that the host can support, and a few that plug directly into PostgreSQL. This provides an enormous array of possibilities for authentication along with the potential to offload the authentication process onto other machines.
Extensibility
Oracle has a plug-in system with mostly proprietary plug-ins. PostgreSQL has an extension system that is supported by the general community, with thousands of available plug-ins.
Read Scalability
Oracle has fairly good vertical read scalability. PostgreSQL can create a virtually unlimited read cluster. The number of nodes is limited only by the amount of resources you would like to throw at it.
Cost
It’s hard to beat “free”, and Oracle doesn’t even try hard. Let’s face it, Oracle is just ridiculously expensive, and they don’t mind charging you again for every single instance.
This is not a linear comparison, either. One of the biggest advantages of PostgreSQL is that you can have all of the instances that you want, with no additional cost. (I guess it is an additional cost of +$0.00, or multiply by $0.00, you decide).
It’s not fair (to PostgreSQL) to compare the cost of a single instance of Oracle to a single instance of PostgreSQL. Once you taste the freedom of free, it will be hard to go back to jamming everything into a single instance just to reduce costs.
How much should a temporary database cost that transforms data on the way to the warehouse? I think free is about enough. How about a transient database for reporting? I’ll take free for that as well. How about one that is the data ingestion point for ETL? Free is good. I like free. It’s very, uh…freeing.
Performance
No, wait! Hear me out on this one. I’ve already mentioned that PostgreSQL can create an unlimited number of nodes in a read cluster. That can dilute the cost of any particular read operation to near zero. But there’s also another way the PostgreSQL is more performant than Oracle.
Because PostgreSQL costs nothing per node, you can tune it differently for every work load. Of course, you can do this with Oracle also, but you’ll be paying per node for the ability to tune it this way. So, if you want to differentiate the tuning parameters for your warehouse to OLTP to reporting to the data lake, PostgreSQL makes this fairly easy, and still be easy on the wallet.
Of course there are a lot more compelling features in PostgreSQL, but I already wrote that article a few months ago. Let me know in the comments if you have migrated to PostgreSQL for any other reason.