PostgreSQL: What's behind the pull of this open source database?
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.
Tech Pro Research
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
- DataStax's 4.5 Cassandra fires up Apache Spark in-memory analytics
- Couchbase CEO: Only three NoSQL firms stand out from the pack
- MongoDB launches managed service on Microsoft Azure
- Oracle's in-memory option aims to beat the rest within 12 months
- A look at MemSQL's memory first database software
- Hadoop big data analytics: Can SQL support bring it to the masses?
- Full SQL on Hadoop? Splice Machine opens up its database for trials
- SQL and NoSQL? Fine, but how does the hybrid database fit in?