In fairness, some of this is historical baggage that MySQL got stuck with from it's early popularity.

mSQL was a free/low cost SQL database in the early 90s. Originally it was an SQL translator built on top of Postgres (that used POSTQUEL). That was too slow (because Postgres had higher system requirements), so a new lightweight engine was developed for it.. and that's what later became MySQL. The point was a lightweight SQL database that ran well on cheap early-90s computers.

The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL.

Postgres on the other hand, was a university project in the 80s and 90s being developed on mainframes. It was always a slow moving, cautious project. Yes, it didn't make the compromises mSQL made, because it didn't have to. It used more memory, more CPU, but was more correct/safe than MySQL.

That was the same reason it lost in the 90s. mSQL was out being used by websites and other projects on cheap computers. Postgres was mostly waiting for computer tech to get fast enough so they wouldn't need to compromise their code too much to take it off the mainframe an on to cheaper computers. Postgres only adopted SQL in response to mSQL's popularity. Early Postgres was also harder to setup, another point that wasn't addressed until after mSQL took off.

MySQL was popular because of the compromises they made. If mSQL just kept Postgres as the engine, MySQL never would have existed. And if they instead waited (even a couple of years), and did things correctly, they wouldn't have beat Postgres -- which had a decade lead in development and was more advanced than mSQL/MySQL.

> The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL.

This doesn't sound accurate to me. Which compromises are you referring to?

Historically, MySQL's major source of criticism related to leniency of type safety / automatic data conversion -- which is unrelated to performance. It's also essentially a solved problem with the advent of strict sql_mode. MySQL made this the default in 2015, but it has been available (and recommended as a best practice) since 2004.

Performance in MySQL, as a general topic, greatly depends on the storage engine. Relative to Postgres, MySQL's pluggable storage engine API is both a blessing and a curse -- it permits use of alternative engines that perform significantly better for specific workloads, at the cost of substantial administrative complexity.

Performance-wise, Postgres generally has the lead for things like number of supported index types, join strategies, query planning for very large queries (and OLAP workloads in general). These may or may not matter for you, depending on your workload.

The main selling point of MySQL at the earlier 2000's was that it used an in-memory storage with only opportunistic disk writes. That made for an incredibly fast database, with obvious downsides that many people refused to notice.

What storage engine are you referring to? I've been using MySQL since the earlier 2000s, and your description doesn't match MyISAM (which uses the filesystem cache for data blocks, only using its own caching for indexes) nor InnoDB.

MyISAM does offer amazing write performance, at the cost of not being crash-safe. But given its reliance on the fs cache, it would be unusual to describe it as "in-memory storage". Anyway, the GP was talking about compromises that "can't be easily changed without breaking the ecosystem", which doesn't describe MyISAM either -- MyISAM is basically deprecated in modern MySQL deployments.

>Postgres only adopted SQL in response to mSQL's popularity.

What, then what did it use before?

It had two methods of retrieving data. The first was what was known as a navigational database: https://en.wikipedia.org/wiki/Navigational_database

This was something that was popular back when you used tape to store data. A record contained navigational references that told you where the next record was, allowing you to fast forward the tape to that position without reading everything in between.

The 2nd was POSTQUEL, which was a QUEL language: https://en.wikipedia.org/wiki/QUEL_query_languages POSTQUEL was the preferred and recommended way of querying postgres.

These were both on their way out even in the early years of Postgres (mid-80s). Navigational databases are from the 60s. SQL was invented at IBM in the early 70s, and adopted by Oracle and DB2 in late-70s, and by the mid-80s they had gained significant market share and most databases that used QUEL had moved to SQL around that time.

I've been involved with postgres from before it had SQL, at Ilustra we added SQL in a parallel effort to the postgresql open source project and I'm not sure I'd agree with your points:

Specifically, POSTQUEL was not really obsolete, it's just that the market had picked SQL by then. Compare the fates of git versus Mercurial. Mercurial is not obsolete, but git is the clear popularity winner.

Second, I don't recall at all a navigational interface to postgres and the idea that this was a primary access method in postgres is quite surprising. Do you have a reference for this? I'd be very curious to read it.

