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."

Comments

In place upgrades

[wannabe]

I'm glad to see work being done on on-place upgrades rather than the current dump and reload.

(First Post?!?)

Re:In place upgrades

[GooberToo]

You're not alone. That issue is one of the last MySQL staples which PostgreSQL users hear about.

Re:In place upgrades

[Trifthen]

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.

Re:In place upgrades

[TheSunborn]

Is there any specific reason you did not just download the postgresql binary from their website, instead of building it yourself?

Re:In place upgrades

[tcopeland]

Yup, I used pg_migrator for the last RubyForge upgrade, very handy!

Re:In place upgrades

[Toonol]

black people are about 13% of the US population yet they appear in easily 80% of TV commercials

If there's an average of 11 people in a TV commercial, that number makes perfect sense. 0.87 ^ 11 = .21

Re:In place upgrades

[Trifthen]

If you'll read the section I have on rebuilding the RPMs from source, you'll notice I say this:

The Official RPMs supplied by Postgres are insufficient for our needs. For pg_migrator to work, we'll need to build new ones.

Followed closely by:

. . . will disable integers from having datetimes assigned . . .

And later, you'll notice my configuration/build flags:

./configure --prefix=/opt/postgres-8.4 --disable-integer-datetimes

That last one, '--disable-integer-datetimes' is required for pg_migrator to work. This flag is, unfortunately, not set in the default binaries distributed by PostgreSQL.

Re:In place upgrades

[Anonymous Coward]

black people are about 13% of the US population yet they appear in easily 80% of TV commercials If there's an average of 11 people in a TV commercial, that number makes perfect sense. 0.87 ^ 11 = .21

you cherry-picked and carefully omitted the "and often outnumber anyone else" portion. good job there Sparkles. when you can comprehend what you read get back to us, mmmkay?

Re:In place upgrades

[Anonymous Coward]

Actually, there's a utility that works on 8.3 and above: pg_migrator, and isn't really that new.

Yeah, and the pg_upgrade in PostgreSQL 9.0 is pg_migrator. But I guess you'd need to RTFA to know that. :)

Re:In place upgrades

[dropadrop]

I hear our dba's complain about replication too. We run services on both mysql and postgresql and from what I gathered it was far more difficult and error prone to configure the replication for complex posgresql databases (defining per table?).

Re:In place upgrades

[Trifthen]

Actually, my point was to address the idea that upgrading without a dump/restore was something new to 9.0. You didn't have to read the article for that part. ;)

Re:In place upgrades

[GooberToo]

That issue is one of the last MySQL staples

That's why streaming replication/hot standby is also included in the 9.0 release. That feature was hoped for 8.4 but didn't make it until 9.0.

Re:In place upgrades

[rycamor]

I hear our dba's complain about replication too. We run services on both mysql and postgresql and from what I gathered it was far more difficult and error prone to configure the replication for complex posgresql databases (defining per table?).

