PostgreSQL 8.1 Available

atani writes "PostgreSQL 8.1 has been posted, though not officially announced as of this moment. This release includes two-phased commits, improved SMP and overall performance, a new role system replaces the older user/group, autovacuum is now within the backend rather than a separate contrib module, and various improvements, performance enhancements, and bugfixes. " You can also read the developer notes for the popular database. One thing is clear- with the newest Postresql and MySql, you have much to choose from.

Comments

Congratulations to the PostgreSQL Team!

[mw]

I've tested RC1 with some applications, and was really impressed by the bitmap-indices. I hope to see soon a multi-master replication now that 2PC is available. I've heard Slony-2 will have it.

Re:Congratulations to the PostgreSQL Team!

[commanderfoxtrot]

Indeed- congratulations.

I'm using PostgreSQL for many things (custom app, Drupal, Apache logging, back-office) and it's great- this new release adds lots of useful bits.

Probably the most useful new feature for those who use it to run mini web sites will be the MAX() and MIN() functions now using indices- this has been a point of contention for some time.

XA will be useful for bigger stuff.

I'm also really pleased auto-vacuum is now part of the DB.

Now, Gentoo- let's get cracking!

Re:Congratulations to the PostgreSQL Team!

[Anonymous Coward]

PostgreSQL has multi master replication, take a look at PGCluster.

Re:Congratulations to the PostgreSQL Team!

[jadavis]

For real multi-master clustering, I think there are commercial options availabile; but yes, it'll be nice when it's included.

Replication may never be "included", in the sense that it's a part of the core distribution. There's really no reason to have it there, and several reasons not to:
(1) New releases of the replication software would have to wait until a new release of PostgreSQL.
(2) There are many completely different things that go under the heading "replication" that are used in different situations, depending on how often you expect the connections to be up, whether you're replicating for redundancy or speed, etc. Often, a real situation will require using multiple types of replication.
(3) It puts an extra burden on the developers to maintain every line of code in every piece of replication software as well as the rest of the database.

What users really want is to have another well-maintained, BSD licensed project that achieves things not possible with Slony-I, PgPool, etc.

Question for Taco

[Anonymous Coward]

Would you consider changing?
Are you sticking with what you know or are you going to bite the bullet and switch?

Have you upgraded mysql yet? (i seem to recall thats what you use)

Re:Question for Taco

[toofast]

Here's what I think he'll answer to your first question:

"
MySQL works fine on Slashdot. It has all the features and performance we need, it has been running flawlessly for years and we're already familiar with it, so why should we change to anything else? What makes you think there's a bullet that needs biting? Granted, PG looks neat and all, but why exchange a dollar for four quarters?
"

Here's what I think he'll answer to your second question:

"
MySQL 5 doesn't offer us any features we absolutely need (otherwise we'd be using PG, right?) We will upgrade eventually, but we have bigger fish to fry right now, and upgrading our database is not very imperative.
"

Something else he might say:

"
Running slashdot is not as simple as running a basement website that gets 3 hits per hour. Thought needs to be put into these decisions. We can't just run off and install something the day it's released.
"

nevermind - I'm just hard of reading this morning

[mgkimsal2]

Found it!

http://www.postgresql.org/ftp/binary/v8.1.0/win32/ is the link.

Clicking the '8.1' link on the 'new releases' section on the front page takes you to the 'source' files only. That was the problem I had!

PostgreSQL 8.1 Release Notes

[dpage]

The URL for the release notes in the story should be: http://www.postgresql.org/docs/8.1/interactive/rel ease.html#RELEASE-8-1

Re:Too big?

[jocknerd]

A small website should probably use http://www.sqlite.org/. It doesn't require administration or setup. A complete database is stored in a single file. And it implements most of SQL92. Probably more than MySQL but I'm not positive.

Much to choose from?

[ltning]

I count two (2) things: PostgreSQL and MySQL.

Since when was two a crowd? ;)

But neither of the two have a good, cross-platform clustering- or multi-master replication solution, which makes things kinda difficult in our end. For MySQL I can use circular replication, but this is undocumented at best, and very error-prone in extreme situations. The clustering in MySQL doesn't count - in-memory storage limited to half of your physical mem - come on, that's ridicolous. And for Postgres, any multi-master solution there would (currently) dramatically reduce performance. Not the best thing either.

