LWN: Comments on "PostgreSQL 8.4 released"

PostgreSQL 8.4 released

hingo — Fri, 03 Jul 2009 20:27:37 +0000

That is correct when talking about MySQL Replication. Google has developed "semi-synchronous" replication which should be in MySQL 5.4. Semi-synchronous is to say that changes are not applied synchronously, but data is copied to both masters so it can be considered safe/redundant.

MySQL Cluster on the other hand does provide synchronous replication, not to mention transparent sharding (scale-out) too.

Also for MySQL there are 3rd party solutions to do synchronous replication. I don't have enough experience to comment on those, I've seen people like them and dislike them all.

PostgreSQL 8.4 released

yoe — Thu, 02 Jul 2009 18:00:31 +0000

That's not how you search for horror stories on google, and you know it.

"Results 1-10 of about 122,000 for mysql replication broken (0.60 seconds)"

Then again, what with mysql being a horrible toy, clustering being broken is hardly a suprise.

PostgreSQL 8.4 released

fdr — Thu, 02 Jul 2009 05:44:16 +0000

You would be correct; MySQL's replication is asynchronous by in large. Much like Postgres there are some less-well traveled ways to acquire syncrep.

PostgreSQL 8.4 released

dlang — Thu, 02 Jul 2009 04:09:59 +0000

I have waited many long nights while sysadmins that I know have had to restore mySQL databases from backup or re-clone replicas from the original on mySQL clusters. I was seeing repeated cases where the replication stops, but claims that it is still going, cases where it would corrupt a copy to the point where it was easier and faster to recreate it, as well as issues with the daisy-chain approach to replication where the replicas downstream of the box that first had a problem suffered as well (sometimes recoverably once the problem box was fixed, other times not so)

this was without any system crashes

no, I don't know of Internet links that document this.

my prior post was intended to make the point that doing a google search for "mysql replication horror story" and not finding a real one in the first ten hits has very little, if anything to do with the quality or lack of quality of mysql (or postgres) replication.

I never like to hear of anyone loosing their data, but to then make the claim that if the replication tool was built-in instead of a seperate project it would not have happened, and that mysql 'just works' as an example of this always being true is just not a valid chain of logic.

PostgreSQL 8.4 released

dlang — Thu, 02 Jul 2009 04:00:22 +0000

my understanding of mySQL is that their replication is not synchronous

for postgres, I think the option you would want is plproxy, it makes changes to all boxes in the cluster at the same time and splits queries between machines.

I haven't used it so check that it doesn't have any limitations that will kill you

PostgreSQL 8.4 released

Cyberax — Thu, 02 Jul 2009 01:29:26 +0000

Slony is asynchronous, and I need synchronous replication _and_ clustering.

I.e. two queries should return the same data if they are executed at the same time (of course, not considering other transactions), even if they are executed on different hardware nodes.

PostgreSQL 8.4 released

akumria — Thu, 02 Jul 2009 00:59:52 +0000

No, I am not the one who said there were 'many horror stories' about MySQL replication.

Please do not weasel out of pointing out information that could be beneficial to those of us attempting to make a balanced consideration between the two.

If you have any stories you can point to -- let us know; I would appreciate the information. It would help to inform my opinion about MySQL and the merits (or otherwise) of it's replication.

Otherwise your comment just serves to inform my opinion about hyperbole on the Internet and its continuing rise.

(pun intended).

Thanks,
Anand

PostgreSQL 8.4 released

dlang — Thu, 02 Jul 2009 00:33:53 +0000

by your own criteria, postgres doesn't have problems with replication iether (just substatute postgres for mysql in your google search)

PostgreSQL 8.4 released

akumria — Wed, 01 Jul 2009 23:53:12 +0000

> there are many horror stories around about MySQL replication eating
> people's data. being 'built-in' doesn't eliminate that possibility.

google: "mysql replication horror story"

Presumably they would be so widely known that showing up in the first 10 hits on google is a reasonable test.

Hmm - which of those non-stories was the 'many' that you had in mind?

Anand

PostgreSQL 8.4 released

alvherre — Wed, 01 Jul 2009 23:37:31 +0000

Yes, it comes from a closed list which is only used to send out press releases. You can see the pgsql-announce archives:
http://archives.postgresql.org/message-id/4A4B77A7.609070...

PostgreSQL 8.4 released

dlang — Wed, 01 Jul 2009 23:20:29 +0000

I've had friends use the MySQL replication extensivly (admittedly a couple of years ago), and it works for some definitions of 'works'

