As a database administrator, you’re probably known in your organization for your pragmatic approach to technology. Sure, it’s important to continually hone your skills, but when somebody comes up with a new application-question, it’s more important to you to have a database-answer at the ready. And for the last few years, application developers have been asking for PostgreSQL (or “Postgres”) because of its support for a wide range of requirements. In fact, it was voted number one in the most-wanted category in the Stack Overflow 2021 Developer Survey — ahead of Redis, and MongoDB. If you’ve already vetted and approved Postgres, then you’re ahead of the game as that open-source database-answer.
This post covers some of the highlights of Postgres, including its history, technical strengths and applications. DBAs will see why Postgres has attained and held the number-four spot in DB-Engines ranking since 2017, and they can gauge the fit for their own database landscape.
What’s special about Postgres?
As computer programming evolved into data management in the 1960s, programmers saw the need to separate data records from ordinary files. Projects like Oracle, System R at IBM Almaden and Ingres at UC Berkeley germinated as early attempts to implement a relational database management system (RDBMS).
Oracle, with its sequential query language (SQL), emerged as a potent commercial player, running against Ingres, the academic leader based on query language (QUEL). Although QUEL had technical advantages, SQL was adopted as a standard language for databases in the late 1980s. Ingres continued in the academic sphere while SQL began to see more rapid commercial adoption.
Over time, Postgres evolved as a platform from Ingres (“post-Ingres”). Its main architect, Michael Stonebraker of Relational Technologies Inc., brought out Postgres as “a system designed to take what Ingres could do and go beyond it.” At the heart of Postgres was object-relational database features: support for object-oriented programming ideas within the data model and declarative query language of a database system.
A viable, open-source database alternative with similarities to Oracle
Postgres is now available as an object-relational database system that uses and extends SQL. As an open-source, non-proprietary project, Postgres is continually updated and improved through a worldwide community of developers. In fact, a recent survey among Stack Exchange developers ranked Postgres the most wanted database, ahead of Redis and MongoDB, and the second-most loved database.
A few technical differentiators help explain its popularity:
- High performance out of the box — Postgres delivers robust performance suited to enterprise applications.
- Similarity to Oracle — Postgres is similar to Oracle, the commercially dominant database standard. Database developers find in Postgres familiar structure, functions, indexes and operational tasks from their experience with Oracle. Plus, transactions and disk management tend to work in ways that Oracle developers are accustomed to. In fact, in the cloud, the highest rates of automatic conversion using the AWS Schema Conversion Tool occur in moving data from Oracle to Postgres.
- Object-oriented programming — Postgres offers the best of two important worlds: NoSQL and RDBMS. It’s a smart choice as you consider your future technology choices.
- Foreign data wrappers — With a Postgres database you’re not limited in your choice of inputs. You can connect to more than 180 different data sources.
- Eminent extensibility — Postgres lets you define your own data types, build out custom functions and even write code in different programming languages without recompiling your database.
- ACID-compliance — Since 2001, Postgres transactions have been characterized by their atomicity, consistency, isolation and durability. It’s an important measure of the enterprise-readiness of any database.
- Geospatial support — Using database extenders like PostGIS you can build geographic information systems (GIS) on Postgres.
Scoping your migration
Of all the technical merits of Postgres, its similarity to Oracle is probably the most prominent in the enterprise. That feature makes it a strong candidate when DBAs consider migration from Oracle to an open-source database.
Postgres is a strong alternative for companies that want to reduce the licensing fees they pay for proprietary database platforms like Oracle. It also provides a way out of vendor lock-in. But before migrating, DBAs want reasonable assurance of the high availability, scalability and interoperability they’ve come to expect from their enterprise database of record.
Oracle is known for security, replication and availability, as well as for its ubiquity in the enterprise. Quest recommends Postgres for applications that require a large database, high performance in reading and writing, strong API compatibility, lower support costs and the ability to execute complex queries. Our customers rely on its adherence to ACID principles, which is why they favor Postgres for mission-critical systems in the domains of finance, insurance, telecom, healthcare, manufacturing, government and education.
Each use case has its own constraints too, so choose the right database for your needs. If you need a distributed database, then take a look at MongoDB. That being said, you won’t be disappointed by having Postgres as a platform ready to offer to your developers, mostly because of the flexibility it offers.
Ready to bring Postgres into your enterprise?
What will you need to take Postgres out of your back pocket and start rolling it out in your enterprise?
Naturally, you’ll want to be confident in a smooth, Oracle-to-Postgres migration, with full interoperability and no downtime. And, along with all the features listed above, you’ll want the same enterprise-grade architecture underneath Postgre that you have under your proprietary database. That could mean replicating existing structures to new ones that can handle large amounts of data, with high availability, disaster recovery, scalability and distributed instances across regions.