Oh well. Maybe by this time next year.

Re:Much to choose from?

[Anonymous Coward]

Well there is also the Firebird Superserver. It's a released under a Mozilla license (not affiliated with the mozilla project, of course.) (and a couple other licenses also).

http://firebird.sourceforge.net/

So that's three open source SQL database systems; MySQL, Firebird, and PostgreSQL.

Here is a comparision between aviable open source relational databases.
http://www.geocities.com/mailsoftware42/db/ (it's a bit dated)

Here is the migration guide for MS-SQL to Firebird, in which I doubt your interested.
http://firebird.sourceforge.net/manual/migration-m ssql.html

Re:Much to choose from?

[namekuseijin]

there's also:
http://firebird.sourceforge.net/

Firebird is kind of a bastard step brother of PostgreSQL, since they share the common Ingres heritage... it's got foreign keys, triggers and stored procedures for far longer than MySQL, for instance. It's Borland Delphi's Interbase code gone open-source. However, it's a relative newcomer and most who care are Delphi developers.

Its license is neither BSD nor GNU, which i think also makes up for some caution people have against it.

Just installed Win32 version

[mgkimsal2]

Just installed the Windows version. Just a quick couple notes here...

1. Installer was seemingly faster than 8.0 version.
2. Installer could do a little bit better job of hiding all the 'options' (ISBN, Fuzzy search, etc) you can install. Put them behind an 'advanced' button or something - it's a little intimidating to see so many options at first. Also the PL language choices are odd - 'pl/Perl' and 'pl/Perl (untrusted)' ??? These are things that could probably be hidden from the majority of people just testing it out for the first time - either install everything by default, or nothing, but put some of these things behind 'advanced' tabs.
3. pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

I'll probably get flamed for #2, but I'm just putting out some suggestions. The fact that there *is* a Windows installer at all is a good thing - I'd just like to see it improve to help reach a wider audience for future releases.

Re:Just installed Win32 version

[dpage]

pgAdmin III bundled tool is 1.4 - I think I was using 1.3 last time I installed. Visually it looks a bit nicer - I'm assuming they've fixed some bugs or something similar to warrant a number change. :)

1.3 was the development series for 1.4, the new stable release as of yesterday. There are a heap of new features over 1.2 (which shipped with PostgreSQL 8.0) - see http://www.postgresql.org/about/news.419 for details.

Re:Just installed Win32 version

[bigHairyDog]

That's the PostGreSQL philosophy - don't hide the complexity. MySQL did that, and was rewarded by popularity and a generation of people who didn't understand database administration. PostGreSQL makes you pay attention to these options, and educates you in the process.

Re:Just installed Win32 version

[LDoggg_]

THEY USE IT BECAUSE IT WAS THE FIRST THING THEY GOT WORKING...

I think there is some merit to this statment.

I'd love to see postgreSQL added to the xampp package.

The nice thing about xampp is that you simply unzip it into a directory and suddenly you have a huge technology stack of apache, mysql, perl, php all ready to go for stand alone development.

I'm betting there are plenty of windows only developers that wouldn't mind giving either database a shot if they understood how easy it was to get started.

Not announced

[Anonymous Coward]

PostgreSQL 8.1 has been posted, though not officially announced as of this moment.

They were probably waiting for their mirrors to finish syncing before some yo-yo posted to /. and caused every other yo-yo to start downloading.

RC1/RC2

[PhYrE2k2]

Been using RC1/RC2 for some time now. I'm impressed with the role feature, although it won't add much to the average user. The speed has been night and day with 7.3. I also haven't had any problems with the RC's in the slightest except one: a renamed table then wouldn't let me delete the sequence it depended on after the renamed table was deleted. Apparently I wasn't the only one who found it and it has been reported as fixed, though who knows.

My only beef with PgSQL has been there since before the 7's. There is still no way to not show the list of databases to users who have no right or access to those databases. Why should userA with rights to databaseA see that there is a databaseB or databaseC? This really seems like a simple feature, yet nobody will accept it into the release.

