justintocci left another comment, which he has since deleted, but since I've spent some time investing in an answer, I'm posting it here. Justin, if you dislike being quoted, let me know.
> In fact, it really should have had these tools a decade ago.
What does "should" mean here? That it'd have been good? Sure. That people working (as a hobby, or as part of their employment) on PG should have stopped doing what they were, and instead focus on doing tooling? Maybe, but given the very very limited number of people and their skills I doubt it'd have been a good trade.
> So I propose a better question. Why doesn't PostgreSQL have free, enterprise-grade tools?
I think there's plenty of reasons, and everyone will weigh them differently:
For one, the development community is actually surprisingly small, especially a couple years back; only in the last few years that's changed to some degree. Which means we're often struggling to get the crucially needed server features done, not to speak of delving into a bunch of features outside of our expertise.
Another big reason, and that might sound counterintuitive, is PostgreSQL's license. Because of it and the long community support cycles, companies around PG have a hard time making money w/ licensing etc. So they have to focus on support, training and value-adds. A number companies tried / try to make money selling software around postgres, more enterprise-y tooling among that. Often that doesn't turn out to work that well, because it's actually a lot of work, and without input from the larger community they don't get the necessary market penetration.
Thirdly, I think that PG's development is done by a number of different companies, with each company's share of development not being large enough to sustain the project, explains some of this. I think it's overall an advantage, but in some areas like coordinated planning, marketing and coordinated effort it has its downsides too. If you want to make value-add software around core you're not going to do so with a competitor.
Lastly, I think for a long time Pgadmin3 was seen as not being good, but kinda somewhat acceptable. But its implementation wasn't particularly clean, in a language relatively few GUI folks use (C++), using a dying and buggy GUI framework (wxwidgets). I think it was for a long time just good enough that MVP of something better wasn't all that minimal anymore.
I actually left two comments and deleted them both. It's very difficult to say these things from my position but I like everything you said a lot. If I had said them my obvious bias would have detracted from it. From my position it's just best if I keep my mouth shut, but these are important issues and I think very much in need of attention.
> One kind of significant downside of partial indexes in PostgreSQL is that they don't work with parameters.
Your example is fundamentally different from parameters sent by a client in a parameterized query, i.e. $1, $2, etc. Also while your flawed example will never use a partial index, parameterized queries can and often will do that. With prepared statements (which are a slightly different concept from parameterized queries) the devil will lie in the details, but even those don't really prevent partial indexes from being used since postgres version 9.2, released years ago.
my `flawed` example is fine for what I'm using it for: demonstrating that PSQL won't use a partial index in an obviously common use case. Feel free to argue about the semantics of what is a prepared statement vs parametrized query vs whatever, but the fact is that the PSQL planner does not evaluate the subquery select prior to execution nor is it capable to alter its original plan after execution of the subquery.
This makes partial indexes of EXTREMELY limited use for complex queries which often need to pass a non-literal or do a join on a partially indexed column.
In fact, it is this EXTREMELY inconvenient limitation that often forces people to rearchitect large tables around a set of smaller tables with inheritance and check constraints, so that the planner will do index scans on the smaller tables, rather than a huge index.
In fact, if partial indexes were properly architected and supported, it would eliminate 90% of the use cases for partitions in PSQL. Partitions, by the way, are also a sham in PSQL 10, merely putting some syntactic sugar over table inheritance and what is basically a table-routing insert/update trigger. The current planner support for them is a joke / non-existent / worse than roll-your-own inheritance with check constraints and triggers.
> Changing from master to replica is easy, but now you have to rebuild that original master off of the former replica now. Completely start over. You can't just start up again from a given transaction ID. MySQL's GTID implementation is much better in this regard. You can change masters and replicas all repoint them without rebuilding. You can't do that (currently) with Postgresql.
Does this help at all with upgrades? Upgrading from 9.4 to 9.5 means you need to rebuild your entire replication topology because the master's identifier has changed (in the initdb step of the official docs).
> They maintained a replication process across both tables as they updated the read processes before updating the write process. Say for whatever reason their offline replication process broke for 2 hours.
From the article I got the impression that both tables were being written to in the same database transaction, so this is not a possible failure scenario at all.
This kind of nannying can be really expensive to do correctly when trying to build high performance systems -- to the point where it doesn't make sense to punish everyone just because someone who didn't read the manual MIGHT misuse the product. It's not at all unreasonable to mix transactions with different guarantees if they never touch the same data, and tracking that accurately enough without pissing off your customers with performance needs seems like a fool's errand.
Hmm.. So was there a workflow where I could say "I'd like for this not to be pushed until I've approved it", even if the patch is assigned to be reviewed by someone else? Of course, I could just comment with that on the issue, but it would be nicer if the issue would pop up somehow after the other reviewer has reviewed it, and it would be obvious to everyone that it's waiting for my approval.
