A look at PostgreSQL [LWN.net]

July 7, 2004

This article was contributed by Joe 'Zonker' Brockmeier.

With the 7.5 release of PostgreSQL not too far away, and news of new features sponsored by Fujitsu and Software Research Associates (SRA), we decided to take a look at the PostgreSQL project and what users might be able to expect in the coming months. We spoke to PostgreSQL steering committee member Bruce Momjian about the upcoming 7.5 release, and the "state" of PostgreSQL. According to Momjian, "the project is doing very well."

We're very organized and thorough in the way we do stuff. That's kind of paid off [in that] every three or four months it seems like we're making another kind of milestone in what we can do with Postgres in terms of adoption and features. It's kind of hard to put it into words, I've stopped getting surprised at how successful it's been.

Though each new release is a milestone, Momjian said that the 7.5 release would have an unusual number of new features. In part, that's thanks to Fujitsu and SRA underwriting the development of tablespaces, nested transactions development and support for Java server-side programming. Momjian is employed by SRA to work with PostgreSQL and the community, and says the company approached him to broker arrangements with developers already working on those features:

Big missing functionality typically takes weeks to develop, very hard for developers to spend weeks volunteering, they've got to put food on the table. Fujitsu would supply X amount of money for the amount of time they're spending working on these features, [which were] very slow going because they were only spending a few hours a week... the infusion of cash allowed them to commit weeks.

The tablespace feature will allow a database to be spread across multiple storage devices. Currently, PostgreSQL requires all of a database to exist on a single filesystem. This can be a problem for performance and space reasons. In 7.5, by default, PostgreSQL will continue to store everything on the same filesystem, but Momjian said that an administrator will be able to use tablespaces to move a table or entire database to another filesystem. Even better, Momjian says that this will not impact an application using the database -- so existing applications will not need to be rewritten to use a database that takes advantage of tablespaces.

Oracle users and developers will know nested transactions by the name "savepoints." This feature in 7.5 will give developers "better control over failure cases with multi-statement locks" and allow developers a better option than simply causing an entire transaction to fail if one statement fails. Momjian noted that PostgreSQL already had "a robust system" but that developers porting applications from Oracle needed finer control than the current PostgreSQL system allows. "Some applications needed logic that would say 'I want to try inserting, but if that fails, I want to do something else.'"

Another feature in 7.5 of interest to many users will be point-in-time recovery. With point-in-time recovery, PostgreSQL will allow users to recover information "up to the instant of hardware failure."

Of course, not all PostgreSQL users are defectors from the Oracle camp. The focus of late for many open source projects seems to be on the "enterprise" features, which might lead hobbyist and small business users to wonder whether those projects will continue to be suitable for their use. We asked whether focus on enterprise features might detract from the "little guy," and he said that while PostgreSQL 7.5 will have many features that are aimed directly at the enterprise users, the PostgreSQL project isn't losing sight of the small-scale users. In fact, there are several features that are directly aimed at the little guy rather than enterprise users.

One of those features is direct import of comma-separated value (CSV) files. Momjian said that many users have asked for the ability to directly import a CSV file produced by a spreadsheet program or other utility. Prior to 7.5, users would have to convert those files into a suitable format for PostgreSQL to import using a Perl script or other utility -- but with 7.5 users will be able to "load CSV natively right into Postgres."

Another "little guy" feature of interest in 7.5 is the ability to change the data type of a column. In prior versions of PostgreSQL, it would be necessary to add a new column, import data from the existing column into the new column, drop the old column and then rename the new column to change the data type. In 7.5, users will be able to simply alter the data type of a column in one easy step.

Momjian also said that the Postgres developers do worry about "bloat," and that "we've managed to come very far with adding features, without impacting performance or readability [of the PostgreSQL code.]" On average, he said that PostgreSQL adds "maybe 50,000 lines every year to the code...no feature goes in unless it fits like a glove."

Though not part of the 7.5 release, the recently announced Slony-I replication system bears mentioning as well. The Slony-I replication system, sponsored by Afilias, does asynchronous master-to-slave replication, slave promotion and failover.

In addition to the obvious new features, there's also a little work underneath the hood that will benefit PostgreSQL users as well. Momjian told LWN that the PostgreSQL team had done a "major redesign" in the way that PostgreSQL buffers disk writes, which will result in a "serious performance improvement" in the next release.

Though perhaps of little interest to the LWN readership, Momjian also pointed out that 7.5 will be the first version of PostgreSQL to have a native port to Win32:

We feel that the Windows port is important to highlight the accomplishments of open source to the people running on the Windows platform. You can't show how good open source is if it's not running on their platform.