-M

Re:RC1/RC2

[PhYrE2k2]

it seems like a simple check, in the same way when it checks for permissions, when doing a list of databases. Seems to make sense.

In a shared database server, it can be important. Although it in a way is security-through-obscurity, many would rather not have their database name 'companyfinances' visible to those with no access. Additionally, on a shared database services, you don't want your customers to know if there are 20 or 200 databases on that server (the number means nothing depending on the size anyway, but looks bad).

-M

Re:RC1/RC2

[chriskl]

No, it's mind-numbingly complicated. If it was easy, it'd have been done by now.

Chris

at last "SELECT ... FOR UPDATE NOWAIT"

[Anonymous Coward]

I always wondered how one could create a multiuser database frontend reliably without this statement:

SELECT ... FOR UPDATE NOWAIT;

If you do

SELECT ... FOR UPDATE;

you either get exclusive access OR you block waiting for the person editing a record to finish. Obviouslt blocking isn't good in a frontend. Hacks like timeout=100, because this can trigger just because the database is being used heavily at that moment, and at best is an ugly work around.

Now with the NOWAIT option you can return instantly and say "Someone else has the row locked", and give control back to the user.

Nice feature to have, but people have been using postgres (and other databases) sucessfully for years. How did they work around it?

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[Daniel_Staal]

Most handle it by giving you the view of the data before the other user started to modify it. The person who is editing's edits do not show until all of the edit is finished.

Postgres docs actually have a chapter on this: Concurrency Control. Like most high-end databases Postgres can handle this situation in different ways, depending on how it is set up...

A database should never tell you that the data is not avalible. It should always give you the best version of the data it has.

Re:at last "SELECT ... FOR UPDATE NOWAIT"

[slamb]

So back to my question, how do existing frontends get around that limitation prior to this very useful feature?

If I understand your question, you're talking about data entry applications doing a "select ... for update" before making changes, waiting for the user to make them, then doing the update and commit. You want to know how applications avoided the "Modify record" page hang until another user was done prior to NOWAIT.

This scheme actually isn't that common. You don't want to block read-only operations on user input, and this would do that. Besides, in a web application, you don't even want to keep a database connection between hits. (You have no way of knowing if the user has closed the browser and isn't coming back. Keeping a connection open for an hour just in case is undesirable, to say the least.)

One way would be to hold a modification lock in an application table or in-memory on the application side. But the most common scheme is called "optimistic locking", which is sort of a misnomer. There's no lock. It works like this:

When you present the modification page to the user, do a select with all of the columns. Keep two copies - the user's modified version and the original. When making the changes, execute an UPDATE like this:

update table set col1 = newval1, col2 = newval2 where id = id and col1 = oldval1 and col2 = oldval2;

If this statement returns a row count of 1, you know that no other changes have been made in this time and you can safely go on. If it returns a row count of 0, the record is missing or changed. Send a "Sorry, another user beat you to it" page back to the user. If you want to get fancy, you can show a three-way diff sort of thing to make their changes anyway.

In the vast majority of cases, this is totally transparent. Only if two people happen to be updating the same columns of the same record in the same table at the same time do you ever get an error. It's almost as simple as completely ignoring concurrency, and it has much better behavior - the user's change being rejected while they still remember what it was, rather than someone's change being silently clobbered.

Re:Too big?

[uits]

(3) SQLite lets me insert a string into a database column of type integer!

This is a feature, not a bug. SQLite does not enforce data type constraints. Any data can be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is sometimes call type or column affinity.

It's pretty sweet

[Mark Round]

I've been running my Blastwave packages[1] of the betas and RCs since the first snapshots became available, and I've been massively impressed with this release. Moving autovacuum into the main package is a really nice touch - all you have to do now is uncomment a few lines in postgresql.conf and it handles it all for you.

The new roles system is also amazingly useful. You can set up a range of roles with a variety of permissions, and then let users "assume" those roles. So you can log in with a day-to-day account, and when you need to do some admin work just SET ROLE [name of your super-user role] and then revert back once you're done. Great if you want to give a junior DBA the ability to create databases, but not the ability to modify other things (such as creating new roles).

