Last checked and updated on 10 Jun 2026
What Is New in PostgreSQL 6.5
| Category | Key Changes |
|---|---|
| New Features | SQL92 syntax additions, new built-in functions, SELECT FOR UPDATE support |
| Performance | Optimizer improvements, index-only scans, faster sorting and joins |
| Data Types | Enhanced date/time handling, new geometric operators |
| Administration | Improved vacuuming, new configuration options |
| Client Interfaces | libpq enhancements, new embedded SQL features |
What SQL92 features were added in PostgreSQL 6.5?
PostgreSQL 6.5 expanded its SQL standards compliance with several key SQL92 features. The update introduced support for the SELECT FOR UPDATE statement, which allows explicit row-level locking during transactions. This is particularly useful for preventing lost updates in concurrent environments where multiple transactions might try to modify the same data.
Additional syntax improvements included better support for outer joins and more comprehensive subquery handling. These changes made the query language more powerful and aligned with the standard, reducing the need for workarounds.
How did the optimizer get better in version 6.5?
The query optimizer received significant enhancements to make it smarter about choosing execution plans. It got better at estimating the cost of index scans versus sequential scans, especially for queries with LIMIT clauses. This meant that for queries fetching only a few rows, the optimizer was more likely to pick an index scan, resulting in faster response times.
Another improvement was the introduction of index-only scans for certain queries. If all the columns needed by a query were present in an index, the executor could sometimes retrieve the data directly from the index without having to visit the main table, reducing I/O overhead.
What new functions and data type operators were introduced?
This release added a suite of new built-in functions to broaden the capabilities available directly within SQL. Functions for string manipulation, mathematical operations, and type conversion gave developers more tools without needing to write procedural code.
For geometric data types, new operators were added to perform calculations and comparisons. The date and time handling was also enhanced with more robust functions for extraction and arithmetic, making temporal queries easier and more accurate to write.
How was database administration improved?
The VACUUM process, crucial for reclaiming space and maintaining performance, was made more efficient. The improvements helped reduce the runtime of vacuum operations on large tables, which was a common pain point for database administrators.
New configuration parameters were introduced, giving admins finer-grained control over memory allocation and other resource limits. This allowed for better tuning of the database server to match specific workload requirements and available hardware.
What changed for developers using client interfaces?
The libpq library, used by C applications to connect to PostgreSQL, saw enhancements in its asynchronous query processing capabilities. This allowed client programs to submit queries and continue working without blocking, checking for results later, which was great for building responsive applications.
Support for Embedded SQL (ECPG) was expanded, making it easier to integrate SQL queries directly into C program code. The preprocessor became more reliable and gained better handling of complex SQL statements within host variables.
FAQ
Does SELECT FOR UPDATE lock the entire table?
No, SELECT FOR UPDATE in PostgreSQL 6.5 acquires row-level locks. It only locks the specific rows returned by the query, not the entire table, allowing other transactions to work on different rows concurrently.
Can the new optimizer use multiple indexes in a single query?
While the optimizer was significantly improved, the ability to perform bitmap index scans and combine multiple indexes in a single query was not introduced until a later version. The 6.5 optimizer focused on better cost estimation and choosing the single most efficient access path.
Were foreign key constraints added in this release?
No, declarative referential integrity (foreign key constraints) was a major feature that came in a later release, PostgreSQL 7.1. Version 6.5 did not include this capability.
Is the new VACUUM improvement automatic?
No, the improvements to the VACUUM command in 6.5 made the manual process faster and more efficient. The automatic vacuum daemon (autovacuum) was a feature developed and introduced in much later versions of PostgreSQL.
Does the async feature in libpq require multithreading?
No, the asynchronous query support in the enhanced libpq library does not require the use of threads. A single-threaded application can use non-blocking connections to submit queries and use PQisBusy and PQconsumeInput to check for results without blocking the main loop.