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."
select FIRST_POST from slashdot where user='Anonymous Coward';
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.
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.
"Go Postgres!"
Indeed, PostgreSQL is such a great system, in a lot of ways its better than mySQL; I'm constantly amazed by the number of orgs that have never heard of it.
Python: 'And then suddenly you have a language which says "we're all stuck with whatever the whiniest coder wants".'
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.
Are you adequate?
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
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
CSV? I have a team of slaves move stones around a field like a giant abacus.
Not a typewriter
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.
And I've been using Google Maps' satellite view to steal all your data. pwned!
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"
Microsoft Access
Get out.
Well, both the real and "trollish" answer is, MySQL has been trying to catch up for almost five years now - and doesn't look like their even close.
PostgreSQL has been a better database for a long time now. The pull of MySQL isn't its technical prowess but its "dumbness." Simply put, MySQL provides a lot in exchange for very little. Its the go to database for people who have little DBA experience, don't know what makes for a good RDBMS, or is simply needing a database where ACID doesn't matter.
Basically MySQL is popular because its the low hanging fruit. Its generally everywhere and most people who need a database don't know any better. So they've heard something about MySQL and its available with their hosting company. That's generally all they needed to know. Of course that completely ignores the fact that for most every project, PostgreSQL provides a vastly superior solution. The down side is, to use PostgreSQL vs MySQL in these cases, you'd have to read all of a dozen pages or so (actually far less, but lets play devil's advocate). And for most, that's simply far too much to ask.
Its basically the lazy or ignorant DBA's database. Or a database where reliability doesn't matter. Or integrity isn't an issue. There certainly are places for those kinds of databases - its just that most who pick MySQL don't realize they've made those trade offs.