there are many horror stories around about MySQL replication eating people's data. being 'built-in' doesn't eliminate that possibility.

there are many different ways to do replication and failover for databases, each has advantages and disadvantages. Postgres (through these external projects) has most of the range covered, the hit-standby,synchronous-write mode is missing, and that is what is going to be added in 8.5

it may be that once this gets in, some of the other solutions that need patches will ask to be added as well, but so far it's been a case of trying to to imply that one solution is better than all the others by including it and not the others

most of the different replication solutions that are available for postgres are significantly better than all of the others, for a specific set of requirements.

PostgreSQL 8.4 released

fdr — Wed, 01 Jul 2009 23:13:30 +0000

PGCluster is sort of in a weird place...certainly not among the most popular solutions. Consider slony (complex, well vetted) or londiste (part of Skype's skytools, mostly for aync multi-master replication)

Also, 'clustering' (which I take to also mean parallel query execution) is orthogonal to replication. Consider middleware, pl/proxy (also a Skype tool), or just old-fashioned hand-rolled application logic.

PostgreSQL 8.4 released

Cyberax — Wed, 01 Jul 2009 22:43:43 +0000

Notice the word "built-in" in my initial post. I know perfectly well about existing clustering solutions.

I don't trust them. I tried PgCluster and it ate my data. So I don't want to try any invasive third-party patches anymore.

It works out-of-box in MySQL, anyway.

ALTER SEQUENCE (expression)

alvherre — Wed, 01 Jul 2009 22:03:38 +0000

Don't hold your breath for using an expression in ALTER SEQUENCE, but you can already use one with the setval() function.

The commenter above is right that WITH RECURSIVE is part of the SQL standard. Yes, RECURSIVE too.

PostgreSQL 8.4 released

dlang — Wed, 01 Jul 2009 21:56:46 +0000

there are several clustering solutions to choose from, and have been for years.

is your complaint that you know that they exist?
that you think that they would be better if packaged in the same tarball?
that you think that there are too many to choose from?
that you think that they are too inefficiant?

the reason this standby mode is going into postgres itself is that it ties much more intimately to the core code to do it's real-time replication

quite nice

flewellyn — Wed, 01 Jul 2009 21:37:28 +0000

Wow, those window functions and WITH queries would be amazingly powerful with PostGIS. I'm going to have to schedule an upgrade within the next few months!

PostgreSQL 8.4 released

Cyberax — Wed, 01 Jul 2009 20:56:21 +0000

Hot _standby_ is not nearly enough. We need a real clustering solution, on par at least with the MySQL.

So far there is a commercial Enterprise DB based on PostgreSQL, but it costs way too much. It's easier to buy Oracle, their RAC stuff is superb.

PostgreSQL 8.4 released

malefic — Wed, 01 Jul 2009 18:32:36 +0000

While initially planned, hot standby patch was postponed till 8.5. The team plans to merge the patch first thing when 8.5 development reopens.

quite nice

tbrownaw — Wed, 01 Jul 2009 18:28:03 +0000

recursive approach was complete surprise (to me) without "connect by" syntax. no coffee yet, so don't understand the overall coolness of WITH.

"connect by" is Oracle-specific, I believe the "recursive WITH" is the ANSI SQL standard way to do recursive queries (but I haven't seen the RECURSIVE keyword mentioned anywhere before, so no idea if this is exactly how the standard says). WITH in general is just a useful way to factor out complicated subqueries, sometimes it's easier to read top-to-bottom instead of inside-to-outside and sometimes you need the same subquery several times.

PostgreSQL 8.4 released

khc — Wed, 01 Jul 2009 17:37:01 +0000

The gmane links report "No such article"

PostgreSQL 8.4 released

Cyberax — Wed, 01 Jul 2009 17:07:06 +0000

Still no built-in synchronous replication and clustering support. Sigh.

quite nice

ccyoung — Wed, 01 Jul 2009 16:51:57 +0000

needed doc:
window: http://www.postgresql.org/docs/8.4/static/tutorial-window...
recursive: http://www.postgresql.org/docs/8.4/static/queries-with.html

recursive approach was complete surprise (to me) without "connect by" syntax. no coffee yet, so don't understand the overall coolness of WITH.

suppress_redundent_updates() sounds like a godsend, but can find no doc on how to actually use it

array_agg() and unnest() are cute little guys that I think will see a lot of use (remember that arrays can be used for "in" in selects)

citext data type for case insensitive text - wonder if it automatically indexes on one case to preserve speed.

LIMIT (expression or subquery) - now if ALTER SEQUENCE could do the same

going up today. congrats to the pg team!!!