There is no set date for the 7.5 release yet, but he said that it should be out be out by the end of the year, once the project has been able to conduct extensive testing of all the new features. After the release, he predicts "increased migration from proprietary databases," and notes that the PostgreSQL project is already seeing 1,000 to 2,000 downloads per week of the unofficial, unadvertised testing release of PostgreSQL for Windows.

In all, the next release of PostgreSQL should be quite impressive, and allow a number of organizations to dump expensive proprietary databases for an open source alternative.

(Log in to post comments)

Grumpy?

Posted Jul 9, 2004 21:25 UTC (Fri) by corbet (editor, #1) [Link]

Databases are definitely on the list. The GE articles are a lot of work to write, though, and this one would be worse than many. So I have no idea when it might be possible to get it done; not anytime soon, certainly.

Grumpy?

Posted Jul 10, 2004 16:59 UTC (Sat) by yodermk (subscriber, #3803) [Link]

What I would also like to see is a "grumpy editor's" comparison of database front ends. Ease of designing forms and reports and scripting events should be considered. That one would likely be a bear to write though.

My organization was looking at replacing Access with an open source tool, but the consensus was that there isn't a suitable alternative at this time. :( Would love to be proven wrong on that!

It would need to work on 'Doze for now, which would seem to eliminate Knoda and Kexi and Rekall, and OOo is just a tad bit klutzy...

Grumpy?

Posted Jul 11, 2004 18:50 UTC (Sun) by james (subscriber, #1325) [Link]

Would it help to post a paying-subscribers-only "article" mentioning that you are planning a Grumpy Editor's guide to (say) databases, and asking for subscribers' comments?

That could help you find all the possible projects (Grumpy Editor articles are particularly valuable when they review low-profile projects that could do with more publicity), and give you a series of features that LWN users like (or have noticed are often absent).

James

Grumpy Guide to Databases

Posted Jul 16, 2004 20:20 UTC (Fri) by ringerc (subscriber, #3071) [Link]

I find it difficult to imagine a topic more fraught with pitfalls, due to
the impressive complexity of the subject matter and the wide range of
users needs. It's also the only topic I can think of that's likely to
result in MORE fuss than a KDE/GNOME article.

Such an article would need to extremely clearly set out the evaluation
criteria, user experience, and user needs before beginning. Comparing
feature lists is IMHO almost useless without knowing what those features
will do for you. Even constructing feature tables is almost impossible to
do in a way that is both comprehensive and vaguely comprehensible (ie not
a 5000 entry table) because what to one person is irrelevent is to another
person the straw that breaks the camel's back.

Also, lots of the things people say about databases are quite subjective.
For example, one frequently hears MySQL being described as incredibly
fast... but that depends to a huge extent on your workload. It's very fast
for many SELECTS on tables that don't see much write activity, but pays
for that in write performance, especially for concurrent clients. At
least, that's my impression. Therein lying the root of the problem - good,
meaningful benchmarks (especially up-to-date ones) are hard to come by.

Meh. It'd still be a really interesting article, it suspect it'd just be a
huge job to do well.

Grumpy?

Posted Jul 10, 2004 0:11 UTC (Sat) by dlang (✭ supporter ✭, #313) [Link]

there really aren't any good comparisons. all of the comparisons that are out there are old enough that both MySQL and Postgres have changed drasticly since the reviews (MySQL has gotten many new features that were missing at the time, but Postgres has moved on in features and drasticly improved it's speed)

also it's hard to think of a topic that people are going to be more passionate about (well, I guess you could do a GNOME vs KDE article ;-)

a happy postgres user since postgres 7.0
David Lang

win32 port

Posted Jul 16, 2004 20:30 UTC (Fri) by ringerc (subscriber, #3071) [Link]

I'd argue that the win32 port of PostgreSQL is quite significant to the
LWN.net crowd - or I'd expect it to be. For one thing, it means that you
can add 'and it'll run on Windows if we later need it to' to the list of
reasons why you chose PostgreSQL when talking to PHBs (say, in project
planning). It also means that if you have developers who use Windows at
home, they can run a DB server for development much more easily.

It also means that if your company has win32 servers, you have the option
of using a powerful, affordable, reliable and portable database for your
in-house application development, knowing there will be no pain if you
want to migrate to *NIX later and that *NIX client drivers won't be a
problem. Oh yeah, and you won't pay anything in software licenses, which
means you can develop an app you might not otherwise be able to.

I suspect a lot of the LWN readership will work with or be exposed to
win32 environments, willingly or unwillingly, and for many of them this
could be quite significant.

For me, it just means one less portability issue to worry about, and
eliminates the one major reason I might be forced to use MySQL for a
project. (The remaining minor reason is MySQL's amazing full-text indexes
and boolean MATCH). It's nice to know that the database runs on win32, but
I don't expect to need to actually do it.