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

Great

[Anonymous Coward]

Great, but why should I use PostgreSQL when I already have a database, you might ask? Here's why.

Magical upgrade needed

[inflex]

Now all I need is a magical installer which will convert my existing 7.2.4 systems to 8.x without missing a single beat or disrupting the existing programs written against the old libs.... I'm serious!

Converting 200Gb of data is not something you want to try do without downtime.

All that said, it's good to see PostgreSQL always improving. Good one guys.

Web Server Difficulties

[Reality_X]

Hi Folks,

Please take it easy on 'wwwmaster'.

'www' fell over a couple of hours ago, and a couple of mirrors are coming online to round-robin the address.

Can someone please change the the first link ("PostgreSQL project") in the story to point to 'www'?

Thanks.

I've been waiting for this

[wackysootroom]

Goodbye Oracle, hello PostgreSQL. Now I can have a mostly SQL92 compliant database with ACID, transactions and now PITR and tablespaces that I can use on the server and on a win32 desktop.

For those of you wanting a great frontend, try PGAdmin3. It works on Win32 and Linux.

What's New in 8.0

[Pan T. Hose]

An adequate replacement for MySQL on Windows. Can anyone say WAPP instead of LAMP?

Not only that. Here's the most important link: What's New in 8.0. (To editors: why there are links to torrents, but no link to features?)

Re:Erm?

[Erik Hensema]

In PostgresQL you can make a table inherit parts of another table. This is what's meant by object relational. Example (from the docs):

