Reskilling to PostgreSQL: How to Navigate the Challenges

The database landscape is constantly evolving. Proprietary software has long been the obvious choice for large organisations, but according to GitHub BCG Analysis: 99% of Fortune 500 companies are currently using open-source software. If you’re a DBA with years of experience in technology that isn’t open-source and want to acquire some new skills - why not consider adding PostgreSQL to your list of skills? Our team of PostgreSQL experts (many of whom have reskilled from another speciality) have put together a list of some of the challenges you may encounter on your reskilling journey. In this blog, we’ll share those challenges and shine some light on how to navigate these uncharted waters.

The journey of reskilling begins with a fundamental shift in the terminology that is used. In Oracle, a "Cluster" typically refers to a Real Application Cluster (RAC) with multiple nodes for read/write activities. PostgreSQL, on the other hand, operates differently, employing one master for read/write operations and potentially multiple read-only copies synchronised by a process. This subtle but significant difference can be a source of confusion for those transitioning. Furthermore, PostgreSQL's security model differs from its robust Oracle counterpart. In PostgreSQL, roles can be inherited, and developers/users set permissions, but there's no equivalent of the all-powerful "sys" or "system" user that can override all user roles and privileges. In some instances, ownership is a prerequisite for altering or dropping roles or assigned privileges, adding complexity to user management.

PostgreSQL uses a unique data storage mechanism known as Multiversion Concurrency Control (MVCC), which manages deleted and updated rows. There is also a vacuum process that is responsible for cleaning up these unused invisible rows on a page. Understanding this intricate process and its potential impact on a system if not properly configured and maintained is a challenge in itself.

One of the most formidable challenges when transitioning to PostgreSQL is learning how to troubleshoot issues effectively. Understanding the PostgreSQL data dictionary, locating essential information for troubleshooting, and grasping the limitations and the lack of comprehensive logging information can be daunting. In contrast to Oracle, where the Automatic Workload Repository (AWR) provides readily available information, PostgreSQL relies on enabling extensions and configuring logging parameters to obtain data for troubleshooting and environment analysis.

Unlike Oracle, PostgreSQL lacks a complete end-to-end monitoring tool or real performance reports out of the box. With limited budgets and an open-source ethos, the focus is primarily on the database engine itself, leaving room for numerous third-party solutions (whether open-source or licensed). This abundance of monitoring tools can present a challenge for PostgreSQL newbies, as choosing the right one can be overwhelming. For the most part, you’ll end up having more than one tool and are still lacking enough to do it properly.

The backup and restore mechanisms in PostgreSQL differ significantly from those of other databases. The open-source nature of PostgreSQL has given rise to a variety of individuals and companies offering backup solutions, both open-source and licensed. The default PostgreSQL tools, PGdump and pg_basebackup, make recovery straightforward by replacing directories and sub-directories alongside associated Write-Ahead Logs (WAL) files.

In Oracle, almost all features are available with the installation or as separately licensed add-ons. In PostgreSQL, the majority of additional features are implemented as extensions, which must be enabled on an instance level and then configured per specific database that is required to use this type of extension.

When you run into issues on most database platforms, you can simply reach out to the dedicated support team for assistance. However, finding reliable support for PostgreSQL can be a challenge. Because the platform is open-source, there is no official support for the product. Finding information with regard to bug fixes, special requests, expert opinions, and troubleshooting assistance is extremely challenging.

In comparison to many of its competitors, PostgreSQL is in its infancy. In more mature database engines, such as Oracle or Microsoft, partitioning is thought through and is easy to use, configure and maintain. However, PostgreSQL’s lack of global indexes make it difficult to maintain uniqueness over a table with a single primary key. Workarounds are available, but they are not ideal and not optimal.

Reskilling to PostgreSQL is a journey that demands dedication and adaptability. The biggest challenge often lies in troubleshooting issues, as familiar routines and tools need to be replaced with new approaches. For example, you may have a tried and tested sequence of checks and actions that could troubleshoot Oracle from any angle. But with all the differences in PostgreSQL, you’ll need to embark on a new adventure, starting from scratch to build a new package of tools and scripts. Our team did courses and read loads of blogs and documents to get an overview of the platform. Our advice is to build test environments - break them and rebuild them. Learn by doing; sit in sessions with your team members when they troubleshoot and take notes. Challenge each other to find out more and then share as much as you can. For us, reskilling to PostgreSQL didn’t make our lives easier or more complicated. It challenged us in a way that was needed and drove us to find out more. It forced us to go back to square one and start from scratch. It forced us out of our comfort zone and enabled us to build a new one.

Related reading: Oracle to PostgreSQL Migration: What Actually Takes the Time · PostgreSQL Support in South Africa: Running Postgres in Production Without the Guesswork · Oracle Database Support in South Africa: Keeping Licensed Estates Healthy and Compliant