MySQL (and thus MariaDB I presume) and SQLite seem to be pretty poor in supporting these "new" features.
SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?
SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?
I am not one of those people -- I think a good database system (like postgres) can make many things dramatically simpler.
It almost feels like a worse-is-better story. As a programmer, PostgreSQL is much better to work with; more tools, better EXPLAIN, more features, more types, more of almost everything. But to use in the heat of battle, it's less clear-cut. PostgreSQL's replication story is complicated. MySQL master-master replication is fairly easy to set up, and if you use a master as a hot failover, it all mostly just works; when the primary site comes back up, it resyncs with the failover. PostgreSQL has a lot of different replication stories - without a strong central narrative, it's hard to gain confidence.
Postgres on the other hand, has always tended towards making things work well (a step that mysql often skips), and then work quickly thereafter.
A lot of MySQL acolytes say Postgres is slow because, unlike MySQL, it doesn't ship with unsafe defaults. MySQL doesn't just allow you to do dumb things, it starts off with many of those settings as the defaults.
To me, the real problem is that people likebarrkel exist. He doesn't know what he's on about, but he likes MySQL. Most of wheat he wrote is flatly false, but he said it confidently. And he's employed someplace that probably uses MySQL.
MySQL got adoption for two reasons:
1) it used to be easier to install; and
2) it has unsafe defaults that mean if an idiot runs a benchmark, it wins.
That's it. That's how it won market. After that, it was network effects, and nothing else. MySQL is a turd. It requires substantial expertise to use MySQL because it is such an awful and dangerous tool. It slows you down as you get better. But most of the people who use it don't know any better, or (like barrkel) they spew nonsense that is the opposite of reality. So it wins.
Network effects suck.
And the issues around "safety" stopped being a concern for most developers a decade ago when the ORM was invented. So this idea that you need "substantial expertise" to use it is simply ridiculous.
You're putting words in my mouth that I didn't say. I don't like MySQL. I prefer PostgreSQL. And I have had a few rough times optimizing some queries in PostgreSQL, whereas I've had fewer such bad times with MySQL, despite using it more often. It's anecdata. Take it for what it's worth.
Time sinks in MySQL have come more from its crappy defaults, from its bizarre error handling (or lack thereof) in bulk imports, and most recently, a regression caused by a null pointer in the warning routine.
If I were working on my own project, I'd probably go with PostgreSQL and figure out the replication story. But I'm not. I do use PostrgeSQL on my personal projects.
(If there was one feature I'd add to PostgreSQL, it would be some means of temporarily and selectively disabling referential integrity. Not deferring it, not removing and readding foreign keys, just disabling. The app I work on does regular 10k-1M+ row bulk inserts, usually into a new table every time (10s of thousands of tables), but sometimes appending to an already 100M+ row table. It would be nice to have referential integrity outside of the bulk inserts, but not pay the cost on bulk insert.)
set session_replication_role='replica';
SET CONSTRAINTS ALL DEFERRED
Then do your inserts, followed by whatever work needs to be done without referential integrity, in the same transaction.
Seriously? you're getting worked up about a database and you conclude that it would be better if certain people didn't exist?
I'd like to remind you that this is HN, not the Linux kernel dev list. For all its flaws, HN still is about civility. You just wished someone out of existence over a database, can you please stop that? Grow up!
It makes for an uncomfortable community.
He created that account to "go after" me with a lot of vitriol. It's a bit mysterious though. Why would someone work themselves up into such rage over a database? Normally, you'd explain this as teenage frustration or something. But he's clearly very unhappy, lashing out.
More mystifying than offensive, since it's impossible to take seriously. How can you deal with these people.
But from complete strangers who are hammering profanity and abuse into their keyboards as hard as they can, that is quite unnecessary and doesn't make for a nice community. If someone was speaking the things some people on here post to my face, I'd be incredibly offended and they wouldn't be the sort of person you'd want to work with, hang around with or even live next door to. But they don't seem to mind typing it????
These posts rear their head in C++ articles and anything to do with OSX it seems. Really disappointing.
I suppose the best way to deal with it is just detach from it for a while, use another forum, go outside, look at the birds or stroke a cat or something.
There's something very therapeutic about picking up a fluffy cat (I have 4 British Shorthairs, great for fussing) or simply watching sparrows and small birds go about their business in the dust or seed feeders. They continue working without worries, but work hard to survive still and seem happy about it (as far as a bird can be happy). I find it a contrast to us sat in yellow-lit offices with deadlines, stresses, possibly incompetent managers/colleagues and concerns about our existence/paying bills etc.
The features that Postgres already has can be solved in MySQL, but take some work. Sometimes they require me to use temporary tables and pre-calculated summary tables.
MySQL's speed is only variable by the queries that are run against it. MySQL is "simple enough" for developers to write queries for it and in 10%-20% of those cases, those queries could be a bit more optimal or the data model could use some more tweaking. In terms of getting things done, you can do a whole lot before needing someone like me to come along and tweak things.
A performance audit from someone like me every 6-9 months after your company's website has been in use for >3 years can be perfectly fine.
Also the main reason MySQL won out back in the 90s was because they had better documentation and answered questions on their forums faster.
Yes, that sums up MySQL pretty well.
And then one day, one of your masters segfaults for no discernible reason. And when you restart it, the replication process (or even the InnoDB recovery) fails to resume with a generic error message that you can't find any useful information about in the mess that MySQL calls "documentation".
That's when you realise that "it all mostly just works" is really not what you want from your database.
To be clear, our master/master replication is strictly read-only at one site and read-write at the other site, and never read-write simultaneously. We have yet to see issues in production under fairly hefty write load, and we've failed over numerous times, and back again. But it's only been 10 months or so.
Because for 99% of the MySQL user base there isn't a need for these features.
Those using MySQL at scale are using them as dumb key-value stores with horizontally sharding. Those who aren't typically are using them with an ORM and so they aren't dealing with the database at the SQL layer.
Everyone else who is manually writing SQL generally was on or moved to Oracle, Teradata, SQL Server, PostgreSQL etc anyway.