The NoSQL Capabilities of PostgreSQL

Many businesses of all sizes leverage PostgreSQL as an open source option to Oracle and other relational databases. Significant cost savings while maintaining a similar level of performance remains a preeminent reason for this switch. A robust community and the availability of commercial-grade support make Postgres worthy of consideration for your traditional database needs. 

With NoSQL gaining popularity all over the technology world, you may wonder how PostgreSQL supports this new database paradigm. Let’s take a look at what functionality exists today in the database with a quick towards the future as well.

Postgres NoSQL for the Enterprise

We’ve talked about EnterpriseDB’s commercial level version of PostgreSQL previously on the blog. The company also offers a Postgres version with support for document databases and key-value stores – two of the most common NoSQL database types. Known as Postgres NoSQL for the Enterprise, this is something worthy of closer attention at companies looking for an open source mix of relational and NoSQL databases.

This Postgres database solution combines the speed and flexibility of NoSQL with the traditional SQL database functionality required for enterprise use – most notably the support for ACID (atomic, consistent, isolated, and durable) transactions. Database instances also easily integrate into the existing business data infrastructure, no matter the platform. In short, it provides the best of both worlds – relational and NoSQL.

ACID transactions are vital for business organizations that depend on the real-time validity of the relationships within its data. Many current NoSQL databases don’t offer this feature, instead following the BASE paradigm which emphasizes speed and availability over the consistency of the data. Postgres NoSQL lets companies combine unstructured and structured data; mixing the performance of NoSQL with the more formalized governance of traditional SQL.

Postgres NoSQL supports many industry standards for programmatic access and data exchange. These include Ruby, Python, and JavaScript for the former, and the JSON and XML formats in the latter case. The superior performance of PostgreSQL combined with the seamless scalability typical of a NoSQL database solution make EnterpriseDB’s combination of Postgres and NoSQL a valid option for any business desiring a flexible database infrastructure.

The Future of PostgreSQL and NoSQL

In a previous article looking at new features of PostgreSQL 10, we noted the relative lack of NoSQL functionality in this newest version of Postgres, slated for release later this year. The new XMLTABLE feature supports the direct querying of data stored in XML documents. Other performance improvements in version 10 bring the speed of the relational database closer to its other NoSQL brethren.

One recent enhancement in Amazon Web Services deserves mention for companies using a mixture of relational and NoSQL databases. The AWS database migration service now includes NoSQL databases, with MongoDB (as a source) and Amazon’s own DynamoDB (as a target) being the first two to be supported. This means companies with a PostgreSQL instance on AWS are able to stream data from Postgres to a DynamoDB instance.

Companies with an investment in PostgreSQL need to explore EnterpriseDB’s NoSQL option to see if any of its features make sense for adding non-traditional database formats to the corporate data infrastructure.

Keep returning to the Betica Blog for additional news and insights from the wide world of software development. Thanks for reading!

PostgreSQL 10 – New Features and Functionality

PostgreSQL remains a popular option for organizations that need a traditional SQL database, but don’t want to spend the money required for Oracle. We’ve covered this open source database in the past here on the blog. For those companies who want extra support, a commercial Postgre option like EnterpriseDB needs to be considered.

With PostgreSQL 10 scheduled for release later this year, many users are undoubtedly curious about the new features and functionality. Let take a closer look at what’s in the feature set so you can consider either an upgrade or using this new version on your next development project.

Improved Query Performance

One of the most important enhancements in PostgreSQL 10 is its faster query executor. The database is already known for performing essentially as fast as Oracle, so any additional speed boost is sure to make those benchmark comparisons even closer.

Robert Haas, Vice President for Enterprise DB and a major contributor to the PostgreSQL codebase, commented on the technical changes behind the executor’s performance boost. “Hash aggregation has been rewritten to use a more efficient hash table and store narrower tuples in it, and work has also been done to speed up queries that compute multiple aggregates and joins where one side can be proven unique,” said Haas.

Improved parallelism is another enhancement in Postgre aimed at boosting query performance. Haas noted that parallel queries now run two to four times faster in version 10. Index scanning is another function now faster because of parallel processing.

The new XMLTABLE support improves query processing against data stored internally as XML. This is the one PostgreSQL 10 enhancement aimed at the NoSQL market.

