Forgot your password?
typodupeerror
Databases Software

PostgreSQL 9.0 Released 344

Posted by Soulskill
from the milestone-achieved dept.
poet writes "Today the PostgreSQL Global Development Group released PostgreSQL 9.0. This release marks a major milestone in the PostgreSQL ecosystem, with added features such as streaming replication (including DDL), Hot Standby and other nifty items like DO. The release notes list all the new features, and the guide explains them in greater detail. You can download a copy now."
This discussion has been archived. No new comments can be posted.

PostgreSQL 9.0 Released

Comments Filter:
  • Thank you! (Score:5, Insightful)

    by bjourne (1034822) on Monday September 20, 2010 @07:14PM (#33643220) Homepage Journal
    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!
    • by WoLpH (699064)

      Second that. By far the most pleasant database I've worked with so far and if you don't have everything you need with the build-in features, it's easy to build them yourself.

      • Re:Thank you! (Score:5, Informative)

        by Anonymous Coward on Monday September 20, 2010 @09:19PM (#33644378)

        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! (Score:5, Insightful)

      by Chrisq (894406) on Tuesday September 21, 2010 @04:13AM (#33646510)

      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 (Score:5, Interesting)

    by iONiUM (530420) on Monday September 20, 2010 @07:16PM (#33643238) Homepage Journal

    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: (Score:2, Informative)

      by jwpye (1905258)
      Not sure if it's in other DBMSs, but it's in the SQL spec.
    • Re: (Score:3, Interesting)

      by rsborg (111459)

      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.

      Isn't this just syntactic sugar? What's the difference between logic in the trigger determining when to issue the payload logic, and the logic outside the trigger... especially if the trigger (re)

      • Re:Cool (Score:5, Informative)

        by GooberToo (74388) on Monday September 20, 2010 @08:34PM (#33643984)

        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: (Score:3, Insightful)

          by butlerm (3112)

          A good optimizer could easily partition a traditional trigger into internal triggers that only ran when certain columns were updated, and in most cases maintenance would be much simpler.

          • Re: (Score:2, Insightful)

            by Anonymous Coward

            So implementing the SQL specification is now a bad thing? The trolls know no bounds.

          • Re: (Score:3, Interesting)

            by TheRaven64 (641858)
            A good optimiser can only do that when the underlying infrastructure supports it. This is only possible now that the engine supports firing triggers when a particular column is modified. You seem to be arguing that they should have implemented this support in the engine but not exposed it to the user. As the AC said, this means that you are recommending that they not implement the SQL spec, which is an interesting perspective. You're not a MySQL developer, by any chance?
    • Re:Cool (Score:5, Interesting)

      by lanner (107308) on Monday September 20, 2010 @08:17PM (#33643814)

      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.

      • by GooberToo (74388)

        I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.

        Hate to burst your bubble, but PostgreSQL's column trigger feature could have actually increased performance many times.

        Anything can be abused. Negatively portraying a powerful feature which can dramatically improve performance, readability, and maintainability, in a polithera of use cases, is nothing but FUD and ignorance.

        • Re: (Score:3, Informative)

          by afidel (530433)
          Triggers *never* increase readability IMHO, and tying features to the RDBMS rarely increases maintainability, so that leaves performance which can be enough of a reason to use them but analysis should be done to determine if there aren't other much more significant areas where the code can be optimized to bring overall system performance up to where they are unneeded. Then again most of the software I do care and feeding for is platform neutral COTS that can be run on any of MSSQL/Oracle/DB2 with Postgres o
          • Re: (Score:3, Interesting)

            by Splab (574204)

            We aren't using triggers for your readability, your maintenance or your speed, we are using triggers and constraints to ensure our dataset is correct.

            OP was bashing the programmers for always committing to hard drive, what was supposed to go there - and is going there in most MMOs - to me it sounded like the hardware setup was their downfall.

      • by butlerm (3112)

        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.

        For a game? That is positively insane. Most of the overhead of a well designed SQL database server is designed to preserve ACID properties for business transactions, on large databases that won't fit in memory. Without those requirements any well designed game algorithm should somewhere between a hundred and a thousand times fast

      • by butlerm (3112)

        By the way, some databases support a feature called "asynchronous commit", which sounds like exactly what these folks needed. No disk wait on commit because you give up the durability guarantee. The other way to resolve that problem is to use solid state disks for your commit logs.

      • by plopez (54068)

        I hate to say it, but good/useful features like that will be abused by stupid DB guys who can't program.
        Or programmers who abuse DBs and handroll crapp code to emulate what DBs do well.

        More than once I was over ruled by heads of programming teams who said "the programmers don't have time for that, we'll do in a {stored procedure|trigger|PL\SQL} etc." despite my warnings of slower speeds due to the interpreter, inflexibility, or lack of maintainability.

        Business logic never belongs in the DB. Even triggers a

        • Re:Cool (Score:5, Insightful)

          by caerwyn (38056) on Monday September 20, 2010 @11:50PM (#33645388)

          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.

    • Re: (Score:2, Informative)

      by OlRickDawson (648236)
      Yes. Oracle has those.
  • by bogaboga (793279) on Monday September 20, 2010 @07:36PM (#33643408)

    Yes first, congratulations to those folks. I am still waiting for a front-end to PostgreSQL that is as functional and easy to program as Microsoft's Access.

    I might be flamed here but there is nothing that bests Access in the open source world. Being able to program business logic into a form is something that Access and VB are pretty good at.

    What open source program can replace these two Microsoft beasts?

    • by TheFuzzy (140473) on Monday September 20, 2010 @07:45PM (#33643496)

      Why not just use .NET with PostgreSQL? You can put whatever you want on the back end.

      Or you could use Once:Radix or Servoy, both of which integrate with PostgreSQL.

      https://sourceforge.net/projects/onceradix/ [sourceforge.net]
      http://www.servoy.com/ [servoy.com]

    • Re: (Score:3, Insightful)

      by h4rr4r (612664)

      Access is too easy. It lets people who have no idea what they are doing make a half working solution that then has to be replaced with real code and a real DB.

      • then has to be replaced with real code and a real DB.

        Oddly enough I spoke to somebody last weekend who makes her living doing exactly that. I suspect that without access to kick these projects off she would have less work overall.

        access -> sqlite -> mysql -> postgres -> oracle

        Everybody looks down on the tools to the left.

        • by h4rr4r (612664)

          I suspect they would call her earlier and the job might be done right from the start.

        • Re: (Score:3, Interesting)

          by TheRaven64 (641858)

          I don't think SQLite belongs in that list. For most tasks I'd pick either SQLite or PostgreSQL, but I can't think of many applications where I would consider both. They are very different projects. The only time I have considered using both is when I wanted to have a large concurrently-usable data set stored in PostgreSQL and then a small single-user subset stored in SQLite on a handheld device. SQLite gives really great performance for single-user applications, but it lacks a lot of the more advanced p

          • Re: (Score:3, Interesting)

            by MichaelSmith (789609)

            I really just intended to remark on the attitude I see around database stories. Its worse than operating system prudes. You get the big database people sneering down at the mysql people and ignoring the fact that different applications require different tools.

            • Re: (Score:3, Insightful)

              by fusiongyro (55524)

              This is because MySQL and PostgreSQL are in the same class. If you need a small, embeddable database, SQLite fits the bill better than MySQL or PostgreSQL. If you need a multi-user database and are willing to run a server, there's no technical reason to choose MySQL over PostgreSQL. And indeed, the only reasons I see MySQL being chosen are:

              • It's the only thing the developer knows, or
              • The business people want the software to run on the lowest-end web hosts, or
              • Some third-party depends on it (WordPress), or
              • La
      • Re: (Score:2, Insightful)

        by domatic (1128127)

        Better that it be Access rather than FileMaker Pro. There is an upgrade path of sorts from Access to SQL Server. So if you have one of those unfortunate cases where it was mandated that a dinky workgroup app be shoved out enterprise wide then at least there are options to move the data and app logic to platforms that can take the load. I'm not saying that it's easy but someone who knows what they are doing can get started on fixing it pretty quickly.

        That situation with Filemaker Pro is much uglier and Fi

    • by guusbosman (151671) on Monday September 20, 2010 @08:46PM (#33644088) Homepage

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

      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.

      • by bogaboga (793279)

        Agreed. Only that I was looking for open source applications. Know of any?

        • Re: (Score:3, Interesting)

          by turing_m (1030530)

          The last time I looked at ooo.org Base (at least a year ago, if not longer), I found it surprisingly capable, even workable. Give it a go, and have some patience. I only really had a look at the forms though, but I used to use subforms a lot and I could do what I wanted to do with it. Did not really look at reports though.

          As far as business logic, put that in PostgreSQL.

          • Re: (Score:2, Insightful)

            by bogaboga (793279)

            As far as business logic, put that in PostgreSQL.

            Well, in many cases of mine, programming this logic right into the form is faster and easier to manage than a full DB.

            Case in point: While developing a healthcare app, I'd like to redraw part of the form that asks about pregnancies if the sex chosen earlier is 'male'. We all know males do not get pregnant for example. Putting this logic into the actual Db engine just slows things down in my opinion.

            Here's another: Input masks. For example, the USA has a string of integers. On the form, I can program the mas

  • As always... (Score:5, Interesting)

    by jd (1658) <imipak&yahoo,com> on Monday September 20, 2010 @07:47PM (#33643520) Homepage Journal

    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.

    • Re: (Score:3, Interesting)

      by jwpye (1905258)
      I think there has been some effort to bring a PostgreSQL "performance farm" online to show the differences in performance across versions of PostgreSQL, and to quickly identify regressions during development. I don't think it's up yet, but a search should reveal some details on the project.
    • by jadavis (473492)

      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 intend this comment with sincerity: everyone would like that. But it's not very realistic, because there are so many variables in play. Even when you try to pick one aspect, like performance, it explodes into all different angles very quickly, and you can't really do an apple

      • by afidel (530433)
        TPC-C and TPC-H are a fairly good vendor neutral way to measure DB performance for OLTP and BI type workloads though there are plenty of games played (read the detailed reports!).
    • An engine like PostgreSQL is so complex, there are few standard tests that could really give you the data you're looking for, unless your application is so vanilla the KKK would endorse it. The only way to understand -- beforehand -- how a new version of a DB like this would work in an existing environment is to set up a test server, set up your database on it, and test it against the real-world operations the production server is experiencing, then compare the two in areas like execution time, memory util

      • by jd (1658)

        To a degree, I agree. There will also be a number of things in database design that a DBA wizard could suggest that go beyond my knowledge. However, let's take a trivial example - basic SELECT, INSERT and UPDATE operations. What can you do with these? For any of the Open Source databases, you can compile with instrumentation and then measure the average length of each arc through the program that you can hit with just those three statements. With this, you can determine the maximum, minimum, mean and varian

    • by TheFuzzy (140473)

      JD,

      You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task. Several people have tried to do similar things, but the number of indexes you need to compare (features, reliability, performance, etc.) is too large. And some things are so different it's hard to compare them meaning fully. Imagine trying to do a head-to-head comparison of all OSes in every way.

      Here's a few comparison links, but they just scratch the surface:
      http://troels.arv [arvin.dk]

      • by jd (1658)

        I fully agree it would be Herculean, which is why it would be good if we could find a Hercules to assign the task to. :)

        In practice, you're right, there are some thing that are too different to compare readily. How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? Even if they used an identical command language, the beasts are very very different. To an extent, that is a good thing - it means you can pick a database that

        • "How do you compare an OO database with a Relational Database? For that matter, how do you compare a Star Database with a Relational Database? [...] To an extent, that is a good thing - it means you can pick a database that's good for the problem"

          Easy: you throw a typical problem from each class and then test all the engines against all of the problems. The fact that a relationally-oriented engine will do worse at an OO problem than an OO-oriented one doesn't preclude the test from being made anyways.

          "I am

      • "You're absolutely correct that such a comparison would be a real asset to users. However, it would also be a Herculean task."

        I don't think so. I think that it even would be quite easy and cheap because, for the most part, it's already done!

        I think that it's not done exactly because what you stated: it would be a real asset to users. RDBM vendors don't want that because RDBM choice is greatly based on gut feelings, which are much better handled by marketing than hard data.

        Think of it: don't you think that

    • Re:As always... (Score:5, Interesting)

      by greg1104 (461138) <gsmith@gregsmith.com> on Monday September 20, 2010 @11:27PM (#33645254) Homepage

      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 [suckit.blog.hu] 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 [2ndquadrant.com] to prevent regressions from popping into future versions of the code too. There is a recently completed beta client [github.com] 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 [toolbox.com] 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 [postgresql.org]. 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.

  • by TheFuzzy (140473) on Monday September 20, 2010 @08:23PM (#33643876)

    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

Aren't you glad you're not getting all the government you pay for now?

Working...