Well of course replication on a complex database is going to be hard. Fact is, most MySQL databases are very simple (the only appropriate use of MySQL, IMHO). Once a MySQL database gets complicated, it is all but impossible to coax robust replication out of it. In the case of one I had to deal with, I had to finally retreat to the simplest master->slave approach, AND I had to upgrade to 5.1 for row-base replication (statement-based replication is just impossible if you have complex stored procedures). Even so, I have to set slave_exec_mode to IDEMPOTENT in order to actually keep replication running, which of course makes the actual replication far less robust. (I know, if they moved to full transactional INNODB tables it probably wouldn't be necessary, but unfortunately they need MyISAM tables for some things).

Point is, replication is HARD to get right on any moderately complex system. I doubt there will ever be a magic bullet in that regard.

SQL!

[Anonymous Coward]

select FIRST_POST from slashdot where user='Anonymous Coward';

Re:SQL!

[medcalf]

SQL ERROR: inconsistent indices

Re:SQL!

[Anonymous Coward]

SELECT comment.* FROM comment
JOIN user ON comment.author = user.id
WHERE comment.sid='1612246' AND user.name='Anonymous Coward'
ORDER BY comment.cid LIMIT 1;

Re:SQL!

[AttillaTheNun]

update slashdot set user = 'AttillaTheNun', comment="First Post!" where post_id == 1 and user 'AttillaTheNun';

-- 2. Profit!

Re:SQL!

[AttillaTheNun]

I know, SQL ERROR

Slashdot pseudo HTML markup escape FAIL.

Re:SQL!

[skarphace]

You can chop a lot of crap out of there. Using aliases and getting rid of some unneeded items.

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

[Qzukk]

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

Re:If you got tired of clicking through articles

[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

Thanks. I just want to see the slides, though, and when I tried to download the presentation, I was told that I'd have to sign up for an account and that I'd get it by email (!).

Can anyone please put this up on a non-braindead site?

Join removal is cool

[wandazulu]

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

[interval1066]

"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:Join removal is cool

[mcferguson]

Note that join removal is only going to work on OUTER joins in some cases, as an INNER join is implicitly part of the predicate. If the joined columns are already related by a constraint, though, it may still be able to eliminate an INNER join.

Re:Join removal is cool

[mcferguson]

Actually, here are the entire conditions for join removal (from Robert Hass's blog):

(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:Join removal is cool

[Kjella]

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:Join removal is cool

[Anonymous Coward]

Last I checked the indexes don't contain transaction data (from 2007: "why is count(*) so slow?") so if you just looked at the index, you might still get the wrong answer. Maybe adding transaction windows to indexes is what this is waiting on? (can't see that not suck either performance-wise or storage-wise though).

Re:Join removal is cool

[mcferguson]

You would need an FK to the other table's unique index / PK, and then the inner join could be removed (if it is not referenced in the select list). In theory the FK has already been checked so the join is not really acting as part of the predicate / filter in that situation.

Re:Join removal is cool

[Joey Vegetables]

I know this will be interpreted as a troll, but I'm asking, in all seriousness, in what way(s) is the current production version of PostgreSQL NOT a better RDBMS than the current production version of MySQL? Or, in other words, where does PG need to improve in order to match MySQL?

Re:Join removal is cool

[deuterium]

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 pgAdmin software. It works fine, but does some odd things like doubly importing data if you don't know not to click the OK button after completion, and not refreshing views after actions.

Re:Join removal is cool

[GooberToo]

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:Join removal is cool

[Anonymous Coward]

You could... You know... Take a look at the execution plan before executing your new query, see that big cartesian join and notice there's something wrong.

Re:Join removal is cool

[Anonymous Coward]

Robert Haas comments on this in one of the blog posts linked in the article:

In some cases you can prove that the inner join can't affect the row count - foreign keys are an essential part of deducing this.

Re:Join removal is cool

[Estanislao Mart�nez]

You could... You know... Take a look at the execution plan before executing your new query, see that big cartesian join and notice there's something wrong.

I'm still waiting for the fantastic explanation of how removing a column from a SELECT clause in a query that executes with no cartesian joins will result in a query that does.

Re:Join removal is cool

[Estanislao Mart�nez]

Robert Haas comments on this in one of the blog posts linked in the article:

In some cases you can prove that the inner join can't affect the row count - foreign keys are an essential part of deducing this.

...and, as a sidenote, this is a perfect example of why it sucks that SQL allows for duplicate rows in tables and result sets. This optimization would be (a) easier and (b) far more generally applicable if the database only returned unique rows, instead of having to worry about returning the correct number of duplicate rows.

Re:Join removal is cool

[Dimes]

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.

dimes

Re:Join removal is cool

[ducomputergeek]

That may be true the procedural languages, but I've found the documentation for PostgreSQL to be pretty darn good.

Re:Join removal is cool

[Anonymous Coward]

Gee, thanks! I was cluelessly thinking about using MySQL on my first project at my uncle's shop. Now that I see how crappy it is, I just decided I'll stick to good ol' Access after all.

Re:Join removal is cool

[sirsnork]

I _think_ what they are saying, is if the column you remove from the select clause, is the only column from that table being used/displayed other than the column used in the join, then the optimizer will remove the entire join from the query, thereby speeding up the query. But I could be very wrong because I've been wondering the same thing as you since it was first mentioned

Re:Join removal is cool

[deniable]

PostgreSQL v MySQL or Postgre v My, pick one. It would make your argument more convincing.

Re:Join removal is cool

[deniable]

It's not really a technical issue. MySQL had a native Windows version much earlier, so the hobbyists could test at home. It's supported by a lot of web hosts and it's where a lot of the apps and docs / books are targeted. That mind-share is hard to beat. MySQL is basically the MS Windows of the FOSS database world. I guess PostgreSQL needs to market itself better and build up the support infrastructure.

Re:Join removal is cool

[Anonymous Coward]

This optimization would be (a) easier

This is true.

and (b) far more generally applicable

Haha no.

Try UNION vs UNION ALL sometime. It takes a (relative) shitload of processing power to care whether two tuples are the same or not, for every pair of tuples in the result set.

Re:Join removal is cool

[Anonymous Coward]

Actually, there's another reason.

I can easily find people with decent MySQL experience, both as DBAs and developers. Sure, it has problems, but those problems are well known, and there's a tonne of information out there about how to work around them or avoid them. I can find plenty of software that works with MySQL, much of which will only work with MySQL. If I have a serious problem, I could even go and find consultants who specialize in MySQL (like Percona, for example).

Sure, PostgreSQL might well be a better solution. The only case I can think of where MySQL is actually better is a read-heavy web application that doesn't care about data integrity and that can not (for whatever reason) use memcache or similar. For everything else, PostgreSQL is certainly better. That doesn't matter if I can't find anyone with any experience with it.

It also doesn't help that PostgreSQL only became available on Windows a few versions ago and, from what I gather, the Windows version didn't work as well as the Unix version anyway. Hell, I've never found a Windows-using developer who has even heard of PostgreSQL, much less used it.

Re:Join removal is cool

[the_womble]

Postgres is a lot harder to use. I recently started using it (because its the safest option with Django) and I made a lot of mistakes that I would not have made with MySQL, mostly with privileges.

Yes, Postgres is probably better, but if MySQL works for you (and with an engine that enforces foreign keys now a common option, it does everything I really need), is cheaper (which it is for web stuff because every host has it installed), and easier, why not use it?

Re:Join removal is cool

[gullevek]

For me it is the other way around. The people who know or use MySQL are in the minority. Everybody I know uses mainly PostgreSQL (unless one shitty OSS project requires MySQL).

Re:Join removal is cool

[gullevek]

At the beginning postgresql might look more difficult, but once you understand it, it just works better.

I personally, after several mysql failures and problems, will never trust that again.

On the long run PostgreSQL is the better choice (imho).

just the command line is soo much better. every did a ^C in mysql, where i throws you back to the shell? Makes me very FUFUFUFU everytime.

Re:Join removal is cool

[Ash-Fox]

For me it is the other way around. The people who know or use MySQL are in the minority. Everybody I know uses mainly PostgreSQL (unless one shitty OSS project requires MySQL).

I'm quite fond of Zimbra, however it uses MySQL. Any ideas about a better solution that replaces Zimbra which is not in the "minority"?

Re:Join removal is cool

[gullevek]

Unless you go google, no.

Re:Join removal is cool

[lucifron]

Postgres, not Postgre.

The name comes from Postgres relation to the earlier Ingres database.

Re:Join removal is cool

[deniable]

No, really? Read the grand-grand-parent post. Why do people drop the SQL from PostgreSQL but not MySQL?

Re:Join removal is cool

[Friggo]

No, really? Read the grand-grand-parent post. Why do people drop the SQL from PostgreSQL but not MySQL?

Perhaps because the word PostgreSQL is 10 characters long while MySQL is 5 characters long?

Re:Join removal is cool

[GooberToo]

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.

Re:Join removal is cool

[kv9]

why so bitter? did a MySQL "DBA" fuck your wife or something?

Re:Join removal is cool

[Anonymous Coward]

I started out using DB2 about 15 years ago. For the past 5 years I've been using MySQL constantly. In the meantime I've used everything from whatever they called DB2 on the S390 through MS SQL Server, PostgreSQL to Oracle.

On the PostgreSQL vs MySQL argument, the difference is: bugger all. You get slightly different performance metrics under different situation; PostgreSQL has a few more features; MySQL has excellent documentation, pervasiveness and integration with everything; MySQL uses a number of different (3rd party) underlying storage engines (this is good or bad depending on your requirements / understanding).

The parent's comment about MySQL not being ACID compliant is either deliberate FUD or indicates the PostgreSQL fanboi-ism of someone who hasn't seriously looked at the alternatives in 5 years.

As with almost everything, they have their strengths and weakness. Choose wisely.

Re:Join removal is cool

[Anonymous Coward]

During the .com bubble, PostgreSQL wasn't available in a native version for Windows. Plus, MySQL was rather lean, fast, and free (where sqlite3 dominates today.)

PostgreSQL came out with a native Windows version shortly after the bubble burst, but it was too late. MySQL was supported by just about every internet server framework, platform, cms, etc. and people love using the default setup.

Unfortunately for MySQL, faces some serious challenges:

  * PostgreSQL and Firebird are getting more attention, especially for scenarios that require more than sqlite3 type of solution
  * sqlite3 is still super fast while getting more features (foreign key required triggers until recently)
  * some are not too happy with Oracle's purchase of MySQL and the uncertain future it brings
  * some buggy releases that pissed off users

I'd rather use sqlite3 for simple needs, and PostgreSQL for more complex and scalable ones. MySQL, like Firebird, sits somewhere in the middle of these.

IMHO, popularity of MySQL will peak soon if it hasn't already. But I'm grateful MySQL existed, because it filled an important gap and helped fuel the growth of the internet.

Re:Join removal is cool

[GooberToo]

Stating the truth doesn't make one bitter.

Why are you projecting? A PostgreSQL DBA fuck your wife or something?

Re:Join removal is cool

[amorsen]

I must admit that I don't understand why people find the Postgres role system confusing. Having the database users match the Unix users by default seems so elegant and easy, and not having to maintain passwords in the database at all is brilliant!

Re:Join removal is cool

[Anonymous Coward]

seriously, why in hell it even exists an application that requires a specific database?

it was defined as a standard for a reason, SQL.

Re:Join removal is cool

[coreboarder]

Can you tell me where those 12 pages are to be found?

Re:Join removal is cool

[jadavis]

I made a lot of mistakes that I would not have made with MySQL, mostly with privileges.

Can you elaborate? The complaint that I saw most often was that there was no good way to manage the permissions for a lot of objects. In 9.0, that was addressed with GRANT/REVOKE IN SCHEMA.

Also, keep in mind that the standard has a lot to say about the privilege system, so postgresql won't want to stray too far from that.

but if MySQL works for you...cheaper...and easier, why not use it?

Of course, go for it.

Just don't let inertia keep you from learning something that might really be an improvement. "Works" is not binary -- some things work better than others. SVN works. Git works better. Notepad and GEdit work. Emacs and vim work better.

Re:Join removal is cool

[jadavis]

PostgreSQL needs some form of MERGE/UPSERT/REPLACE. MySQL has REPLACE and INSERT ... ON DUPLICATE KEY ....

Also, postgresql should support index only scans (or similar), which MySQL supports.

I still prefer postgres by far, but those are two legitimate issues.

Re:Join removal is cool

[Ash-Fox]

seriously, why in hell it even exists an application that requires a specific database?

it was defined as a standard for a reason, SQL.

Actually it uses mysql out of the box and runs really well, but I am amused that nobody can seem to provide any alternative to this or even bothered asking if it worked well to begin with.

Re:Join removal is cool

[Migala77]

The way the SQL 'standard' is usually implemented makes even IE6 look extremely standards-compliant by comparison.

Re:Join removal is cool

[GooberToo]

I think a lot of it stems from the fact that its different from what they first learned, MySQL, no matter how initiative. It may be brilliant, it may be obvious, and it may be easy to use, but since its not what they "learned" but reading nothing at all, just one product experience, its hard. In other words, if I don't know it for absolutely no reason, "its hard."

As I said before in one of my other posts, MySQL is the lazy "DBA's" database.

Re:Join removal is cool

[interval1066]

"(MySQL)...is cheaper...(than PostgreSQL)"

Well, if your measuring time as money, which is certainly a valid measure, then perhaps, if you have a learning curve before you with Postgres, I could see it. Look, if your a hobbyist, if you want to throw that web site up as quickly as possible, you don't care about the technicalities of data bases, your have minimal security concerns, and just want to "get it done", MySQL certainly has its appeal. I've been able to create some pretty interesting queries using mysql language extensions. But all dbm systems have their own quirks to the language.
The main deal with postgres is its a more acid-compliant system than mysql, and psql's transaction processing is much more complete and robust than mysql's. If you're needing a high end, high availability, enterprise level database for the money, postgresql is the go-to dbms. But like I said, if your goal is to simply throw up any old web site on the web, mysql is perfectly adequate.

Re:Join removal is cool

[RAMMS EIN]

``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.''

At least, it used to be that way before MySQL grew a more complete set of features while at the same time growing some annoying bugs. Now it _looks_ like MySQL is a proper RDBMS, and people still think it's easy to use like in the 3.x days. In so far as that is true, it's great - but my experience is that, sooner or later, you are going to get bitten by one of the bugs. I haven't yet into one that I couldn't figure out a workaround for, but it does destroy the ease of use - and I doubt MySQL has much of an edge over PostgreSQL there to begin with. Don't get me wrong, MySQL is useful and it has certainly grown a lot since I took my first steps with it, but if I had known then what I know now, I would have gone with PostgreSQL to begin with.

Having said that, I wish both projects best of luck. They're doing a good job and making the world a better place, as do the various other useful open-source database systems.

Re:Join removal is cool

[GooberToo]

if your goal is to simply throw up... mysql is perfectly adequate.

There, fixed that for you. ;)

Re:Join removal is cool

[GooberToo]

In general a database is pretty much as good as its DBA.

This has always been true. Accordingly, these discussions assume an equally competent DBA, which frankly is far, far less likely with MySQL given its target audience. The simple fact is, MySQL became popular precisely because DBAs were NOT part of picking the solution. There is a HUGE difference between a web developer and a knowledgeable DBA.

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

Such as? The fact you make such statements clearly shows you know nothing of PostgreSQL. MySQL has been chasing PostgreSQL's features for a long time now. Some they've obtain over the years, some they're still chasing. Regardless, MySQL is still in pursuit and there are no signs that will change.

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

Except those "hacks" are far more reliable and robust than MySQL's out of the box replication solution. The only thing superior about MySQL's replication vs pre-9.0 PostgreSQL replication is that its plain simple to get running and comes out of the box. Robustness is generally considered crap and reliability is generally considered extremely poor with MySQL's solution. That's not true with PostgreSQL's third party replication add-ons.

Using your irrational, fanboyism logic, InnoDB is a complete crap "hack" - yet its the primary add-on which prevents MySQL from being complete crap, and almost totally useless. The fact this isn't obvious to you, well, says a lot about the validity of your position.

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

Actually PostgreSQL has supported WAL shipping for some time now. With a multitude of third party solutions you have many options of how PostgreSQL replicates; including synchronous or asynchronous.

Much of your position seems to hang on the fact that MySQL is superior because it has an out of the box replication solution. Even though that doesn't hold true, the simple fact is, the majority of users don't even use replication. Its a higher-end feature. The fact that its a higher end feature means most users who require replication are already NOT using MySQL. But what it does mean is, those very high end PostgreSQL users now have yet another replication solution in their toolbox. Likewise, for the lower end users who might otherwise consider MySQL yet require an easy to use replication solution, PostgreSQL is now an option; rather than requiring semi-complex, third party solutions.

Re:Join removal is cool

[GooberToo]

Postgres is a lot harder to use.

I hear this time and time again but frankly I can't identify. Did you have SQL RDBMS experience prior to trying MySQL? Can you please expand on that some. I'm not trolling, I'm genuinely curious.

Re:Join removal is cool

[Anonymous Coward]

The parent's comment about MySQL not being ACID compliant

No famboyism required. The reality is, there are many times MySQL does not survive faults nearly as well as just about any other well known RDBMS. So you can say "supports" ACID. Do some searching and you won't have problems finding people talking about corrupted databases following some type of failure which should have been entirely survivable.

Its one thing to check, "ACID", on MySQL's list - its another thing to actually support it. Which is the same thing as saying, MySQL doesn't have much to offer in the ACID category. Which really isn't that surprising considering it was never intended to do so.

Direct link

[toxygen01]

the mention slides to be found here:
http://www.slideshare.net/xzilla/intro-to-postgres-9-tutorial
It took me a while to realize it was not either of 2 mentioned presentations...

Built-in replication

[atomic777]

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.

Re:Built-in replication

[BSAtHome]

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:Built-in replication

[Bovius]

Yes! I cried out in jubilation when I heard they finally included proper "read-only slave" replication in the next major relese.

This has been the shameful shortcoming of Postgres for a long time.

Re:Built-in replication

[atomic777]

I see the replication feature as being more about perception than anything else.

Postgresql has long had a variety of replication options 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, but it has been deemed a serious enough problem for the postgres team to finally include replication in the core.

Re:Built-in replication

[ducomputergeek]

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 and restart it. That could take upwards of 15 - 20 minutes or more on any database of size.

We use PostgreSQL as our database with our Point of Sale application and I've been looking forward to the Warm Standby feature. Most of our locations run 2+ POS terminals. Warm standby would be nice since then we can install the database server on each machine set up for replication. One goes down, the other one takes over, we get notified at technical support, go in after hours remotely and fix the the database that crashed. That being said, we've yet to have PostgreSQL crash in production that was not the result of a hardware failure. (Blown power supply.).

Re:Built-in replication

[Ash Vince]

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:Built-in replication

[Slashdot Parent]

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:Built-in replication

[tweek]

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:Built-in replication

[Anonymous Coward]

Postgres has had replication for a long time. What it has not had is supported, easy, performant and integrated replication. Quite frankly, I would rather poke my eyes out with an ice pick than ever use Slony again.

Re:Built-in replication

[GooberToo]

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 completely different use cases here as far too often, MySQL users are more than happy to wave ACID.

I completely agree with you. While Slony isn't an "out of the box" replication solution, it is a very strong offering.

Re:Built-in replication

[Slashdot Parent]

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:Built-in replication

[atomic777]

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, until now, is an "out of the box" solution that will be used by default, by the uninitiated, to get postgresql in the door. Then if they ever learn what ACID stands for, if they understand what asynchronous or synchronous replication is, they will be happy as hell that they didn't choose mysql way back when their whole site/business ran on only 2 servers.

Re:Built-in replication

[GooberToo]

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:Built-in replication

[GooberToo]

Where's the "+1 super genius" mod tag when you need it? I completely agree with you!

Please mod up.

Re:Built-in replication

[Bill, Shooter of Bul]

Yes there were third party tools each with a different foucs, none of which was as adaptable as Mysql's built in replication support.

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 other responder noted, thats why one would promote a slave in that scenario.

Re:Built-in replication

[Qzukk]

Can you explain the new built in replication's conflict handling to me?

According to the docs, it looks like the major conflicts are basically exclusive lock issues. Since it's a one-way replication system, the standby server can't block the primary server from allowing the user to lock a table, so it has to break queries in order to comply. The table locks are used for ALTER and DROP TABLE commands. The other issue is that the VACUUM process removes rows from the database that are no longer visible to any transactions it knows about, and this too is written in to the WAL and communicated to the standby server, so VACUUMing the primary can delete rows that old transactions on the standby server are still looking at.

Re:Built-in replication

[Bill, Shooter of Bul]

The other issue is that the VACUUM process removes rows from the database that are no longer visible to any transactions it knows about, and this too is written in to the WAL and communicated to the standby server, so VACUUMing the primary can delete rows that old transactions on the standby server are still looking at.

Why doesn't that just work? With Mysql's INNODB storage engine, by default each transaction is isolated from each other (known as the READ_REPEATABLE isolation level). If you start a transaction in Connection A, and only do reads from the database it will continue to see all of the records it has examined. They won't change. So if on Connection B, a transaction has Begun after the the transaction in Connection A and Deleted a row that transaction B selected from, there is no conflict. Every thing is fine. Its know as READ_REPEATABLE isolation level. It can be configured to an isolation level similar to what you are describing, known as READ_COMMITTED, but most people don't do that for the very same reason you are mentioning. Can't you get PostGres to operate with a similar level of isolation?

Re:Built-in replication

[Anonymous Coward]

Just as I'm modding you up, I'd like to point out that I know perfectly well what ACID is, and which browser passes which version of it.

And don't wave those dusty Joe Celko papers in my face, thank you.

Re:Built-in replication

[TheSunborn]

Yes you can, but there seems to be a bit of difference between how Mysql and PostgreSQL define Repeatable read. According to postgresql (http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html) the repeatable read does allow phantom read(That is: The same select executed in the same transaction can give 2 different results). According to mysql http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read) this is not allowed in mysql.

If you want to avoid phantom-read in postgresql you need to specify the Serializable level, which seems to be same as repeatable read in mysql.

Oh and a funny fact: Postgresql don't have a specific repeatable read level, so if you request it, you will get Serializable but this is i guess subject to change.

Re:Built-in replication

[Bill, Shooter of Bul]

So, if I set everything up correctly, would I be able to get similar behaviour from PostGres for Replication? IE, no conflicts?

Re:Built-in replication

[Qzukk]

Why doesn't that just work?

It works fine when Connection A and B are on the same server and know about each other and can lock each other when needed. When you DELETE a row from a table, it's marked as deleted with your transaction ID so that transactions with a higher ID no longer see it while transactions with a lower ID still can. When the table is vacuumed, the vacuum process finds the lowest numbered transaction still running, and marks any rows with a DELETE transaction ID lower than that as free space to be filled in by the next transactions.

It doesn't work in this case because the Write-Ahead Log is essentially just a list of deltas to the filesystem representation of the database. When it says "remove this" it's gone, and has to be because the next WAL messages coming in are going to say "write this where the data you just deleted was". Worse, because the transactions are on a different server, there's no way to know if transaction #52112 on the standby server is before or after the data marked as deleted by the master server's transaction #15215902155. Even worse, "beforeness" can't ever be based on chronological order since it will take several seconds to several minutes for the database to fill the 16MB WAL chunk then transmit it over the network and process it on the other side, so if you try to have the slave read transaction IDs out of the WAL, it will always have old transaction IDs.

Thus, you get the "grace period" during which the slave stops processing WAL segments and lets you finish. Set it high enough that the slave can finish its transaction and you'll be fine as long as it's not so high (or used so often) that the slave can't keep up with incoming WAL segments.

If you need the transactions to always match on every server, then what you're looking for is a synchronous replication system.

Re:Built-in replication

[Bill, Shooter of Bul]

Ok, thanks for the explanation. That makes more sense. Traditionally, Mysql uses Statement based replication. So the whole statement was relayed to the slave for it to execute. This allows it to avoid this problem.

Row based replication is fairly new in MySql, and sounds closer to what you are describing. I'll need to research the details of that, but I'm 80% sure it doesn't have this problem with replication conflicts.

Personally, I love statement based replication. I'm also aware that Postgres has some really nice features and better Vertical scalability. I would love to benchmark them against each other, but this replication behaviour pretty much rules that out. Its not a viable solution for long running queries on a slave. We need to be able to run long running queries on slaves that are not far behind in replication. MyISam cannot do MVCC, and thus would block replication during the long running query. The servers would be days behind in replication. This was a pretty bad situation for us, so we switched to Innodb for just this reason.

Re:Built-in replication

[Qzukk]

For even just one-way statement replication to work reliably, extra work has to go on behind the scenes to make sure that the slave processes all of the commands in the exact same order that the master processed it. (Even within transactions. For instance, Postgres sequences exist outside of the transaction in order to allow two transactions that start at the same time to get two different numbers from the same sequence). Additional work is needed if you want to have the slave server skip those SELECT commands that don't have side effects. This kind of issue is one of the reasons a lot of multi-master replication systems require you to declare one server to create even rows and one to be odd: if it can't guarantee that both servers work in lockstep, at least it can guarantee that they'll never assign the same ID to different rows ;)

I haven't looked at this row-based replication but it sounds like it might fit the spot between postgresql's filesystem replication and statement-based replication. By transferring individual records that were updated after the statement has taken place you can skip worrying about whether running the query on another server will have all the right side effects and get the right IDs. By not requiring the filesystem to be identical across servers, the slave servers can vacuum in their own sweet time without having to stop everything while a query runs.

Re:Built-in replication

[Bill, Shooter of Bul]

Yeah, I think I understand what you are saying. Its not as trivial as it might first seem. Mysql doesn't have sequences, (which allows it to ignore that problem), but that means it uses auto-incrementing columns which are less flexible and require a pseudo table lock on inserts into Innodb tables which limits its concurrency (impact lessened with 5.1 and row based replication). Believe me, I wish I had access to Postgres style sequences.

other then features...

[jellomizer]

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:other then features...

[GooberToo]

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...

[schmiddy]

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:other then features...

[skeptical_monster]

What I would love to see is some standardization for SQL languages.

That sounds great! I would love to see every child with their own flying pony. Use an abstraction layer.

Re:other then features...

[Kjella]

They have tried. 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...

[schmiddy]

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:

And work on MERGE support is itself blocked behind the fact that PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking. See the notes for "Add SQL-standard MERGE/REPLACE/UPSERT command" at http://wiki.postgresql.org/wiki/Todo for more information.

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)?

