PostgreSQL 8.4 Out 191
TheFuzzy writes "PostgreSQL version 8.4 is now out and available for download. The main cool features in this version are: recursive queries (for doing trees etc.), windowing functions (for doing reports) column-level permissions, parallel database restore, a beta in-place upgrade tool, and a host of administrative improvements. And, of course, better performance, mainly on reporting queries. Some of the over 200 new or enhanced features are listed here."
And more... (Score:5, Informative)
Upgrade in place is done via pg_migrator [pgfoundry.org].
VACUUM now makes use of a "visibility map", which means that it doesn't need to process old data each time VACUUM is run. If you run VACUUM on a large table, and then immediately run it again, the second run will be instant.
The recursive queries are the SQL standard common table expressions, that is, WITH and WITH RECURSIVE.
The window functions is a great addition, but with PostgreSQL it's even better because you can define your own custom window functions with CREATE FUNCTION.
There are also a huge number of little improvements, like "auto explain" which is a module that can automatically log the "EXPLAIN ANALYZE" output when a query takes a long time. This is a great convenience for DBAs, because you don't have to look at long-running queries in the log and attempt to EXPLAIN ANALYZE them manually.
Re:So why (Score:5, Informative)
No, the replication and hot standby patches were not considered of high enough quality to be a part of 8.4. They will be a high priority during development of 8.5.
PostgreSQL would never, ever ship something which has a WTF-list as long as MySQL's replication does: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html [mysql.com]
Oh, hallelujah! (Score:4, Informative)
That alone is worth the upgrade to me. Currently, if you have two deadlocked transactions, it's a pain in the neck to figure out exactly which ones are involved.
Windowing Functions (Score:3, Informative)
OK, the windowing functions are exactly what I was waiting for. RANK and DENSE_RANK are phenomenal.
See the presentation by Hitoshi Harada here: PDF Presentation [pgcon.org]
Re:cross database joins?? (Score:5, Informative)
If you're coming from MySQL: What MySQL calls "databases" are called "schemas" in PostgreSQL. MySQL has no equivalent of PostgreSQL's "database".
There is also db-link and dbi-link.
Parent is correct (Score:5, Informative)
You can have multiple schemas with the same table names and disambiguate them by referring to schema.table instead of just table. In other words, they're like MySQL databases, and some mod owes the parent an apology.
Um... (Score:1, Informative)
SQL Server has always been a decent database. Going to PostgreSQL would probably be a downgrade. Though if I have to say one thing, it would be this: SQL Server 2005 is a lot better in my experience than 2000.
Re:So why (Score:3, Informative)
MySQL definitely supports foreign keys. Just make sure you're not accidentally using a myisam table, and don't use column level reference defs, cause both will result in a silent ignoring of what you asked it to do.
Wish there was a way to tell it to only use certain table types and throw errors when things go wrong though.