What Is New in PostgreSQL 7.0
PostgreSQL 7.0 is a major release packed with foundational improvements that significantly boost performance, enhance SQL standard compliance, and improve overall usability. This version marks a substantial step forward in making PostgreSQL a top-tier open-source database.
| Category | Key Changes |
|---|---|
| New Features | Foreign Key Constraints, Outer Joins, SQL92 Standard Syntax |
| Performance | New Optimizer, Better Query Plans, MVCC Overhaul |
| Improvements | JDBC & ODBC Drivers, Enhanced Privileges, New Functions |
| Deprecated | Old Join Syntax, pg_dump -o flag |
What are the major SQL feature additions?
The headline features are full support for foreign key constraints and a complete implementation of outer joins. This finally brings PostgreSQL's DDL capabilities up to the commercial database standard.
You can now define REFERENCES constraints to enforce relational integrity directly. The optimizer understands these constraints, which can lead to better query plans. For joins, the standard SQL92 syntax with LEFT JOIN, RIGHT JOIN, and FULL JOIN is fully supported, making complex queries much clearer to write.
How does the new optimizer improve performance?
The 7.0 optimizer is a complete rewrite, making it far smarter at choosing the fastest way to execute a query. It's the single biggest change under the hood and affects almost every query you run.
It does a much better job with joins, subqueries, and queries involving OR conditions. In practice, many applications will see a noticeable speedup without changing a single line of code. The old optimizer had some blind spots, especially with complex queries, which are now largely eliminated.
What changes were made to the MVCC implementation?
The Multi-Version Concurrency Control (MVCC) system was overhauled to reduce disk space consumption and improve vacuuming efficiency. This tackles one of the long-standing administrative headaches of earlier versions.
Tuple headers are now smaller, so tables take up less space. The vacuum process is also more effective at cleaning up after deleted or updated rows. This matters because it reduces the frequency of required vacuuming and helps maintain performance over time.
Are there any client connectivity improvements?
Yes, both the JDBC and ODBC drivers were completely rewritten from the ground up. The new drivers are more reliable, standards-compliant, and performant.
The JDBC driver (postgresql.jar) now supports the full JDBC API, making it much easier to build Java applications. Similarly, the new ODBC driver provides a robust connection path for Windows and other ODBC-based tools.
What syntax is now deprecated?
The old non-standard outer join syntax using *= and =* is officially deprecated. You should migrate to the standard SQL92 LEFT JOIN and RIGHT JOIN syntax immediately.
Additionally, the -o (oid) option for pg_dump is deprecated. The new method is to use the -i option or the PGOPTIONS environment variable to include OIDs, which provides more flexibility.
FAQ
Do I need to dump and reload my database to upgrade to 7.0?
Yes, a full dump and reload is required when upgrading from a pre-7.0 version. The on-disk storage format changed significantly to support the new features and MVCC improvements.
Will my existing applications using the old join syntax break?
They will still work for now, as the old syntax is only deprecated, not removed. However, you should plan to update your queries to the standard SQL92 syntax to ensure future compatibility.
What's the most practical benefit of foreign key support?
The database now enforces data integrity for you. You no longer have to rely on application logic or triggers to prevent orphaned rows, which reduces bugs and simplifies code.
How noticeable is the performance gain from the new optimizer?
It varies by workload, but complex queries with multiple joins or subqueries often see the most dramatic improvement. Some queries could be orders of magnitude faster.
Are the new JDBC and ODBC drivers backwards compatible?
They are compatible with existing databases, but you will need to update your client applications to use the new driver JAR or DLL, as the old ones are incompatible with the 7.0 server.