Re:other then features...

[DragonWriter]

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.

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:other then features...

[butlerm]

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:other then features...

[Kjella]

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...

[XanC]

I've never heard of one that doesn't make that guarantee. MySQL's certainly does.

Re:other then features...

[MobyDisk]

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:other then features...

[MobyDisk]

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...

[rachit]

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:other then features...

[Sxooter]

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

Re:other then features...

[butlerm]

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...

[butlerm]

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:other then features...

[butlerm]

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"

Re:other then features...

[XanC]

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:other then features...

[butlerm]

MySQL has sequences these days, does it not?

Re:other then features...

[XanC]

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html contains some instructions for "simulating sequences". So I guess that's a no.

Re:other then features...

[Anonymous Coward]

Microsoft Access

Get out.

Re:other then features...

[boxwood]

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:other then features...

[Anonymous Coward]

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).

Well actually the EnterpriseDb PostgreSQL plus does support Oracle's PL/SQL, though I'm not an Oracle user, so have no need for it. They call it Redwood I think, for obvious reasons.

http://www.enterprisedb.com/solutions/tech_case.do

Re:other then features...

[butlerm]

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 table, you can always make your own counter table, and allocate unique values safely using "SELECT ... FOR UPDATE; UPDATE". The problem with that is the counter row ends up being locked until the transaction commits, which is slow if you have lots of concurrent transactions that need unique ids. Hence the invention of database sequences, or the more limited alternative - the "serial" data type.

