eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
A new version of the open-source PostgreSQL database is now available, providing improved reliability and data management features. PostgreSQL is a widely deployed open-source database with roots that go back to 1985 and the UC Berkeley Postgres database project.
One of the key new features in PostgreSQL 9.3 is a capability known as Streaming-Only Remastering. This new feature enables database administrators to have cascading replicas, such as for geographically distributed failover, Josh Berkus, PostgreSQL core team member, explained to eWEEK.
“This means that administrators can set up replica sets in other geographies without needing to worry about shipping files in addition to the streaming replication connection,” Berkus said. “When we reduce complexity, it enhances reliability.”
The new PostgreSQL release also provides new capabilities for “Foreign Data Wrappers” (FDW). According to the PostgreSQL 9.3 release notes, FDW provides a way for PostgreSQL to access external data sources from within PostgreSQL using SQL. FDW was first introduced in PostgreSQL 9.1, which was released in 2011. With the initial release, FDW was read-only and now it is read-write, permitting two-way transfer of data.
LATERAL JOINS
For database developers, PostgreSQL includes a number of new capabilities to manage and control what’s in a database table. One of those features is a new implementation of the LATERAL JOIN capability. Berkus explained that while LATERAL JOIN has its own use in the Structured Query Language (SQL) spec, in PostgreSQL the primary use is somewhat different. SQL is the language used for database queries.
“PostgreSQL allows you to return results of a user-defined function as a table, and then query that table,” Berkus said.
He added that this is a feature used extensively to query nonstandard data, such as arrays, matrixes, JSON, XML and foreign data wrappers.
“Prior to LATERAL JOIN, though, you couldn’t use a column from another table to join the table function to other tables in the query,” Berkus said. “Now you can.”
Performance
While the previous PostgreSQL 9.2 release that debuted in 2012 included a number of general performance improvements, the performance improvements in the new PostgreSQL 9.3 release are not as general, Berkus noted. Rather, the new release can improve performance for certain specific cases. Those use-cases include bulk loading and indexing regular expressions.
In general though, performance for many use-cases on PostgreSQL 9.3 will be similar to what it is with PostgreSQL 9.2.
“Partly, this is because we put so many different performance improvements into version 9.2,” Berkus said. “We’ve used up our ideas on major general performance improvements for a while.”
Sean Michael Kerner is a senior editor at eWEEK and InternetNews.com. Follow him on Twitter @TechJournalist.