Become a fan of Slashdot on Facebook


Forgot your password?
Databases Software

What Is New In PostgreSQL 9.0 213

Jamie noted a blog that says "PostgreSQL 9.0 beta 2 just got released this week. We may see another beta before 9.0 is finally released, but it looks like PostgreSQL 9.0 will be here probably sometime this month. Robert Treat has a great slide presentation showcasing all the new features."
This discussion has been archived. No new comments can be posted.

What Is New In PostgreSQL 9.0

Comments Filter:
  • Built-in replication (Score:5, Interesting)

    by atomic777 ( 860023 ) on Wednesday June 09, 2010 @01:37PM (#32513250)

    A better summary of the changes is here [].

    After years of resisting, one of the more significant changes is the inclusion of WAL shipping-based replication into postgresql core, and the ability to do read-only queries on the standby systems. This will hopefully go a long way towards appeasing mysql users used to the "easy" replication that mysql provides.

  • Color me skeptical. (Score:4, Interesting)

    by Estanislao Martínez ( 203477 ) on Wednesday June 09, 2010 @01:42PM (#32513346) Homepage

    This bites me occasionally in Oracle where you've got a big query that has lots of tables joined together, and then at some point one of the columns is removed from the select part, and the query performance suddenly goes to hell. Then you have to go through and verify that each table is actually being used (even worse if the column that was removed from the select came from deep joins).

    Do you have an actual example? I simply don't see how removing a column from a select clause would make a query slower, unless you're talking about uses of aggregate functions, and even there I'm having a hard time seeing how a removal could make things worse.

    The thing that determines how much work the database has to do in order to produce the results is the FROM, the WHERE and the GROUP BY, because those are the ones that determine what's going to be accessed, joined, sorted and how. The SELECT (except for the use of aggregate functions) primarily just decides what information to present from the join results and how to present it.

  • by BSAtHome ( 455370 ) on Wednesday June 09, 2010 @01:57PM (#32513570)

    And streaming replication. It makes it a lot easier to have a backup server that is up to date. It makes me happy so I can do partial restores without a lot of fuss.
    Now, if only the enterprise apps could find out to reconnect to the database and continue where they left off without crashing and trashing.

  • by Rallias Ubernerd ( 1760460 ) on Wednesday June 09, 2010 @02:09PM (#32513798) Journal

    I don't understand. What is the advantage of PostgreSQL verses MySQL or a seperate HTTP server?

    Forget it. I know someone is going to freak out and mod me troll. I do not intend to cause harm.

  • Re:Still waiting (Score:3, Interesting)

    by cxreg ( 44671 ) on Wednesday June 09, 2010 @03:57PM (#32515284) Homepage Journal

    and now there's Postgres-XC, which looks very promising

  • by Dimes ( 10216 ) * on Wednesday June 09, 2010 @09:02PM (#32518740) Homepage

    What you just stated sounds all well and good...but its not very informed. Or maybe just informed from 10 years ago.

    In general a database is pretty much as good as its DBA. That said, your statement about Postgre being vastly superior is strictly a contextual one, and even then would be a trade-off at best case.

    For the DBMS corner cases that MySQL doesn't do, there are some incredibly important things that Postgre doesn't do.

    I see in the updates that Postgre is finally doing Streaming Replication? Just now? Before this, you had to rely on 3rd party "hacks".

    Meanwhile, MySQL has done instant replication and failover for quite some time.

    More importantly, MySQL has done Master/Master replication for years now, natively, with relative ease. I have been doing Live Hitless upgrades to sites for years now, to infrastructures built on MySQL. Sites doing 5-10k questions per/sec. on minimal hardware(relatively). No Down Time. That is huge when it comes to "stability" of a website or web application when everything is DB dependent.

    Don't get me wrong, I like Postgre. I have for years. But don't go around talking the old line that MySQL is an severely incomplete DB for the ignorant. It has matured over the years into one of the most capable, feature complete, stable, and useful databases available.


  • Re:Related Tangent (Score:3, Interesting)

    by Ash-Fox ( 726320 ) on Thursday June 10, 2010 @04:15AM (#32521142) Journal

    It runs POSIX compliant unixes, even Mac OS 10.5 and later, Linux, etc.

    Sorry to say, but OS X is no where near POSIX compliance.

    To give one example, it can't even forking properly, it disallows a process to fork violates the POSIX standard. It does not forbid you to fork(), it demands you to fork() and exec() when it cannot guarantee you that the libraries you are using are safe from async-signal-safe. Guaranteeing your code can be forked even in a signal handler at any time is what POSIX demands. Trying to fork() without exec(), this is allowed by POSIX standards, however, if OS X cannot guarantee that the libraries in use are 'async-signal-safe' and this is not allowed, so it crashes the thread. There is tonnes of broken crap like this in OS X. Don't even get me started on the broken POSIX threading.

    Now, the funny thing is that Windows actually possess a POSIX, which is fully compliant while OS X's is not. Why isn't VoltDB available for it? I find this sort of thing stupid.

  • by borgboy ( 218060 ) on Thursday June 10, 2010 @12:31PM (#32524748)

    Above and beyond SQL-92/SQL-99, PostgreSQL does a good job of implementing the non-optional parts of SQL:2003 and SQL:2008 as well, and in that regard are competitive with or better than the commercial alternatives.

    PL/SQL is probably unlikely to ever be available in the Open Source PostgreSQL product, but it is a feature of EnterpriseDB, which is a PostgreSQL superset.

    INSERT..ON DUPLICATE KEY UPDATE is IIRC similar to the new SQL:2003 MERGE statement, which is on the TODO list for PostgreSQL.

    CLUSTER is a subset of Oracle's index-organized tables / SQL Server's clustered index features.

    One of the truly innovative features that is arriving is exclusion constraints. If you've ever had to implement a scheduling system that deals with concurrent updates, you'll recognize that PostgreSQL has an absolutely killer feature that makes it trivial to solve the concurrent range-excluded searched update problem without messy application code. This feature is pure gold.

    Designing a performance-intensive application that is portable across multiple databases is a frustrating, difficult task. Starting with ANSI/ISO syntax is indeed a great way to base your design, but the devil is truly in the details. ORMs can hurt as much as they help.

IN MY OPINION anyone interested in improving himself should not rule out becoming pure energy. -- Jack Handley, The New Mexican, 1988.