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."

PostegreSQL 8.3?

[by Anonymous Coward]

Would that be POSTGR~1.SQL?

Cross Database Joins??

[Foofoobar (318279)]

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 (140174)]

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

Re:Cross Database Joins??

[glwtta (532858)]

Often databases are created in a vacuum and only later does one need to utilize multiples in a query.

That feels wrong somehow; if your (logical) databases are so distinct that you can't plan to co-locate them in the same (Postgres) database, does it make sense to have such tight coupling on the query side? Now you have to synchronize the data between them, and you can't move them off the same machine, so what's the point of keeping those databases separate? It also seems like client code should never have to know where different databases are physically located.

I don't agree that this is the "simple solution", it's a horrible hack on the part of the database engine (I don't actually know if anyone apart from Oracle does this) with unpredictable performance results - looks more like the "lazy solution".

I don't know, just seems like such a thing breaks the database/application "contract".

Besides, shouldn't your ORM layer abstract such minutiae away pretty easily?

Re:Cross Database Joins??

[prog-guru (129751)]

I don't know, seems to me a cross database join is neither simple nor proper structure. If you are stuck with it schemas would probably work, painless in Active Record, perl DBI requires 'schema.table' syntax.

Re:Cross Database Joins??

[dfetter (2035)]

There are several ways to do cross-database JOINs in Postgres including dblink [postgresql.org], and even to other DBMSs via dblink-tds [pgfoundry.org] and DBI-Link [pgfoundry.org], but try schemas first, as another poster mentioned.

Will it be used?

[by Anonymous Coward]

I'm a postgresql fan, I've considered it a superior database for years.

However, it seems every client I come into contact with (I am a freelance software designer) seems to believe mysql is the only open source database available and certainly the best one for all jobs.

Mysql is great (or at least, was great) for fast connection times and speed but for a stable, feature-rich excellent database, postgresql has always been ideal.

It's just a shame no one seems to be aware of it.

Re:Will it be used?

[ByteSlicer (735276)]

That, and people are more likely to remember 'My' than 'Postgre'.

You are on to something.

[NotQuiteReal (608241)]

If PostgreSQL changed their name to OurSQL it would be easy to remember, and a sound a lot less selfish than MySQL.

Re:Will it be used?

[jadavis (473492)]

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:Will it be used?

[Plaid Phantom (818438)]

You have a 100 GB database and you're not concerned with stability??

Do you work for the government?

asynchronous committ

[stoolpigeon (454276)]

this was a new feature for Oracle with 10g R2 also - and as a DBA I can only shake my head and ask "why?" Why would you want to drop the durability part of ACID? Why would you risk losing data for speed? There are so many ways to tune things and speed things up without taking such drastic measures. I know I'd fight tooth & nail before I'd turn this on in anything I managed. I just hate to think that someone with less understanding is going to think of it as a 'go-faster' button and then blame postgres when they lose something important.

Re:asynchronous committ

[Wesley Felter (138342)]

Haven't you heard? In Web 2.0, data integrity doesn't matter.

Re:asynchronous committ

[nuzak (959558)]

> 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

[RelliK (4466)]

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:asynchronous committ

[greg1104 (461138)]

Why would you risk losing data for speed? There are so many ways to tune things and speed things up without taking such drastic measures.

The new async commit feature bypasses the requirement that records physically hit disk in order to complete a commit. If you must wait for a disk commit (typically enforced by fsync), the maximum number of true commits any one client can do is limited by the rotation speed of the hard drive; typically an average of around 100/second for a standard 7200RPM disk with PostgreSQL. There is no way whatsoever to "tune things and speed things up" here; that's how fast the disk spins, that's how fast you get a physical commit, period.

In order to accelerate this right now one needs to purchase a disk controller with a good battery-backed disk controller and pray it always works. If it doesn't, your database might be corrupted. With async commit, you can adjust the commit rate to something your disks can keep up with (say 50/second) just with this software feature while still allowing a write rate much higher than that, and at no point is database corruption possible (from this cause anyway). This makes people who want to use PostgreSQL in things like shared hosting environments have an option that allows heavy writes even for a single client while having a reasonable data integrity policy--only server crashes should ever lose you that brief period since your last true commit. That's a fair trade for some applications (think web message boards for example) and lets PostgreSQL be more competitive against MySQL based solutions in those areas.

Re:asynchronous committ

[Simon (S2) (600188)]

Sure. Like so many times in software development "only you can decide whether data loss is acceptable to you. But there are many classes of applications, including high-speed data ingest programs whose only goal is to get the stream of data into the database, where commit-but-don't-wait is not only acceptable but very desirable performancewise." (Tom Kyte)

long live postgres

[squoozer (730327)]

As far as databases go I don't think it gets much better than Postgres and I reckon over the years I've tried most popular databases. What I really don't understand though is why Postgres doesn't own more of the database market. Sure it was a bit slower than MySQL a few years ago but the benifits that you reaped for that slightly slower speed far outweighed the cost. The difference know is, I would say, much small and less significant.

I can only assume that MySQL keeps it's large market share because it has commercial backing and therefore good support. I'm sure there are plenty of products taht don't require that level of support though.

Time for a cross-DB comparison

[jd (1658)]

PostgreSQL 8.3 is nicely timed. I've been looking forward to trying it in a setting which wouldn't allow the use of betas. Now I've got the on-topic stuff out the way, onto my main point.

There are so many Open Source databases (MySQL, MaxDB the last GPL version, Firebird, Postgresql, Ingres Community Edition, hsqldb and H2) that it is hard to know which ones implement what, which ones are useful for what, or which ones are optimal for what. Simple benchmarks (a) rarely are, and (b) usually want to promote one specific product over another. There are standardized tests, for a small fortune and by a relatively closed group who probably don't have a fraction of the range of experiences of databases in the real world, so cannot possibly be trusted to authenticate a standard or measure that could be used to compare databases.

We could really do with some serious side-by-side evaluations of these database engines, or at least decide what such evaluations would need to be to be actually useful. (Hey, maybe CmdrTaco can add a comparison section, get it sponsored by Which? or some other consumer guide, and have some of us run evaluations. It'd have to be done well to not be flamebait, which I think might rule me out, but if it could be done, it would be hellishly useful.)

Re:Time for a cross-DB comparison

[TheNarrator (200498)]

Here's a feature matrix of new features vs. old versions. It's easy to see that 8.3 is a huge upgrade.

http://www.postgresql.org/about/featurematrix [postgresql.org]

Re:And then...

[thrillseeker (518224)]

well, we will just have to polka holes in that discussion

Re:Nice.

[Seumas (6865)]

8.3 had me at "full-text search".

Now, please excuse me while Postgres 8.3 and I go take a little alone-time in a dark closet.

Re:New HOT, faster Postgres

[naasking (94116)]

Yeah, if only for those darn inconvenient facts demonstrating that PostgreSQL is faster than MySQL, particularly under load [tweakers.net]. Note that the benchmark was PostgreSQL 8.2. Now note that 8.3 is up to twice as fast as 8.2 [kaltenbrunner.cc]. I think the polarity on your order of magnitude performance difference should be reversed.

Of course, if you actually care about data integrity and database features, there's not contest at all. But the performance gap is now non-existent, if not completely reversed.

Re:New HOT, faster Postgres

[glwtta (532858)]

Oh give it a fucking rest. MySQL is 10-15% faster on simple queries, with few threads, on a single disk.

And that's only with MyISAM (in which case, why bother with a database server? SQLite is probably enough for your needs).

Re:How quickly they turn on you ..

[swimmar132 (302744)]

Going from 8.2 to 8.3 in postgresql is not a 'minor' release. It's quite a major step with a lot of new features.

Would it make a difference to you if they bumped up the version number to 9?