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."
SQL! (Score:5, Funny)
select FIRST_POST from slashdot where user='Anonymous Coward';
Re:SQL! (Score:4, Funny)
Re: (Score:2)
-- 2. Profit!
Re: (Score:2)
Slashdot pseudo HTML markup escape FAIL.
Re: (Score:2)
SELECT c.*, u.*, t.sid FROM comment c JOIN thread t USING (sid) JOIN user u USING (uid) WHERE c.cid = 1612246
There's no reason cid would not be unique, so your order and limit, and doing where on sid and user is not really needed.
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!
Re:Join removal is cool (Score:5, Insightful)
"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.
Re: (Score:2)
Re:Join removal is cool (Score:5, Insightful)
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.
Re: (Score:2, Interesting)
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?
Re: (Score:2)
Re: (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
Re: (Score:2)
Re: (Score:2)
Isn't it odd? It's been around forever, works on many platforms, and supports just about any feature you might need. I started using it a few years ago for a scientific simulation project, and I haven't looked back. I think the main hindrance, in a circular argument manner, is that is hasn't been as popular as MySQL, so there're a smaller community. It wasn't as easy to find a good PL/pgSQL book as it was to find material for all the others.
My only other complaint would be the relative immaturity of the pgA
Re: (Score:2)
That may be true the procedural languages, but I've found the documentation for PostgreSQL to be pretty darn good.
Color me skeptical. (Score:4, Interesting)
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.
Re: (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 i
Re: (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: (Score:2)
For now, the more interesting part is this part:
We can't skip joining to any of the other tables, because those are inner joins. That's an implementation restriction which I hope will be lifted in PostgreSQL 9.1, but some more logic is needed to make that safe.
Not sure exactly how he's going to pull that off since a join can remove rows, but if you join on a unique index you should be able to do it just using the index without touching the table itself which should be very fast.
Re: (Score:2)
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...
Built-in replication (Score:5, Interesting)
A better summary of the changes is here [postgresql.org].
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.
Re:Built-in replication (Score:5, Interesting)
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.
Re: (Score:2)
If an application (enterprise or not) doesn't have a mechanism that handles stale connections(connection pool or not), it might not want to call itself an application.
Seriously.
Re: (Score:2)
I don't think "easy" means what you think it means. Better term would be "native" replication support vs 3rd party tools. Yes MySQL does replication out of the box. PostgreSQL had a number of 3rd party tools, each with a slightly different goal in mind. So you had to know which one best suited your purpose whether you were going for HA or load balancing. Where I've seen MySQL fall down has been true HA clustering. Before MySQL 5.1, if the master node went down, you had to bring down the entire cluster
Re: (Score:2)
Before MySQL 5.1, if the master node went down, you had to bring down the entire cluster and restart it. That could take upwards of 15 - 20 minutes or more on any database of size.
Only if you wanted to keep the same master as before. The trick was to let the slave that failed over into being the new master stay that way permanently and then concentrate on bringing the the old master up as a slave.
Re: (Score:2)
Can you explain the new built in replication's conflict handling to me? I don't understand why the conflicts would happen with a multi-version concurrency control. According to them, a Delete coming in from the master would conflict with a long running read only query on the slave. That really should not happen if you have true MVCC, and is a real deal breaker.
As the ot
Re: (Score:3, Insightful)
I see the replication feature as being more about perception than anything else.
Postgresql has long had a variety of replication options [postgresql.org] outside of the core that serve various needs, but it seems that the perception out in the community remained that postgresql was a stable, stand-alone database, and getting replication to work on top involved "hacks", while mysql, despite its faults, had "solid" replication that lent itself better to large installations.
Of course this perception is far from reality, bu
Re: (Score:2)
You're representing slony as anything other than a fragile, unscalable, dirty hack? Wow, just wow.
I'll be interested to see how native replication performs. Including it in core was long-overdue, and I'm glad they finally got it in there.
Re: (Score:2)
Not to mention you'll find plenty of MySQL DBAs with large multimaster deployments who swear calling it "replication", let alone multimaster replication generous even when everything is working correctly.
It would be nice for PostgreSQL to have an out of the box multimaster solution but those who claim MySQL is replication panacea and PostgreSQL has nothing to offer is only highlighting their ignorance. Slony is consistently used on very large datasets where ACID actually matters. We're talking about two com
Re: (Score:2)
Not to mention you'll find plenty of MySQL DBAs with large multimaster deployments who swear calling it "replication", let alone multimaster replication generous even when everything is working correctly.
Why, exactly, are they running a large multimaster deployment? Instead of Cluster NDB, anyhow?
Running a large MySQL multimaster "cluster" sounds painful. I'm not a MySQL expert, but my understanding is that MySQL replication was not intended to handle that.
Re: (Score:2)
Honestly, I'm parroting. I can't answer that.
Frankly I'm just amazed that post wasn't market troll or flamebait as is usually the case when anything negative is said about MySQL.
Re: (Score:3, Insightful)
You've touched on the fact that data replication is a hard problem and all user scenarios can't be (sensibly) solved with a single solution. MySQL replication works well enough for the web crowd that has no idea what ACID stands for and its adoption has spread as a result. There being only one choice of replication solution also makes that an easy choice to make.
Even being able to choose which replication solution to use with postgresql requires a substantial level of expertise. What postgresql has lacked
Re: (Score:2)
Where's the "+1 super genius" mod tag when you need it? I completely agree with you!
Please mod up.
other then features... (Score:3, Insightful)
What I would love to see is some standardization for SQL languages. It would be nice to take an App say say in PostgreSQL then use it in Oracle if you find that you need to go to a bigger infrastructure... As well move down, as a lot of apps are running on DB servers that are too big for their use. While the language has some nice features it would be much better to have an updated set of common function and calls so you can make your SQL more cross platform. A lot of the real work behind SQL isn't much in the Language but in what is happening underneath.
Re: (Score:2)
What I would love to see is some standardization for SQL languages.
There is a standardization. PostgreSQL is one of the few RDBMs which actually attempt to follow it. Just the same, "compatibility kits" have been created by third parties to allow for improved MySQL and even Oracle migrations to PostgreSQL. I don't know how well they work or how comprehensive the coverage.
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: (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 q
Re: (Score:2)
Auto-increment columns are a really bad idea unless your database guarantees that the same value will never be used twice. Otherwise you should use sequences, which are fast and which both Oracle and PostgreSQL support. Oracle invented the idea, PostgreSQL adopted it.
Re: (Score:3, Informative)
I've never heard of one that doesn't make that guarantee. MySQL's certainly does.
Re: (Score:2)
Oracle sequences don't. If you wrap around and use CYCLE in your sequences, it can re-use the same number.
With large enough ID spaces (UUIDs, even just plain 64-bit), this may not be an issue, depending on your app.
Re: (Score:2)
Oracle most certainly does guarantee that the values are unique, unless you tell it not to. Unless you need more than 10^38 unique values, Oracle has no problem at all.
Re: (Score:2)
Microsoft Access doesn't. The problem is you delete a row and Access re-allocates a number that has previously been used, because it is not present in the table.
Re:other then features... (Score:4, Funny)
Microsoft Access
Get out.
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: (Score:2)
Mercy. That's really dumb. MyISAM tables store the auto increment value on the disk, so you have to go way out of your way to reset it. Otherwise it won't ever repeat.
Thanks for pointing this out. I've been looking at switching to InnoDB, there's one more reason not to...
Re: (Score:2)
MySQL has sequences these days, does it not?
Re: (Score:2)
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html [mysql.com] contains some instructions for "simulating sequences". So I guess that's a no.
Re: (Score:2)
but what if you have two clients get the MAX(pkey_column) at the same time? wouldn't they both be inserting the same key?
I'd rather just have the database handle it, its trivial to implement in every database system I've used. And seems a hell of a lot safer than having every single app accessing the database handle it themselves.
Re: (Score:2)
I am referring to using database "sequences" which are independent objects that Oracle and PostgreSQL support that generate a sequence of unique integers in a high performance manner.
You can get a unique value from the sequence just by using "sequence_name.nextval" in your INSERT, UPDATE, or SELECT statement. You can also get the last allocated value in your session by referring to "sequence_name.currval". Slightly more work, much more flexible.
If you really have to generate unique values using a database
Re: (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: (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: (Score:2)
Enterprise DB implements a 95% or so solution to the oracle compatibility thing, including plsql.
http://www.enterprisedb.com/exposure/oracle-postgres_wp-1.do [enterprisedb.com]
Re: (Score:3, Interesting)
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 su
Re: (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
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: (Score:2)
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:
On more research, yes that seems to be my bad. It was probably a very borked implementation attempt I read the discussion of, the subtle difference between "NO WAY" and "NOT *THIS* WAY" was lost at that point I guess.
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.
That's not all folks (Score:2)
Besides being 99% compatible to Oracle in PL/SQL, it's also 99% compatible for embedded SQL in C applications (Oracle Pro*C). So if you want to migrate an Oracle installation, or create a prototype for a future Oracle installation, Postgresql is the right option.
Re: (Score:2)
As a whole, Postgres started as an open-source Oracle clone.
Not accurate, please see this [wikipedia.org], which notes that postgres is descended from Ingres, an early object/relational effort. It's not even close to an Oracle clone, which you will see if you bring up the command line client and try to display a table's structure.
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: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: (Score:2)
Re: (Score:2)
Huh. And most databases don't run any operating system, much less such a wide variety. But, really, is that a feature people look for in a DBMS?
Re: (Score:3, Interesting)
I'd say something, but someone will freak out (Score:2, Interesting)
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: (Score:2)
What does an HTTP server have to do with a DB?
PostgreSQL has a lot of more enterprisy features than MySQL.
Re: (Score:3, Funny)
What does an HTTP server have to do with a DB?
PostgreSQL has a lot of more enterprisy features than MySQL.
You mean like clustering [mysql.com]?
Oh, wait...
Re: (Score:2)
Wait, is that really a released, supported, and ready for production version? I thought mysql was still working on getting 6.x out the door. Cool if it is.
Re: (Score:2)
Wait, is that really a released, supported, and ready for production version? I thought mysql was still working on getting 6.x out the door. Cool if it is.
FYI, the version # of MySQL Cluster does not track with the version of the DBMS.
Re: (Score:2)
What does an HTTP server have to do with a DB?
PostgreSQL has a lot of more enterprisy features than MySQL.
You mean like clustering [mysql.com]?
Oh, wait...
How about proper ACID compliance?
Not null constraints?
Of course, if you talk to MySQL users you soon realise most won't consider anything an "enterprisy feature" until it is supported by MySQL.
I nearly fell off my chair a while ago when I read the changelist for the last major MySQL release trumped the exciting new ability to do *online database backups*.
I mean, seriously? lets not kid ourselves, MySQL has its uses as a quick and dirty database, can be fast if you stay away from the slow bits (things like
Re: (Score:2)
Yeah, nobody uses it for anything useful or anything.
Except skype. .org and .info tlds)
And Afilias (the guys what run the
And Cisco.
And the USGS.
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: (Score:3, Funny)
If you want to freak out people, you say something like this:
I don't use any database, I use plain XML text files.
Re: (Score:2)
Re:I'd say something, but someone will freak out (Score:4, Funny)
CSV? I have a team of slaves move stones around a field like a giant abacus.
Re:I'd say something, but someone will freak out (Score:5, Funny)
And I've been using Google Maps' satellite view to steal all your data. pwned!
Re: (Score:2)
on Windows.
Re: (Score:2)
I don't use any database, I use plain XML text files.
I use EBCDIC punch cards, you insensitive clod!
Do you know the verb "to google"? (Score:2)
Google [google.com] is your friend.
Re: (Score:2)
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.
For your sake, I hope that was a troll.
By the way, what's the advantage of a car versus a truck, or a separate gas station?
Re: (Score:2)
There are a number of advantages of PostgreSQL versus (not "verses") MySQL: the robust query-rewrite rule engine, better SQL-standard compliance in many areas, support for CTEs with the WITH... and WITH...RECURSIVE constructs, etc., native geometric types, better support for user-defined types and operators (which 9.0 exclusion constraints will make a bigger advantage), the mature GIS add-on, etc.
There are also some advantages to MySQL ove
Still waiting (Score:3)
For the multi-master replication that was promised for 8.4.
Re: (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 spr
Re: (Score:3, Interesting)
and now there's Postgres-XC, which looks very promising
Re: (Score:2)
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.
Um, no.
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?
And those queries return incorrect results when they are behind the master. Pretty cool, huh?
This is why Postgres/MySQL replication is not even remotely the same thing as Oracle RAC.
Re: (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 C
Re: (Score:2)
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)
Oh, believe me, I understand this very well. The poster I was replying to claimed that Postgres's new replication solution was "an awesome feature which lets Postgres compete with the big boys like Oracle."
My response then, and continues to be, that while a decent replication solution in Postgres core is an extremely welcome addition; it does not, in any way, shape, or form, compete with Oracle RAC. It's just not in the same league.
Don't get me wrong, Postgres is great, especially considering its price.
Re: (Score:2)
Imagine that you still have a single point of failure if your big expensive database server goes down. While replication helps for increased load capacity, especially on read heavy applications, it is not a clustering solution. Which is really what I want to see. That being said there are some postgresql based products which do this, like GridSQL. We use PostgreSQL as the database for our point of sale application. Unlike MySQL, it doesn't require a $500 per seat licensing fee to distribute and Postgre
PostgreSQL rulez! (Score:2)
So I recently had to find a free SQL DB so I could do "select * from sm1 where (times,command,descriptor) not in (select sm1.times,sm1.command,sm1.descriptor from sm1,sm2 where abs(sm1.times-sm2.times)1 and sm1.command=sm2.command and sm1.descriptor=sm2.descriptor);"
PostgreSQL was the choice, it was easy to install on my Mac, it rulez!
No more slony? (Score:2)
We have been using slony 1.x for master/slave replication with pg 8.3 and it has worked well for us. It does have its problems, specially lock issues when modifying schema for busy tables. I see that 9.0 includes built-in replication and a work-around for these situations (i.e.: kill read-only queries that are in the way).
I wonder why this didn't make to their list of favourites. It is on the runner-ups though.
Re: (Score:3, Informative)
You're not alone. That issue is one of the last MySQL staples which PostgreSQL users hear about.
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: (Score:2)
Is there any specific reason you did not just download the postgresql binary from their website, instead of building it yourself?
Re: (Score:2)
If you'll read the section I have on rebuilding the RPMs from source, you'll notice I say this:
Followed closely by:
And later, you'll notice my configuration/build flags:
That last one, '--disable-integer-datetimes' is required for pg_migrator to work. This f
Re:In place upgrades (Score:5, Informative)
Yup, I used pg_migrator for the last RubyForge upgrade [blogs.com], very handy!
Re: (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: (Score:2)
I'd agree with you. I'm expert in neither MySQL nor Postgresql, but have worked with both, and I prefer working with Postgresql. It just makes more sense to me, and I find it a little easier to use even though I've used MySQL more.
Re: (Score:3, Informative)