The stuff Citus has been landing in PostgreSQL is fantastic.
They call out a number of forks that do quite nicely. I work for Pivotal, which sponsors Greenplum. Companies pay handsomely for the capabilities it brings to the table.
But they are right that rebasing is a nightmare. My understanding (possibly wrong) is that the broad selection of APIs that make an extension-only approach did not appear in PostgreSQL until more recent versions -- anyone who forked earlier (such as Greenplum) have to first catch up and then migrate.
I do know that the Greenplum team have decided to catch up until they are working against mainline. It is, as you might imagine, a slow process: rebasing millions of lines of code a release at a time is not the easiest task on earth. But maintaining a fork will, in the long run, be harder.
Ever for smaller changes, it is very well known that you better contribute back to the Free Software project - because having your patch included and being maintained there is a lot less trouble than maintaining your fork and updating your patches every few months.
One exception might be one-off changes, but we all know that nothing is more definitive than the temporary.
The only real exception is when your patch is superseded by another patch (or a better solution). Then, you maintian your private patch only until next version is finalized.