Congratulations to the PostgreSQL team anyway - for doing things "the right way" :)

[1]=http://www.blastwave.org/testing/

Indeed, SQLite is an excellent choice.

[CyricZ]

Indeed, SQLite is an excellent choice for small web sites.

I recently set up a small site for a local business. Basically they wanted to be able to upload and edit product information, in addition to other such tasks.

Using a combination of OpenBSD, Python and SQLite, it was very easy to get a system set up for them. Best of all for them, it turned an otherwise obsolete computer into a working server.

Making backups of the database is quite easy too, considering a single file (or three, in their case) can be copied and archived.

While I did not benchmark the system, there is a good chance that the SQLite solution is far more efficient than that of MySQL, and most likely moreso than PostgreSQL. But then again, SQLite doesn't offer many of the more advanced features of larger database systems. If you don't need those features, you might as well trade them off for faster execution and reduced RAM consumption.

MySQL vs. PostgreSQL

[ChrisF79]

I run a very small website as a hobby and I've just always used MySQL because that's what my PHP book featured. I hear quite a bit about mysql in forums on PHP that I go to, and on slashdot itself so I'm asking you... how popular is Postgre? If I used forum posts as any proxy, it would look like MySQL is dominant. Is that really the case?

Re:MySQL vs. PostgreSQL

[elp]

If I used forum posts as any proxy, it would look like MySQL is dominant.

In the webhosting world that is the understatement of the year. In my business I have a little over 1000 mysql clients and all of 3 postgresql clients. I've been advertising Postgres along side mysql for 2 years now. Its not worth the space or staff training costs.

I think the problem was that postgres was always much more complicated to set up and it used to have some pretty serious performance problems in the real world. It was also much harder for a beginner to learn with, error messages like: syntax error near "'" on a 20 line create statement dont help either.

I think too many people started on mysql and stayed with it as their skill improved, to the point where postgres has become an also-run, no matter what impression slashdot might give you. As for the postgres features, looking through some of my customers databases and code it looks as though a lot of them have never heard of indexes much less normalised data, stored procedures, views etc. SELECT DISTINCTS on 30000 row tables... <shudder>

I would love to hear from other people with some real world usage stats.

Re:MySQL vs. PostgreSQL

[kashani]

I'm and ISP/hosting geek as well and see exactly the same thing. Hundreds of Mysql users and one Postgres user. And pretty much the same thing in the users code and databases. My favorite being the 30 minute query which I fixed by suggesting that a 2 million row tables should probably use a int(11) as a primary key instead of a (128)varchar containing the users email address. They still think I'm a genius.

kashani

Re:MySQL vs. PostgreSQL

[StrawberryFrog]

If poularity is your criterion, then why not run your website on MS Access, and allow only Internet explorer clients, while you listen to Britney Spears, drink coke and eat a big mac?

I'm not sure exactly what you mean by "dominant", since both databases are free, so neither takes sales from the other. Both are actively developed. PostgreSQL is IMHO better engineered and has more features, and has had them for longer. But postgreSQL has a reputation as not beeing as drool-proof as mySQL. I wouldn't know, I've been using all kinds of databases for a long time; I find PostgreSQL quite pleasant, and mySQL painfully juvenile.

Embedded version?

[Kunta Kinte]

I would switch to PostgreSQL if there were an embedded version.

