100% naive question: why is mysql and their similars so popular then?
Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language.
The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does not know the difference between character encodings. What does he care what MySQL does, he knows that he can put strings in if he calls them VARCHAR, and he can pull the right one out with a WHERE. He cares not enough to check that user bios fit in his VARCHAR, and when he learns to JOIN he does not understand which constraints to put in the ON clause.
I do have nicer words for the L and A in the stack.
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.
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.
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.
What, then what did it use before?
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.
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.
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
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.
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.
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)
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.
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.
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.
Because of those two advantages (that really only lasted a couple of years), a small ecosystem developed around it. This was the database you used to develop interactive websites in the early/mid-90s.
Then MySQL came. MySQL extended mSQL, and kept compatibility with it's ecosystem... and people switched from mSQL to MySQL.
Postgres also existed in the 80s and 90s, but it used POSTQUEL, was harder to setup, and had higher system requirements. Not a lot, but enough to discourage use on low end computers. (Remember, Postgres at the time was being developed on university servers, where they had plenty of system resources.)
They did address these points in postgre95 (in fact, Postgres adopted SQL as a response to mSQL popularity), but by then it was too late. mSQL and MySQL already had an ecosystem.. and in that brief moment, they lost the race.
The idea that it was slower stuck around for many years, even after the improvements in computers, and (less so) optimizations in Postgres made it no longer true.
Postgres didn't start being considered again until Go, RoR, et al started to become popular.. displacing the PHP ecosystem. And the biggest reason you see MySQL around so much today is that there's about 25 years of legacy code built around it.
Edit: If you're curious what POSTQUEL was like: https://en.wikipedia.org/wiki/QUEL_query_languages
Both for beginners where it was packaged with PHP and available with basically every webhost, and for advanced users that took advantage of the simple and reliable replication. There were a lot of problems with it, but the novice users didnt run into them and the advanced users knew how to deal with it. These decades of mainstream use carry a lot of momentum and many large companies continue to use it because they've always used it (like Youtube for example).
PostgreSQL only recently became much more usable after v9.5 and is now being recognized and growing in popularity.
I think I first started using it at v6 or 7. I liked it could I could run it from a .bat file and spin it up for tests without installing it.
It always worked well, but there are many newer developments around operations and scaling that finally made it much easier to run. Even now there are some limitations with logical replication and horizontal scaling but the main system can easily be installed and running in a few commands to serve most users just fine.
It should be noted that the rise of Docker containers and K8S has also helped greatly in letting users run all kinds of software with minimal work.
Some of the MySQL backup/restore stuff is a lot more straightforward (innobackup vs WAL shipping).
MySQL replication with binlog_pos was a total crapshoot. The reason innobackupex exists (and anything from Percona, to be honest) is because MySQL's default options were so unworkable. The Postgres equivalent is probably something like barman.
MySQL replication was workable before GTID. GTID simply made things a lot easier. GTID was hard to work into existing DB's at places like Facebook, but it we managed to get things going without a DBA in several multi-TB databases (sans downtime).
GTID is over 4 years old at this point. That's not relatively new in the tech world. Especially with the pace of things like Kubernetes and containers.
There are 3rd party additions to Postgres, too. pg_rewind was written by eBay (?) to address the obvious shortcomings of repointing primaries and replicas.
To be fair, PostgreSQL was noticed way before, Estonia's e-nation website was built on it (even the logic!) and I can't honestly remember when that thing has been down. The UI would need a bit of refreshing but I have the feeling if they're going to update it they're going to replace it all with something shittier.
Early on in my career, the biggest selling point for Mysql was the excellent web admin tool "phpMyAdmin" - it really helped get applications off the ground, since the core of most modern systems is the data model. Users could modify data, without your needing to create a UI for that use case.
It conveniently used the same stack as the rest of the software, but I remember spinning up a phpMyAdmin instance even after moving away from PHP as an application language.
Postgres still doesn't really have anything like that! The closest that comes to mind is Django's Admin tool.
Basically a port of phpMyAdmin to postgres. I used it for several years around ~2003-5.. it's nearly identical.
That said, and with a disclaimer that my own bias leans the other way (as I have a lot more MySQL experience than Postgres experience) although I try to be impartial about these things:
* Spanner: I've heard this second-hand and might be incorrect, but as I understand it, Google's original internal MySQL team was shuttered in ~2009 and some of those folks may have transferred over to the Spanner team. I wouldn't say that Spanner is particularly based off of MySQL anyway though, but I don't have enough familiarity to say that conclusively.
* Aurora: AWS now offers a Postgres-based Aurora as well. As for why the built the MySQL one first, I'd assume that was likely a business decision based on mysql-vs-postgres RDS usage at the time.
* Those two aside, there are definitely major products based around Postgres. AWS Redshift is one example. Or look at CockroachDB, which chose wire-protocol compatibility with Postgres. (There are some examples the other way too; e.g. TiDB, which chose wire-protocol compatibility with MySQL).
* Regarding why MySQL has historically been popular, there are a lot of factors. I'd say the biggest one for large users has been replication; some of my older HN comments delve into that more, https://news.ycombinator.com/item?id=16880663 for one example. For smaller users, ease-of-use has been important. The other discussions in this thread hint at this, especially around silly simple things like the "exit" command.
It also used to be more scalable, because PG's replication story used to be poor. This is why Google and (I assume) Amazon used MySQL internally. This is all a long time ago, but I suspect this is the reason more people are familiar with MySQL than Postgres at these companies. Keep in mind that Spanner was developed and used internally at Google long before it was publicly announced.
Amazon actually uses a lot of Oracle with some scattering of MySQL.
But maybe at their scale even Oracle can be made tolerable.
That, and they have some serious leverage in negotiations.
(I used to deal with Oracle DBs a lot a few years ago but thankfully not anymore.)
Because MySQL for some reasons is more accessible[1] to beginners, juniors, average developers, and other people who may prefer a lower barrier of entry over correctness/safety (e.g. full-stack devs, managers, people who don't have much time); who form the bulk of the community combined.
It's the same for languages, frameworks, and other tech; as several people noted already.
[1] Or maybe "was"; in which case the answer becomes "historical superior accessibility".
Once the herds picked MySQL (and php) the economies of scale started, and advantages grew. Meanwhile, the proper and safe competitors were generally disgusted (drama word but not entirely an exaggeration) but who cares about them, maybe even a weird positive to the new breed of web devs who were inventing the future.
My preference is MySQL purely because the syntax is easier, or at least consistent with what I learned at uni in the late ‘90s.
I also like MySQL workbench, although I’m sure there is an equivalent for PG I haven’t needed to look for one in recent years.
If PG allowed me to use the same syntax as MySQL I’d probably switch, purely because experts and those with more experience than I generally prefer PG for a whole lot of reasons.
EDIT: I should have googled first before replying. Looks like the syntax is near enough to identical for my simple needs and pgadmin will do what I need. I’m not sure why I had to learn some other weird syntax in 2012 for postgres, perhaps it was just shortcuts. Either way I’m going to seriously consider migrating now. :)
Also, Postgres' documentation is really fantastic. I bought a book on Postgres when I switched, but really I could have just stuck to the online manual and saved the thirty bucks.
I like many things about MySql [as you imply, syntax a bit more verbose but easier to remember - e.g. "SHOW TABLES" vs "\d" ; I also like the nonstandard behavior of MySql 'GROUP BY' as it makes good sense to me ]-- but overall I'm really enjoying the flexibility of Postgres. PGadmin4, on the other hand, leaves much to be desired -- I generally stick to psql.
It's a shame, psql is awesome once you know enough to use it, but GUIs is how people start using RDMSes, and it's about the only thing that MySQL is clearly superior to PostgreSQL.
PostgreSQL's approach isn't the most intuitive but it's well documented and very powerful. A few aliases could probably help, it's good they've added `quit`/,`exit` this time around.
postgresql still seems to have 2 ways, but the majority of tutorials will demonstrate the "become the postgres user and add unix-level accounts". Things like having a unix-level "adduser" program that manages pg stuff is... odd to me, because it's not 'self-contained' in the app. It's more system-level admin stuff I need to do or be aware of.
Whenever I bring this up, I get push back that I'm doing it wrong, and that of course you can just create user accounts from within pg itself via psql, and ... all other sort of attendant feedback. This approach had not seemed to be the norm/default approach, nor the one that was in multiple postgres books I had 10 years ago.
I use pg on some projects, but am no expert in it.