LWN: Comments on "PostgreSQL: the good, the bad, and the ugly"

Try shorter release cycles

jospoortvliet — Fri, 07 Aug 2015 08:21:12 +0000

Online upgrades were mentioned as part of a cure. Besides, you can do lts releases every 18 months and support the other releases for a shorter time... It creates extra work for sure, backporting and stuff (but git is good at that). But you get testing between lts releases you don't get today...

Try shorter release cycles

jospoortvliet — Fri, 07 Aug 2015 08:17:42 +0000

That is actually where shorter release cycles with occasional LTS releases helps. For some users, the shorter cycle releases are great as they have need for the features and/or don't put such crucial data in the db (or are fine with rolling back a backup). They will report bugs and help ensure your LTS releases are stable. And no, they would not try beta and alpha releases - different thing, really.

Try shorter release cycles

jospoortvliet — Fri, 07 Aug 2015 08:10:03 +0000

That is entirely different as these don't built on each other, introducing new features at a similar stability level, instead just being more stabilized versions of the same features. If multiple PostgreSQL versions were in development at the same time, it would work (but that has its own downsides).

Try shorter release cycles

flussence — Mon, 01 Jun 2015 14:57:33 +0000

Postgres already has these rapid feature releases people are clamouring for: they're (correctly) labelled alpha and beta.

Try shorter release cycles

zenaan — Sun, 31 May 2015 09:32:14 +0000

This is exactly the reason why normal and LTS releases would be perfect for PostgreSQL - just like Ubuntu, PostgreSQL can be a little more conservative in each LTS release - focusing on polishing and bug fixing and documentation, and if say there is a 3-month "normal release cycle" then at most 6 months go by from when a developer "misses a release" until the next non-LTS release, and most of the time it would only be 3 months; even 6 months is much better than what's already the case.

"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

zenaan — Sun, 31 May 2015 08:56:58 +0000

Point being, PostgreSQL's idea of "LTS" is all that matters for the details. The concept is the interesting part for any project for that matter. Just choose parameters appropriate to the project at issue for "LTS support duration", "normal release frequency", "LTS release frequence" etc.

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

dlang — Sun, 31 May 2015 00:01:35 +0000

It's not version number phobia at work here, it's the combination of the following facts

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

toyotabedzrock — Sat, 30 May 2015 23:34:02 +0000

The version number phobia needs to go away. Postgres needs 3 month cycles so there is not the anxiety to get your feature added no matter the cost because the wait is too long.

They will have to promise no breaking changes for each major version number of course.

PostgreSQL: the good, the bad, and the ugly

eduperez — Fri, 29 May 2015 09:28:11 +0000

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

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.

Try shorter release cycles

madscientist — Tue, 26 May 2015 11:51:55 +0000

> So what? Systems often don't boot normally after the crash

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.

PostgreSQL: the good, the bad, and the ugly

ringerc — Tue, 26 May 2015 01:57:57 +0000

FWIW, we looked closely at adopting an existing syntax and didn't take adding a new one lightly.

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

Try shorter release cycles

ringerc — Tue, 26 May 2015 01:20:50 +0000

The problem is that adding "QA time" in the sense of longer freezes doesn't help if nobody pays attention.

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

ringerc — Tue, 26 May 2015 01:19:02 +0000

That's not true of a properly set up server though. Lots of effort generally goes into ensuring that it'll recover without administrator intervention on unplanned reboot.

PostgreSQL: the good, the bad, and the ugly

tomik — Mon, 25 May 2015 16:37:31 +0000

How is that worse than having the same syntax, but doing something different? If the behaviour is different, there's no way in keeping compatibility at the SQL level. IMSNHO it's exactly the opposite - it's way better to visibly break the application, than to accept the SQL and do something subtly different.

Try shorter release cycles

khim — Mon, 25 May 2015 15:48:28 +0000

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.

PostgreSQL: the good, the bad, and the ugly

dvdeug — Mon, 25 May 2015 03:51:26 +0000

"The new syntax". Because that's what everyone needs, is the endless proliferation of different incompatible extensions to SQL. At least if there was talking with MySQL and SQLlite, the incompatibilities between open source SQL databases could be minimized.

Try shorter release cycles

robbe — Sun, 24 May 2015 20:08:48 +0000

