Technical Technical — Postgres Monday 17th May 2021

6 Reasons to Migrate From Oracle to PostgreSQL

By Kirk Roybal

The role of PostgreSQL in the market is as a post-object relational database management system. PostgreSQL has been available in the market for more than 25 years. In this time, it has grown to be a mature offering for data services. These services are produced by thousands of developers across the world.

In this time, PostgreSQL has become a more than worthy replacement for Oracle. Here are six reasons why I love PostgreSQL so much, and why you should consider migrating to PostgreSQL if you’re currently using Oracle. 

Download resource: Migrating Oracle to PostgreSQL® with Features and Benefits

Ease of Getting Started

The PostgreSQL documentation is fantastic. I say that without any hint of sarcasm or humility. Anything that can’t be found directly in the official documentation is available on public forums and communication platforms. There is a very high standard of documentation required for the developers to submit patches to the PostgreSQL project, and those submissions are used to produce the documentation on the website. Nobody is hoarding any proprietary knowledge about the community edition of PostgreSQL—you are an equal member of the community. Maybe even better, since you’re new you’re likely to get a lot of individual attention! And if that isn’t enough, you can always just read the code and the comments in it.

A PostgreSQL database is easy to set up, and you’ll find yourself making instances where you would never have bothered with Oracle. For instance, you might want to make sandboxes for every change request moving through a development/design process. Why not have one for design integration before QA, and yet another one for QA? Want several versions of PostgreSQL to test a feature? Sure, install all of them. On the same machine, or not on the same machine. Make another one just to test deployment of your changes. Throw them all away and make new ones tomorrow. 

Licensing and Ecosystem

The PostgreSQL License (similar to a BSD License) is one of the most liberal in the open source community. You can do pretty much anything with it, except wrap it in quotes and claim you said it. 

Attribution to the PostgreSQL Global Development Group is a requirement for all submissions, so anything that goes into the project goes directly into your installation with no hassle. This is another form of freedom that allows you to participate fully in all improvements with no additional licensing costs. Parallel query? Free to unlimited nodes. Partitioning? Free. See something you like? Yeah, that’s free too.

Who do you call? Well, everybody. There are a lot of people behind the PostgreSQL project.  Probably many more than you’d expect. If you want to know exactly how a Btree index works, just call the person who wrote it. He will explain to you in excruciating detail exactly what it does and why it does it that way. Almost everybody who has worked on PostgreSQL in any significant way is still available somewhere in the community, and perfectly willing and sometimes eager to tell you about it.

True Open Source

Because PostgreSQL is a true open source solution, this gives you flexibility and portability. If you’re managing it in-house and your lead developer quits, it’s easy to bring on vendor support ASAP. Or if you have a vendor supporting you and don’t like them, you can switch to another without a lot of hassle. As opposed to proprietary solutions (like Oracle), you won’t get locked in with ever-escalating license fees. 

The organization of the PostgreSQL global development group ensures that this will continue to be the case for many years to come. It is made up of selected members of the community that truly understand open source in its own right. These members are intentionally drawn from several commercial and academic institutions, not a single source.   This ensures that there is actually no way that PostgreSQL will ever be available for purchase.

Read Now: Understanding Open Source Business Models

Similarity to Oracle

PostgreSQL has basic Oracle similarities that are baked into the product. Indexes, functions, structure, and operational tasks are all very similar to Oracle. PostgreSQL also has extensions available to make the process even easier and more Oracle-esque. The underpinnings of the data storage strategy (multi-version concurrency control) are based on the same idea also, so transactions and disk management behave, mostly, the way you would expect them to.

Extensibility

PostgreSQL has two facilities for the extension. The first is called “extensions” which can be produced by anyone and does not require any modification of the underlying PostgreSQL source code. They are packaged independently of PostgreSQL. A package manager to deploy them is available on the most popular platforms. For details, check the PGXN website

The other way to extend PostgreSQL is by contributing directly to the PostgreSQL project. Contributors are welcome and very sought after. If you think your addition to the product is compelling, please get in touch with the PostgreSQL Global Development Group (PGDG) by subscribing to the pgsql-hackers mailing list on the postgresql.org website.

PostGIS

The PostGIS community discovered the delights of PostgreSQL quite a while ago. Because PostgreSQL is extensible, two (or more) floats can be grouped together into your own custom data type making a point. Two point data types grouped together is a line. Add more points and you get a polyline. Imagine that the line is a closed loop, and you have a polygon. Now imagine that the polygon is three-dimensional, and filled (or not). Got all that? Okay, now add your own custom functions for distance, proximity, overlap, etc. Now just use SQL to do some pretty amazing queries with a relatively simple syntax for all that power. 

PostGIS is so powerful that it has taken the GIS community by storm. There are arguably more participants in the PostGIS project than in the PostgreSQL community.

Oracle to PostgreSQL Migration

PostgreSQL is a powerful and versatile database management system. While it’s not perfect at any one thing, it can do many different things reasonably well. This makes it flexible enough to work even if you know your long-term goals might evolve. If you’re currently on Oracle and considering making a change, PostgreSQL is more than worthy of evaluation.

Also Read: Migrating Oracle to PostgreSQL® with Features and Benefits