The data runs through it

Siloed data – the new Postgres feature

Pierre Fricke
Silos image via Shutterstock

With CIOs struggling to harness a patchwork of data solutions and siloes, newly enhanced Foreign Data Wrappers for Postgres are providing critical integration for consistent data management and analysis.

In today’s digital age companies are trying to store, manage and make sense of vast pools of information. Data has also become more varied with the rapid proliferation of smartphones, new web apps for fun and business, and all the things that comprise the Internet of things, just to name a few.

Consequently the typical data centre now contains a patchwork of data management technologies. From enterprise class relational databases to standalone, niche NoSQL-only solutions to specialized extensions, the arsenal for managing data has become more diverse.

The need for solutions that can support new data types and evolving data demands, has given rise to “shadow IT” haunting the data centre. Developers are the biggest culprit and their tech tools of choice are NoSQL-only database solutions. Driven by a need to deliver new types of applications faster and respond more directly to business and operations departments employing agile development methods, application developers are seizing on solutions that enable them to work faster. They’re spinning up clusters in the cloud or on-premises for new specialized applications, often working outside official controls. But by using NoSQL-only solutions to address specialized applications, they’re adding silos of data to the enterprise environment.

Using NoSQL-only solutions, however, poses a host of challenges, complexities and even serious risks to the organization. “By 2017, 50 % of data stored in NoSQL DBMSs will be damaging to the business due to a lack of applied information governance policies and programs,” according to a recent Gartner report.

This has pit application developers against database professionals – the database architects, DBAs and IT executives charged with maintaining data flow, stability and integrity throughout the enterprise. But Postgres has emerged as the data management solution for integration challenges with a clever feature called a Foreign Data Wrapper that can integrate data from disparate sources, like MongoDB, Hadoop and MySQL. Initial, FDWs had only read capabilities but recent enhancements to PostgreSQL enabled EnterpriseDB to develop new FDWs MongoDB, Hadoop and MySQL hat have write capabilities as well. These new FDWs are available on GitHub.

Postgres – The data runs through it

Foreign Data Wrapper (FDWs) link Postgres databases with other data stores and let users access and manipulate the data as if it were part of a native Postgres table. Indeed, FDWs enable Postgres to act as the central hub, a federated database, in the enterprise. It does this by using the JSON datatype, one of the important features Postgres has added in recent releases that suppose NoSQL capabilities.

Advances in NoSQL capabilities alongside longstanding relational database features have given Postgres the ability to support the new unstructured data types and programming methods common to many NoSQL products. In Postgres, you can even combine unstructured data with relational tables, all while maintaining compliance with the Atomicity, Consistency, Isolation, Durability (ACID) principles of relational technologies as well as centralized business processing rules and logic.

This is possible because Postgres is extensible. Unique in the database world, Postgres was developed with expansion in mind, making it easy to incorporate new data types, indexing schemes, languages, and much more without de-stabilizing or compromising existing features.

Achieving this interconnectivity is a simple thing for Postgres because of how the database has been designed. As it is an object-relational model, Postgres defines functionality as simple objects. Objects can be manipulated or enhanced and new ones can be built. So creating say, a new data type like JSON as the perfect stand-in for data from foreign sources, is simply a means of following the sale rules as all the other objects, and building and inserting an object.

And as demand for new capabilities emerged, the open source community behind the PostgreSQL project has simply built these new capabilities into the database.

The history of Foreign Data Wrappers

Four years ago, Dave Page, member of the PostgreSQL community Global Development Group and EnterpriseDB Chief Architect, helped develop and release the first Foreign Data Wrapper, an experimental version of an FDW, for MySQL. The tool quickly became production ready and underwent a few changes, including being updated for PGXN point release and 9.2 support two years ago. Subsequently, EnterpriseDB developed and released to the open source community an enhanced FDW for MySQL, building on the work Page that had done.

Foreign Data Wrappers (FDW) enable DBAs to use the database as a single integration point to read, analyse and write to data from many remote data stores. Developers working in C can create new FDWs using the hooks exposed by the database, and many FDWs have been published in the Open Source community. FDWs essentially bridge disparate data sources, pulling data from other databases and inserting it into Postgres tables where users can work with it as if it were native Postgres data.

The implementation for Postgres FDWs is based on the SQL standard SQL/MED (SQL Management of External Data), which was introduced in 2011 in PostgreSQL 9.1. It is a standard way of accessing external data stores ranging from SQL and NoSQL-only databases to flat files. FDWs provide a SQL interface for accessing remote objects and large data objects stored in remote data stores.

This enabled developers in the community to build FDWs with read capability. Support for the SQL/MED standard was improved upon in recent Postgres releases. Most recently, PostgreSQL 9.3 added the ability for FDWs to support write capabilities and that has opened up whole new opportunities for organizations to use Postgres and these features to solve integration challenges.

Foreign Data Wrappers – Value for the enterprise and its customers

Postgres FDWs offer IT organizations the ability to leverage existing data deployments with an enterprise-ready relational DBMS with NoSQL capabilities in a unified platform. By bringing these disparate data sources together, IT can offer up holistic views of key entities like customer and partner.

These holistic views allow new applications of engagement to make intelligent recommendations and take targeted actions on the behalf of their users and customers. This smart, focused customer engagement is the forefront of industry leadership and customer satisfaction and underlines the crucial business value FDWs can play in today’s digital age.

Pierre Fricke
Pierre Fricke is Vice President of Product Marketing at EnterpriseDB Corporation and was previously a Red Hat JBoss executive.

Inline Feedbacks
View all comments