PostgreSQL Database for Enterprises – Part I

In today’s brave new world, data and information of all sorts are rapidly proliferating. Many companies who have used traditional databases such as the Oracle database or Microsoft SQL Server for their data requirements since the 1990s have now started to explore new and different databases – primarily because their database license costs have been spiraling over the years, with more database servers and more CPU core licenses being required.

One strong database contender to replace the traditional large corporate database is PostgreSQL (also simply called Postgres, as a nickname of sorts). It has a double advantage in that it is provided as an open source community edition, and it is compatible with the Oracle database. This is quite attractive to companies who have been using Oracle databases, since some of their applications may install a lot of database-level code in the form of PL/SQL procedures, functions, and packages, and if the database-level code can be moved to an Oracle-compatible database, well and good. Of course, there are some applications created by Oracle such as Oracle E-Business Suite (EBS) that cannot be moved to other databases like PostgreSQL, due to their deep integration with the Oracle database.

In this article series, we will explore why and how enterprises are starting to use PostgreSQL. In particular, we will look at enterprise-powered versions of the PostgreSQL database, such as EnterpriseDB Postgres Advanced Server (EDBPAS), that are even more closely compatible with Oracle and make it easier to start using this low-cost database, either on premises or to the cloud. In the coming parts of this series, we will look more closely at the features of EDBPAS and explore installation and other features.

First, let us recap a few basic facts about PostgreSQL. Essentially, it is an open-source object-relational database that is more than 20 years old, and was inspired by the white paper "A Relational Model of Data for Large Shared Data Banks" by Edgar Frank Codd; the same paper that also influenced Oracle to create its initial relational database management system (RDBMS). PostgreSQL is owned by the Postgres foundation, and has an open source license. Because the database code is open source it can be forked and modified. It runs on the major operating systems – Linux, UNIX, and Windows. UNIX variants that can be used are IBM AIX, HP-UX, Solaris, Tru64, BSD, SGI IRIX, and macOS. The database includes most ANSI-SQL:2008 data types, and supports many of the features of the SQL:2011 standard.

PostgreSQL is supported by a large international community, mainly in postgresql.org and other countries in the world. The PostgreSQL Global Development Group regularly releases updates. On 21st September 2017, PostgreSQL 10 RC 1 was released, which is the first release candidate of the new version 10 of PostgreSQL. The database is ACID (Atomicity, Consistency, Isolation, Durability) compliant, object-oriented, and with geospatial features. There are some production PostgreSQL databases worldwide that have more than 4TB of data.

PostgreSQL, even in its open source version, has sophisticated capabilities suitable for large enterprises. One example is Multi-Version Concurrency Control (MVCC). This feature enables concurrent access to the database and avoids table locks when data is being updated by database writer sessions, with database reader sessions being able to read the previous version of the data at the same time. MVCC thus allows the database to scale to a large number of concurrent sessions without succumbing to contention between readers and writers.

As another example of its enterprise-level features, the PostgreSQL database performs Write Ahead Logging (WAL) to achieve fault tolerance and write efficiency. Due to MVCC and WAL, the PostgreSQL database can also enable online (hot) backups. The WAL feature also makes Point in Time Recovery (PITR) possible from a database backup along with the WAL log, and also enables streaming replication (asynchronous by default) between the primary and standby databases.

Nested transactions can be performed via database savepoints, and the database also boasts a powerful internal query planner (optimizer). PostgreSQL is a database that is considered to be “compatible” with the Oracle database, one reason being its own PL/SQL-compatible language. Stored procedures can be created in 12 languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and PL/pgSQL, which is like Oracle’s PL/SQL. Tablespaces can be created, and the database allows partitioning to manage large tables effectively and with better performance, with a maximum table size of 32TB.

The database allows datatypes such as varchar, integer, numeric, XML, and JSON/B. The database can also enforce referential integrity with foreign keys. There are out-of-the-box functions such as to_char, to_date, and constraints such as check, not null, primary. Foreign Data Wrappers (FDW) can be used to connect to external data sources, similar to the database links available in Oracle, except that PostgreSQL can connect to different databases such as MongoDB.

User Defined Functions (UDF) can be created in  open source PostgreSQL, but an important consideration is that there are no packages (stored collections of procedures and functions) like those in the Oracle Database, and, as for reports, this has stopped a number of Oracle Database users from moving to the open source version of PostgreSQL. However, the EnterpriseDB Postgres Advanced Server distribution does allow the creation of database packages.

Now, let’s consider a large cloud company and its database offerings. Many of us are aware of Amazon AWS and the Relational Database Service (Amazon RDS) that it offers to customers around the world. Amazon allows the setup of multiple third-party databases in RDS (such as MySQL, Oracle, SQL Server, PostgreSQL, and MariaDB). Amazon’s own Aurora database was released initially with MySQL compatibility, followed by a PostgreSQL-compatible version in late 2016.

As per Amazon, this was due to customer demand and interest in PostgreSQL along with AWS optimizations for the cloud. The idea was to allow this database as an option for new greenfield deployments, or even migration from other existing databases in the company such as Oracle or SQL Server. Amazon now offers Aurora as PostgreSQL 9.6 on the Amazon Aurora cloud-optimized storage.

But what are the benefits of EnterpriseDB Postgres Advanced Server (EDBPAS) as compared to the Aurora version? We continue in the next part of this article series, when we will look more closely at the features of EDBPAS, and then explore the installation and other aspects of this distribution, which is fast becoming popular around the world.