PostgreSQL: What's behind the pull of this open source database?

PostgreSQL: What's behind the pull of this open source database?

Summary: With more new features due soon, the Postgres relational database continues to strengthen its hand, says the open-source project's core team member and EnterpriseDB chief architect Dave Page.

Chief architect Dave Page: Postgres closing in on Oracle's functionality. Image: EnterpriseDB

Postgres continues to ride high in the database popularity stakes — and the new features appearing in its next release will only add to that appeal, according to Dave Page, a member of the open-source project's core team and EnterpriseDB chief architect.

The open-source relational database — full name PostgreSQL — for which EnterpriseDB sells apps and services as well as its own commercial fork, currently sits in fourth place in the DB-Engines rankings, behind Oracle, MySQL and Microsoft SQL Server.

"We're certainly seeing that Postgres is closing in on Oracle in terms of functionality," Page said.

"It's almost been one of those well kept secrets that people just haven't really cottoned on to. In large part in the early days that was because PHP [server-side scripting language] became very popular among web developers, and the PHP developers were MySQL guys, so that was their database of choice.

"Since Sun purchased MySQL, and then Oracle obviously purchased Sun, what we've started to see is a big turnaround."

Three or four years ago, discussion threads would extol the virtues of MySQL and question why anyone would use Postgres, Page said.

"These days it's the other way around. People are saying, 'Postgres has got all these features. Why on earth would you use MySQL, which is controlled by Oracle, when you've got Postgres that can offer you 10 times more in terms of features, compatibility and so on?'," he said.

"That to a large extent is what has caused the turnaround. People have been put off MySQL by Oracle. They've looked at the alternatives and said, 'Hang on. This Postgres really is good' and then word has spread from there."

Postgres can trace its origins back to the Ingres database developed at Berkeley and the post-Ingres project of the mid-1980s, to which it owes its name.

Page described the database as solid and feature-rich, with a history of conforming to standards, such as the SQL spec, and technical correctness.

EnterpriseDB's Postgres Plus Advanced Server fork adds a layer of Oracle compatibility on top of PostgreSQL, which is designed to make it easier to migrate apps from Oracle without extensive code rewrites. Other tools include Postgres Enterprise Manager, which also works with community Postgres and offshoots such as Postgres Plus Cloud Database.

Page said the wide feature set of Postgres derives from its open-source nature and the numerous hook points in the code that allow people to add capabilities.

"One of the hot topics for us in the community at the moment is looking at NoSQL-type workloads, which we recognise there is a place for but we don't believe they necessarily will replace relational databases," Page said.

"Obviously there are workloads where a fully ACID relational database is absolutely essential, especially in the enterprise. That's going to be the vast majority of places — anything financial, for example."

Important recent developments include foreign data wrappers, which allow remote data to be defined as a table within Postgres, so that SQL queries can be run across it and local Postgres tables as if they were all local.

"Each foreign data wrapper is effectively an interface between Postgres and some other remote data source. So, for example, if you use the MongoDB foreign data wrapper, you can make the data in your Mongo database appear in Postgres as if it was local to the Postgres database," Page said.

"You can query it and you can run the same SQL over it as you could run over a table within Postgres, and that's really powerful. It allows you to use Postgres as a data hub for all the systems that you can't or for some reason are unable to migrate to Postgres."

Due for release in Postgres 9.4 in September, another significant feature is support for the JSONB data type, which allows data to be stored within Postgres in a binary format.

"What that has allowed us to do is create a new index operator that we can use on that JSONB data, and it's incredibly quick," Page said.

More operators have also been added, including the containment operator, which allows a select query on a JSONB column to be made to show all the documents that contain a given sub-document, rather than just a specific value.

"Pretty much anything that you might use in a document database such as Mongo you can now do with Postgres. So, if you have data that needs to be stored somewhere that has full ACID compliance, you're now able to store unstructured JSON data along with that," Page said.

"You retain the ability to do multi-statement transactions and ensure when a transaction says it's committed it actually is written to disk, unlike some NoSQL databases. This allows you to get the benefits of the transactional database like Postgres, as well as the ability to use unstructured data that you would get with a NoSQL solution."

Because Postgres offers the hstore key-value data type as well as JSON, it can handle document-type data and key-value data as seen in some NoSQL databases.

Also in 9.4 will be a new facility called logical decoding, which can take changes to the database and stream them to external sources, providing the infrastructure for tasks such as replication and auditing.

"The advantage of this over the current solutions is, if you're comparing it with regular streaming replication, you get the ability to do the filtering and not replicate the entire database, if you want. If you compare it with the trigger-based solution, it's far quicker." Page said.

