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?

Would that be POSTGR~1.SQL?

Cross Database Joins??

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

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

Re:Cross Database Joins??

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

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:

the simple solution is often easier and faster than the complex solution

When you want something that walks like a duck and talks like a duck, the simplest, fastest, and easiest solution is to get a duck. What you want is done by schemas in postgresql. If you're really doing the separate databases for "performance" reasons, then one presumes that at some point you're going to be putting the databases on separate servers, in which case you'll be wishing you had started with dblink in the first place.

What yo

Re:Cross Database Joins??

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.

what do you mean by cross-database join?

Are you thinking like you'd do in SQL server (IIRC) or MySQL, where you have a db reference in the table list (i.e., SELECT * from db1.table1, db2.table2 WHERE [join clause])?

you'd probably just want to use a schema for that; the concept maps more or less the same way.

Re:

I think the disconnect is that PostgreSQL has a different definition of schema [postgresql.org] than you are using.

Will it be used?

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:

People like myself who design software requiring a database usually prefer speed over features.
Thats why MySQL is usually chosen.

Re:Will it be used?

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

You are on to something.

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?

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:

If you want raw speed, you should be using SQLite instead of MySQL. Plus, are you using the InnoDB database engine? If you are, then you are not getting MySQL's raw speed. That only comes with their native database engine. You know, the one that doesn't support much of SQL at all.

Re:Will it be used?

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

Do you work for the government?

Re:

You people who design software should read up on this thing called data integrity, and enforcement of foreign key constraints.

MySQL is pretty bad at those, but if you use an innodb table and try to use them, you find it's no faster than postgresql. And still missing many many features that postgresql gives you.

Re:

And even more instances where they are needed, but not known/understood by a MySQL user.

And then...

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

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

asynchronous committ

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

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

Re:asynchronous committ

> 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

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:

Because sometimes you don't really care if the data will be there after the commit or not, you just need to do it fast. For example say you have a sensor that counts how many nails go in a package. You have to fill the package with 1000 nails, but it is not really important if there are 999 or 1001 nails in the package, the important thing is that the counter goes fast, say 1000 counts in 1/100 of a second.
It's not a feature you will use in your web or c/s app, but it has it's uses, and it's good to have it

Re:

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

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:

Such a count does not require a DB write per transaction ( nail++ ). Such minor amounts of data are better left memory resident if they are wiped after a quantifier is reached. DB writes are for the purpose of keeping state. In your example the only reason to keep state is should the machine fail and the boxes be partially filled the remaining count necessary to complete the box needs to be known. That is better done with a physical weight measurement than a DB query.

Asynch writes are useful for keeping

Re:

Are you sure this is such a disaster? As far as I can tell this only means that executing "COMMIT" doesn't block (wait) until the commit has actually happened but returns immediately, and the actual operation is performed "later". The data still goes through the journal (WAL), is still fsynced when needed, etc.

Re:asynchronous committ

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:

Why would you want to drop the durability part of ACID? Why would you risk losing data for speed?

SO you can write sloppy inefficient code and do sloppy DBA work and get away with it?

Just an idea.

Re:

It is a lot safer than turning fsync off. That's the point of the feature :)

long live postgres

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:

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

Re:

The problem with the whole LAMP-acronym is, we have a lot more to choose from. What do you think of: LLPP (Linux, lighttpd, Perl, PostgreSQL) or something like that.

Re:

What do you think of: LLPP (Linux, lighttpd, Perl, PostgreSQL) or something like that.

My personal favorite is FAPP (FreeBSD, Apache, PostgreSQL, Perl).

...

What's so funny?

Why PostgreSQL doesn't have more of the market

I have used PostgreSQL as my primary db since 2000 (version 6.5!) and I have watched it for a while.

PostgreSQL had a number of problems in the past which made it hard to work with including:
1) No ALTER TABLE DROP COLUMN support and other things needed for prototyping (fixed in 7.2 iirc)
2) Issues with dependency tracking in text SQL dumps (fixed in 8.0) meaning that some times one had to hack dumps to get them to restore properly.
3) maintenance tasks required exclusive locks on tables (corrected sometime in

Re:long live postgres

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 [postgresql.org] 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 [postgresql.org] 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 [postgresql.org] 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 [postgresql.org]

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 [postgresql.org]

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

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:

A couple [geocities.com] such comparisons [devx.com] already exist. They may be a year or two out of date however.

Re:Time for a cross-DB comparison

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:

PostgreSQL vs. MySQL WIki [wikivs.com]

Seems to be features only, no performance.

Upgrade Procedure

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

Re:Nice.

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:

The 8.3 release notes list the Bucardo project http://bucardo.org/ [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

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

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

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?

Re:

8.2 was released over a year ago - this is not a minor revision.

MySQL is FAST, PgSQL FARTs on MySQL's Performance

Fast
And
Sorta
Transactional

PostgreSQL is
Fast
And
Really
Transactional

And under heavy loads with normalized db's PostgreSQL's planner does *much* better than MySQL's.