I have always had a special affection for PostgreSQL because it is the first RDBMS I used. At the time, it was a version of PostgreSQL 7.1.3. The new version of PostgreSQL offers some new large concepts which are oriented for high availability.
The Hot Standby allows you to run read-only queries while the server is in recovery or standby mode archive.
The streaming replication allows a standby server to stay up to date than is possible with log shipping in files. The standby connects to the primary, which sends the standby WAL records once they are generated, without waiting for a WAL file to get filled.
The full list of the main innovations:
Replication and scalability
- Warm restart
- Continuous Replication
Administration and Security
- Getting to level pg_upgrade integrated with on-site, formerly known pg_migrator, to make major upgrades without having to make backups (from experience, I’d still …)
- RADIUS Authentication – Attention, the user must already exist in the database before RADIUS can be used for authentication.
- Check the strength of passwords with password check, as the size of the password or if the password contains the login
- Orders to facilitate the management of permissions in the database (GRANT ALL ON and DEFAULT TEMPORARY)
Database design and SQL
- Unique constraint delayed
- Triggers on condition
- Triggers column-level
- Sort in aggregates
- New window functions (PRECEDING and FOLLOWING ROWS)
Stored Procedures
- Blocks anonymous procedural code (instruction DO)
- Stored Procedures Perl and Python improved (including support for Python 3)
- Calls for named parameters
Performance and advanced features
- Enhanced Messaging events (LISTEN / NOTIFY)
- Support for Windows 64-bit
- Optimization of queries generated by the ORM (deletion of JOIN)
- Unique keys for data non-scalar (exclusion constraints)
- Extended support key-value data (hstore)
- Plans to JSON and XML formats