Contributed by Jason Davis
The release of PostgreSQL 9.5 marks a turning point for enterprises using PostgreSQL for the development of high-speed mobile, web and digital economy applications, many for customer engagement. As enterprises evolve, their infrastructures must adapt constantly to stay competitive and meet rising consumer expectations. PostgreSQL has responded with new features and capabilities. It’s exciting to see.
PostgreSQL continues to deliver on its promise as the world’s most advanced open source database for enterprise-class workloads. As global enterprises upgrade systems to bigger machines with higher core counts and larger memory footprints, PostgreSQL has responded with performance and scalability increases. And as data-driven applications demand greater insight, PostgreSQL has responded with complex analytics functions that deliver more highly tailored results.
Much has been said about new features like UPSERT, Row Level Security, and new analytic functions like GROUPING SETS / CUBE and ROLLUP. To be sure, they are important new features. To read about these, I highly recommend reviewing the PostgeSQL 9.5 Release Announcement as well as the PostgreSQL 9.5 release notes.
But here is my personal list of three improvements and enhancements in PostgreSQL 9.5 that may go ‘under the radar’:
1) Performance and Scalability Improvements
Critical enhancements co-developed by EnterpriseDB (EDB) and contributed to the PostgreSQL 9.5 core code base have increased the database’s ability to deliver greater performance and scalability on large multi-core and large memory systems. The advances focused on running workloads on large, ‘big iron’ servers with four to 12 cores/socket, four to eight sockets and 128 GB or more of RAM.
EDB focused its engineering resources on solving bottlenecks in specific locking subsystems, including shared buffer eviction locks amongst others. The end result is less contention in the system and better performance on new high-end hardware, as evidenced by various benchmarks we have run.
EDB conducted benchmark tests that showed PostgreSQL 9.5 exhibits a 96% performance boost at 64 concurrent connections on a 24 core / 496 GB RAM system and a 25% performance improvement over v9.4 for 32 concurrent connections.
EDB’s customers and Postgres users, especially those with workloads supporting high numbers of concurrent users, can expect measurable benefits from a variety of PostgreSQL 9.5 use cases:
- Increased database output volume and improved scalability
- Increased performance with existing infrastructure
- More effective decision-making by developing decision support systems (DSS) for analysis
- Faster response times and significant customer service improvements.
2) Block Range Indexes (BRIN)
The new Indexing type called BRIN provides for very small indexes that use metadata (like minimum and maximum values) about ranges of data to improve the performance of queries.
It solves the problem facing DBAs who must regularly run queries that ask broad, open-ended questions of vast amounts of naturally ordered data (i.e. by numeric sequence or data/time fields). Consider companies with daily transaction loads that number in the millions. Data analysis teams regularly search for greater efficiencies or new revenue opportunities but don’t know ahead of time what they want to ask of the data. With existing indexes like BTrees, that type of analysis could be a very slow process. BRIN creates indexes on ranges of values in the database so whole blocks of records can be ruled out without having to be loaded. This speeds up queries significantly.
Some have heralded this feature as proof of PostgreSQL’s ability to support more ‘big data’ use cases. But while this is exciting, I think it’s important to reiterate it’s proof yet again that PostgreSQL has an ability unique in the database industry. PostgreSQL is extensible. It was designed from the beginning, as an object-oriented database, to be extended so that new features and capabilities could be easily added and the database could be extended into supporting new areas. PostgreSQL 9.4 brought us the new JSONB data type to support document database / NoSQL workloads, an example of how PostgreSQL could be extended to support unstructured data. Now, BRIN in v9.5 shows how PostgreSQL can be extended to support specialized indexes for data warehousing methods within a relational database system.
3) Parallelism Infrastructure
Parallelism is not a single feature, but rather a foundation for multiple features for future capabilities that will propel Postgres forward dramatically. It will enable the database to execute queries in parallel and better leverage the high core counts available in modern hardware. This will result in even greater performance increases, particularly for long-running types of workloads.
End users will not be able to leverage parallel query features until next year in PostgreSQL 9.6, but they wouldn’t be possible without a series of new additions in PostgreSQL 9.5. Robert Haas, EDB’s Vice President, Chief Database Architect, wrote about the work he did laying the foundation in this year’s release with EDB’s Amit Kapila, Senior Database Architect, in a blog in March 2015. These patches provided the final foundational components needed for parallel processing to become a reality in PostgreSQL.
With these patches in place, we have already started to see end-user features become committed into v9.6. Parallel sequential scan is complete and parallel sort has been making nice progress. I expect to see more capabilities such as parallel join and aggregation coming to the forefront soon.
In summary, I see PostgreSQL 9.5 as a great step forward, and not just in terms of new features. PostgreSQL is becoming an integrated data platform that supports the organization today but also well into the future, something enterprises need to know as they examine their strategic initiatives and plan for competing in today’s digital marketplace. With the latest advances, PostgreSQL 9.5 has proven once again why it remains the world’s most advanced open source database.
To download PostgreSQL 9.5 using a one-click installer, please visit here to install. For information on how EDB can help your organization achieve success with Postgres, please contact us directly.
Jason Davis is Senior Director, Product Management, at EnterpriseDB.