Sure, of course QUEL still worked... but Postgres was one of the last databases to use QUEL. It was pretty clear SQL won even when Postgre was just getting started (mid-80s). The Ingres project (that developed QUEL) ended by the time Postgres started (obviously, since the name is Postgres = Post Ingres). Ingres influenced Sybase, which switched to SQL in the late 80s. Ingres Corp even switched to SQL in the late 80s. What other databases using QUEL existed in the mid-90s?

POSTQUEL was definitely the primary interface to postgres. The navigational interface was an early Postgres feature. See page 3 and page 10 ("fast path") of Stonebraker's 1990 paper on the implementation of postgres: http://db.cs.berkeley.edu/papers/ERL-M90-34.pdf

I agree that commercially SQL was winning by the time postgres got started, but I think it was not yet obvious there was room for only one query language. I also suspect that the early implementation shared a lot of Ingres code as the focus was on the "post-relational" topics. Building SQL would have been a distraction. Perhaps also Stonebraker did not want to Osborne Effect Ingres. I'm sure even early Oracle marketing would have had fun with "even the founder of Ingres agrees QUEL is obsolete". Finally there was a lot to like in QUEL and SQL has quite a few warts.

Thanks for the link. Fast path was mostly about the function manager and only incidentally about data access. As in, "if you call all these functions that are part of the infrastructure, you can get at data". But that's not really a navigational interface (get first of set), more a side effect of exposing the internals via the function manager.

I started at Sybase in 1988, there were a lot Britton Lee alums, who had also worked on the ingres project as well. I ran in the the rest at Illustra in the 90s.

This.

One also has to remember that long ago there was no serverless, no VPS/cloud/ec2/linode instances, no heroku/beanstalk/appengine, no docker/vagrant - to get going fast you had $x/mo shared hosting and [W|X]AMP for development, and this horrible thing called FTP. The only reasonable way to host a website cheaply was on some terrible shared hosting server which only [reasonably] supported php and mysql (okay I lie there was a cgi-bins and mod_perl) otherwise you'd be looking at dedicated servers.

That's a broad generalization of PHP "coders"

I think it's safe to say we've all seen good code a crap code -- regardless of language.

It's not the wand, it's the magician.

(nb: I've been building software for 20+ years, I use PHP (among others) and knew JOIN and types in PG before I ever saw PHP, I cannot be the only one)

Yes, but the proportions between "bad", lets rather call them inexperienced, "coders" and experienced coders varies strongly in different ecosystems.

PHP and JavaScript both democratized programming, as BASIC did years before, but that also meant that more tutorials and howtos for PHP were written by people relatively new to programming compared to say howtos on Haskell or C.

It's an "Eternal September" effect that never really wears off. I'm growing increasingly concerned that this is descending upon Python now. The internet as a whole is a wide enough system that it was able to withstand its Eternal September (though it's arguable that newsgroups weren't), but any specific language ecosystem, I fear for the moment it hits "mainstream".

You're not the only one, but you are one in a thousand.

When I work with Python, Java, C++, bash, SQL, or Javascript in a team I feel mediocre at best. I can get around, but I know that I'm easily outclassed. Contrast with PHP, where I'm almost always the best dev in the room. True that I have much more experience with PHP than in the other technologies that I've mentioned, but PHP has the dual curse of having a very low bar to entry, and seems-to-work enough that most "PHP coders" never feel the need to progress beyond the most basic of understanding.

When I meet new devs I deliberately try to postpone mention of PHP as long as I can, to avoid attaching myself to the tainted stigma that PHP has acquired.

Is dollars not as floats just a MySQL issue? I am not sure why you wouldn’t want to do this.

No, that is not just MySQL. Just do a little googling and you'll run across enough information on why it's bad practice in any language or database. You need to avoid converting dollar values to a floating point at every place in your stack otherwise you will introduce floating-point rounding errors.

It's not just the data type that it's stored in, if you convert it to that at any point, you lose the exact precision and introduce those errors. It can add up to some serious differences over a lot of values.

I did some analysis on my database to see if I had stored any of those numbers is floating points, what would the amount difference be in total, and what would be the absolute difference for any single account. it was awhile ago, but I believe that the absolute difference per client was around $20 or $30, but the absolute difference per account within that client was as high as $100. Obviously that's pretty damn unacceptable.