The foundation of relational databases began with an experimental system. The core of that system was the basis of the System R research. Today this research remains the key functionality of modern database technology. The need for concurrency control and scalability built and maintained by the power of the SQL language exists among the most popular databases of choice, such as MySQL, Oracle, SQL Server, and the popular attention-seeker PostgreSQL. It’s not often that you combine open-source and closed proprietary source databases in the same sentence, especially when you think about the giant or the champion in the database realm known as Oracle. But the contender, PostgreSQL, has a lot of similarities to the champion. In both cases they are ACID compliant with full transactional logging capabilities. When you add the EDB Postgres Advanced Server database, the gap between the champion and the contender starts to close even more. Exploring the quadrants, Gartner appears to recognize that this gap may be closing in the relational database arena.
Comparing the two technologies, we look at Oracle Enterprise Edition, including tools, and the EDB Postgres Advanced Server, including tools. The comparison allows an Oracle DBA to make an easier transition to the PostgreSQL environment. At first glance you can immediately notice some overall similarities, especially when it comes to SQL capabilities and application development. It’s intriguing to think of being able to execute familiar SQL or PL/SQL syntax directly in a PostgreSQL database.
Understanding the terminology can often be confusing if you have been accustomed to Oracle for a number of years.
The perfect database may not really exist, both databases have some unique options that will cause a DBA to have nightmares:
- Unlimited database size
- Unlimited rows per table
- Unlimited indexes per table
Unlimited nightmares are true options, showing the flexibility and control you can have within the database versus the restrictions within Oracle. The restrictions are for a good cause, so the unlimited options are possible with PostgreSQL but definitely not advised.
Capacity is almost equal between the technologies when you consider options for creating a stable database that you can maintain. The variation of the columns has a range for PostgreSQL due to the different data types being used.
Tables and partitions are quite similar, and both are feature-rich. There have been some great improvements in recent releases of PostgreSQL with the addition of declarative partitioning and features allowing partitions to be created on multiple columns. Temporary tables are always useful, but the concept of global temporary tables does not exist in PostgreSQL.
Data types can be the heart and soul of your database. The flexibility within a data model is key to supporting an application. It’s known that an Oracle database can lend its flexibility to support everything from OLTP to data warehouses. This is also true for PostgreSQL, which also offers an extra layer of flexibility, allowing you to combine the likes of a NoSQL database with the flair of a relational database. The addition of the JSON and JSONB data types makes this possible.
Spatial data in conjunction with blob capabilities can be a necessity in the world of storing maps and navigational system– related databases. PostGIS is an easily configurable extension built to handle spatial data.
There is a common misconception that PostgreSQL can’t handle blobs or clob data. The underlying community PostgreSQL has the data type of bytea, which indeed handles binary data. Advanced Server adds the additional blob/clob data types, which will allow data to exist in the same format as Oracle.
Indexing options are quite similar between the two technologies for standard database options. When thinking of a relational database, you do not typically think about the ability to store documents or complete full-text searches. This could be one advantage where PostgreSQL offers the options of GIST and GIN, allowing you to speed up full-text searches built with the same technology of inverted indices as Elasticsearch. GIN can also be smaller than a B-tree index after creation, offering GIN as a possible substitute for B-tree indexes.
In the Oracle world, we are used to transactions being implicit. For example, a new table creation will do a commit internally. The concept of rollback is not available in this scenario. Within PostgreSQL you have the ability to create transactional DDL, which can include DDL and DML. Transactional DDL will allow everything in your script to roll back if there is a failure. Rerunning a clean process can often be beneficial.
If we make a guess, we may say that 50% of all SQL scripts written to support application code will use the SYSDATE or ROWNUM. Common SQL extensions and DBA favorites such as the mysterious DUAL table are also present.
Comparing the two technologies often confuses most techies that have been working in one particular industry. Aside from the terminology, some overall concepts can be confusing. In the simplest form, we know that PostgreSQL is considered open and Oracle is considered closed. Oracle conceptually has an isolated operating system environment, whereas PostgreSQL will adapt and integrate into its surroundings.
For example, it has been said that Oracle is a resource hog, using what’s available—mainly because it functions like an operating system. PostgreSQL, on the hand, believes in allowing the operating system to carry the load and not trying to reinvent the wheel: “Why do the work when the O/S can do it for me?”
Conceptually there are some users, roles, and schemas with different meanings but with the same purpose in mind. In Oracle you have users and roles, where PostgreSQL only has roles. But with these roles you can actually log into the database.
If confusion settles in, Advanced Server can make things more relatable. A short navigation through the database brings things to life with the capabilities to utilize the all_ and user_views or some of the most common DBA views.
With these common views, the ability to use standard scripts to tune and monitor the database is available. Some key dictionary tables, such as pg_stat_statements, pg_stat_activity, and pg_locks, can produce standard session details.
If scripting isn’t your tool of choice there are GUI options such as the standard pgAdmin that ships with PostgreSQL, allowing good options to monitor a single node. For a more complete enterprise solution, EDB offers Postgres Enterprise Manager (PEM). For a complete solution, the functionality will bring things closer in relation to OEM offerings. In most environments, the developers’ favorite is Toad for Oracle. Having used Toad for many years, there is no fear: with the release of Toad Edge for Postgres, things are really shaping up.
To share or not to share, that is the question. RAC appears to have found a place within the infrastructure of every large corporation. Sometimes the true purpose of a good technology gets lost in the hype. The actual comparison is a matter of shared disk vs. shared nothing.
Understanding your use case is the key. The fundamental purpose of RAC is to provide a high availability cluster with load balancing. For PostgreSQL, if the use case arises for shared disk, the Red Hat Cluster Suite can be implemented. To complete the solution for HA with load balancing, you could make use of the streaming replication that’s native to PostgreSQL, with Pgpool for load balancing, and implement EDB Postgres Failover Manager (EFM) to give you full control over the HA environment.
With a proper HA solution in place, you tend to consider your disaster recovery needs and a possible means to have your data geographically disbursed. This can leave you in search of a proper multi-master replication option. Oracle has Golden Gate, which can assist you in this area, but with PostgreSQL, the EnterpriseDB tool replication server will also give you the power of active-active replication with change data capture and features to handle the conflict resolution.
With both databases, the deployment options are almost endless. You truly can run the same Postgres everywhere.
- Bare metal (Windows, RHEL, CentOS, Linux on Power, SLES, Debian)
- Virtualized deployments (VMware)
- Container deployments (OpenShift, Kubernetes)
- Public/private cloud deployments (AWS, Azure, Alibaba, Google)
An open-source initiative can truly be achieved with a PostgreSQL solution. The price point can be affordable without causing procurement nightmares. Independent of virtualization, a per-core subscription model with no vendor lock-in can sound appealing without the fear of a daunting audit lingering in the shadows.
Sample performance stats show that it’s possible to achieve high TPS, billions of writes, and scaling of concurrent users with some flexibility in database size.
Global mobile ad network
- Largest database is 14 TB
- 1.2 billion transactions a day, 55 K transaction per second
- 400 concurrent users
- Analyzes 240 TB of data per day
Online brokerage firm
- 1 billion writes a day
- 3,000 transactions per second
- 800 concurrent users
Global stock trade underwriter
- Largest database is 8 TB
- 6 to 10 million transactions per day
- Global consumer financial services provider
- Example application database is 2 TB
- 200 K SELECT statements per second
PostgreSQL and Oracle can now be mentioned in the same conversation, as they both share a solid place in the database ecosystem. The key to a successful migration will be to determine the proper use case, evaluate the application, and perform a thorough analysis. EDB has performed a multitude of successful migrations to date, building a deep knowledge base of the comparison and challenges. Comparing the technologies may excite you—or you’ll appreciate the quick installation and setup—but proper planning will result in a successful project.
*This article first appeared in the February 2019 issue of The NoCOUG Journal as a special feature, pages 16-18.