SQLite is great but concentrates on being a small database. This design choice is great for many applications probably, though poor handling of large rows ( can't read partial blobs, etc. ), weak concurrency model, etc. inconveniences others.

Re:Embedded version?

[Anonymous Coward]

You should look into Firebird or Ingres for an embedded relational database.

Re:Too big?

[tezza]

The parent is a little disingenuous

sqlite is good. But it does require setup.

You still need to use CREATE TABLE, INSERTs and the like.

You need to connect to the thing in your code, with the same attendent problems as other connection strings. sqlite has a poor jdbc element, if java's your gig.

There are a couple of versions of sqlite. Be aware of this. There is sqlite and sqlite3. Note the jump there from 1 straight to 3. You'll need different DBD drivers there in Perl.

Also, there is no multiple write capability, so you have to synchrinise on this yourself. Who do you trust to write code for ACID commits? Yourself or say, the Postgres team?.

Where sqlite rocks is where the data is entirely read only, which eats out Mysqls lunch.

Also remember that sqlite and Postgres are not mutually exclusive.

In my sqlite setup, I deploy a sqlite database as a file for the fast read only webservers. Where is this data drawn from? My Postgres database with perl to generate the file. So you can get the UPDATE integrity of Postgres with the blazing speed and compactness of sqlite. This works where your application has discreet state where everything is consistent and you can batch update your sqlite.

Re:Postgresql for large databases

[Anonymous Coward]

Handling terabytes of data and 100s of millions of records with pgSql is nothing new.

For details, see

http://www.powerpostgresql.com/Downloads/terabytes _osc2005.pdf

quick question.

[CDPatten]

I am not trolling here, I honestly don't know allot about database software.

How does this compare to SQL 2k and the newly released SQL 2k5? I already know the comments about open source, free, etc.

THAT ASIDE, how do the released compiled programs compare? Does anyone know?

Re:quick question.

[jadavis]

PostgreSQL is rock solid, and very extensible (user-defined aggregates, user-defined procedural languages, user defined functions, triggers, user defined types, table functions, and much more). It probably also performs better in many situations due, in part, to MS SQLs locking vs. PostgreSQL's MVCC.

However, there are more tools available for MS SQL, and there is some form of multi-master replication and probably better table partitioning. MS SQL is not really a bad database, but I think PostgreSQL has it beat except on those two points.

Any real performance analysis is heavily application dependent, however. If that's what you care about, you need to do your own tests.

Re:quick question.

[killjoe]

1) It's free. SQL server costs 5000 per processor or 16,000 per processor depending on the version
2) It runs on every platform. SQL server only runs on windows. It's the only database in wisespread use that locks you to one operating system.
3) It has no limits on how much memory it uses. SQL server standard edition limits itself to 2 gigs as of SQL server 2K (don't know if they fixed that by now).
4) it supports text fields that are only limited by your OS and uses them extremely efficiently. These are not like SQL server blob fields but they are like HUGE text fields that can be indexed or used in aggregate functions.
5) It has user definable data types, user definable operators, user definable functions.
6) It can use perl, python, java, tcl or PG/Pqsl as it's stored procedure languages. YOu can also use C and even compile your C stored procs in with the server if you want super speed.
7) It has multi version concurrency control. This means readers never block writers, ever.
8) No lock escalation. SQL server users know the value of this, everybody else takes it for granted.
9) Lots of built in datatypes like arrays, IP address, geometric types, GIS types etc. Yes it's possible to write a query that asks "select all rectangles that contain this point" or "select all ip addresses in this address mask"
10) Support for hierarchies (in the contrib) so you can natually and intuitively model graphs without writing code or using complex self joins and such. Look up ltree.
11) A fantastic rule system. You can make anything look like a updateable recordset if you are willing to code it.
12) PostGIS.

I am just scratching the surface. I am sure I have missed some other features but that should whet you appetite.

/. Meta question: a wheelbarrow?

[markhb]

Okay, I'm going to bite: what on earth does a red wheelbarrow have to do with databases?

Re:/. Meta question: a wheelbarrow?

[amorico]

I assume it is from the William Carlos Williams Poem, Red Wheelbarrow

so much depends
upon
a red wheel
barrow

glazed with rain
water

beside the white
chickens.

A database being the red wheelbarrow of course. Don't ask about the chickens.

Still no FULLTEXT indexes?

[inio]

I'd really like to move a few projects at work to Postgres, but there's one essential feature that it still appears to be lacking: FULLTEXT indexes. I don't even need the fancy ranking stuff MySQL does - a boolean word match would be enough.

Re:Still no FULLTEXT indexes?

[rtaylor]

Full text searching has existed for a number of years and is actively used by a few Russian search engines on large volumes of content (millions of text documents), among other places.

Take a peak in the contrib directory of the source tree for tsearch.

Documentation for TSearch

Re:Still no FULLTEXT indexes?

[jabbo]

Take a look at openFTS.

