Say oops, UPSERT your head: PostgreSQL version 9.5 has landed
Row-level security, web app data integrity lead the list
PostgreSQL has pitched its latest release with a cheeky dig at MySQL as a "legacy" database.
Version 9.5 of PostgreSQL adds a feature called UPSERT – INSERT, ON CONFLICT UPDATE – which it says removes the last barrier for MySQL users to migrate. The feature targets web and mobile environments, by handling conflicts between concurrent data changes. In MySQL, the same functionality is provided by the INSERT ... ON DUPLICATE KEY UPDATE command.
According to the PostgreSQL media release, the ON CONFLICT clause means the application can ignore new data, or can update different columns or relations "in ways which will support complex ETL (extract, transform, load) toolchains for bulk data loading."
The PostgreSQL Global Development Group also picks out row-level security as a key feature in the release.
Row-level security is handy in environments where a user should be limited to viewing only their own data. The example given here is logfile data: users can only view rows they create.
The per-row and per-column access controls are also designed to integrate with external security stacks like SE Linux, the developers say. It's also designed to protect against application-level security vulnerabilities like SQL injection.
KaiGai Kohei of NEC, Stephen Frost from Crunchy Data, and Dean Rasheed are credited with writing the row-level security feature.
For big data users, there's BRIN indexing as a faster, smaller replacement of B-tree indexes on very large tables; and an abbreviated keys algorithm for faster table sorting.
PostgreSQL 9.5 adds the SQL clauses CUBE, ROLLUP and GROUPING SETS to help compress sumarization into fewer queries. Other big data features include Foreign Data Wrappers, and TABLESAMPLE for taking statistical samples of large tables. ®