PostgreSQL 9.0 beta 2 just got released this week. We may see another beta before 9.0 is finally released, but it looks like PostgreSQL 9.0 will be here probably sometime this month.
Robert Treat has a great slide presentation showcasing all the new features. The slide share for those on Robert Treat's slide share page.
We'll list the key ones with our favorites at the top:
Our favorites
- The window function functionality has been enhanced to support ROWS PRECEDING and FOLLOWING. Recall we discussed this in Running totals and sums using PostgreSQL 8.4
a hack for getting around the lack of ROWS x PRECEDING and FOLLOWING. No more need for that. This changes our comparison we did Window Functions Comparison Between PostgreSQL 8.4, SQL Server 2008, Oracle, IBM DB2.
Now the syntax is inching even closer to Oracle's window functionality, far superior to SQL Server 2005/2008, and about on par with IBM DB2. We'll do updated compare late this month or early next month.
Depesz has an example of this in Waiting for 9.0 – extended frames for window functions
- Ordered Aggregates. This is extremely useful for spatial aggregates and ARRAY_AGG, STRING_AGG, and medians where you care about the order of the aggregation. Will have to give it a try.
For example if you are building a linestring using ST_MakeLine, a hack you normally do would be to order your dataset a certain way and then run ST_MakeLine. This will allow you to do
ST_MakeLine(pt_geom ORDER BY track_time) or ARRAY_AGG(student ORDER BY score) This is very very cool. Depesz has some examples of ordered aggregates.
- Join removal -- this is a feature that will remove joins from the execution plans where they are not needed. For example where you have a left join that doesn't appear in a where or as a column in select. This is important
for people like us that rely on views to allow less skilled users to be able to write meaningful queries without knowing too much about joins or creating ad-hoc query tools that allow users to pick from multiple tables. Check out Robert Haas why join removal is cool for more use cases.
- GRANT/REVOKE ON ALL object IN SCHEMA and ALTER DEFAULT PRIVILEGES. This is just a much simpler user-friendly way of applying permissions. I can't tell you how many times we get beat up by MySQL users who find the PostgreSQL security management tricky and tedious to get right.
Of course you can count on Depesz to have an example of this too Waiting for 9.0 - GRANT ALL
Runner ups
- pg_upgrade is now included in contrib and much improved we hear. Can't wait to try this out. This will allow for in-place migration from PostgreSQL 8.3+ -> 9.0
- Streaming replication, Hot standby more details Built-in replication in PostgreSQL 9.0
- STRING_AGG -- this is a nice to have so you don't need to do array_to_string(ARRAY_AGG(somefield), '\') and with the ORDER BY feature its even better.
- Conditional triggers - triggers that only run based on some boolean expression. Improves trigger speed by a bit for some kinds of triggers
- ANSI SQL column level triggers
- DROP IF EXISTS for columns and constraints
- Python 3 support - now you can write database stored functions in PL/Python 3 as well as the 2.5/2.6 series.
- Explain plan improvements -- now you can see shared buffers using
EXPLAIN (ANALYZE, buffers) SELECT * FROM sometable;
- DO it -- run anonymous PL functions subroutines. Useful for one off kind of stuff or mixing various languages in a work process.
- Windows 64-bit support
- exclusion constraints - this is useful for schedules and so forth to prevent overlapping periods. Jeff Davis has some slides up on how this works. Not Just Unique.
- other stuff
Another new feature is the GUC for outputting bytea data as HEX or ESCAPED. The default is now HEX. This has broken our ability to display geometries using OpenJump ad-hoc query tool
unless we do a work-around of ALTER DATABASE gisdb SET bytea_output='escape';. We are still not quite sure how much of an issue this is. PostgreSQL 9.0 ST_AsBinary rendering