Re:other then features...

[Nedmud]

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.

I'm a little unclear about where exactly this works in PostgreSQL. It's true that, for stored procedures (functions), there is a plugin system for the stored procedure language. But there is only one SQL dialect that can be used outside of stored procedures. The new DO command arguably expands this, but it still looks a lot like a stored procedure body.

Second, the primary language is PL/PGSQL which is a clone of Oracle's PL/SQL.

Tiny quibble: I don't know if you could call it the "primary language" since it's just as primary as all the others. I recall a time when I had to enable it in each DB that needed it -- perhaps if it's included by default it is now slightly more primary than some others...

Re:other then features...

[Alpha830RulZ]

As a whole, Postgres started as an open-source Oracle clone.

Not accurate, please see this, 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.

Re:other then features...

[Anonymous Coward]

However, you are correct: there needs to be a standard.

For Larry the standard is 'everyone uses Oracle'.

Re:other then features...

[Anonymous Coward]

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 the SQL standard does define a language for writing stored procedures, it's just not widely adopted by DBMS vendors yet. It's called SQL/PSM, and it's pretty close to Oracle PL/SQL or PostgreSQL PL/pgSQL. There's a PostgreSQL implementation at http://pgfoundry.org/projects/plpsm/.

Re:other then features...

[mvdwege]

