7 JANUARY 2016: The PostgreSQL Global Development Group announces the release of PostgreSQL 9.5. This release adds UPSERT capability, Row Level Security, and multiple Big Data features, which will broaden the user base for the world's most advanced database. With these new capabilities, PostgreSQL will be the best choice for even more applications for startups, large corporations, and government agencies.
Annie Prévot, CIO of the CNAF, the French Child Benefits Office, said, "The CNAF is providing services for 11 million persons and distributing 73 billion Euros every year, through 26 types of social benefit schemes. This service is essential to the population and it relies on an information system that must be absolutely efficient and reliable. The CNAF's information system is satisfyingly based on the PostgreSQL database management system."
A most-requested feature by application developers for several years, "UPSERT" is shorthand for "INSERT, ON CONFLICT UPDATE", allowing new and updated rows to be treated the same. UPSERT simplifies web and mobile application development by enabling the database to handle conflicts between concurrent data changes. This feature also removes the last significant barrier to migrating legacy MySQL applications to PostgreSQL.
Developed over the last two years by Heroku programmer Peter Geoghegan, PostgreSQL's implementation of UPSERT is significantly more flexible and powerful than those offered by other relational databases. The new ON CONFLICT clause permits ignoring the new data, or updating different columns or relations in ways which will support complex ETL (Extract, Transform, Load) toolchains for bulk data loading. And, like all of PostgreSQL, it is designed to be absolutely concurrency-safe and to integrate with all other PostgreSQL features, including Logical Replication.
Row Level Security
PostgreSQL continues to expand database security capabilities with its new Row Level Security (RLS) feature. RLS implements true per-row and per-column data access control which integrates with external label-based security stacks such as SE Linux. PostgreSQL is already known as "the most secure by default." RLS cements its position as the best choice for applications with strong data security requirements, such as compliance with PCI, the European Data Protection Directive, and healthcare data protection standards.
RLS is the culmination of five years of security features added to PostgreSQL, including extensive work by KaiGai Kohei of NEC, Stephen Frost of Crunchy Data, and Dean Rasheed. Through it, database administrators can set security "policies" which filter which rows particular users are allowed to update or view. Data security implemented this way is resistant to SQL injection exploits and other application-level security holes.
Big Data Features
PostgreSQL 9.5 includes multiple new features for bigger databases, and for integrating with other Big Data systems. These features ensure that PostgreSQL continues to have a strong role in the rapidly growing open source Big Data marketplace. Among them are:
BRIN Indexing: This new type of index supports creating tiny, but effective indexes for very large, "naturally ordered" tables. For example, tables containing logging data with billions of rows could be indexed and searched in 5% of the time required by standard BTree indexes.
Faster Sorts: PostgreSQL now sorts text and NUMERIC data faster, using an algorithm called "abbreviated keys". This makes some queries which need to sort large amounts of data 2X to 12X faster, and can speed up index creation by 20X.
CUBE, ROLLUP and GROUPING SETS: These new standard SQL clauses let users produce reports with multiple levels of summarization in one query instead of requiring several. CUBE will also enable tightly integrating PostgreSQL with more Online Analytic Processing (OLAP) reporting tools such as Tableau.
Foreign Data Wrappers (FDWs): These already allow using PostgreSQL as a query engine for other Big Data systems such as Hadoop and Cassandra. Version 9.5 adds IMPORT FOREIGN SCHEMA and JOIN pushdown making query connections to external databases both easier to set up and more efficient.
TABLESAMPLE: This SQL clause allows grabbing a quick statistical sample of huge tables, without the need for expensive sorting.
"The new BRIN index in PostgreSQL 9.5 is a powerful new feature which enables PostgreSQL to manage and index volumes of data that were impractical or impossible in the past. It allows scalability of data and performance beyond what was considered previously attainable with traditional relational databases and makes PostgreSQL a perfect solution for Big Data analytics," said Boyan Botev, Lead Database Administrator, Premier, Inc.
For explanations of the above features and others, please see the following resources:
HTML documentation and man pages are installed with PostgreSQL, but feel free to browse, search and comment on our extensive, interactive online documentation.
PostgreSQL uses the PostgreSQL License, a BSD-like "permissive" license. This OSI-certified license is widely appreciated as flexible and business-friendly, since it does not restrict the use of PostgreSQL with commercial and proprietary applications. Together with multi-company support and public ownership of the code, our license makes PostgreSQL very popular with vendors wanting to embed a database in their own products without fear of fees, vendor lock-in, or changes in licensing terms.
Web PagePostgreSQL home page
English-Language Press Inquiries
USA, Canada and General Inquiries
PostgreSQL Press Team
Phone: +1 (347) 674-7759
+353 (0)87 2241365
Arul Shaji Arulappan and Richard Sayad
All logos are available for modification and redistribution under The PostgreSQL License. The PostgreSQL name and logo are trademarks of The PostgreSQL Community Association of Canada.
PostgreSQL is the world's most advanced database system, with a global community of thousands of users and contributors and dozens of companies and organizations. The PostgreSQL Project builds on over 25 years of engineering, starting at the University of California, Berkeley, and has an unmatched pace of development today. PostgreSQL's mature feature set not only matches top proprietary database systems, but exceeds them in advanced database features, extensibility, security and stability. Learn more about PostgreSQL and participate in our community at PostgreSQL.org.
Annie Prévot, DSI de la CNAF, déclare, "La CNAF sert 11 millions d'allocataires , elle verse 73 milliards d'euros par an , au travers de 26 types de prestations , ce service essentiel à la population s'appuie sur un système d'information qui se doit d'être absolument performant et fiable . Le système d'information de la CNAF s'appuie avec satisfaction sur le gestionnaire de Base de données PostgreSQL . En utilisation sur la moitié des systèmes , ce logiciel open source offre toutes les garanties de fonctionnement et de fiabilité et est en phase de déploiement sur l'ensemble des systèmes."
(English version) Annie Prévot, CIO of the CNAF, the French Child Benefits Office, said, "The CNAF is providing services for 11 million persons and distributing 73 billion Euros every year, through 26 types of social benefit schemes. This service is essential to the population and it relies on an information system that must be absolutely efficient and reliable. The CNAF's information system is satisfyingly based on the PostgreSQL database management system. In use on half of the systems, this open source software offers all the functionality required and a guarantee of reliability. A deployment of PostgreSQL on all systems is underway."
"The new BRIN index in Postgresql 9.5 is a powerful new feature which enables Postgresql to manage and index volumes of data that were impractical or impossible in the past. It allows scalability of data and performance beyond what was considered previously attainable with traditional relational databases and makes Postgresql a perfect solution for Big Data analytics," said Boyan Botev, Lead Database Administrator, Premier, Inc.
Premier, Inc. (Nasdaq: PINC) is a healthcare performance improvement alliance of approximately 3,600 U.S. hospitals and 120,000 other providers. Our mission is simple: To improve the health of communities. As an industry leader, the Premier alliance has created one of the most comprehensive databases of actionable data, best practices and cost reduction strategies. Contact Premier, Inc. through its website.
PostgreSQL enjoys the support of numerous companies, who sponsor developers, provide hosting resources, and give us financial support. See our sponsors page for some of these project supporters.
There is also a large community of companies offering PostgreSQL Support, from individual consultants to multinational companies.
Donations gladly accepted.
Or you can buy some of this fine merchandise from our Zazzle PostgreSQL Store.