PostgreSQL 9.0 Released

Thank you! by bjourne · 2010-09-20 11:14 · Score: 5, Insightful

Congratulations to all the Postgres developers and a big thank you from me for an amazing job! Postgres is a wonderful RDBMS and one of the best free software projects there is. Rock on!

--
Football Odds

Re:Thank you! by Anonymous Coward · 2010-09-20 13:19 · Score: 5, Informative

Thirded. There is absolutely no reason for anyone to be using MySQL any more other than the old silly excuse "my hosting provider doesn't have anything else". PostgreSQL is now faster than MySQL in all but the most trivial of contrived cases, doesn't require you to choose between table types for different load types, is just as easy to use and install, has all the features that MySQL has and runs on a Windows server (for those idiots who think that is a good thing). Also, the PG community is vastly more helpful and knowledgeable than the rabble that is the MySQL user base.
Finally, PostgreSQL is a proper independent open source project with a structure that all other open source projects should be judged by. MySQL has gone from hand to hand in the corporate world and has a future that is far from certain.
Down with the joke that is MySQL, and down with all the idiots that make me work with it.
Re:Thank you! by Chrisq · 2010-09-20 20:13 · Score: 4, Insightful

Congratulations to all the Postgres developers and a big thank you from me for an amazing job! Postgres is a wonderful RDBMS and one of the best free software projects there is. Rock on!
Apart from that it now really is just about the only alternative to Oracle or Microsoft.

Cool by iONiUM · 2010-09-20 11:16 · Score: 4, Interesting

I read the notes, noticed the Column and WHEN triggers. Is this in other SQL databases? If it is, I haven't seen it before. In any case, it's pretty cool that you can setup triggers on a conditional statement. That would really help me out in a lot of scenarios, as I work in the BI space, so alerting is a big deal.

Re:Cool by lanner · 2010-09-20 12:17 · Score: 4, Interesting

I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.
Once upon a time I worked in the entertainment industry and was working on a big MMO game project.
Company X could not scale up their game clusters past about 1000 players. Somewhere between 1000 and 2000 players, the game would just start bogging down and in-game events piled up and everything trainwrecked and was unplayable.
So, it turns out that most of the game logic was built off of complicated SQL stored procedures, triggers, logic, etc. Basically, they were using their hard drive as a processor.
The problem was with the MS-SQL server disk IO Wait. CPU was okay on all of the systems, but they could just not imagine that the disks in the database server (only one DB server per cluster) could be the source of the problems. Every time there was an item dropped, crafted, or certain other special things happened, there was an atomic commit and that basically required writing to disk on the spot. Get enough of that going and you're whole 20-something CPU cluster sits with idle CPU while the DB server works it's hard drives.
Company went chapter 11, all staff eventually let go, and later was sold off for nothing.
I had pointed out this problem to them, but it was late in development and when you tell the people who are responsible for designing the product that they are idiots, well, they behave like idiots and don't really listen. Not that they could have fixed it anyway due to time and intellect restraints.
Anyway, point of the story is that cool SQL features are cool. But don't use your hard drive as a processor.
Re:Cool by GooberToo · 2010-09-20 12:34 · Score: 4, Informative

What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger...