manually incrementing your sequence using MAX(pkey_column)

That is just so wrong, it hurts my brain to even read it. The purpose of auto-incrementing columns is to provide a key, which is why PostgreSQL implements them using a sequence as a default value. Using the above technique is a recipe for duplicate values.

Mart

Re:other then features...

[mvdwege]

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:other then features...

[schmiddy]

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?

Yup. Look up SELECT ... FOR UPDATE for your database for the answer to your question, though again the real solution is just to use the appropriate sequence type provided by your RDBMS and let it do the work for you.

Re:other then features...

[MobyDisk]

Wow! Good to know.

Re:other then features...

[borgboy]

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.

Re:other then features...

[Anonymous Coward]

Absurd, non-scalable, and in the face of multiple isolated write transactions, it's obviously, head-slappingly, face-palmingly WRONG. This seriously calls into question the guy's expertise.

There's a reason the SQL standard even has autogenerated sequences built in now.

Re:other then features...

[Anonymous Coward]

"This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction."

Color me skeptical.

[Estanislao Mart�nez]

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.

Re:Color me skeptical.

[Anonymous Coward]

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:Color me skeptical.

[MariusBoo]

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.

I don't think this is correct. The SELECT list does impact the performance of the query because if it requires un-indexed columns that are not used any other clause(WHERE, FROM) than a lookup or a table scan will be needed to retrieve the values. This being said the only way that removing a column form the select list of a query will make it slower is if the query planner has some bugs :)

