“EDB’s contributions to the Postgres Community during this development cycle have focused heavily on performance and scalability. PostgreSQL 9.5 provides significant scalability enhancements that benefit our large enterprise and government customers, since they run on high core count servers that support large numbers of concurrent users and mission-critical applications,” said Marc Linster, Ph.D., Senior Vice President of Products and Services at EnterpriseDB.
Benchmark Test Results
EDB’s benchmark tests show that PostgreSQL 9.5 exhibits significant performance improvements for high concurrency workloads. For example, 64 concurrent connections on a 24 core / 496 GB RAM system showed a 96% improvement over PostgreSQL 9.4.
EDB’s customers and PostgreSQL users, especially those with workloads supporting high numbers of concurrent users, can expect measurable benefits from a variety of PostgreSQL 9.5 use cases: (i) increased database output volume and improved scalability; (ii) increased performance with existing infrastructure; (iii) more effective decision-making by developing decision support systems (DSS) for analysis; and (iv) faster response times and significant customer service improvements.
Performance and Scalability
The advances in PostgreSQL 9.5 related to performance and scalability include:
- Shared buffer concurrency: PostgreSQL 9.5 can support greater data volume, thereby increasing performance as a result of higher optimization of the locking regime in shared buffers.
- Expanded concurrent locking: With a reduced number of page locks and pins holding indexes during scans, PostgreSQL 9.5 can support more concurrent users in high-transactional systems, which also increases performance.
- Buffer mapping: With enhanced shared buffer management, PostgreSQL 9.5 can support a greater number of shared buffer partitions.
- Prefix sorting: Using an abbreviated sorting optimization, PostgreSQL 9.5 can sort large text and numeric fields 20% to 30% faster than PostgreSQL 9.4, according to benchmark tests.
Horizontal Scalability and Integration
Foreign Data Wrappers (FDWs) set the groundwork for accessing data across multiple servers, which will be the foundation for future developments in horizontal sharding. A new FDW feature in PostgreSQL 9.5, IMPORT FOREIGN SCHEMA, automates importing of schemas from external databases connected to PostgreSQL through FDWs. This reduces the potential for errors and replaces a manual process when accessing data from outside sources.
FDWs enable PostgreSQL to read and write data from other databases and access it as PostgreSQL tables where developers can query it with SQL, as if it were native PostgreSQL. The development of these enhancements are being led by EDB and Japan’s Nippon Telegraph and Telephone (NTT).
Productivity and Security
Key advances in PostgreSQL 9.5 for improving productivity and security, as well as accelerating data analytics, include:
- A new indexing type called BRIN (Block Range Index), which provides for very small indexes that use metadata (e.g., minimum and maximum values) to describe a range of information to improve the performance of queries. For tables that store data in a sequential manner (i.e. by numeric sequence or data/time fields), this allows queries to skip the majority of the table when looking for data within a certain range.
- A trio of new analytic features (GROUPING SETS/CUBE/ROLLUP) quickly summarizes large amounts of data across various dimensions. These are typically used in data warehousing environments where businesses need to summarize information like employee headcount across department, locations and job role, or ecommerce order histories across various degrees of demographic and geographic information. This delivers higher performance for complex queries, and allows businesses to produce more tailored results.
- UPSERT is one of the most commonly requested features to be added to Postgres. Syntactically, the new "ON CONFLICT DO NOTHING" (or "ON CONFLICT DO UPDATE") gives database developers a more effective and efficient way to insert data, while managing potential insert conflicts without writing any custom code.
- Improvements to the document data type JSONB furthers Postgres’ ability to manage unstructured and semi-structured data. New operators and functions simplify the developer’s ability to compare and update individual elements of the document in the database.
- Row Level Security allows DBAs to implement policies to limit what can be seen or updated depending on any number of properties, such as user name, clearance level, or organizational membership. While Row Level Security is new to the open source community Postgres, EDB Postgres™ Advanced Server has featured built-in Row Level Security for several releases -- a capability that has been used by EDB’s Postgres enterprise customers for several years. EDB Postgres Advanced Server’s version of this feature is syntactically compatible with Oracle’s implementation, and includes enforcement of column-level policies (limiting visibility on sensitive values or columns) and affords a more restrictive policy application (using AND rather than OR to apply multiple policies).
PostgreSQL 9.5 is generally available. To download the free version of PostgreSQL using an easy one-click installer, click here to install, or visit the PostgreSQL community download page here. To leverage the full value of PostgreSQL for enterprise deployments, the database also is available as part of the EDB Postgres Standard subscription, which includes the PostgreSQL 9.5 database, plus migration, integration and management tools for developers and DBAs, as well as 24x7 global support. In addition, a broad range of training, professional services and certification solutions are available from EDB and EDB’s partners.
To learn about PostgreSQL 9.5, email firstname.lastname@example.org and/or download the webcast, Overview of PostgreSQL 9.5, recorded by Bruce Momjian, a co-founder of the PostgreSQL Global Development Group and EDB Senior Database Architect.