No! Its far more than syntactic sugar. Performance, readability, and maintainability are what this brings to the table.
The difference between PostgreSQL's new column trigger feature and traditional triggers is they are only called when the column is modified rather than when any row is modified. This means, in many cases, the trigger will never be called and therefore, the DB isn't having to run at PL/SQL interpreter speeds during the execution of the trigger, to then determine there is nothing for it to do. Furthermore, a big headache which is extremely common to trigger code are IF/THEN/ELSE or long CASE statements to determine which columns are modified, or to determine if the trigger even cares that the row in question (example, columns which the trigger doesn't care about) has been modified.
The above combination of traditional triggers means lots of overhead, lots of needless PL/SQL code execution, and hard to read/maintain triggers for non-trivial actions. Whereas with the new feature, you can now have a single trigger relate to specific column, which is only ever executed when the trigger should actually execute. Its a win, win, win for all PostgreSQL users. And best of all, this means you can have smaller triggers when you need to perform different actions based on different column changes.
Re:Cool by caerwyn · 2010-09-20 15:50 · Score: 4, Insightful

Business logic never belongs in the DB. Even triggers are suspect. They can be horribly inefficient.
The fact that triggers *can* be inefficient is no reason not to use them when there's a good implementation and competent DBAs to make sure they *aren't*. Also, business logic never belongs in the DB? To the contrary- a lot of business logic is sets of rules to maintain consistency between various things. That sort of logic is *precisely* what belongs in the DB, rather than scattered throughout a variety of applications running on top of it.

--
The ringing of the division bell has begun... -PF
Re:Cool by GooberToo · 2010-09-20 16:41 · Score: 4, Interesting

There is a difference between the engine checking a constraint versus a call into an interpreted language. One is doing less work. The other is doing more work. Which is ideal? Obviously less work is better. And that's before you even get into the PL/SQL code which is essentially doing the same work, but slower. Furthermore, all too often, triggers are called when there is no work to be done but you don't know that until the PL code decides this is the type of row change its interested, otherwise it should have really been a NOP. Whereas with the column trigger, the call to the PL code simply never takes place. So we not only save on the call but all of the wasted time inside of a trigger which ultimately decides its has nothing to do.
Also, when the trigger should be called, in a row trigger, triggers frequently must evaluate which columns have changed before it can even determine if it cares about this row. Should it then decide it does care about this row, likely you've already passed through a mass of CASE and/or IF/THEN/ELSE codes, which ultimately states yet more CASE and/or IF/THEN/ELSE to determine exactly what it should now be doing now that its decided it does need to process this row. Or, you can call a much smaller section of code which is dramatically simplified because one, its only called when its pre-qualified (saving the creation of much redundant code) and two, since its now pre-qualified, we can immediately get to performing whatever logic the trigger in question should do when the column in question has changed.
Those are worlds apart in performance, readability, maintainability. Not to mention the added granularity makes possible a reduction in the test matrix, regression tests, and even makes it more difficult (though far from impossible) to create a regression.

Re:"Great leap forward" by catmistake · 2010-09-20 11:36 · Score: 4, Insightful

LMAO... unless my sarcasm detector is malfunctioning, comparing Postgres to MySQL is extraordinarily absurd... like comparing megaliths to legos.

--
The Admin and the Engineer

Re:Has the Documentation Been Improved? by caerwyn · 2010-09-20 11:37 · Score: 4, Informative

Err, have you actually used the PostgreSQL manual? It's one of the best manuals I've ever seen for a software product.

--
The ringing of the division bell has begun... -PF

Re:"Great leap forward" by Anonymous Coward · 2010-09-20 11:41 · Score: 5, Informative

Yes, but MySQL has a shoddy parser (needs a space after the -- comment tag), poor trigger failing (you have to do a kludge and dump a varchar into an int to get it to fail), apparent lack of direction (how many forks and engines?!), no CTE support and the list goes on. I am constantly banging my head against a wall with MySQL. I use MSSQL for work, Postgres at home and MySQL on hosting.
I am truly surprised that most web hosting companies do not offer Postgres. Postgres also allows writing of DB functions in C, Java, PHP etc. like Oracle, which is useful for bundling code into the DB (making the DB the application) without everyone having to see your SQL source for functions. It is also licensed on BSD which is good for using their libpq library in commercial apps; MySQL's C API is GPLd or licensed expensively from Oracle, although there are moves toward making it free for use in commercial apps (as far as I can tell from the mishmash of info coming from their sales rep via email).
Also, as far as I know, MySQL puts all of its indexes in memory for replication which is a problem if the node goes down. Can anyone enlighten me?

In any case, well done to the Postgres team. Not only is their software package neat, their documentation is some of the best I have ever seen.

As always... by jd · 2010-09-20 11:47 · Score: 5, Interesting

The new features are much admired by all (and deservedly so), but a heavier footprint typically means poorer performance overall even if there's accelerated performance in specific areas or improved programming. I'd like to see a performance plot, showing version versus performance versus different types of system load, in order to see how well new stuff is being added in. It might be merged in great and the underlying architecture may be superb, but I would like to see actual data on this.

Also, PostgreSQL and MySQL aren't the only Open Source SQL databases. Including variants and forks, you really need to also consider Ingres, Drizzle, MariaDB, SAP MaxDB, FireBird and SQLite. If you want to also compare against Closed Source DBs, then you'd obviously want to look at DB/2, Oracle, Cache and Sybase. I'd love to see a full comparison between all of these, feature-for-feature, with no bias for or against any specific development model or database model, but rather an honest appraisal of how each database performs at specific tasks.

I like PostgreSQL a lot. I rate it extremely highly. However, without an objective analysis, all I have is my subjective perception. And subjective perceptions are not something I could credibly use in a workplace to encourage a switch. For that matter, subjective perceptions are not something I would consider acceptable for even telling a friend what to use. Perceptions are simply not credible and have no value in the real world.

--
It's a small world and it smells funny; I'd buy another if it wasn't for the money; Take back what I paid (SoM)

Re:As always... by greg1104 · 2010-09-20 15:27 · Score: 5, Interesting

You've got the performance part backwards for PostgreSQL; it goes up with every release, sometimes a little, sometimes in a big way. See PostgreSQL history for a comparison covering versions 8.0 to 8.4. The mild regression in 8.4 shown there is actually reversible; it's mainly because a query related parameter for how many statistics to collect and use for query planning was increased by default. That results in better plans for most real-world queries, but it detuned this trivial benchmark a little bit. You can get performance back to 8.3 levels just by turning the parameter back to the "optimized for trivial queries" default of the older versions if you care about that. Most people prefer the new default. In the real world, 8.4 is actually faster due to improved handling of background VACUUM tasks too, which don't show up in simple benchmarks either.
I'm the current lead architect on building a PostgreSQL Performance Farm to prevent regressions from popping into future versions of the code too. There is a recently completed beta client for that purpose. We're in the process of working out how to integrate into future development, starting with 9.1, so that potential regressions are spotted on a commit by commit basis. I haven't seen any performance regressions between 8.4 and 9.0, only moderate improvements overall and large ones in specific areas that were accelerated.
Now, if you use some of the new replication features aggressively, that can add some overhead to slow down the master. But that's true of most solution; the data coming off the master has to take up some time to generate. The way PostgreSQL 9.0 does it is is pretty low overhead, it just ships the changed blocks around. Theoretically some statement based solutions might have lower overhead, but they usually come with concerns about non-determinism on the slaves when replayed (random numbers, timestamps, and sequence numbers are common examples).
Given the non-disclosure terms of most of the closed source databases, nobody can publish benchmarks that include them without going through something like the TPC or SPEC process. The last time that was done in 2007, PostgreSQL 8.2 was about 15% slower than Oracle running the same database-heavy workload. And note that it was PostgreSQL 8.3 that had one of the larger performance increases, so that was from just before a large leap forward in PostgreSQL performance.
At this point, Oracle and most other commercial databases still have a large lead on some of the queries run in the heavier TPC-H benchmarks. Links to more details as to why are on the PostgreSQL wiki. It just hasn't been a priority for development to accelerate all of the types of queries required to do well in that benchmark, and nobody so far has been willing to fund that or the subsequent certification via the TPC yet. Sun was the only one throwing money in that direction, and obviously the parts of that left within Oracle will no longer do so.

Re:Has the Documentation Been Improved? by jwpye · 2010-09-20 11:56 · Score: 5, Informative

hrm? The documentation is regularly updated... http://www.postgresql.org/docs/9.0/static/

Re:Has the Documentation Been Improved? by shutdown+-p+now · 2010-09-20 12:12 · Score: 4, Informative

It's actually one of the best manuals for SQL in general - at least, in my experience, it has the most clear explanations of many more advanced SQL constructs that are common between various RDBMSes.

I guess we really are the leader now by TheFuzzy · 2010-09-20 12:23 · Score: 4, Funny

PostgreSQL *must* be the leading open source SQL database, now. People are bashing us on Slashdot. That's always a sign of success.

Thanks, guys!

--Josh Berkus
PostgreSQL contributor

Re:Waiting for a capable PostgreSQL front-end by guusbosman · 2010-09-20 12:46 · Score: 5, Informative

You know that you can point your MS Access client to any supported back-end right? Just create an ODBC connection on your Windows machine to your PostgreSQL server and you can use Access with pretty much all the features that work for the Microsoft JetEngine (PostgreSQL has ODBC drivers here; http://www.postgresql.org/ftp/odbc/versions/)

Earlier this year we converted a huge Access application from MSSQL to PostgreSQL and the technical conversion, using ODBC to PostgreSQL instead of connecting to MSSQL, was a piece of cake.

Re:Meh by Daniel_Staal · 2010-09-20 13:20 · Score: 4, Informative

Um, yeah. MySQL, out of the box, using the defaults, doesn't support foreign keys now. You have to specifically create the tables with a non-standard SQL code to get them to use the right database backend to get foreign key support.

Unless you mean by 'support' 'Will silently accept and throw away'...

Foreign keys have been enabled and working by default in Postgres since version 7. (There was no version 5...) That was released just over ten years ago at this point.

--
'Sensible' is a curse word.

Re:"Great leap forward" by caerwyn · 2010-09-20 15:53 · Score: 4, Insightful

Part of the reason MySQL gets treated as a toy is its release discipline- or lack thereof. At least one of the 5.x releases came out with *known* data-loss bugs; that's just not even remotely acceptable in a database, and that's the sort of impression that's hard to shake: people aren't just going to look at subsequent releases and go "oh, well, they say they're paying more attention this time, I guess that's good enough".

--
The ringing of the division bell has begun... -PF

19 of 344 comments (clear)