Re:Color me skeptical.

[GooberToo]

Do you have an actual example?

Here's some cut-n-paste action and some additional reading.

Join removal -- this is a feature that will remove joins from the execution plans where they are not needed. For example where you have a left join that doesn't appear in a where or as a column in select. This is important for people like us that rely on views to allow less skilled users to be able to write meaningful queries without knowing too much about joins or creating ad-hoc query tools that allow users to pick from multiple tables

And it continues with:

When people talk to me about the (limited implementation of) join removal that will be part of PostgreSQL 9.0, the conversation usually goes in two ways. Some people ask how the feature works and then say something like "oh, I guess that could be useful every once in a while". Other people already know exactly how the feature works and usually say some variant of "this is an amazingly wonderful feature that I am looking forward to with great enthusiasm".

The difference between these two groups of people (I think) is not so much their level of technical knowledge or how closely they've been following pgsql-hackers, but their use case. If your database is primarily a data warehouse, my guess is that you won't have many occasions to benefit from join removal. Where this feature really comes in handy is in OLTP workloads with highly normalized data, in situations where users are generating queries against views (perhaps through some sort of reporting interface) and expecting to get results back immediately.

Let's take an example...

You can read more about Why Join Removal is Cool as well as lots more general information on the new PostgreSQL features at Planet PostgreSQL.

Re:Color me skeptical.

[Estanislao Mart�nez]