Firefox ESRs are good for a year. This does not qualify for "long" in the space PostgreSQL occupies.

Try shorter release cycles

madscientist — Sun, 24 May 2015 14:07:14 +0000

Remember that sometimes restart != scheduled downtime. Sometimes restart == crash.

Try shorter release cycles

khim — Sat, 23 May 2015 23:24:05 +0000

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.

PostgreSQL: the good, the bad, and the ugly

dlang — Sat, 23 May 2015 00:14:52 +0000

I didn't say there is no backup. I said that there was no extra system with all the CPU, RAM, fast disks, etc needed to run the full system in parallel while you are replicating.

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

andresfreund — Fri, 22 May 2015 22:18:14 +0000

> Again, I stand by my assertion that performing live upgrades of mission-critical stuff is a horrendously bad idea.

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

pizza — Fri, 22 May 2015 22:14:23 +0000

> you don't always have the storage to be able to replicate everything to a new copy for the upgrade.

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

dlang — Fri, 22 May 2015 19:12:28 +0000

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

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

dlang — Fri, 22 May 2015 19:04:18 +0000

> Never, never, never do live upgrades of critical stuff.

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

andresfreund — Fri, 22 May 2015 15:52:14 +0000

That's obviously not how it should be. I'd suggest a bugreport or a mail to the hackers mailing list. The dependency computation inefficiency "unfortunately" is already fixed in 9.4.1.

PostgreSQL: the good, the bad, and the ugly

niner — Fri, 22 May 2015 15:35:37 +0000

Before I posted my comment, I started pg_upgrade 9.4.1 with a copy of our production database (currently running 9.2) on a test machine. It's been running since and spending its time in "Creating dump of database schemas". That's about 5 hours now.

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

andresfreund — Fri, 22 May 2015 14:58:24 +0000

It shouldn't take hours. There've been a couple known efficiencies (dpeendenciy walking, parallelism inside pg_upgrade) fixed. It'd be interesting to do the exercise again and tell us what's slow and help us fixing it.

Try shorter release cycles

NAR — Fri, 22 May 2015 13:21:32 +0000

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

PostgreSQL: the good, the bad, and the ugly

niner — Fri, 22 May 2015 10:43:04 +0000

"So we haven't actually change the on-disk format for a while (since 8.3). That's not the real issue with upgrading, since pg_upgrade* will allow you to upgrade-in-place with a relatively short downtime, certainly not hours."

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

ringerc — Fri, 22 May 2015 03:28:54 +0000

MERGE is often (ab)used as an upsert, but the concurrency semantics aren't really defined by the standard and vary between vendors. It seems like lots of apps just use it and hope it works, not hitting concurrency issues often enough to care.

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

ringerc — Fri, 22 May 2015 03:24:04 +0000

We can't just "update the SQL standard". Heh. That'd be nice.

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.

Try shorter release cycles

ringerc — Fri, 22 May 2015 03:18:42 +0000

We have a hard enough time getting people to upgrade within the five year support lifespan as it is. Adding more releases to support would not improve that situation.

I still work with a customer on 8.2...

PostgreSQL: the good, the bad, and the ugly

flussence — Fri, 22 May 2015 00:10:49 +0000

> ...then WTF are you upgrading a production-critical database at all?

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

jberkus — Thu, 21 May 2015 23:16:10 +0000

Simon,

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

pizza — Thu, 21 May 2015 23:12:54 +0000

> 1. Even 5 minutes of downtime is a lot for someone's production-critical database.

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

simon@2ndQuadrant.com — Thu, 21 May 2015 23:08:34 +0000

Well, thats strange. I'd like to point out that you didn't invent the commitfest.

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.

Origin of PostgreSQL commitfests

alvherre — Thu, 21 May 2015 23:07:36 +0000

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

Lennie — Thu, 21 May 2015 22:59:09 +0000

Was just wondering if that was the reason or if there was an other.

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

andresfreund — Thu, 21 May 2015 22:59:03 +0000

The plan is to integrate as much as possible, it just takes a lot of effort and time.

PostgreSQL: the good, the bad, and the ugly

Lennie — Thu, 21 May 2015 22:58:18 +0000

"Applications have to be extensively tested for compatibility with the new versions."

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.