Last checked and updated on 05 Jun 2026
What Is New in PostgreSQL 8.4
| Category | Key Changes |
|---|---|
| Monitoring | pg_stat_statements, monitoring functions |
| SQL Features | Windowing functions, common table expressions |
| Administration | Parallel restore, column-level permissions |
| Performance | Hash indexes, caching improvements |
| Data Types | Arrays, composite types, ENUM enhancements |
How does PostgreSQL 8.4 improve monitoring and profiling?
The biggest monitoring upgrade is pg_stat_statements, a module that tracks execution statistics for all SQL statements. It captures query counts, total time, and rows processed, which is invaluable for identifying performance bottlenecks.
New system functions like pg_stat_get_activity() and pg_stat_get_progress_info() provide deeper visibility into backend activity. In practice, this means you can finally see what queries are running and how they're performing without third-party tools.
What new SQL capabilities were introduced?
Window functions are the headline feature, bringing analytical queries directly into PostgreSQL. You can now use ROW_NUMBER(), RANK(), and OVER() clauses for complex reporting without cumbersome self-joins.
Common Table Expressions (CTEs) with the WITH clause make queries more readable and maintainable. This matters because it allows breaking down complex queries into logical parts, though recursive CTEs didn't arrive until later versions.
What administration tools got better?
pg_restore gained parallel processing, significantly speeding up database restoration from backups. This was a game-changer for large databases where restore times were previously measured in hours.
Column-level permissions arrived with GRANT and REVOKE support on specific columns, providing finer-grained security control. You can now restrict access to sensitive columns like salaries without creating separate views.
How did performance change in 8.4?
Hash indexes became crash-safe and WAL-logged, making them viable for production use. They're particularly useful for equality comparisons where b-tree indexes were overkill.
The free space map management was overhauled to handle larger databases better, reducing vacuum overhead. The shared memory allocation was also improved, which helped with connection scaling on busy systems.
FAQ
Is pg_stat_statements enabled by default?
No, you need to add it to shared_preload_libraries in postgresql.conf and create the extension in your database. It's worth the setup effort for any production system.
Do window functions work with existing aggregates?
Yes, you can use standard aggregates like SUM() and COUNT() with OVER() clauses. This lets you create running totals and moving averages directly in queries.
How much faster is parallel restore?
It depends on your hardware and backup size, but typically 2-4x faster with multiple jobs. Use the -j flag with pg_restore to specify the number of parallel workers.
Are hash indexes better than b-tree now?
Only for specific cases - equality operations where the index fits in memory. B-trees still outperform for range queries and ordered results.
Can I use CTEs for recursive queries?
Not in 8.4 - recursive CTEs came in 8.4.1. The initial release only supports non-recursive common table expressions.