You don't seem to understand what's being talked about. GP said that sometimes removing columns from SELECT clauses in Oracle causes drastic slowdowns. You respond with a bunch of cut-and-paste about join removal in PostgreSQL 9.0, which apart from just not being relevant, is a feature that potentially causes speedups when you remove columns from SELECTs (the opposite effect of what's being discussed).

Re:Color me skeptical.

[GooberToo]

You don't seem to understand what's being talked about.

Pot - kettle - you.

That's the response for attempting to provide additional detail for the subject matter? I never asserted anything other than additional information can be found - including an example - and provided a link. The link does provide an example of how the feature benefits people as well as explains how the feature works. Frankly, I'm really not sure how he believes his stated use case is benefited by this feature. But then again, I never asserted anything, one way or the other. I assumed you could glean that for yourself.

Regardless, the confusion is absolutely not on my end. Clearly, lack of comprehension rest squarely between your keyboard and chair. A "Thanks." would be more appropriate next time. Or perhaps even, "Thanks, that indicates the op doesn't know what he's talking about." I'm completely at a loss how providing additional, topical information, allowing you to agree or disagree on your own, establishes I'm confused. WTF?!?

Next time, just say you don't understand anything and anyone attempting to provide additional information to allow you to decide for your self is a complete waste of time. Or that you must be lead to a conclusion by the nose. As in this case, that's exactly what you are - a complete waste of time.

Related Tangent

[geoffrobinson]

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.

Re:Related Tangent

[bill_mcgonigle]

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:Related Tangent

[DragonWriter]

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

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:Related Tangent

[geoffrobinson]

Well, yes, different use cases. But that's the one thing I've been noticing about Michael Stonebreaker. He seems to have different project for different needs.

Re:Related Tangent

[geoffrobinson]

I forgot the word "on."

Re:Related Tangent

[rrhal]

Maybe a Volt DB with a PostgreSQL data warehouse. Stonebreaker has been behind quite a number of RDBMS's - Ingress, Postgress (spun off Ingress), sybase, MS SQL Server (evolved from Sybase), Informix, and now volt. I don't know that the list is even complete.

Re:Related Tangent

[geoffrobinson]

He's behind Vertica as well, which is growing fast. Very good columnar database, optimized for data warehousing.

Re:Related Tangent

[Ash-Fox]

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.

Re:Related Tangent

[Anonymous Coward]

Because the POSIX subsystem that windows used to ship (which it hasn't done since XP) is not particularly fast, nor does it implement *anything* beyond POSIX.1 like, oh, threads or sockets. And since it's a subsystem, you don't get to just call into Win32 to get those functions back -- one subsystem per process.

Needless to say there's a reason Microsoft steers you toward SFU (nee Interix) nowadays. And even that isn't terribly featureful, tho at least it's enough to do NFS.

I like....

[pdxp]

the join removal. But what about the partitioning features that have been on the table for quite some time now? The scaffolding is already there, but implementing partitioning is still quite a chore. I just want to be able to gracefully create/manage partitions as in Oracle. There are also a few papers floating around out there with proof-of-concept parallelization/optimization schemes that can be applied with partitioning knowledge.

Re:I like....

[0racle]

It got pushed off until later when the decision to integrate streaming replication in this release was made.

I'd say something, but someone will freak out

[Rallias Ubernerd]

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:I'd say something, but someone will freak out

[h4rr4r]

What does an HTTP server have to do with a DB?

PostgreSQL has a lot of more enterprisy features than MySQL.

Re:I'd say something, but someone will freak out

[coolgeek]

It's a real database with ACID compliance designed in from the start, not as an afterthought.

Re:I'd say something, but someone will freak out

[Yvan256]

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:I'd say something, but someone will freak out

[The MAZZTer]

Database? XML? CSV has always worked fine for me!

Re:I'd say something, but someone will freak out

[hardburn]

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

[Anonymous Coward]

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.

Why do you assume that MySQL is the default and that anything else must have an "advantage"? (Postgres has many advantages, of course, but that's beside the point.)

Re:I'd say something, but someone will freak out

[theskipper]

on Windows.

Re:I'd say something, but someone will freak out

[Slashdot Parent]

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:I'd say something, but someone will freak out

[Slashdot Parent]

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?

Oh, wait...

Re:I'd say something, but someone will freak out

[jdoverholt]

Straight answer from a friendly DBA: each has its advantages and appropriate applications. Neither has anything to do with HTTP, totally different game. They're both in common use, they both support a good deal of the SQL standard language features (though Postgres is better there), and each has its own extensions to the standard. Just like any other topic here, each has its respective group of fans who will tout its "betterness" and maybe flame you for thinking anything otherwise (see anon response #32513876 above). This extends outside the open source world to Microsoft SQL Server and Oracle, too.

Why have two? Well, competition drives innovation, so think of it as a good thing.

Re:I'd say something, but someone will freak out

[shutdown -p now]

I don't use any database, I use plain XML text files.

I use EBCDIC punch cards, you insensitive clod!

Re:I'd say something, but someone will freak out

[Anonymous Coward]

That's MySQL.

Re:I'd say something, but someone will freak out

[Anonymous Coward]

And I've been using Google Maps' satellite view to steal all your data. pwned!

Re:I'd say something, but someone will freak out

[Sxooter]

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:I'd say something, but someone will freak out

[Sxooter]

Yeah, nobody uses it for anything useful or anything.

Except skype.
And Afilias (the guys what run the .org and .info tlds)
And Cisco.
And the USGS.

Re:I'd say something, but someone will freak out

[Slashdot Parent]

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:I'd say something, but someone will freak out

[DragonWriter]

I don't understand. What is the advantage of PostgreSQL verses MySQL

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 over PostgreSQL; installation and administration, I think, are still simpler for MySQL (though the last several version of PostgreSQL have closed that gap a lot), and there are probably more introductory resources and prepackaged solutions that target MySQL for lots of common use cases.

or a seperate HTTP server?

Uh, what? The advantage of PostgreSQL vs. an HTTP server? That's like asking what the advantage of a Ferrari is vs. a banana plantation. They aren't alternatives to each other.

Re:I'd say something, but someone will freak out

[Johnno74]

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?

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 joining more than a couple of tables together, or concurrent updates) but its an enterprise database in the same way MS Access is.

Re:I'd say something, but someone will freak out

[Slashdot Parent]

I think your MySQL spec sheet is about 10 years out of date.

Re:I'd say something, but someone will freak out

[Anonymous Coward]

In real time no doubt.

Re:I'd say something, but someone will freak out

[tigersha]

If had modpoints....

Still waiting

[coolgeek]

For the multi-master replication that was promised for 8.4.

Re:Still waiting

[schmiddy]

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:Still waiting

[Anonymous Coward]

I don't recall multi-master ever being promised as part of the core database. The hot standby we're talking about now was promised and slipped.

Postgresql core will probably not get a multi-master replication system until someone figures out how to do it for real (ie not MySQL's asynchronous multi-master). This isn't an easy question to answer. If DB2 is down, should DB1 stop accepting data? Should it switch back to asynchronous? Should your answers to these questions be forced on everyone else?

Re:Still waiting

[cxreg]

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

Re:Still waiting

[Slashdot Parent]

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:Still waiting

[schmiddy]

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:Still waiting

[ducomputergeek]

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 PostgreSQL has proven to be far more reliable that MySQL when it comes to not corrupting data. It's fast, it's stable, and HotStandby is welcome.

In most of our installs, the client is running on multiple terminals. Currently one is the master terminal hosting the database, the other terminals are just clients. It works well, but with HotStaby we can now run a database instance on each terminal with failover redundancy. That will make it into the next release of our POS slated for early next year and I'll be glad to include it in the default feature list.

Re:Still waiting

[Slashdot Parent]

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. It meets the requirements of most RDBMS use cases. Most applications do not need Oracle RAC. But if you do, then Postgres is not an option for you.

Re:Still waiting

[TheSunborn]

Does Oracle RAC really always ensure that if a query B is issued after* an other transaction A is comitted, then B will always see the result of A? Even if A and B are issued from different clients, and issued to different database servers?

*When looking at the wall clock. But it may in fact not be possible to say if B was really send before or after B comitet if the sender and reciveer of A and B are 4 different computers.

Re:Still waiting

[Slashdot Parent]

Does Oracle RAC really always ensure that if a query B is issued after* an other transaction A is comitted, then B will always see the result of A? Even if A and B are issued from different clients, and issued to different database servers?

Yes, it does.

Oracle RAC uses a "shared-everything" design where multiple Oracle instances access a single database. It is not possible for one RAC node to "fall behind" another node.

Re:Still waiting

[TheSunborn]

But then we are talking about different solutions. The feature added to Postgresql is a shared nothing replication. A shared everything(It is just storage which is shared right?) will be able to handle synchronization much better.

But don't the RAC solution leave you with the storage system as a single point of failure then?

Re:Still waiting

[schmiddy]

Imagine that you still have a single point of failure if your big expensive database server goes down.

Not true, if you set things up properly. Look up STONITH -- it's quite easy to quickly turn a slave into the new master if your master dies (so long as you are sure the master is dead, and you can decide which slave to promote!).

Re:Still waiting

[Slashdot Parent]

But then we are talking about different solutions.

Yes. I think I've made this point about 12 times already. Postgres is not able to compete with Oracle here because they are different solutions.

The feature added to Postgresql is a shared nothing replication. A shared everything(It is just storage which is shared right?) will be able to handle synchronization much better.

Yes, it is only storage that is shared. FWIW, I didn't invent the term "shared-everything".

But don't the RAC solution leave you with the storage system as a single point of failure then?

Yes, which means any HA RAC architecture relies upon HA storage. If your SAN goes down or offline, your RAC is going to crash with 100% probability.

In practice, I have to give mixed reviews to Oracle RAC. My background is that I own an enterprise architecture consulting firm. I know enough about a variety of technologies to produce an architecture that satisfies requirements, but if you have specific questions about RAC implementation, I'm not going to be able to supply you with any details. I've never implemented one myself.

So back to my experience in the real world. I've found that RAC's "single point of failure" is often inexperienced DBAs. RAC is extremely flexible, and requires an expert to set up and tune it, properly. I don't see many storage failures, as most of my clients just write a big, fat check to EMC and say, "Make sure it don't die."

So real world, I've seen some RACs that run for years without a downtime. Oracle patches are applied to the running cluster via rolling restart.

On the other hand, I've seen some RACs experience more downtime than I would expect with just a single Oracle instance on a single node. The reason? Inexperienced DBAs. Although I had one client who must have had the worst EMC SAN ever, because that thing crashed about once every 6 months, taking down their beautiful RAC with it.

So, yes. There is a single point of failure on the storage. Typically, HA storage is not difficult to achieve, so this is not normally an issue. It certainly can be, however.

Do you know the verb "to google"?

[mangu]

Google is your friend.

Re:Do you know the verb "to google"?

[Rallias Ubernerd]

I don't like having friends.

Re:when they have a tool like phpmyadmin

[h4rr4r]

Phppgadmin is pretty much that, there is also pgadmin which is a desktop app.

I suggest you educate yourself before making such statements.

Re:kfuck!!

[Anonymous Coward]

Sup dawg! I herd you liek SQL databases so we put a MySQL in your Postgres so you can Insert while you Insertin'!

You're gonna have to explain that better...

[Estanislao Mart�nez]

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.

You know, you've lost me there, and I don't think it's me. You're going to have to explain that better if I'm going to believe it.

The best I can make it, you're assuming that there is a table that's structured/ordered/partitioned/whatever based on the values of the column that gets removed from the query. I assume that's what you mean by "blocked" or "pinned" columns.

However, I still fail to understand how including such a column in the SELECT list of a query provides any optimization opportunities. The only ways I can see it affecting query execution (e.g., table access paths and join methods) still involve use of the column in the join conditions in the FROM, restrictions in the WHERE, or use of the column in GROUP BY.

Re:You're gonna have to explain that better...

[pwagland]

In theory you are correct. If the optimiser was perfect, then it would not make any difference by removing a column. However, since optimisers cannot be perfect, by removing a column you may be inadvertently removing the equivalent of a database index hint, and it might generate a different, and worse, query plan. Just because it doesn't make sense does not mean that the database won't do it ;-)

Re:when they have a tool like phpmyadmin

[ffreeloader]

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.

PostgreSQL rulez!

[TheSync]

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!

Re:PostgreSQL rulez!

[Qzukk]

I wonder if that couldn't have been written as

SELECT sm1.* FROM sm1 LEFT OUTER JOIN sm2 ON sm1.descriptor=sm2.descriptor AND sm1.command=sm2.command AND NOT abs(sm1.times - sm2.times) [whatever operation that was] 1 WHERE sm2.command IS NULL;

This would match up every sm1 with a sm2 row based on command and descriptor, unless that sm2 row's times value matched the operation. By using a left outer join and checking for null right sides, we get all the sm1 records with no matching sm2 records (or where sm1.times-sm2.times did whatever it was).

Re:PostgreSQL rulez!

[TheSync]

Its was abs(sm1.times - sm2.times) greater than 1.

Thanks! I have an aversion to JOINs, but I'll give it a shot...

That's not all folks

[DiegoBravo]

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.

No more slony?

[XCondE]

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:No more slony?

[stub667]

Slony-I and Londiste won't be dying any time soon. The built in replication will work for simple installations. 3rd party replication gives you a lot more rope to play with for complex replication environments or masochists.

I think you're getting mixed up.

[Estanislao Mart�nez]

I _think_ what they are saying, is if the column you remove from the select clause, is the only column from that table being used/displayed other than the column used in the join, then the optimizer will remove the entire join from the query, thereby speeding up the query. But I could be very wrong because I've been wondering the same thing as you since it was first mentioned.

I think you're mixing up two things:

1. The explanation of how join removal works in PostgreSQL 9.0, which you are stating the very basics of.
2. The thread's original poster's claim that, in Oracle, removing a column from a SELECT clause sometimes results in a modified query that performs much worse than the original.

You're basically right in your understanding of (1), though the details are considerably more complex.

I expressed my skepticism about (2) in another comment in this thread. Then the comment you're replying to was my sympathetic yet skeptical reply to another poster who assumed that the column removal from the SELECT clause might cause a cartesian join (a join of two tables without any conditions restricting the joins, which tends to perform very badly and is usually an indicator that your query is written wrong).

Is time for postgres..

[phylevn]

I think is time that postgres will be the most database used in open source & comercial software, MySQL current now is most used but dont have all functionality that Postgres.

Deadlocks on Multiple Inserts

[RAMMS EIN]

Perhaps slightly off-topic, but I would like to throw this question out here in case someone has an answer:

The other day I was working on an application that uses a MySQL database. I found that when hammering the application with hundreds of actions that caused data to be inserted in the database, I would often get a message to the effect that a deadlock had been detected, with the advice to "try restarting the transaction". In the cases I investigated, the deadlocks were caused by two transactions trying to insert into the same table. I don't really understand why this causes a deadlock. Is this something I could expect to happen with any RDBMS (e.g. because I'm doing something wrong or because this is simply unavoidable), or is it related to implementation choices? Under what circumstances does and doesn't PostgreSQL deadlock?

Re:Deadlocks on Multiple Inserts

[nebosuke]

That is a problem with MyISAM tables, which only do table-level locking. PostgreSQL will do row-level locking with concurrent write transactions, so you would only 'deadlock' if both transactions modified the same row.

If you'll be writing to the same tables with concurrent write transactions and you have to use MySQL, you should consider using InnoDB tables instead, which do allow row-level locking, but also come with their own quirks, so a bit of research is advised before committing to that solution.

For concurrent read and write transactions, PostgreSQL has Multi-Version Concurrency Control (MVCC), as do InnoDB tables, so there should theoretically be no locking of read-only transactions due to write transactions in progress. That being said, InnoDB MVCC will only work if all your tables are controlled by the InnoDB table handler, which, due to the quirks alluded to above (including severe performance penalties in some situations) may make that requirement a deal-breaker.

As always, you have to have a good understanding of both your application's requirements and the tools available in order to make the right decision.

Re:Deadlocks on Multiple Inserts

[RAMMS EIN]

Thanks for your answer! I forgot to mention this in my earlier message, but I'm actually using InnoDB tables (I need ACID to be able to sleep at night). As far as I understand, the problem has something to do with locking, indices, and auto_increment. How that causes a deadlock isn't clear to me; I would expect both transactions to acquire the locks pertaining to the same table in the same order, which would avoid the problem. Apparently, that isn't the way it works, at least in that version of MySQL.

I am aware of PostgreSQL's MVCC, which is why I was wondering if PostgreSQL may perhaps avoid the problem that MySQL runs into. The reason that I can't answer that question for myself is that I don't know exactly how or why MySQL ties itself into a knot.

Re:Deadlocks on Multiple Inserts

[nebosuke]

The involvement of auto_increment may be your culprit. As another poster mentioned on a thread above, InnoDB auto_increment is implemented as roughly 'select max(t.col) + 1 from table t' rather than using an actual SQL sequence. This could mean one of 2 things (not familiar enough with InnoDB's inner workings to know which it is): either A) the concurrent write transactions are getting the same value for the auto_increment field, causing problems if the field is the pkey or an otherwise unique index, or B) transaction 1 locks its rows, transaction 2 locks its rows, transaction 1 tries to grab the next val for an auto_index column and deadlocks because the auto_increment select needs to grab rows locked by transaction 2.

As I said, there are quirks :P.