Having used both MySQL and Postgres pretty close to each system's limitations, for years (~5 years in each case), I must admit that I now prefer PostgreSQL (again). I've gone back and forth.

One major problem with MySQL is that you have to choose EITHER fulltext indices OR transactional tables (InnoDB). You can't have both. With Postgresql, you can.

Re:Still no FULLTEXT indexes?

[otisg]

If PostgreSQL's full-text indexing is anything like MySQL's, I urge you not to use it. Things I heard about MySQL's full-text index are horrible! Instead, integrate Lucene with your application/database. If you need a book, there is Lucene in Action with free code and sheap eBook version. Full disclosure: I'm one of the authors. Simpy is a good example of PostgreSQL + Lucene integration.

Oh, and if you want non-Java solution, there are several Lucene ports available: C++, Python, Perl, C#, Ruby...

Re:Still no FULLTEXT indexes?

[frostman]

I never install without tsearch2. It's amazing. Super flexible, super smart, and fast fast FAST.

I normally attach it to template1 and tune it to my environment, then just basically forget about it.

However, I must say it's one of PG's big failures of marketing that it hasn't been included in default installations. I know the PG folks are perfectionists, and I love them for it, but in winning users over you would do well to keep websites in mind, almost all of which need an FTI.

Anyway, if you don't know tsearch2, check it out. Great stuff.

Postgresql has multimaster replication.

[Some Random Username]

Its just not part of the default postgresql install, its 3rd party. Search for pgcluster.

because only one was actually late

[jbellis]

"Yukon," which became Sql Server 2005, was originally targetted for "early 2004"

Re:The inherit bug?

[cayenne8]

"It is too bad that objects does not work yet"

"do features like that really get used in the real world?"

I'm with you on that question. These, well at least Postgres, are Relational Database Management Systems. They're not object databases...and IMHO, it isn't worth messing with them in that fashion. Set it up as a relational database, and let your middle tier deal with the object to relational conversions as needed if you use object oriented programming. If not...then, use an object oriented database...if you can find one that is up to par...which I've not run across yet.

Some ancient history

[jd]

The word "wheelbarrow" originates from the ancient burial mounds called barrows and are traditionally the same shape as the more ancient of these, called "long barrows". The common theme being dead stuff buried under large mounds of earth.

Databases are often used for dead projects, buried under large mounds of data. The parallel would seem to be pretty exact.

Re:Article text for your convenience

[egoots]

Wooww... This looks more and more like Oracle version 7 released in the early 90's. Oracle has had 2PC (2 Phase Commit) for about 15 years now. Nice catchup

So has Firebird... See: http://firebird.sourceforge.net/index.php?op=guide &id=ib6_overview.

Re:The inherit bug?

[rycamor]

For those who really understand relational database design, there is nothing that object inheritance can represent that you can't handle relationally.

In fact, the PostgreSQL team has been musing for years on whether to drop object inheritance completely, because it just muddies the waters of relational design, and causes implementation details to affect logical design.

But interestingly, table inheritance has become a solution to a lower-level implementation problem: "horizontal" table partitioning. Using inheritance together with tablespaces (available since 8.0), you can break a large table into multiple subtables, each on a different physical storage device, then make a table that inherits from them all to present unified querying to the whole range of data. This is a *big* advantage for very large databases.

Re:Upgrading between 8.0 and 8.1

[egoots]

From the Release notes:

E.1.2. Migration to version 8.1

A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

Autovacuum Integration Details

[Zeut]

With 8.1, autovacuum is integrated into the main backend, it's no longer a external contrib module. This has several benifits. First, it's easier to setup, just uncoment the setting int he postgresql.conf. Also, you can now set vacuum and analyze thresholds as well as vacuum delay settings on a per table basis. This was probably the biggest problem people had with the contrib version. In addition it is more reliable since DB activity data is no longer lost between database restarts. Also, you can now exclude specific tables from being touched by autovacuum.

Re: there are more tools available for MS SQL

[brennz]

(Note - some content cross posted from the recent MSSQL2005 posting I made)

I take issue with the number of tools.

Postgresql has a great variety of tools, both OSS and commercial that work great. I've been working on an updated list of all the tools. Here are a few of the most popular admin tools:

