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."
If you got tired of clicking through articles (Score:5, Informative)
While the changelog is cool and all, if you just want to see the slides go to http://www.slideshare.net/xzilla/intro-to-postgres-9-tutorial [slideshare.net]
Join removal is cool (Score:3, Informative)
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).
Go Postgres!
Direct link (Score:2, Informative)
http://www.slideshare.net/xzilla/intro-to-postgres-9-tutorial [slideshare.net]
It took me a while to realize it was not either of 2 mentioned presentations...
Re:In place upgrades (Score:3, Informative)
You're not alone. That issue is one of the last MySQL staples which PostgreSQL users hear about.
Related Tangent (Score:3, Informative)
There's a new open-source database from one of the founders of PostgreSQL (Michael Stonebreaker): http://www.voltdb.com/ [voltdb.com]
I believe it is based on the H-Store project from MIT, and if it is anything like Stonebreaker's Vertica, should be similar in language and syntax to PostgreSQL.
VoltDB should be for high-demand OLTP. It keeps everything in memory and is MPP (not to mention full-ACID compliance). It runs POSIX compliant unixes, even Mac OS 10.5 and later, Linux, etc. They only support CentOS (which is RHEL if memory serves).
Anyway, if anyone is interested in PostgreSQL, I would take a look at this.
Re:In place upgrades (Score:5, Informative)
Actually, there's a utility that works on 8.3 and above: pg_migrator, and isn't really that new. I wrote a long article [bonesmoses.org] on it a while ago that covers how we used it, and most of those instructions are not especially specific to our use case. Of course, before 8.3 you'll have to rely on a parallel restore (8.4's pg_restore client has a -j flag much like make, that will load several tables simultaneously, which drastically cuts migration time except for the initial dump.)
All in all, it's a much better DB than it was in the 7.x days, and that's after the drastic improvements in the 8.x tree. I can't wait for 9.0.
Re:Join removal is cool (Score:2, Informative)
(1) it's a left join, (2) there is a unique index on all or a subset of the join columns, and (3) none of the attributes from the nullable side of the join are used elsewhere in the query
Re:other then features... (Score:5, Informative)
You're basically describing the SQL language itself (PostgreSQL does a good job of implementing most of the various SQL standards up to SQL-92 and even SQL-99). Of course, add-on procedural languages like Oracle's PL/SQL aren't going to be supported as-is anytime soon on PostgreSQL, or anywhere else. And of course, each database vendor has their own extensions to the SQL language itself, which other vendors aren't always keen to copy (think MySQL's INSERT ... ON DUPLICATE KEY UPDATE, or PostgreSQL's CLUSTER).
If you're designing a database application which you want to be easily portable across various SQL databases, just try to keep any non-standard-SQL use to a minimum and use of procedural languages simple and only when necessary. Books by Joe Celko stress this ideology, though my take is that it's just about impossible to really get the most out of your database unless you really make use of its extensions, which are there for a reason. For example, Celko discourages the use of auto-increment columns (serial type in Postgres, auto-increment primary key in MySQL), in favor of manually incrementing your sequence using MAX(pkey_column) or similar, which strikes me as absurd and non-scalable.
Re:Color me skeptical. (Score:3, Informative)
you are basically correct, but yet still wrong. Certain optimizations use page reads to improve performance when returning a result set. It will try to access pinned/blocked columns before accessing trivial columns there by reducing collection times. Although these columns may not be indexed per se, they are ordered so retrieval pretty much sequential. Removing those pinned columns (either from the query or from thjs DB in general) may cause the DB to to start 'thrashing' and reduce the performance. This is another reason why vectored indexes are a good thing.
Re:In place upgrades (Score:5, Informative)
Yup, I used pg_migrator for the last RubyForge upgrade [blogs.com], very handy!
Re:I'd say something, but someone will freak out (Score:5, Informative)
It's a real database with ACID compliance designed in from the start, not as an afterthought.
Re:Related Tangent (Score:4, Informative)
Anyway, if anyone is interested in PostgreSQL, I would take a look at this.
They don't really have similar use cases, but if you need a tight in-memory ACID database Volt might be just the thing. I think if you've ever been tempted to run sqlite on a ram disk, Volt is your baby. If you need high performance ACID and can afford lots of RAM, Volt probably makes you really happy.
Re:other then features... (Score:3, Informative)
They have tried [wikipedia.org]. But the databases evolved so much faster than the language specification, especially when it comes to anything past plain SQL like triggers. Hell, even such a thing as automatic numbering is done differently in almost every database. Some things they just don't *want* to implement on ideological reasons, like "UPDATE OR INSERT" or "CREATE IF NOT EXISTS" in PostgreSQL at least. PostgreSQL is definitely on the better side of that though, Oracle is pretty much last so I don't know what to tell you, it'll never happen. It's more likely PostgreSQL will grow into an Oracle than that Oracle will ever support the standards as well as PostgreSQL does. By the way, one thing I've noticed with them is that they're very clear on pointing out what they don't support of the standard or if they do anything extra compared to the standard, that's *very* nice even if it's likely unportable anyway...
Re:other then features... (Score:4, Informative)
Some things they just don't *want* to implement on ideological reasons, like "UPDATE OR INSERT" or "CREATE IF NOT EXISTS" in PostgreSQL at least.
Definitely not true. There's a lot of support to implement the MERGE command from the SQL standard. It's been proposed a few times, but it's more difficult than it sounds to implement. From here [postgresql.org]:
Your gripe about CREATE ... IF NOT EXISTS might hold water, depending on what exactly you're complaining about (CREATE TABLE? Or for indexes/constraints?). There does seem to be some resistance [postgresql.org] to CINE, though from what I can tell it's mostly because people would rather have CREATE OR REPLACE, but COR is much harder to implement (what do you do when the object already exists, but is slightly different than the one you're trying to create)?
Re:Still waiting (Score:3, Informative)
Eh? When was multi-master replication ever promised in core? You're probably thinking about hot standby -- the Streaming Replication/Hot Standby code which is the "killer feature" of 9.0 was originally slated for 8.4, but didn't make it in time. I'm really surprised there aren't more comments about SR/HS, as it's an awesome feature which lets Postgres compete with the big boys like Oracle.
Imagine having your expensive database server be dedicated *only* to writes, and having all your read-only queries spread across one or more slave(s) which are also your backup servers. Pretty cool, huh?
Re:other then features... (Score:3, Informative)
Actually, while ISTR that Celko notes that using non-standard autoincrementing columns is non-portable and that you have to use other ways of doing so if you want code that is usable across different SQL backends, I'm pretty sure that he actually spends quite some time railing against the entire idea of using autoincremented counters, particularly in their common use as automagical keys which are then exposed to the end-user, and advocates, instead, constructing safe exposed identifiers with features like check digits in such use cases.
I don't think there is any case in which Celko argues that an autoincrementing counter column is the right solution to a problem; the non-portability of the common features for these is, for him, one more stroke against using them, but not the main problem.
Re:when they have a tool like phpmyadmin (Score:3, Informative)
Phppgadmin is pretty much that, there is also pgadmin which is a desktop app.
I suggest you educate yourself before making such statements.
Re:other then features... (Score:3, Informative)
I've never heard of one that doesn't make that guarantee. MySQL's certainly does.
Re:other then features... (Score:5, Informative)
Postgres actually does this... almost.
First, unlike other SQL engines Postgres is language-independent. There is a plug-in system, and it already ships with a few different SQL variants.
Second, the primary language is PL/PGSQL which is a clone of Oracle's PL/SQL. As a whole, Postgres started as an open-source Oracle clone. If you do a Google search, you will find several success stories of OraclePostgreSQL migrations because the stored procedure language is so similar.
However, you are correct: there needs to be a standard. I see that someone posted and said "SQL is the standard" but that isn't good enough. Raw SQL doesn't provide control structures. There's no loops, no if-then-else, etc. As a whole, I like to avoid those, but they are inevitable.
Re:I like.... (Score:3, Informative)
Re:other then features... (Score:3, Informative)
add-on procedural languages like Oracle's PL/SQL aren't going to be supported as-is anytime soon on PostgreSQL
Actually, PostgreSQL ships with PL/PGSQL which is pretty-much a clone of PL/SQL.
Re:other then features... (Score:4, Informative)
Actually, MySQL doesn't. At least not with InnoDB [mysql.com]:
"InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;
InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1"
Re:Still waiting (Score:3, Informative)
And those queries return incorrect results when they are behind the master. Pretty cool, huh?
Yup, that's the idea behind "asynchronous replication", and that's exactly how it's billed. If that's a deal-killer for you (for a large number of read-only queries it won't be.. think web applications where an eventually consistent state is perfectly fine) just wait until Postgres 9.1, when we should have more knobs for controlling master-slave replication. Then you should have the option to force the master to wait until a slave has received its WAL file update, and even fsync'ed it to disk, before your COMMIT returns, giving you the synchronous replication behavior you want.
The tradeoff with synchronous replication is increased latency on the master of course, since you're hanging around waiting for the slave to fsync (or just receive the data, depending on how you turn the knob). There have even been some proposals floating around for a "quorum commit" model whereby you would tell the master that you want your COMMIT; to go through when n out of m slaves have received the data.
Re:other then features... (Score:3, Informative)
A sidenote here: the SERIAL datatype in PostgreSQL does not exist. It is merely a shorthand form to create an INTEGER column, a sequence, and assign the nextval() of that sequence as the default value to the column.
Mart
Re:Join removal is cool (Score:3, Informative)
the Windows version didn't work as well as the Unix
That was true back when PostgreSQL was a cygwin port; many, many years ago. For many years now PostgreSQL is a first rate sibling application on Windows. In fact, one of the killer performance combinations is 64-bit windows with 32-bit PostgreSQL - despite the fact 64-bit PostgreSQL is also available on Windows. The combination allows for 64-bit file cache from the OS (PostgreSQL heavily relies on the OS to provide robust file caching) and 32-bit binaries which further enhances the CPU's cache for executing instructions and caching data. Unless your queries require "64-bit datasets", performance is said to be excellent.
Bluntly, anyone who tells you PostgreSQL is not a first rate Windows DB either has an ax to grind or is simply ignorant and in no position to be making such recommendations.In the end, you are absolutely correct. The lack of a proper PostgreSQL Windows port not only hindered general use adoption of PostgreSQL, but directly helped catapult MySQL's adoption, all those years ago. That fact was identified and is the reason why a first rate Windows port exists today.