PostgreSQL: the good, the bad, and the ugly
lwn.net
lwn.net
This comment really resonates with me, and wasn't something I thought of -- the pressure to deliver when your release cycle is so infrequent is something I hadn't noticed before but once mentioned, realize it's absolutely true.
Of course, the next release will then have a _different_ minor bug. Then the pattern repeats.
If you release every two weeks and something isn't fully signed off at the end you don't release it - it's only two weeks until the next one. If you release every three months you cram in everything you can because it'll be at least three months until the next release.
Of course it's only true if you treat releases as an opportunity, not a constraint. If you don't promise dates but keep the opportunities frequent you get more done.
things like backporting fixes and maintaining upgrade paths are extremely expensive in the long run from my experience, something that I think short release cycles would exacerbate.
I think it comes down to what's mentioned in response to the comment about short release cycles: people are reluctant to upgrade an RDBMS because of the risk involved and expense to test compatibility with a new release.
I dunno maybe the problem is finding the shortest release cycle possible without putting people off and adding too much overhead?
On top of which, they incur heavy user cognitive costs when tools change every few weeks. Short release cycles seem to be more about making developers happy by alleviating them of a weighty responsibility, rather than making users happy by shipping something that stands on its own as a solid, well-made product that doesn't need a stream of constant updates.
I want software to behave more like reliable products that have survived the decades in the real world: they work as advertise, and they keep working. This is something PostgreSQL has achieved for many years.
Churn doesn't make sense for PostgreSQL; people who rely on software as a tool to accomplish their work generally do not benefit from incomplete poorly considered "minimum viable" solutions and a constant stream of instability. Strategies like continual delivery -- or focusing on MVP -- optimize for quickly proving/disproving user traction at a minimum possible expenditure, as to more quickly reach a stage where further growth can be funded via more investment.
In the process, these strategies incur a high percentage of false negatives -- including ideas that could work if given more consideration and care. These strategies are ultimately about exit events, not making well-crafted products that stand the test of time. Ironically, the latter is what often leads to much more substantial renumeration.
Your error is the erroneous assumption that centralised control is even feasible. This assumption has been proven wrong repeatedly. You're assuming the bazaar development method is riskier, but the certainty offered by the cathedral method is that of project overruns and likely failure. Unless you fund it sufficiently for NASA-like certainty, which you're not doing.
(Note that Postgres has been done much more that way all along, and is famous for quality; the crappy alternative, MySQL, which is in practice a product showing the sort of qualities you describe, was a one-company project for most of its history.)
Go reread The Cathedral and the Bazaar http://www.catb.org/~esr/writings/cathedral-bazaar/ and note that it's been proven pretty much entirely correct in practice. (By the way, "agile" at its best is literally an attempt to port successful open-source development to commercial practice.)
PostgreSQL's careful approach has always required "cathedral" centralization of technical management to ensure that things are done correctly, or not at all.
I also have to question the assertion that Linux provides an objective example of well-written software, when the code quality and issues present in Linux are most reminiscent of MySQL.
Relative to a so-called "cathedral" model such as FreeBSD's (or PostgreSQL's), Linux's software development model produces:
1) Code of considerably lower overall quality than that of FreeBSD in terms of bug count, maintainability, and simple consistency.
2) Poor (and often immediately replaced) architectural design designs that must be supported indefinitely.
3) Additional cost levied against downstream consumers of the product; simply shipping a reliable kernel requires considerable effort on the part of downstream distributions.
Time-based releases work to get your software in order. See Linux and Libreoffice as well. https://en.wikipedia.org/wiki/Release_early,_release_often
Command-and-control does have some advantages. And maybe as Postgres grows, the advantages related to a more orderly process become more visible. (For the record, I think Postgres is a fantastic RDBMS + JSON store)
That's a big "if." There are many projects that have been controlled by "hard to work with" developers, and it's not always a good thing. Even if the project doesn't suffer significantly from it, that doesn't mean it's helping the project either.
That may not be the case here, but just as a general statement, you can't chalk up "hard to work with" as equating to "focused on quality" and/or "good for the project."
> If someone is too difficult to work with, the rest will just fork and move on with life.
This is a gross simplification.
Robert Haas asked for suggestions toward the solution of some nasty data-corruption issues associated with the "multixact" feature [...] 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.
there is concern within the PostgreSQL community that its well-earned reputation for low bug rates is at risk
For example, upsert that is worth releasing should:
* Work with all unique indexes that may exist on a table, not only a primary key,
* Be correct even at highly concurrent writes, at any isolation level from Read Uncommitted to Serializable,
* Be fast even at highly concurrent writes, so no table locks, locks may not be held for more than an instant, and no re-running the entire transaction in a loop,
* Never throw an "another transaction modified data" exception at Read Committed isolation level, it should always correctly succeed as an insert or update even if another parallel transaction inserted or deleted the matching row.
I'm not familiar much with the other database engines, but my impression has been that their upsert or SQL merge do not provide all of these.
I mean this in the best, nicest possible way; we've all been there. But learning how to use the CLI will pay dividends in the short and long run.
- Change the sort order? (PMA: Click a column. CLI: Edit your query to add an ORDER BY clause?)
- Edit a value? (PMA: Double-click and edit. CLI: Check the primary key and write an UPDATE query? Hope not to make a typo when writing the key.)
- Look up the row corresponding to a foreign key? (PMA: click it. CLI: write a SELECT query?)
- View the extreme values of a result set? I.e. I run a query which returns 200 results, and I want to look at the first few and the last few. (PMA: home and end. CLI: scroll?)
CLI has a learning curve but what many will say is that we are SO GLAD we learned to not use our mouse to work with our data base and especially our SQL code.
The reason why I like using CLI is I can use GIT or any version control and make sure I have a clear history of my SQL commands and everything I did.
TL:DR Database Vendor with a Windows Server product screw with the logs and put the blame on me. I had a version control of all our history of CLI commands. Vendor employee or manager changed our log files externally. We won big court case (Before trial of course). Wouldn't have happened if I used the GUI tools.
Long story short. The vendor at the library I want was 100% evil. I asked by phone about two things. I emailed the engineer after the phone call for the two questions. BOOM I lost one month of work unless my backups work. Boom I lost half a day of work for 2 librarians. Vendor tells my boss and my school it will cost us $5,000 due to my mistake for them to fix something (Took them 15 minutes work on the phone due to their error).
My boss and college have a good relationship with me and we go on a conference call. Vendor tells a very convincing story and logs how I screwed up. They don't have records of my phone call and have 100% confidence that their employee would never okay both those things. I call them on the table. I have the email with the answers that the engineer gave me. BOOM their Engineer VP jumps in and says see if you did what he told you nothing bad would have happened and our logs show ... I than email my version controlled CLI commands for the past 6 months and show my commands that I did that day and that if the logs on their end our different they have a serious problem internally.
Long story short we won the court case and we got over hundreds of thousands of dollars back from that vendor.
SQL is difficult to learn for new programmers...but I find that that hurdle quickly pays off...no one ever keeps track of how much time they lose to struggling with the GUI because, well, doing things with the GUI are generally very hard to track. And it's a useful revelation, that being explicit, that having the ability to express exactly what you want, has a certain power to it. It's like teaching how to read and write to people who've so far learned only how to communicate in emoji
I'd love to know how.
EDIT: I've just barely tried it, but it looks like you could say `\g |vim -` (with a csv plugin) or `\g |csvlook` (not great) and then browse/sort/aggregate the data from there. You probably need to change the output formatting though, because it looks like \g doesn't automatically give you something easy to parse. I have a feeling an interactive command-line CSV reader (less(1) for csv files) would be really useful to a lot of people, but I don't know of anything like that. csvkit isn't interactive as far as I know. csvtool dies on big files. Any alternatives? It would be the perfect thing to combine with \g.
EDIT EDIT: Or just \pset pager and you don't need \g at all. . . .
Also these two seem decent: http://sol.gfxile.net/galaxql.html
http://sql.learncodethehardway.org/
The Hard Way has semi-decient issues.
I would say SQL is easy to learn and would take only a few days to get the basics and than just learn as you come to stuff your missing. SQL is a very tiny language.
> Edit your query to add an ORDER BY clause?
> Check the primary key and write an UPDATE query? Hope not to make a typo when writing the key.
> write a SELECT query?
did you think that I was saying: "I think this is how I would do it in SQL, if only I knew SQL. Won't you please point me to somewhere I can learn SQL so that all my problems will be solved"?
Because what I was going for was: "here is how I would do it in the CLI, but I much prefer how I would do it in PMA. Please tell me if there's a better way to do it in the CLI". I know SQL, I thought it was obvious that I knew SQL, and pointing me towards SQL tutorials is super unhelpful.
I googled searched by copy and paste your question for 5 seconds for each question:
> Edit your query to add an ORDER BY clause? http://www.techonthenet.com/sql/order_by.php
> Check the primary key and write an UPDATE query? http://stackoverflow.com/questions/3930338/sql-server-get-ta...
> write a SELECT query http://www.w3schools.com/sql/sql_select.asp
I think your issue is you are biased against CLI and think you get more benefit from a GUI. We can convince you what is best for you. We are just saying everything you pointed out is easily done in CLI with SQL commands. We believe everyone who works with databases should use CLI for a number of benefits because of our own experience.
> I know SQL, I thought it was obvious that I knew SQL, and pointing me towards SQL tutorials is super unhelpful.
makes you think that I just need really basic SQL tutorials?
I get the impression that you're glancing at my posts, picking out a few words, making up a question that someone else might have asked using those words, and answering that question instead of the one I'm actually asking.
This is incredibly frustrating for me. As such, I decline to continue this conversation.
What is your question besides show me how to do 1, 2, 3?
Note that jperras said "Stop using web-based admin tools". I think the CLI isn't good enough to fully replace them, but I don't think that one should stop using the CLI. I use both.
The CLI is supposed to let you do more of everything you want, versus a GUI, at the cost of being more complicated...but that's programming in general...programming is always harder and more tedious than the pre-designed solution, but people go the programming route when they need to ability to go beyond anticipated use cases. But if by "everything I'd want" you're referring to not just functionality, but overall usability...no argument, the CLI doesn't provide that. Although I'd argue that at a certain point, the CLI is easier when you need performance and peace of mind that something happened even if it is not easily visible via the GUI.
Well, you could do that; if you know you are interested in extremes to start with, use ORDER BY and LIMIT to just get the extremes in the first place (or, for more refined concepts of "extremes", consider the use of window functions.)
You do understand the phpXAdmin's are built on top of the CLI and the same API?
If anything, you can do more with the CLI than with a higher-level abstraction tool someone has to build logic paths/workflows into.
SELECT TOP ORDER BY ?
If that is what you mean, I think the advantage remains with PMA.
Edit to clarify: I'm thinking here in terms of data exploration. I want to look at the whole result set, but the most interesting ones are likely to be at the top and bottom. With PMA, I can get the whole result set, and nothing else, on a single page. So I just scroll to the top or bottom of the page to see the extreme values.
With a CLI, it can be difficult to find where the output of that specific query started. (This is a trade-off, because the CLI allows you to see results from your previous queries.)
If there was a CLI feature like 'view the previous result set in less, without re-running it', I think that would satisfy me on this point. If you have to re-run it, that's less good, but it would beat scrolling.
Or run it once into a temporary table, and then query the temporary table however you want to get subsets of interest. Which, if you are doing data exploration on the potentially large results of a potentially expensive query, is probably what you should be doing, no matter what tool you are using. Because it lets you do a lot more than just check the top and bottom easily.
If you're really stuck on a GUI editor give JackDB a try if you want something web based or Postico a try if you want a native GUI.
My hunch is that this gap exists because MySQL is often the default DB choice for new developers, whereas Postgres tends to be used by people already familiar with database CLI tools. Once you learn how to use the command line effectively, you'll be able to work a lot faster and won't want to use a web interface again, which is to say that you will become yet another reason for phpPgAdmin to be neglected.
The only thing we let people use MySQL for now is third-party frameworks that pretty much require it (Magento, MediaWiki, WordPress, Drupal). In fact, we're moving our Oracle to Postgres, with great success. (Oracle is fantastic at storing your data and giving it back, but everything else about it is the opposite of "agile".)
this is a really useful GUI tool for interacting with a postgres database.
I think that'd be a fair tradeoff.