Work is also going on in bi-directional replication, with the goal of performing extremely fast selective multi-master replication of anything from an entire server down to a single table or even a subset of records from a single table, using logs rather than triggers.

"The bi-directional replication project is going on at the moment as an external module, so we hope we'll see that shortly after 9.4 is released and then merge it into the core later on," Page said.

In the broader context, databases of all types are competing to provide features equivalent to those offered by rival technologies.

"MySQL are trying to catch up wherever they can by adding various new features that we've had for years or Oracle have had for years. The NoSQL guys are going to be doing the same," Page said.

"Ultimately, what it will come down to is what offers the best value for customers. That's not just in terms of money, that's in terms of ease of use," he said.

"What can they use universally? What offers the widest range of features and does it well? What's going to cost the least to run? How can they minimise training costs by not running 20 different types of database, and so on?

"Postgres is very well positioned to do that. Because it's an open-source project, because we've made it a pluggable architecture and people have made use of that, we've got kind of a head-start. It puts us in a very strong position to be the database of choice."

Version 9.4 will be one of the focuses of the PostgreSQL Conference Europe 2014 on 21 to 24 October in Madrid.

More on databases

Topics: Enterprise Software, Big Data, Data Centers, Open Source, Oracle

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.


Log in or register to join the discussion
  • What's nice about it

    What's nice about PostgreSQL is they try to copy Oracle's features, so people that use Oracle find it relatively easy to also use PostgreSQL for small projects.
    Buster Friendly
  • Not just Oracle! Don't forget MSSQL

    Also being added to Postgres is T-SQL compatibility. It isn't 100% (complicated functions like "convert" don't work) but a lot does work. See

    Also in the planning stage is a layer permitting the TDS communication protocol (underlying MSSQL and Sybase) to talk directly to Postgres. Your old apps that use the DB-Lib API will be able to connect.

    Postgres won't be a drop-in replacement anytime soon, or probably ever, but compared to many other things your MSSQL developers could get comfortable fast. And a migration effort is relatively easy.

    Forget about the cost, I just hate managing licensing and bearing the cost news to folks who sign the checks.
    • Database migrations are never easy

      "relatively" or otherwise! (Other than maybe MySQL MariaDB.)
      • Agreed

        Should not have used the word "easy" even with the word "relatively" in front of it. My experience is that migrating data is not so bad, but DB-side logic, proprietary APIs, and proprietary communication protocols can have really big teeth. Among other things.

        Sybase to MSSQL isn't so awful. They were the same product many years ago. MS has a free migration tool that includes stored procedures, and it really works. Open-source FreeTDS supplies a communication protocol that works really well with both.

        If you are lucky/smart enough to have used ODBC or JDBC, that helps a lot migrating that piece; even MSSQL to vanilla Postgres isn't horrible in my experience.

        Still hard, though!
  • It's very quiet in here...

    "EnterpriseDB's Postgres Plus Advanced Server fork adds a layer of Oracle compatibility on top of PostgreSQL, ..."

    Gasp! Does this mean that PostgreSQL has implemented some Holy Oracle SQL APIs? When Google implemented Java APIs in Dalvik, these talkbacks were filled with shrill squeals from folks who insisted that you can't implement someone's proprietary APIs without their express consent; that to do otherwise is theft, because APIs are What It's All About (and that coding is for grunts...).

    And here's PostgreSQL offering Oracle compatibility, and there's nothing except the tumbleweed...
  • The thing about PostGRES that has given it popularity

    is the unpopularity MySQL has acquired under Oracle.

    I actually think Oracle has been a good steward of MySQL, unlike their job with Java. The support that Oracle offers for MySQL, if you're having problems, is second to none. And I wouldn't want to do a DB without support.... things with your business can get pretty catastrophic if you can't bring in rapid and expert tech support on a dime.

    But Oracle's kind of earned a bad reputation due to their OpenOffice and Java shenanigans, so some people are avoiding MySQL these days, I guess.
  • Postgres Achilles heel, lack of Multithreading

    Feature wise I really like Postgres, be it for custom Procedure Languages, custom column types, table inheritance, great SQL language, GIS support and more. But it is the 2014 year, and they don't have multithreading instead they use Procedure Forking, spanning a new Process for each connection and each connection is still single threaded, so they are not able to reap the benefits of Multiple CPUs as easily as a true Multithreaded application.
    Postgres fans will tell you that Procedure Forking brings some benefits and it is not a show stopper, but the true is that it is not that efficient, and the scalability suffers because that design DOES NOT make the best use of Multi CPUs servers, and that is really a show stopper.
    Martin Cab