PostgreSQL 8.3 Released

jadavis writes "The release of the long-awaited PostgreSQL version 8.3 has been announced. The new feature list includes HOT, which dramatically improves performance for databases with high update activity; asynchronous commit; built-in full text search; large database features such as synchronized scans and reduced storage overhead; built-in SQL/XML support; spread checkpoints; and many more (too many major new features to list here). See the release notes for full details."

Cross Database Joins??

[Foofoobar]

The one thing that has stopped me from picking up Postgresql yet is that I can't do cross database joins on the same server. Should a user have the same permissions on two separate databases on the same server, a properly constructed query should be able to join across multiple DB's but they still don't implement this yet that I am aware of.

Re:Cross Database Joins??

[geniusj]

You should consider using schemas as opposed to entirely separate databases.

And then...

[n3tcat]

someone will make a comment regarding how sad the story of Postgres's popularity is, and how they've seen German folk music with more of a following.

Re:asynchronous committ

[nuzak]

> Why would you want to drop the durability part of ACID?

SQL already allows you to drop to READ_UNCOMMITTED if you really really want to -- though the DB actually under no obligation to drop to that level, you're just specifying that you don't care. That removes A, C, and I all at once. Why not make the D part optional too?

Not all databases are commerce. My company processes several billions of rows a day of data, and if we accidentally lose some data, it just degrades effectiveness a little bit and means our statistics just have to interpolate a smidge. In fact, we deliberately drop a lot of it anyway.

Re:asynchronous committ

[stoolpigeon]

I have a limited frame of reference - my experience has primarily been in the support of mission critical business processes - where data loss is the end of one's job. And from the replies I guess I can see that circumstances exist where this might be desirable, though part of me wonders, if in such cases that a database is the right tool.
 
My other concern still stands - I hope the documentation makes the ramifications of choosing this option clear.

Re:asynchronous committ

[RelliK]

SQL already allows you to drop to READ_UNCOMMITTED if you really really want to -- though the DB actually under no obligation to drop to that level, you're just specifying that you don't care. That removes A, C, and I all at once. Why not make the D part optional too?

False. SQL standard explicitly specifies that writing to the database under READ UNCOMMITTED isolation is not allowed. You can only do read-only queries. Further, PostgreSQL doesn't even support READ UNCOMMITTED. There is no need for it. PostgreSQL implements MVCC [wikipedia.org] such that each transaction gets a private snapshot of the database. With that you get READ COMMITTED for free.

I'm with the original poster here. Asynchronous transactions seem like a bad idea. But then it's not PostgreSQL's responsibility to enforce good software design. And maybe in some corner cases people can find use for them.

Re:long live postgres

[costing]

I can only assume that MySQL keeps it's large market share because it has commercial backing and therefore good support.

No, it's because people are used to LAMP, and tons of easy-to-install apps only have MySQL support. But there is hope, I see more and more PHP apps allowing you to choose PostgreSQL instead. I think this is the turning point, once they reach the critical mass needed to turn the developers' heads it will become THE open source database. And for a good reason, it beats MySQL in every way you imagine, including the obvious features and not so obvious performance. Well, maybe for two queries in a 10 rows table MySQL will see an edge and enjoy, but let's face it, it never scaled up to more than that :)

I guess we will have to get used to saying LAPP from now on and not grin when we do. :D

Upgrade Procedure

[AltImage]

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

Postgres have a habit of making you do this to upgrade and it really sucks. I understand the reasons behind it, but that does not reduce the amount of suck, especially for a large database.

Postgres Books?

[crustymonkey]

Does anyone know of a good, semi-recently written book on PostgreSQL? Everything I find is from at least 3 years ago. Is it that PostgreSQL hasn't changed much, barring this release, in the past few years?

Re:Will it be used?

[jadavis]

People like myself who design software requiring a database usually prefer speed over features.

Keep in mind that PostgreSQL may have more stable performance for a varied workload. That may mean fewer surprise slowdowns for you.