CREATE TABLE cities (
name text,
population float,
altitude int -- (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

Re:How does it compare to Oracle?

[nemesisj]

It's got a long way to go as far as enterprise features.

There is no clustering support in PostgreSQL (and I mean real clustering, not some Java hack where transactions are shipped off to two separate DB servers, both of which don't know they're part of a cluster). This is pretty much a show stopper as far as using PostgreSQL in the company I work for, as high availability is a large concern, and any downtime would be serious.

In previous versions of PostgreSQL, the pg_dump and pg_restore tools were not very good - dumps that included tables or views often would fail on reimport because PostgreSQL wouldn't know the order in which to import everything. You also had to pass in a number of options on the command line just to get a dump that made sense, and large object support was kind of clunky.

That said, I still use PostgreSQL for many many projects and have used PostgreSQL for many years. It's a great product, but it isn't near Oracle in terms of enterprise level features.

Or LAPP

[Quattro Vezina]

LAPP, maybe, but certainly not WAPP.

Linux + Apache2 + PostgreSQL + Python == kickass webserver

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:Two more features...

[JohanV]

Most of your first request is already implemented in PostgreSQL 8. You can combine a hot backup of the files on the filesystem with the WAL-archiver to have the backup feature you want. It is not per tablespace (yet) so you have to backup your entire database.

For the second request, keep a close eye on the mailinglists. Affilias has hired a core developer to make it happen.
The first stage, master-slave replication, has been released in the form of Slony-1. Yes, it is an add on. No, it is not integrated. But you can add Slony-1 to a running system and add slaves without ever taking the master down, and it is backwards compatible so you can even use it to upgrade running 7.3.x installations to PostgreSQL 8.
The second stage, Slony-2, will be a full multi-master replication solution. (I read something about a 'kickoff' meeting today hosted by Affilias.) The goal is to be able to take a single, out of the box installation of PostgreSQL, plug Slony-1 into it, replicate the database to another box and when that box has caught up switch to full multi-master mode under Slony-2.
The code won't fall out of the sky tomorrow, but people are working on it.

Re:plperl

[AhBeeDoi]

One of the most exciting features of 8.0 is plperl, their Perl-based server side language

I'm not convinced. SQL is supposed to a standard, so you can move from one database server to another with not much effort. This is a big step away from that. Much like the features you'd find in Oracle or MS SQL.

Hardly.

First of all, SQL is supposed to be a sub-language used by other languages to access an RDMS. PL/Perl is a server side procedural language which allows you to write PgSQL functions in Perl. How standard is PL/SQL for Oracle? It's proprietary. In fact, Oracle and MS SQL are not entirely compliant with SQL 92 as they have their own extensions.

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.

It's easier to install and admin than mysql

[hqm]

The dirty secret is that Postgres is actually easier to install and administer than mysql. I don't want to get into a religious argument with mysql users but ... oh hell yes I do.

I have worked with self-educated programmers who did not know how to do simple table joins or even modestly complex SQL queries or transactions, because they had learned what they thought was SQL by using Mysql. There is a whole generation of developers who now think that transactions aren't really necessary in a database application.

Postgres is really an Oracle killer at this point, and I know, having used Oracle. There is quite simply no reason to use any other relational database at this point, especially to back a live web site.

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

[Cajal]

In MySQL (with MyISAM tables), the reason things like count(*) are fast is that MyISAM pre-computes those values. It can do this because it locks the table on insert and update. PostgreSQL doesn't lock the table on modifications -- it allows concurrent access via Multi-Version Concurrency Control (MVCC). Basically, each row in the DB has additional information (used internally by PostgreSQL), which stores which transaction created and last modified the row. PG uses this to determine if any given row should be "visible" to the current transaction. Because this informaiton is constantly changing (and varies from transaction to transaction), you can't precompute things like count(*) and sum(*). See http://developer.postgresql.org/pdf/internalpics.p df for more info (start around page 56).

Re:How does it compare to Oracle?

[kpharmer]

Postgresql is ready to take on the smaller, non-critical databases that oracle used to get. This is significant proportion of the databases out there, and will take revenue away from oracle. (Mysql will actually probably take more revenue away, but it has too many quality problems and functionality gaps to really deserve to.)

But there are many other, more demanding databases that postgresql isn't yet ready for. Oracle, DB2, and even SQL Server 2005 all have very mature & solid: optimizers, replication, partitioning solutions, parallelism, failover/clustering support, etc.

Here are two examples:

Using db2 for example, you can create a view which is automatically populated by the database like a table (MQT). Then any queries against the base tables that could be sped up by hitting this view will be rewritten by the engine to hit the view. Now, this might seem like needless fluff if you're just writing a hobby php app. But if you need to implement a commercial app like SAP with its 6,000 tables - and you have performance issues - you can make adjustments in the database layer this way. Also, if you're provoding adhoc reporting for hundreds of users on a terrabyte of data - this technique can provide *dramatic* performance benefits.

Another example is partitioning. Back to db2 (which I work with the most): you can spread a database across a dozen separate servers using a hashkey. Now, every query will have all dozen servers working independently on its own fraction of the data. On each of those servers, you can then partition again, this time using ranges or values (MDC) - so that data that doesn't apply to a query will be skipped in tablescans of that table. Using these techniques you can get sub-second response to *adhoc* queries against a terrabyte of data - without indexes (notoriously unreliable here).

Lots more examples where the above came from. Sure, you will pay real money for licensing, hardware, and labor to implement these. Then again, the two above features actually save you in hardware costs. Additionally, some problems are big enough that they can easily justify the cost of licensing a product like this. I've seen these techniques used to save companies million, even hundreds of millions of dollars.

Re:PGSQL has its own gotchas

[amorsen]

If your RDBMS is doing a full table scan just to do a count(*), then it (or your code) is badly broken.

Postgres knows how many rows are in the table, but it does not know how many of those you can see. Some of them may be inserted speculatively by another transaction. Postgres needs to go through each row to determine whether or not that row is actually visible to you. It is possible to turn this into an O(1) operation if you're willing to do sufficient work on inserts and deletes. Whether this is a good tradeoff depends on how often you do count(*) compared to how often you do inserts and deletes.

the Oracle comparison

[justins]

Oracle will always have more features than any other given database product you care to compare it to, and there will always be those who find some of those Oracle-unique features irresistable. That's what it means to be the central product of a very large, wealthy and reasonably effective software company.

It is not a very meaningful comparison, though. Oracle also has a lot of stuff that nobody would be happy to see tacked on to PostgreSQL. Oracle is notoriously difficult to administer. The download of Oracle's database product is probably 500 times larger than PostgreSQL. (and 20 times larger than Oracle itself was just a few versions ago, which is interesting, and yes I'm guesstimating but still...)

In short, PostgreSQL doesn't need all the features of the Oracle db to be successful, as *nix and NT did not need all the features of a mainframe OS to be successful.

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.

Re:That's really dumb.

[Cajal]

You're telling me that MySQL locks the entire table when I insert a row? You must be joking - that would bring a database to it's knees.

MySQL can use different "storage engines" for different tables. One of their older (and arguably, more widely deployed) engines in MyISAM, which does table-level locking (their newer engine, InnoDB, does row-level locking). From the MySQL Manual (sec 7.3.1):

Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.

and from Section 7.3.2:

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

This is one of the many problems with MySQL. I agree with you that Oracle (and PostgreSQL) are much better designed.