Slashdot Mirror
What Is New In PostgreSQL 9.0
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."
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
If I have been able to see further than others, it is because I bought a pair of binoculars.
There's a new open-source database from one of the founders of PostgreSQL (Michael Stonebreaker): http://www.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.
Except for ending slavery, the Nazis, communism, & securing American independence, war has never solved anything.
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 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.
Read: Rabbit Rue - Free serial nove
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.
http://cltracker.net -- powerful craigslist multi-city search
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.
Yup, I used pg_migrator for the last RubyForge upgrade, very handy!
The Army reading list
It's a real database with ACID compliance designed in from the start, not as an afterthought.
cat
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:
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 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)?
http://cltracker.net -- powerful craigslist multi-city search
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.
Actually, MySQL doesn't. At least not with InnoDB:
"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"