PostgreSQL in Database Race

Introduction

Information technology is one of the key areas which keep changing drastically and we should be ready to accept these changes as per the moving trend. These changes will help enterprise organizations align with their current and future business needs.  As we know the current trend for most organizations, including small/medium/large businesses, is toward open source and cloud technologies.  Today we can find open-source offerings of almost any kind of software required to run a business of any size.

In any enterprise organization, database is one of the core components of the IT infrastructure and there are many database technologies available.  But it’s really difficult to decide which database should be used. In order to make this decision we should evaluate certain factors: budget, features, support, reliability, stability, manageability, scalability, availability etc.

PostgreSQL is one of the major open source database technologies that has been adopted by many organizations.

In this article we will see the major features that PostgreSQL offers in comparison with other enterprise databases. We will also cover the current position and adoption of PostgreSQL database technology.

Key features

To choose a new product in the market at very first stage we need to evaluate the product offerings, so in this section we will see what PostgreSQL offers to meet the requirements for running an enterprise application.

PostgreSQL is one of the reliable open source databases currently available and it has a very strong community. You don’t need a paid license to use this product; you can simply install and deploy it in a production environment.

Data Management Features:

–          Datatypes

–          Functions & aggregate functions

–          Operators

–          Procedural languages

–          Partitioning

–          Indexing

–          Foreign keys

–          Complex queries

–          JSON support

–          Updatable views

–          Triggers

–          Multiversion concurrency control (MVCC)

Data Availability:

–          Point-in-time recovery

–          Replication

Security:

–          Support encryption

–          Isolation of datasets (using pg_hba.conf & RBAC)

–          Supports both encrypted and non-encrypted connections

–          Auditing

Postgres Plus Advanced Server (PPAS/EDB):

The only concern across industry with using PostgreSQL is that it’s open source and thus does not offer a support agreement. Because of this some organizations are hesitant to use PostgreSQL in their mission-critical production environments. There is an enhanced version of open source PostgreSQL that runs on the PostgreSQL engine offered by EnterpriseDB (‘EDB’). It includes all the features in the open source version, plus enhanced options that are required by enterprises. EDB has two different versions available: Standard ,for which we don’t need a license, and EDB Postgres Advanced Server, which has a commercial license. EDB Postgres Advanced Server, also known as Postgres Plus Advanced Server (PPAS), offers technical support services based on Service Level Agreements (SLAs).

EDB/PPAS is recommended for use in critical production systems as it has better security, better performance, and better management when compared with the no cost version. On top of which, we can have a valid support contract agreement based on SLAs and open support cases to get issues fixed. This is not the case with the free version of PostgreSQL.

The illustration below shows the building blocks of Postgres Plus Advanced Server (PPAS). PostgreSQL is the foundation; the  additional features can help an enterprise run its mission-critical operations hassle free.

PPAS Security:

–          It has enhanced auditing capabilities when compared with the no-cost version. It has the capability of encrypting rows (VPD)

–          It has a SQL injection attack guard

–          It has an option for protecting server-side code

PPAS doesn’t support encryption at the database level like Oracle TDE Tablespace level; only row level encryption is supported.

PPAS Performance:

–          It has an enhanced version of table partitioning that facilitates significantly faster writes and selects

–          It has index advisors that speed up queries

–          It provides SQL advisors for faster query response time

–          Supports Hints in queries for better optimizer control

–          It provides faster bulk data loads

–          It supports dynamic tuning of database servers with a DYNATUNE option

–          It supports dynamic runtime statistics

PPAS Management Tools:

–          EDB Postgres Enterprise Manager (PEM)

–          EDB Postgres Backup and Recovery

–          EDB Postgres Failover Manager

–          EDB Postgres Replication Server

–          EDB Postgres Migration Assessment

–          EDBPostgres Migration Tool Kit

–          EDB Postgres Data Adapters

PPAS Support & training:

–          EDB/PPAS provides 24 X7 x365 support based on the service level agreements and support type.

–          EDB offers multiple courses from basic entry level to advanced in both developer and administrator tracks.

Data Size and Platforms Support in PostgreSQL and PPAS:

Customer business case: 

One of our customers was the largest stock exchange in the Middle East and North Africa and their core trading application was running on Oracle Database 11gR2. There were 150+ databases (including PROD/UAT/SIT/DEV/TEST/TRAINING) running on 40 physical machines and the annual support cost was really huge. The customer started looking for other reliable databases and conducted POC’s for migration/load testing/scalability/availability. Initially, based on results and price, they decided to go with open source PostgreSQL in collaboration with another third party vendor. But due to certain limitations on security and support constraints they opted for EnterpriseDB (EDB/PPAS) and migrated all core trading databases from Oracle to PPAS within three months. 

There was 80% reduction in annual operational and maintenance costs when compared with Oracle. The PPAS is able to meet the customer service level agreement in all aspects.

Conclusion

Undoubtedly PostgreSQL is leader in the open source database market; there is a continuous uptrend in the adoption rate of PostgreSQL across different business verticals. PostgreSQL supports and can run on multiple platforms, whereas EDB/PPAS can run on certain specific versions of Windows and Linux.  PostgreSQL cannot be fully compared Oracle as it doesn’t provide many key features that Oracle provides, such as Real Application Cluster, Data Guard, Audit/Database Vault, TDE, Maximum Availability Architecture (MAA), Database Editions etc.  Oracle is far advance with respect to availability, scalability, and manageability in my personal opinion.

There are many customers who are using Oracle Enterprise edition licenses and not using many of the advanced features that are part of their licenses; they are using database engines for simple OLTP systems. Such customers can use PostgreSQL, which will offer a good return on investment for their organization.