Replication is now Better – and Easier

PostgreSQL 10 now supports replication at the table level; previous versions required the full database to be replicated. This additional flexibility comes with the bonus of being easier to use as well. Called Logical Replication, it is a feature greatly anticipated in the PostgreSQL community.

Extended Statistics help with Query Planning

Developers who write complex queries against a PostgreSQL 10 instance enjoy the benefit of expanded statistics that help the query planning process. Haas explains this in more detail: “If the query planner makes a bad row count estimate resulting in a terrible plan, how do you fix it?  With extended statistics, you can tell the system to gather additional statistics according to parameters that you specify, which may help it get the plan right.”

Other PostgreSQL 10 Enhancements

Other significant Postgre 10 improvements include Declarative Partitioning which makes inserting new records faster, among other benefits. Support for SCRAM authentication enhances the security of a database instance. Durable Hash Indexes are another new feature aimed at boosting database performance.

One future enhancement potentially coming out in a point release is just-in-time compilation. This is expected to add yet another performance boost to any PostgreSQL implementation.

PostgreSQL 10 definitely adds enough new functionality for current users as well as organizations interested in an alternative to Oracle. While its NoSQL support remains limited, it is definitely a traditional SQL database worthy of your interest. EnterpriseDB also offers commercial-level support for companies still wary of an open source solution.

Keep returning to the Betica Blog for additional dispatches from the software development world. Thanks for reading!

An Overview of Neo4j – the NoSQL Graph Database

NoSQL databases have grown in popularity over the last few years because they meet many needs of modern businesses better than traditional relational databases, especially when trying to gain meaningful knowledge out of the masses of data generated by social media – i.e., Big Data. The “NoSQL” moniker covers a whole host of database formats and structures, with document, graph, and key-value pair databases being three of the most common types. Many of the popular NoSQL databases also have open source origins.

Graph databases are highly suitable for those “needle in the haystack” scenarios when trying to find a singular relationship within a Big Data store. Neo4j continues to be an industry leading example of this NoSQL type. Here is an overview of Neo4j.

The Genesis of Neo4j

Developed by Neo Technology, the first version of Neo4j became available in early 2010. An open source edition of the product is freely available for developers and database professionals to explore its functionality. A variety of commercial licenses, including the Neo4j Enterprise Edition, give businesses additional features, like support for large volumes, scalability, and online backups.

Version 3.1 is the most recent stable release of Neo4j. The growing popularity of graph databases in general is one of the reasons Neo Technology closed on $36 million of venture capital in November of last year. The open source version of Neo4j has been downloaded 2.5 million times.

What makes Graph Databases so great?

Graph databases focus on the connections within the data; greatly outperforming traditional SQL databases in finding relationships between records in real time. Because of this superfast query speed, graph databases are highly suitable in a variety of scenarios, including fraud detection, social network applications, searching for information, and more.

This database format is also appropriate for organizations building applications using Agile. Time isn’t wasted creating massive database diagrams where one table change affects many parts of an application. As such, it nicely serves the needs of the nimble business.

The Advantages of Neo4j

One major advantage Neo4j holds over many other NoSQL and graph databases it its support for ACID (atomic, consistent, isolated, and durable) transactions. This helps ensure the quality of data, especially in widely distributed architectures where data gets replicated across different Cloud-based server farms.

The Enterprise Edition of Neo4j includes a feature known as “elastic scalability” where internal memory stores offer fast queries, with high availability provided by a replication protocol. Even greater scalability is achieved when using the Neo4j version compatible with IBM’s POWER8 processor.

Driver support for many of the most popular programming languages – Java, C#, Python, JavaScript – is included. The robust Neo4j community has also developed drivers for Ruby, PHP, and other languages. The database also plays well with many other data programming frameworks, such as JDBC, Django ORM, Spring Data, and more.

Neo4j also integrates with other popular NoSQL databases, including MongoDB and Cassandra, giving developers a measure of flexibility in building database applications to handle different needs.

If your organization is interested in NoSQL databases, download the open source version of Neo4j and explore how easy it is to create graphs and build queries against them. Soon your customers will be able to find the needle in their haystack of Big Data.

Keep checking out the Betica Blog for additional insights from the wide world of software development. Thanks for reading!