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

Comments

PostegreSQL 8.3?

[Anonymous Coward]

Would that be POSTGR~1.SQL?

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.

Re:Cross Database Joins??

[dfetter]

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

Re:Cross Database Joins??

[glwtta]

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]

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.

Will it be used?

[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]

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

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

[Plaid Phantom]

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

Do you work for the government?

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:And then...

[thrillseeker]

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

asynchronous committ

[stoolpigeon]

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]

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

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

[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 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

[Simon (S2)]

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)

Re:asynchronous committ

[greg1104]

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:Nice.

[Seumas]

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.

long live postgres

[squoozer]

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.

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

Re:long live postgres

[greg1104]

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. It's better now but still harder to put the pieces together than it should be.

Start with the documentation on creating a cluster: http://www.postgresql.org/docs/current/static/creating-cluster.html In 8.3 the default of using auth you mentioned has been removed, for the reasons you described. So it now runs as unsecured for local users by default and you have to worry about this yourself, which since it reduces the frustration at getting started was deemed an improvement.

That page suggests some options you can pass to initdb to adjust the default security level. Now you want to look at the initdb docs: http://www.postgresql.org/docs/current/static/app-initdb.html See that if you use -W/--pwprompt (same thing) you can assign a password to the database superuser at cluster creation time. If you do that, you can now change the default authentication scheme to password-based (-A md5 passed to initdb will do that), you'll be secure, and you'll have one user you can login as (postgres) to create more.

To see what other authentication methods are available and to learn what your options are look at http://www.postgresql.org/docs/current/static/client-authentication.html The one you really need to dive into is pg_hba.conf which is the magic text file to edit here. A new one of those will be put in the base directory of your new database cluster. Open that file up, look at the documentation, and you'll need to add a line to add network support like one those in the examples. Probably something like

host postgres all 192.168.12.0/24 md5

(allow access to anybody on the 192.168.12 subnet access the database with a password)

That should get you past the ugly initial hurdles. The next document you may need is how to add more users: http://www.postgresql.org/docs/current/static/sql-createrole.html

again look at the examples first and then backtrack to the parameters, will make more sense that way. After that you'll want to create more databases with createdb: http://www.postgresql.org/docs/current/static/app-createdb.html

And then you should be able to find your away around from there using the psql command line tool.

Note that once you get past accepting connections over the network, you could use a tool like pgAdmin III to handle the rest of this work using a slicker interface. There's even a copy of it bundled with the Windows installer you can use on such a client to administer a remote server running a real OS. It's of course possible to install pgAdmin manually on other platforms as well, see http://www.pgadmin.org/ for other verions (note that binary packages for platforms like RPM don't show up in every release, you have to go back to v1.8.0 to get the last full set of packages).

Time for a cross-DB comparison

[jd]

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]

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

Re:Multi-master replication built in

[Acheron]

The 8.3 release notes list the Bucardo project http://bucardo.org/ for multi-master replication. I haven't used it... is there something that it is lacking that you think would be addressed by bringing it into the core code base?

Re:New HOT, faster Postgres

[naasking]

Yeah, if only for those darn inconvenient facts demonstrating that PostgreSQL is faster than MySQL, particularly under load. Note that the benchmark was PostgreSQL 8.2. Now note that 8.3 is up to twice as fast as 8.2. 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]

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]

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?

You are on to something.

[NotQuiteReal]

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