PGadminIII
http://www.sqlmanager.net/products/postgresql/mana ger [sqlmanager.net]

DBvisualizer
http://www.minq.se/products/dbvis/ [www.minq.se]

EMS Postgresql Manager
http://www.sqlmanager.net/products/postgresql/mana ger [sqlmanager.net]

PHPpgadmin
http://sourceforge.net/projects/phppgadmin [sourceforge.net]

Sybase Power Designer
http://www.sybase.com/products/enterprisemodeling/ powerdesigner [sybase.com]

ERWIN data modeller
http://www3.ca.com/Solutions/Product.asp?ID=260 [ca.com]

CASE Studio 2
http://www.casestudio.com/enu/default.aspx [casestudio.com]

Postgresql has a vibrant tool community. If you want more info on Postgresql tools see
http://techdocs.postgresql.org/v2/Guides/PostgreSQ L%20GUI%20Tools/document_view [postgresql.org]

Zero downtime Upgrading between 8.0 and 8.1

[team99parody]

Using Slony you can do even better, with zero-downtime(!!!).

• Make your old 8.0 system the master in the Slony replication pool.
• Set up a slave using 8.1,
• Wait until the initial sync is done.
• turn off your 8.0 system and failover to the slave (now running 8.1).
• Make your new 8.1 system the master.

Re:Firebird already does all of this

[davegaramond]

I feel for the Firebird community, they probably do need more recognition and press. But then, maybe if they could release stuffs more often, they would get mentioned more often too.

It's hard to get more attention if your "competitors" are of PostgreSQL caliber. Postgres do major releases every 6-12 months and is getting better all the time at faster rate. It's getting picked up by companies and has the strongest open source support around it.

Meanwhile, when will FB 2.0-final be released? When will I be getting index longer than 240 (or was it in the order of 500) bytes? When will the documentation be half-decent?

I consider my decision to go with Postgres over Firebird several years ago was the best decision I've ever made.

MySQL is more popular

[lorcha]

For a small website, MySQL is overwhelmingly more popular then Postgres. The reasons for this are:

1. MySQL is very simple to learn
2. MySQL is supported by virtually all webhosting companies, whereas you have to look pretty hard to find a host that provides PostgreSQL.
3. MySQL historically has performed much better than Postgres for both reads and writes. Nowadays, MySQL only outperforms Postgres on reads. So for a website, where most database calls are SELECTs, MySQL will often (but not always) give better performance.
4. Nearly all blog, forum, photo gallery, etc. packages support MySQL. They do not all support Postgres, but some do.

Now, of course, Postgres has many advantages over MySQL, but for a small hobby website, you should just use whatever you know best and your host supports and your software supports. The answer is normally MySQL for your type of website.

Postgres is generally better than MySQL (though MySQL is closing the gap) in terms of SQL standards compliance and RDBMS-type features. If you're implementing a true DB app, you should definitely give PostgreSQL a second look. But for a web forum, stick with MySQL.

P.S. Did you go to Wisconsin?

Re:MySQL is more popular

[jadavis]

Nowadays, MySQL only outperforms Postgres on reads.

That's certainly not true in the general case. There are many situations in which PostgreSQL will outperform MySQL on reads. Particularly if you need one of PostgreSQL's features to achieve reasonable performance.

Read the writeup, dipshit

[lorcha]

mnovotny writes "Computerworld reports that Microsoft is finally set to release their belated SQL Server 2005. From the article: 'Despite a two-year delay, several users who have tested the software cited the improved performance and new functionality it brings as positive developments that likely will convince them to upgrade soon.' The free version can be downloaded directly from Microsoft."

When Debian Sarge was finally released, the slashdot writeup went so far to assert that "Hell has officially frozen over!". So the sword cuts both ways.

Nice kneejerk reaction though, buddy.

thank you #postgresql IRC channel

[kervel]

A big thanks to the very helpful people on the #postgres irc channel. I have been there several times asking for help, and always i have gotten an excellent, very in-depth answer, for what i tought were quite difficult questions (performance problems, weirdness with inherited tables, or just how to build a query to give you what you want in an efficient way). Thank you !