PostgreSQL: the good, the bad, and the ugly
Please consider subscribing to LWN Subscriptions are the lifeblood of LWN.net. If you appreciate this content and would like to see more of it, your subscription will help to ensure that LWN continues to thrive. Please visit this page to join up and keep LWN on the net. |
The PostgreSQL development community is working toward the 9.5 release, currently planned for the third quarter of this year. Development activity is at peak levels as the planned feature freeze for this release approaches. While this activity is resulting in the merging of some interesting functionality, including the long-awaited "upsert" feature (described below), it is also revealing some fault lines within the community. The fact that PostgreSQL lacks the review resources needed to keep up with its natural rate of change has been understood for years; many other projects suffer from the same problem. But the pressures on PostgreSQL seem to be becoming more acute, leading to concerns about fairness in the community and the durability of the project's cherished reputation for high-quality software.
The good
The news from PostgreSQL is certainly not all bad, though; the project continues to grow and to add interesting new features. One feature that was recently merged for the 9.5 release fills a longstanding gap: the lack of an "upsert" command.
The SQL standard provides two basic commands for placing data into a table. The INSERT command adds new rows, while UPDATE will modify the data stored in existing rows. What is missing is a way to modify a row if it exists, but to add a new row otherwise; that leads to the need for program logic to check for the existence of the row in question before choosing the correct command to make the change. Many relational database systems contain an "upsert" mechanism that combines INSERT and UPDATE in this way, but PostgreSQL has always lacked this feature.
Adding this functionality is harder than one might expect. One can start with the question of what it means for a row to be already present in a table. A simple answer is that the table has a primary key and that a row containing that primary key already exists, but one could imagine more subtle approaches. Once the problem of defining "presence" has been solved, there is the little problem of dealing with race conditions; the operation as a whole needs to be atomic or bad things will surely result. The challenges involved in getting this feature right have kept it out of PostgreSQL so far.
With 9.5, though, that situation will change — though there is still no UPSERT command. Instead, the new syntax, as implemented by Peter Geoghegan, looks like this:
INSERT INTO table_name <insert stuff> ON CONFLICT conflict_target conflict_action;
The conflict_target is, in its simplest form, the name of a column for which a uniqueness requirement would be violated if the row were to be inserted directly. The conflict_action, instead, can take either of two forms: either DO NOTHING (which causes the insert operation for the conflicting row to be silently aborted) or DO UPDATE, which provides an UPDATE command to execute instead of the INSERT. There are, of course, numerous details that have been skipped over here; see the above-linked document for the full story.
This feature went through a number of cycles of review and change; it was finally committed to the PostgreSQL mainline on May 8. Once the 9.5 release is available, a famous gap in PostgreSQL functionality will be no more.
The bad
On May 9, PostgreSQL developer Robert Haas asked for suggestions toward the solution of some nasty data-corruption issues associated with the "multixact" feature, which is an internal mechanism supporting the use of multiple locks on any given row in order to increase concurrency. There are, it seems, a number details involved with the multixact feature that have not yet been thoroughly worked out. That might be acceptable for a feature planned for the 9.5 release, but, unfortunately, the relevant multixact changes were merged during the 9.3 development cycle. The 9.3 release happened in September 2013, but the fallout from that particular change is still being dealt with.
After Robert and others dealt with the latest round of problems, Bruce Momjian started a discussion on how this particular patch should have been dealt with. He suggested that perhaps the feature should have been reverted, but that didn't happen.
The ensuing discussion made it clear that there is no single thing that can be fixed to avoid the possibility of similar problems in the future. Bruce feels that there are not enough people jumping in to fix bugs when they come around. Joshua Drake pointed his finger at the PostgreSQL release cycle which is, he says, too short and too oriented toward the addition of new features. The one-year cycle used by the project is already long relative to many others, though. Tom Lane noted that the ratio of developers paid to fix bugs to those paid to add new features is too low, making it hard to get a problematic feature fixed. Robert attributed the bulk of the problems to the complexity of the particular patch involved.
Whatever the reason, there is concern within the PostgreSQL community that its well-earned reputation for low bug rates is at risk. That concern carried over into Robert's message on the remainder of the schedule for the 9.5 development cycle. While he is glad to see stuff being pushed in under the deadline, some of it worries him, too:
He noted that he is already worried about some of the patches, upsert among them, that have gone in for 9.5. Bruce, despite his concerns mentioned above, still seems to be more worried about the prospect that interesting features will be left out of 9.5; he has encouraged reviewers to push to get as many of them in as possible. But the mood of the community as a whole appears to be moving in the direction of letting questionable features wait rather than rushing them into the 9.5 release.
The ugly
That mood can be seen in Tom's posting of the remaining open items and his suggested disposition of each, but it turned a bit uglier as people read the list and saw what was proposed. In particular, Tom recommended that the grouping sets feature should be left out this time around. Grouping sets allow for more flexible grouping of results from a query; the feature has been under development for some time.
The specific problem in this case is that Tom had stated some months ago that he would be the one to shepherd that patch set through the review process. His role in the project is such that, once he lays claim to a patch in that way, other developers are unlikely to work on it; why bother, when the patch appears to be in the hands of one of the community's top developers? In this case, though, Tom never found the time to help get the patch into shape for merging; since nobody else did either, the patch languished. When he then said that the patch should not go into 9.5 because it was not ready, that led to charges of unfairness and calls to give the patch another chance.
This episode has made it clear that some developers, at least, are worried about how the development process looks to PostgreSQL contributors. As Josh Berkus put it:
Andres Freund observed that the grouping sets patch poses a relatively low risk to the stability of PostgreSQL as a whole; it is highly unlikely to cause data corruption problems. There have been a number of other patches, he said, that have gone into 9.5 with less review and in worse shape. Keeping this patch out, he said, is likely to worsen the perception of the development community as a whole:
In the end, Andres committed the grouping sets patch on May 16, so this feature will indeed be a part of the 9.5 release, but the incident seems to have left a bad taste in some developers' mouths. Nobody seems to believe that Tom intentionally stalled the patch, but they would have liked to see this story play out in a different way.
In general, it seems that the "commitfest" process used by the PostgreSQL community since the 8.4 release is showing some signs of age. Andres suggested that commitfests are frustrating for both reviewers and contributors while blocking good patches and failing to keep the bad ones out. He suggested a more direct "judgefest" process that would try to reach quick decisions on patches without allowing them to languish, but there has not been a lot of uptake for this idea on the list. This is not the first time the commitfest model has been called into question, of course, but the voices seem to be getting louder.
Development-process issues seem to come to the fore in just about every
successful project at some point; a process that worked at one point in a
project's evolution may well be unsuited to the next stage. In the end, a
project is not just hacking the code; it must also hack the process by
which it produces that code. That can make for some messy public
discussions, but most projects come out of process-hacking phases as more
smoothly functioning, better-tuned machines. PostgreSQL is not working
badly now by any objective standard; with luck, its community will
find a way to make it work even better.
(Log in to post comments)
Try shorter release cycles
Posted May 20, 2015 21:07 UTC (Wed) by roc (subscriber, #30627) [Link]
Try shorter release cycles
Posted May 20, 2015 21:14 UTC (Wed) by andresfreund (subscriber, #69562) [Link]
I'm of the opinion that the one year cycle is on the upper end of what's acceptable though; I think, in contrast to Josh Drake, that a longer release cycle wouldn't help at all, and present a bunch of new problems.
Try shorter release cycles
Posted May 20, 2015 21:39 UTC (Wed) by dlang (guest, #313) [Link]
Testing a release would not really be a problem. Initially it would look like it was, but since less would change release to release, the testing would be easier. In addition, testing is getting more automated. Eventually you get to a short enough cycle time that testing becomes the limiting factor. But when starting with a 1-year cycle time, you have a long ways to go before you hit that limit.
But people are not going to be updating their databases that frequently, so you would have many more different versions "in the wild" at any one time. Upgrades would also need to consider upgrades that skip versions much seriously than they have in the past.
Try shorter release cycles
Posted May 21, 2015 12:25 UTC (Thu) by Lennie (subscriber, #49641) [Link]
LTS releases: Linux kernel has them, Linux distributions have them, even Firefox has them.
Try shorter release cycles
Posted May 24, 2015 20:08 UTC (Sun) by robbe (guest, #16131) [Link]
Try shorter release cycles
Posted May 31, 2015 8:56 UTC (Sun) by zenaan (guest, #3778) [Link]
For PostgreSQL may be:
- normal release every 3 or 4 months
- LTS release every 12, 18 or 24 months
This model provides:
- higher frequency normal releases to
. a) showcase new features to the public and
. b) reduce pressure on developers wanting to not miss an "infrequent annual" release; and
- lower frequency LTS releases to
. a) focus testing, stability and long term support resources
. b) satisfy "conservative/ enterprise" RDBMS admins
Everyone happy. This is a model proven to work - Firefox, Linux kernel and various distributions at the very least.
Try shorter release cycles
Posted May 21, 2015 8:30 UTC (Thu) by frnknstn (guest, #68647) [Link]
Chrome and Firefox have essentially traded a little stability for a lot of new features. For PostgreSQL, losing the same amount of stability is not an option.
Try shorter release cycles
Posted May 31, 2015 9:32 UTC (Sun) by zenaan (guest, #3778) [Link]
"Losing stability" is a misnomer - stability for those who care about it comes from feature testing in non-LTS releases combined with long term security and bug fix promises for the actual LTS release - 5 year stability in fact is what's provided currently, but unfortunately without the benefits of a high frequency feature-train broader/public "normal" release testing regime.
PostgreSQL really ought to read one of Corbet's Linux kernel statistics articles and take good notice of the incredible development and release velocity in action here. Yes PostgreSQL is different, in code, developers, layer in the stack etc, but the kernel release "model" works, and works incredi-firetruckingly well to the point it is mind boggling! Firefox seems to be doing alright too. LibreOffice has kicked off to an awesome start in its relatively young life.
The model works! and it works abundantly well.
The PostgreSQL team made a great move to a dvcs - git. This next step is needed to lift their game to the next level. I hope they see this sooner rather than later, and I do think their dev community is ready.
In Debian, those who want the latest go sid/"unstable" which is not only updated daily but is for any half-competent user, incredibly stable!
What we are achieving in different parts of our libre software communities is absolutely astounding, almost beyond belief. We truly live in a time of amazing software abundance and new feature velocity.
Those who want "real stability" in debian go "stable", which is an update every ~2 years, or if you stick with LTS as "oldstable" for a second LTS/stable cycle, you get around 4 years.
This matches rather well the 5-year LTS cycle currently provided by PostgreSQL.
And with more frequent "public feature train but non-LTS" PostgreSQL feature test releases, new features would get public exposure and the relative abundance of testers which is needed (the debian sid and bleeding edge gentoo crowd).
Combine this with an Ubuntu style "conservative" (say 3 months duration) cycle for each LTS release where risky or untested features are bumped to the next feature train release and even buggy features removed if needed for this LTS (to be added again in the next feature train release) we have ample evidence all over the large software project map that this must result in LTS releases with overall higher quality, better testing, more features and it's all more scalable for the developers involved.
Another 3-months and the feature train resumes...
<woo wooooo>
Try shorter release cycles
Posted May 21, 2015 22:17 UTC (Thu) by tomik (guest, #93004) [Link]
It's a bit like autovacuum - if it hurts, you should crank it up and do it more frequenty / smaller bits.
Try shorter release cycles
Posted May 30, 2015 23:34 UTC (Sat) by toyotabedzrock (guest, #88005) [Link]
They will have to promise no breaking changes for each major version number of course.
Try shorter release cycles
Posted May 31, 2015 0:01 UTC (Sun) by dlang (guest, #313) [Link]
1. it takes a lot of testing to be sure that your app works with a new version of the database and that bugs that affect you haven't crept it.
2. transitioning from one version to another can involve a lot of data needing to be copied/converted
3. there is a significant cost to the developers to support each additional version, because each version will have a different set of bugs/limitations/features.
so switching to a 3-month cycle and still supporting all versions released within the last X years is going to be a huge amount of extra work on the part of the developers (#3), but because of #1 and #2, you still aren't going to have companies running on the latest versions.
For my personal stuff, I like short release cycles. For projects that I trust, I auto-update to their newest versions. In some cases I run alpha or beta versions. But for stuff that will cause serious impact if it breaks, and will cost real money if it's down, auto-updating is just too risky, validation and testing is needed. The worse the impact of a problem is, the more testing is needed, and therefor the longer the time between updates will end up being (there is only so much time available, so as testing takes more time, fewer cycles of testing fit into that available time)
Try shorter release cycles
Posted Jun 1, 2015 14:57 UTC (Mon) by flussence (subscriber, #85566) [Link]
Try shorter release cycles
Posted Aug 7, 2015 8:10 UTC (Fri) by jospoortvliet (guest, #33164) [Link]
Try shorter release cycles
Posted May 20, 2015 23:10 UTC (Wed) by robert_s (subscriber, #42402) [Link]
Try shorter release cycles
Posted May 21, 2015 2:08 UTC (Thu) by khim (subscriber, #9252) [Link]
What exactly makes them so different? Note: it's not just browsers, it's also things like Colossus and Spanner—these, too, are developed in fashion similar to Chrome.
Try shorter release cycles
Posted May 21, 2015 12:36 UTC (Thu) by Lennie (subscriber, #49641) [Link]
If you increase the number of versions of a RDBMS you'll increase the number of versions running in the wild you'll have to support.
People don't update their RDBMS everytime a new release comes out
Try shorter release cycles
Posted May 21, 2015 16:22 UTC (Thu) by khim (subscriber, #9252) [Link]
How many versions of Colossus and Spanner are running in the wild ?Two or three, I think. The same principle as with Chrome. Or, perhaps the other way around?
People don't update their RDBMS everytime a new release comes out.Then RDBMS should just silently update itself. Problem solved. Yes, I know that PostgreSQL couldn't do that but that's self-inflicted wound, I don't see why SQL server couldn't do what Chrome, Colossus, Spanner and other Google products (like Google Mail or Google Maps) are doing.
Try shorter release cycles
Posted May 21, 2015 16:27 UTC (Thu) by Lennie (subscriber, #49641) [Link]
Try shorter release cycles
Posted May 21, 2015 16:49 UTC (Thu) by khim (subscriber, #9252) [Link]
Because of this: upgrades are offline and might require taking hours of downtime.
As long as PosthgreSQL insists on painful offline upgrades short release cycles and automatic upgrades are just not feasible. But there's nothing fundamental in that requirement: most DBMS systems out there somehow manage to support online upgrades, PostgreSQL is rare exception.
Try shorter release cycles
Posted May 21, 2015 17:05 UTC (Thu) by Lennie (subscriber, #49641) [Link]
Try shorter release cycles
Posted May 21, 2015 22:01 UTC (Thu) by tomik (guest, #93004) [Link]
The other products (Spanner, Colossus, ...) can do that, because those products were designed as distributed from the very beginning. But we are moving in the direction of zero-downtime upgrade too - the Logical replication is part of that.
Try shorter release cycles
Posted May 22, 2015 13:21 UTC (Fri) by NAR (subscriber, #1313) [Link]
Then RDBMS should just silently update itself.Do you really want to do that? I mean I don't care if e.g. Firefox stops working after an auto-update, I can easily start to use Chrome (or Opera or Safari or Internet Explorer or something else) instead, maybe bookmarks and saved passwords have to be imported and that's all. On the other hand I would most definitely not want my database server update itself on a whim on a balmy Friday evening, loose all the data and notice it only on Monday morning...
Try shorter release cycles
Posted May 23, 2015 23:24 UTC (Sat) by khim (subscriber, #9252) [Link]
If it's done as with Chrome then it'll only be updated when it's restarted which means that your system is, most likely, restarted too. If you have a habit of restarting database server on a balmy Friday evening without looking on the result then you only have yourself to blame.
Try shorter release cycles
Posted May 24, 2015 14:07 UTC (Sun) by madscientist (subscriber, #16861) [Link]
Try shorter release cycles
Posted May 25, 2015 15:48 UTC (Mon) by khim (subscriber, #9252) [Link]
So what? Systems often don't boot normally after the crash (happened to me with my workstation just a few hours ago). New version of SQL will not change that dynamic much.
Try shorter release cycles
Posted May 26, 2015 1:19 UTC (Tue) by ringerc (subscriber, #3071) [Link]
Try shorter release cycles
Posted May 26, 2015 11:51 UTC (Tue) by madscientist (subscriber, #16861) [Link]
First, "often" is grossly overstated: it's actually very rare that a system fails to reboot properly (as in, the services it provides would not start). Second, it might not be the entire system that crashed, it might be just the database itself.
Try shorter release cycles
Posted May 21, 2015 15:30 UTC (Thu) by skriticos (guest, #102673) [Link]
Try shorter release cycles
Posted May 21, 2015 15:52 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
And the relatively fast moving kernel *does* cause problems. A lot of the MM changes (transparent hugepages, page migragation/automatic numa balancing, changes in the writeback logic) caused *massive* performance problems over many kernel releases. With a different mix of problems in many of the releases. It's probably still the right choice, but it's definitely not without problems.
Try shorter release cycles
Posted May 21, 2015 0:11 UTC (Thu) by roguelazer (subscriber, #101286) [Link]
In fact, given how recently the 9.2.5/9.3.1 data corruption debacle (https://wiki.postgresql.org/wiki/Nov2013ReplicationIssue) happened, I'm be more inclined to think that *point* releases need more QA time...
Try shorter release cycles
Posted May 21, 2015 13:01 UTC (Thu) by Lennie (subscriber, #49641) [Link]
PS Steve Singer from the Slony project was one of the first outside developers to try out the new logical decoding code when it became available so I hope they implement 'an output plugin' as it's called.
Try shorter release cycles
Posted May 26, 2015 1:20 UTC (Tue) by ringerc (subscriber, #3071) [Link]
What we need is more people putting more time into actually reviewing and testing the changes. Presently everyone's focused on their projects and getting their patches in, and not many people have much time at all dedicated to testing and QA.
If we can increase the time slice given to QA and testing then we won't _need_ longer freezes. If we can't, then they won't do any good.
Try shorter release cycles
Posted Aug 7, 2015 8:17 UTC (Fri) by jospoortvliet (guest, #33164) [Link]
Try shorter release cycles
Posted May 22, 2015 3:18 UTC (Fri) by ringerc (subscriber, #3071) [Link]
I still work with a customer on 8.2...
Try shorter release cycles
Posted Aug 7, 2015 8:21 UTC (Fri) by jospoortvliet (guest, #33164) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 20, 2015 21:07 UTC (Wed) by andresfreund (subscriber, #69562) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 13:26 UTC (Thu) by Lennie (subscriber, #49641) [Link]
- PostgreSQL is freaking awesome
- my hope is it stays that way
- if the development community wants to decrease the time between releases, maybe it's a good idea to create a 'long time support' releases.
- I think it would be great if someone made a production ready replication system based on a logical decoding output plugin. That would make it a lot easier to upgrade PostgreSQL. The reason I mention this is: when upgrades are easier to do and with less downtime certain parts of the user base will do it more often which hopefully will catch issues much sooner.
When I saw Steve Singer which works on the trigger-based replication Slony project getting involved with logical decoding on the hackers mailinglist before the 9.4 release I got really exciting. But hey I might be reading to much into this. :-)
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 15:38 UTC (Thu) by tomik (guest, #93004) [Link]
> on a logical decoding output plugin. That would make it a lot easier to upgrade PostgreSQL.
> The reason I mention this is: when upgrades are easier to do and with less downtime certain
> parts of the user base will do it more often which hopefully will catch issues much sooner.
You mean like BDR? http://2ndquadrant.com/en/resources/bdr/
Most of that got already merged into 9.6 (although not all of them). Anyway, there's
a subset of BDR that works on 9.4 (uni-directional replication, but that is enough for
the online upgrade).
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 17:29 UTC (Thu) by Lennie (subscriber, #49641) [Link]
http://git.postgresql.org/gitweb/?p=2ndquadrant_bdr.git;a...
I guess I had more faith in code base I've used in production before. Which Slony is. But it doesn't really apply here. I expect Slony would need not just a few changes to use this, but I could be wrong.
Also as someone mentioned in an other thread: other database servers have no problem working with old data. Why has PostgreSQL not done that yet ?
Anyway I'm glad progress is being made. However it's solved. Replication would be fine, as people that care a lot about downtime don't have just a single PostgreSQL installation running in production they usually use some kind of clustering or replication.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 19:15 UTC (Thu) by simon@2ndQuadrant.com (guest, #102687) [Link]
BDR is designed to overcome the architectural difficulties of Slony and related systems. We've tried to preserve as many of the concepts as possible, to make it easy for people to migrate.
Thanks for pointing out that UDR is still described as experimental, we can remove that now. Zero-downtime upgrade with UDR is fully working and will allow 9.4 -> 9.5 upgrades.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 20:00 UTC (Thu) by jberkus (guest, #55561) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 20:20 UTC (Thu) by jberkus (guest, #55561) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 21:47 UTC (Thu) by Lennie (subscriber, #49641) [Link]
Also I think it's great work you are doing, I'm not complaining. :-)
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:12 UTC (Thu) by tomik (guest, #93004) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:56 UTC (Thu) by Lennie (subscriber, #49641) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:59 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 21:13 UTC (Thu) by kleptog (subscriber, #1183) [Link]
Also as someone mentioned in an other thread: other database servers have no problem working with old data. Why has PostgreSQL not done that yet ?Supporting backward compatibility comes at a substantial cost. Right now when adding features the developers don't need to worry about maintaining the current schema forever. There is an effort to keep datatypes in constant format so that pg_upgrade can do its fast upgrades. But the best way of doing live upgrades has always been via replication like Slony-II, and more options are coming along.
Most of the kernel (FS's being the exception) and software such as Firefox have almost no persistent data, which makes it much easier to maintain that little bit of state over upgrades. Postgres's on disk format has gone through quite some changes over the years, none of which would have been possible if backward compatibility was a requirement.
I guess those other databases are either not adding as many features, or have the manpower you maintain the backward compatibility.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:43 UTC (Thu) by jberkus (guest, #55561) [Link]
The issues with upgrading are:
1. Even 5 minutes of downtime is a lot for someone's production-critical database.
2. Applications have to be extensively tested for compatibility with the new versions.
Point 2 means that upgrading Postgres is a lot like upgrading the major version of your main programming language, something which people *also* put off doing for years. For example, RHEL still ships Python 2.6, as (I believe) does OSX. So any development plan which doesn't take into account that users will stay on the same version of Postgres for 3-5 years at a time is doomed to fail.
(* there's also that pg_upgrade isn't trusted by many users due to bugs in its past)
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:46 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
I'd personally consider the catalogs a part of the on-disk format. It's just that we have a offline migration tool for it (pg_dump in binary upgrade mode).
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:58 UTC (Thu) by Lennie (subscriber, #49641) [Link]
So I guess having a good low-overhead replication system between versions helps with setting up test environments as well.
Which is easier than using a pg_dump and pg_restore.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 23:12 UTC (Thu) by pizza (subscriber, #46) [Link]
...then WTF are you upgrading a production-critical database at all?
Never, never, never do live upgrades of critical stuff.
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 0:10 UTC (Fri) by flussence (subscriber, #85566) [Link]
Working under people who won't budget enough to allow the developers to do their job sanely?
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 19:04 UTC (Fri) by dlang (guest, #313) [Link]
you don't always have the storage to be able to replicate everything to a new copy for the upgrade.
Even if you can, how do you make the new copy have everything the old copy has if the old copy is continually being updated.
At some point you have to stop updates to the old copy so that you can be sure the new copy has everything before you cut over to it. If you have real-time replication to a copy that's got the same performance/redundancy as your primary (the slony approach that's mentioned several times here), then you can make the outage very short.
But if you aren't setup that way, you have to either never convert, or convert in place.
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 22:14 UTC (Fri) by pizza (subscriber, #46) [Link]
You mean to tell me that you don't have any sort of backup for your system, at all? "pg_dump | xz > dump.xz" takes less space than the live PG database.
Again, I stand by my assertion that performing live upgrades of mission-critical stuff is a horrendously bad idea. Justifying it with excuses about lacking sufficient resources to do this properly is even worse, because that tells me you're one failure away from being put out of business entirely.
> But if you aren't setup that way, you have to either never convert, or convert in place.
I'm not saying converting in place is necessarily the wrong thing to do.. just that doing it on a live system with no fallback option is insane -- What it there turns out to be an application bug with the new DB?
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 22:18 UTC (Fri) by andresfreund (subscriber, #69562) [Link]
I think that's a statement pretty far away from reality. If downtimes cost you in some form or another, and dump/restore type upgrades take a long while due to the amount of data, in-place isn't a bad idea.
> just that doing it on a live system with no fallback option is insane -- What it there turns out to be an application bug with the new DB?
Why would inplace updates imply not having a fallback?
PostgreSQL: the good, the bad, and the ugly
Posted May 23, 2015 0:14 UTC (Sat) by dlang (guest, #313) [Link]
A backup can be on much slower storage, and can be compressed (or without indexes that get created at reload time, etc)
There are lots of ways that a large database could be backed up so that it could be restored in a disaster that don't give you the ability to create a replacement without taking the production system down
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 10:43 UTC (Fri) by niner (subscriber, #26151) [Link]
Oh it can certainly take hours, if it finishes at all. Ever tried to upgrade a database containing > 90000 tables and about the same number of views and indexes? To my knowledge the only feasible way is to run multiple instances of pg_dump and then psql in parallel with xargs (in our case the tables are neatly divided into ~ 1200 schemas with no interdependencies). pg_upgrade would do a schema dump with pg_dump. This process cannot be parallelized and may run into all kinds of memory limits. And it takes hours or longer.
Database upgrades are pain and the only occasion where we have planned down time of our cluster :/
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 14:58 UTC (Fri) by andresfreund (subscriber, #69562) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 15:35 UTC (Fri) by niner (subscriber, #26151) [Link]
I would certainly like to help improve pg_upgrade to a point where it becomes useful for us :)
The fixed known efficiencies, are they fixed in 9.4 or 9.5?
Where's the appropriate place to continue this discussion?
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 15:52 UTC (Fri) by andresfreund (subscriber, #69562) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 22:59 UTC (Thu) by Lennie (subscriber, #49641) [Link]
I wouldn't be surprised if you are right that other databases got less new features in the same years.
PostgreSQL: the good, the bad, and the ugly
Posted May 20, 2015 21:18 UTC (Wed) by jberkus (guest, #55561) [Link]
As the person who originated the Commitfest, I couldn't agree more. It's been six years, and the project is far bigger and more critical than it was back then. The purpose of the commitfest was to solve two problems: (a) to make sure patches weren't getting lost completely, and (b) to give the committers some time off to work on their own stuff. It's still working OK for (a), but it's completely failing at (b) now. Time for some substantial changes.
(Note: the above is my personal opinion, and not the opinion of the PostgreSQL Core Team or any other group.)
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 15:34 UTC (Thu) by tomik (guest, #93004) [Link]
It may have been clear back in 2008/2009, but nowadays a lot of people on pgsql-hackers use their own definitions of CF (what they think it should be) rather than what it was meant to be. That not only makes it difficult to follow the CF, but it makes it almost impossible to reform the process to reflect the progress of the project.
Origin of PostgreSQL commitfests
Posted May 21, 2015 23:07 UTC (Thu) by alvherre (subscriber, #18730) [Link]
It's interesting that you see yourself as originator of the commitfest idea, but I don't think it's true. Consider this message from October 2007, where Tom Lane replies to Simon Riggs about him "liking the idea" of having "checkpoints in the development cycle" where the patch queue is emptied every two months. This seems to me quite distinctly the origin of commitfests. A few messages later, Tom first mentions the term commit-fest. I think it's fair to consider Simon the originator of the idea and Tom the one that christened it.
We first managed the commitfests as pages in the wiki. You can see the first one here, which I started.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 23:08 UTC (Thu) by simon@2ndQuadrant.com (guest, #102687) [Link]
The term was first coined by Tom Lane here
http://www.postgresql.org/message-id/4373.1193177340@sss....
after I (Simon Riggs) had suggested almost exactly what we have now, in this post/reply
http://www.postgresql.org/message-id/9973.1193146198@sss....
Which just goes to show that whatever the facts are, there are always multiple viewpoints on them.
My view is that the process isn't broken, we're just undercapacity. While that might appear as a problem, it says good things about the PostgreSQL project.
One point in that regard that I want to repeat publicly: if every patch needs a reviewer then we clearly need as many reviewers as we do patches. My proposed solution of this is to force developers to provide one review for every patch they submit. Otherwise everybody just writes patches and fewer people review.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 23:16 UTC (Thu) by jberkus (guest, #55561) [Link]
Oh, I've been blamed for the commitfests so many times I figured I must be responsible. If you wanna take the blame for them, them please be my guest. ;-)
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 19:12 UTC (Fri) by dlang (guest, #313) [Link]
But it doesn't say that the process shouldn't be changed.
Being undercapacity for the existing process doesn't mean that you would be undercapacity for a different process.
going off on a slight tangent as an example
One thing you learn from dealing with auditors is that what you try do do doesn't matter nearly as much as if you do what you say you are going to do
If you have company A that says that they do a very basic process, but actually do it, they will do much better in audits than company B that says they do a 10x better process but are undercapacity to dot all the i's and cross all the t's of their process and so are only 5x better in practice.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 0:08 UTC (Thu) by smoogen (subscriber, #97) [Link]
I kept reading that as "but there has not been a lot of upsert for this idea on the list." and I was like "well of course, they haven't released it yet." Stupid reading too fast.
Thanks everyone
Posted May 21, 2015 1:57 UTC (Thu) by gracinet (guest, #89400) [Link]
My company works on software that relies on PostgreSQL for... almost everything, and PostgreSQL itself is without doubt the best part of the stack. I often qualify it as "cream" (don't know how this sounds in english, but I hope you get the idea). From time to time, I read posts on the "general" mailing list and I witness how great and supportive a community this is.
So, I'm sorry to hear of these growth problems, but I suppose it's kinda natural for growth to come with new challenges.
There's not much I can do myself to help apart from having some CI bots run on the future pre-releases (but that won't exert new features, that aren't much used in my code base).
Anyway, keep up, guys, you're doing an amazing job.
Oh, and by the way, if that upsert providing system works, it'd have a tremendous value: everyday application code usually handles this *very* badly
Thanks everyone
Posted May 21, 2015 15:20 UTC (Thu) by tomik (guest, #93004) [Link]
But even running a regular buildfarm animal (pgbuildfarm.org) is valuable - especially if you use an unusual platform (say, IBM Power and such).
Thanks everyone
Posted May 21, 2015 17:15 UTC (Thu) by gracinet (guest, #89400) [Link]
(we never found any problem).
As for the buildfarm, I'll look into it, but nothing exotic here (Debian 7 x86 / amd64).
If you're looking for ppcel64 builds, I suppose you tried asking hosting companies with Power8 offerings already ? There's at least one in France.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 10:42 UTC (Thu) by meyert (subscriber, #32097) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 13:48 UTC (Thu) by ehiggs (subscriber, #90713) [Link]
http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YY...
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 19:06 UTC (Thu) by pr1268 (subscriber, #24648) [Link]
Or, why not update the SQL standard to include:
INSERT INTO <table> IF EXISTS UPDATE VALUES (primary-key, other-values, ...)
Just a thought. I suppose the bigger issue is atomicity of the above (as described by our editor).
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 3:24 UTC (Fri) by ringerc (subscriber, #3071) [Link]
It's run by a cabal of major commercial RDBMS vendors and users, and AFAIK is rife with the kind of politics that makes pgsql-hackers look like a peaceful little backwater. We're pretty much passengers on that train.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 20:46 UTC (Thu) by jberkus (guest, #55561) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 20:48 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 22, 2015 3:28 UTC (Fri) by ringerc (subscriber, #3071) [Link]
See http://stackoverflow.com/q/17267417/398670
If we implemented a subset of MERGE with strong concurrency guarantees for upsert, we'd then have a really hard time implementing all the other parts of MERGE where it's not possible to offer sane concurrency guarantees. Or we'd have a single command that was concurrency-safe for some variants of the syntax and unsafe for other variants.
MERGE is also an awfully verbose and awkward way to express an upsert, especially for the common case of a single target table and single input row.
That's why an upsert syntax has been added. It's well defined in its concurrency behaviour and narrowly scoped to the problem at hand. We can still implement the OLAP-style MERGE command and slap a giant "LOCK all the tables first" warning on it, like the other database vendors should (but don't) do.
PostgreSQL: the good, the bad, and the ugly
Posted May 21, 2015 19:35 UTC (Thu) by simon@2ndQuadrant.com (guest, #102687) [Link]
Yes, bugs are Bad and new features are Good.
New features solve old problems but cause bugs, so its a difficult balance as to what to include or exclude for any release. Each individual developer makes his own choices based upon their understanding, preference and time allocation. What I have learned over many years is that everyone is concerned to do this with the highest quality and sometimes that takes time. Time not just for software to work, but for everyone involved to come to believe the feature is working correctly and that we can support it for many years to come in its current state.
IMHO its unfair to single out any individual, particularly Tom, for inclusion within the Ugly section here. I'm not sure we needed that section at all but thanks for the publicity.
PostgreSQL: the good, the bad, and the ugly
Posted May 25, 2015 3:51 UTC (Mon) by dvdeug (subscriber, #10998) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 25, 2015 16:37 UTC (Mon) by tomik (guest, #93004) [Link]
PostgreSQL: the good, the bad, and the ugly
Posted May 26, 2015 1:57 UTC (Tue) by ringerc (subscriber, #3071) [Link]
MySQL's ON DUPLICATE KEY UPDATE is the most popular form. In addition to being a little bit ugly (missing SET, etc), though, it offers no way to specify which unique constraint or index should be considered the key. MySQL's documentation says that behaviour in the presence of multiple unique indexes is undefined, which isn't something we're willing to accept in PostgreSQL. Additionally, the MySQL form offers no way to access both the old tuple and the proposed tuple in case of conflict.
Lots of people ask why we didn't just use MERGE, but that's already been explained in detail upthread. Basically it isn't upsert, it's an OLAP statement with largely undefined concurrency semantics, and if we try to turn it into an upsert we'll break the real use cases for MERGE.
The PostgreSQL syntax is fairly close to MySQL's, and we'd really love the MySQL folks to adopt it since it'd be a fairly simple extension of what they currently have.
What we really need here is for the SQL standard to recognise that MERGE does not serve the purpose of OLTP upsert, and define a standard UPSERT, either via an actual UPSERT statement, or via extensions to INSERT. Naturally I'd prefer to see them adopt the PostgreSQL syntax, but we're more likely to land up with something baroque and verbose with at least two unnecessary new keywords used in confusing contexts and at least one bizarre pseudo-function that requires new parser logic...
PostgreSQL: the good, the bad, and the ugly
Posted May 29, 2015 9:28 UTC (Fri) by eduperez (guest, #11232) [Link]
And we have a race condition here, if someone else inserts a new (and conflicting) row between the check and the change! Just kidding and nitpicking on a very good article, as usual around here; but I have seen that mistake made into code so many times, I just had to say it.