I don't know your specific situation, but you may want to re-evaluate postgresql for your needs, especially if you care about performance -- PostgreSQL made leaps and bounds in this area in 8.3. I'm not sure what the last version you tried was, but 8.2 was a good performance boost as well.

And if it still doesn't hold up to your expectations, please post your benchmarks to pgsql-performance, so that others can either help you meet the needs, or improve postgresql for the future.

I would think also, as a developer, you might like the data integrity available in PostgreSQL that can help catch tough bugs early. Also, MySQL has many configurable options that may make your application not work and your customers unhappy (including table type -- changing table types from MyISAM to InnoDB or vice-versa may break applications). PostgreSQL's options (for the most part) don't affect the application.

Re:long live postgres

[turbidostato]

"This is one of those things that's painful only until you've crossed a certain threshold of information, then it never bothers you again."

For the most part then it's too late.

There are *a lot* of people that started their days on DBs by their own or as another issue clogged to their back. Then they go opensource just because it's easier to start with (no provisions, not a dozen calls to a provider, just an "apt-get install" and you are on your way). Then you look for Postgres; it has a fame of a serious, reliable full-featured database, you start toying with it, having a light look at the manuals (remember: you are either a neophyte or burdened with other tasks; no time or ability to spend the time on a dense manual and start seeing the results next week)... and you go nowhere. Let's try the next one, you say, so you go with MySQL; you install it and within five minutes you get your first "show databases" and "select * from table"; you smile, you feel the strong feedback and there you go. Of course, after a year using MySQL you understand some basic concepts and you could give another try to Posgres, but who cares now? MySQL has the work done for the most part, you know its nuisances and you have invested a lot of time on it.

Does it support multithreaded queries?

[G3ckoG33k]

Hi, I read that "MySQL does not uses several CPUs to execute single query - only multiple connections may benefit from several CPUs.". That was written January 6 2004 by Peter Zaitsev, then a full-time developer at MySQL AB, www.mysql.com. I found the quote at http://lists.mysql.com/benchmarks/45 [mysql.com]

Does anyone know if PostgreSQL supports a dual or quad CPU when it comes to executing a single query, or if MySQL now supports it?

The reason I ask is that I have a database with tens of millions of records and even 'simple' queries take a long time. Would it be beneficial to buy a 8 core machine, i.e. dual quad, over a single quad cpu?

Thanks for any tips or links!

Just one issue

[bytesex]

I'm a great fan of postgres but I ran into an irritating limitation recently; I replicate a database over a large number of very small nodes using slony. I really don't care about the integrity of the slaves - they're read-only to their clients and should I suspect they're corrupt I just reboot (they live in memory and the OS lives on a 1 Gb read-only flash drive). But postgres insists on having a WAL directory (pg_xlog) with chunks of 16MB in it. And that's big if you live in 128MB of ramdisk, and you can't turn that off. I mean, from my reasoning - the WAL isn't really used unless you do recovery; the versions of the data are in the db itself (otherwise we wouldn't need vacuum, now would we ?) So why can't I just configure postgres to not use WAL ? And then if the db is corrupt we just die. No, say the guys on IRC, you just have to recompile it with its hard-defined value of 16MB down to something lower. Yeah right. I'm not interested in hacks - I want a versatile RDBMS.

Re:Nice.

[LWATCDR]

The real advantage that MySQL has right now is the number of packages that are MySQL centric.
You need a Wiki? Odd are that it supports MySQL and can be made to work with Postgres. Need a content management system? Odds are that it was written for MySQL and might work with Postgres. So you probably already have MySQL in house so people tend to go with MySQL even when Postgres is a better solution even if for no other reason than why maintain two databases.
The only reason why we use Postgres for several of our in house programs is because I wrote those before we put up an in house Wiki or other web stuff. Postgres offered row locking back then when MySQL didn't. I loved transactions when I finally got to use them.
The sad thing is that if I did it over today I would probably pick MySQL since it is so popular and now supports row locking and transactions.
Postgres is the better system but it lacks the depth of support that MySQL does. Sort of like BSD vs Windows.