PostgreSQL 8.0 Released

Christopher Cashell writes "The PostgreSQL project has released version 8.0 of their well known Object-Relational Database. New features include: Win32 Native Server, Savepoints, Point-In-Time Recovery, Tablespaces, and lots more. Downloads are available via bittorrent for Unix/Linux, and the much anticipated Win32 version, or via ftp (use a mirror!)." (Here's the official announcement.)

Comments

Two more features...

[teqo]

... and it would really really rock!

• Hot tablespace-based backups, combined with write-ahead log backups, as seen in Oracle since version 8 (or even 7?) This is an extremely nice feature when you have large databases and no chance for regular scheduled downtime and still want backups, both complete and incremental ones. Compared to the export-based feature of PostgreSQL, it would put way less load onto the server, because tablespace-based and WAL-based backup bypasses the SQL engine, so it is copying a (for example) 200 GB files vs. 200 GB query-based export
• Better and more integrated replication. There are a number of independent projects that want to create replication add-ons, like pgreplication and the older, more academic Postgres-R, but that's not really production quality so far. According to some consultant that is working tightly with PostgreSQL and the developers, they are working on it, but he was really hedging when asked about advanced features and was theorizing how practically impossible and/or expensive multi-master realtime replication would be... An optional feature for many users, granted, but still something you might want for scaling beyond certain limits.

Said that, PostgreSQL is a really great thing, and being FOSS, I could of course always go ahead and add the named features... .)

Re:PGSQL has its own gotchas

[JohanV]

I would consider the PostgreSQL gotchas to be of an entirely different category as the MySQL gotchas.
In PostgreSQL the gotchas are all about performance. And while it may be bad that the database crawls if I didn't schedule a job with vacuum and analyze, that does not have any long term effects. I just run the maintenance tasks manually, add them to a cronjob and I am good to go.
In MySQL however, the gotchas are mostly about data integrity. And that means that they can be disasterous because they can lead to dataloss. Unless you can explain to me how to get back whatever was silently truncated by MySQL if I discover the problems a few days later.

I do very much prefer PostgreSQLs focus on data integrity. But of course I actually read the manual so I won't be bitten by them in either database.

Re:PGSQL has its own gotchas

[StormReaver]

I have several tables with over 3 million rows, and most aggregates return immediately (or nearly immediately). count(*) is the only dog since it reads every row to get the count, but it's acceptable since I rarely count all rows in the table. I've often wondered why the developers couldn't keep an internal count of all active rows, so count(*) would return immediately, but I'm sure they have their reasons.

You've got to be joking about PostgreSQL having a weak optimizer. If it's weak, only the computer can tell.

Your Wiki link spent most of its space praising PostgreSQL for its advanced features, while your intent is clearly to denigrate it. If that represents PosgreSQL's worst facets, then I am very, very happy.

Re:PGSQL has its own gotchas

[JohanV]

Performance is still an issue when you don't read the manual and never perform maintenance on your database. No matter how good the planner is, if it doesn't have acurate statistics because nobody ever runs ANAYZE your database will crawl. No matter which version you use, if you never VACUUM dead row versions will accumulate and eventually kill your performance.

I use PostgreSQL on a daily basis and when I change a database schema I will make sure that I run a VACUUM ANALYZE after committing the changes. But many of the people that we host don't bother to read the manual and don't do this. Usually this is no big deal because it gets picked up by the scheduled jobs. But every now and then somebody calls in a panic because their website is slow. And it always turns out to be major schema changes throwing the planner off. A quick VACUUM ANALYZE gets the performance up again and the customer is happy and has (hopefully) learned a valuable lesson.

This is a real gotcha in PostgreSQL. It has made a few of our customers' websites crawl for a few hours on occasion. But it has never caused dataloss.