50 ms·
Leaving MySQL
- RustyRussell 5y agoOk, I have to admit I LOL at the bottom of this critique of MySQL code quality to hear he was going to work on Google Chrome!
- edmundsauto 5y agoWhy is that? I admit I know little of chrome code quality. But either they choose to work on a high quality product, or they choose to work on a product whose quality they can improve (low hanging fruit). What is LOL worthy?
- RustyRussell 5y agoI've heard horror stories from Google engineers, but I don't think they're public so I shan't quote them. I am tempted to put a reminder in my calendar for a few years' time to see where this engineer is, though...
- shp0ngle 5y agoI never worked on Chrome directly, but I looked into the Chromium code sometimes when debugging some weird JS issues. What strikes me first is how much code there is… There are tons of code, some things copied multiple times, because Chrome nowadays does a lot of things; basically it’s an entire operating system, which accesses USB, runs assembly code, runs WebGL, basically all. And it’s all in C++, and very verbose Google C++. But in the end I always found what I was looking for there. And I can’t say how good or bad the C++ is, as I’m not C++ dev. Just there is a LOT of it.
- cblconfederate 5y agoI wonder if there is any piece of software that actually works and yet developers don't call its source code a shitshow. I understand code as akin to biology, full of repetitions and garbage DNA because these things are live and evolve along with the environment, and cannot be "pure" for more than a few months. C++ is very versatile, but i bet that using a verbose makes it much easier for newcomers to find their way around with the code compared to using an uber-abstracted style.
- RustyRussell 5y agoThe core of the Linux kernel, in my experience. It's regularly refactored by grizzled oldtimers, which helps as standards rise.
- vinkelhake 5y agoChrome is almost all open source and Sesse has worked at Google before. I'm pretty sure he knows what he's getting into.
- ksec 5y agoDoes any one know when is MySQL 9.0 coming? It has been a unusually long time between a major version or a dot version release. MySQL 8.0 was in April 2018. Compared to Postgres which has been shipping features after features.
- tpetry 5y agoIt‘s not planned. They are shipping new features in patch releases, yes, you heard correct. They had the genius idea to stay at 8.0.x and still add new features with every release.
- vanusa 5y agoRare, and utterly refreshing for its frankness. Let's hope this person does well wherever he goes in his career.
- steve_adams_86 5y agoIt is incredibly hard to improve something that your user base doesn’t think needs improving. That part really stands out to me. When I first moved to Postgres I was fairly reluctant - my impression was that it was some arbitrary flavour of sql, and since I was used to MySQL (the only sql database I’d ever used) it seemed incredibly unnecessary to bother with. I imagine there are tons of people out there like I was who would rather let things be. Since the first few weeks I don’t think I’ve ever chosen to use MySQL again; Postgres totally changed my career for the better.
- agumonkey 5y agoCultural inertia is a strange issue.
- degenerate 5y agoI have very strong feelings that a universally pronounceable product name is ESSENTIAL to wide adoption. PostgreSQL never rolls off the tongue, and it's not fun to say.
- Alacart 5y agoAgreed, though most people just call/pronounce it "post gress". Wish it had a better name though.
- whimsicalism 5y agoi both pronounce and spell it postgres. is that not a formal thing?
- eyelidlessness 5y agoIt’s how nearly everyone pronounces it, and for the most part spells it in common usage. The formal thing IIRC is that the name is PostgreSQL as it was originally a successor to Ingres, and added SQL support later.
- 5y ago
- leetrout 5y ago> MySQL is a pretty poor database, and you should strongly consider using Postgres instead. Wow, right on the chin with that one. > More jarring were the people who insisted everything was OK (it seems most MySQL users and developers don't really use other databases) I only have anecdata of my usages of MySQL and Postgres but I swear people that cut their teeth on MySQL and have never used Postgres just don't know what they are missing. Yes Postgres can be slower out of the box and yes Postgres has worse connection handling that usually requires a pooler but the actual engine and it's performance makes it worth it in my opinion.
- johnmato 5y agoI have worked with MySQL and every database has its ups and downs.
- toast0 5y agoVaccuuming sounds much more pleasant than dealing with innodb without file per table?
- leetrout 5y agoYes I would rather have the ability / requirement to vacuum than have unused space in large table files especially one large innodb file.
- toast0 5y agoUnless MySQL changed it since I last looked, the one large innodb file is a big PITA, but otherwise you can do OPTIMIZE TABLE to compact tables; apparently this can sometimes be done in place, but I'm used to it writing to a new file so you'd need room for old and new; and at least for MyISAM locks the table for the whole time, so I hope you've got a way to manage that too, but that's about the same as VACUUM FULL.
- xd 5y agoInnodb can be split into individual per table files and has had this ability for years.
- danielrhodes 5y agoWhat a burn - but not surprising given MySQL's history and things like MariaDB or Percona which exist to fill the gaps. My impression is that many people will choose Postgres over MySQL when starting a new project as it progresses so quickly and has a far richer set of features.
- smackeyacky 5y agoOr just use Auroradb on AWS, with maria as your test setup.
- tflinton 5y agoI've had too may anecdotal failures from replication, innodb consistency corner-case issues and finally being adjacently associated with Oracle killed it for me. Since moving to Postgres, I've been very happy.
- gigatexal 5y agoWow: “ MySQL is a pretty poor database, and you should strongly consider using Postgres instead.1”
- deleted 5y ago[deleted]
- chris_wot 5y agoDoes MySQL still have crazy Unicode collations?
- brightball 5y agoYes. I just spent a couple of months writing and testing some conversion code to update them for a client.
- whoknowswhat11 5y agoI used MySQL ages ago. It was pretty fast out of the box. I never needed to vacuum. You could get up and running with Upsert and connections without trouble. It did feel hacky, but don't discount that it mostly worked for a lot of use cases. It always reminded me of software developed with use cases in mind (vs theory). That's actually a complement (ie, full joins are rare, left joins more common - so MySQL was better at left joins). Going to postgre - much pickier. I found it slower out of box. You couldn't just throw tons of connections at it (ie, connection buildup / teardown felt slower). I had issues initially with quoting and capitalization etc. That was a long time ago. Now I enjoy postgres and haven't touched MySQL, but there is a real history where MySQL was the database you could get going with pretty easily (I was pre-Oracle buyout).
- brightball 5y agoThe connection story is the primary reason that PHP + MySQL was a big thing. The PHP model of spinning up on every request depended on fast and lightweight connections to the DB. This was the one area that MySQL really excelled IMO.
- jake_morrison 5y agoTo some extent, Postgres just follows the path that Oracle did to optimize performance. To see what Postgres needs to do next, just look at what Oracle had to do. Postgres forks a listener process for each connection, which is relatively heavyweight. Oracle used to do that, but implemented a separate multi-threaded listener process for performance reasons. MySQL is multi-threaded from the beginning.
- DaiPlusPlus 5y ago> Postgres forks a listener process for each connection [...] MySQL is multi-threaded from the beginning. I thought on Linux that forking had the same cost, more-or-less, as spawning a new thread?
- 5y ago
- doctor_eval 5y agoI think they are right that many people who swear by MySQL have never used another SQL database. When MySQL hit the scene at the start of the whole LAMP thing, PG was much slower in some use cases than MySQL, and proprietary databases were super expensive. There weren’t too many options. PG was still fairly rough around the edges. Of course I also needed transactions, and they didn’t come with MySQL - that would hurt performance apparently - so when I was forced to leave Solid due to HP’s acquisition and subsequent pricing hike, PG was the only option for me. But most developers didn’t need (or didn’t know they needed) transactions, and SQL was the tech de jour, so off they went. I tried to like MySQL, but the weird not-quite-SQL syntax, lack of transactions and this _weird feeling I got_ put me off.
- bradleyjg 5y agoAt that point your options were either a dog slow database (PG), a database that randomly lost data (MySQL), or big money for Oracle. The first thing everyone did when they got their first VC check was spring for Oracle licenses and Sun boxes to run it.
- gip 5y agoI'm curious - when what true? In the 2000s? Before? I joined my first startup on 2010 and we had a managed PG database that was good enough for us I reckon.
- headgasket 5y agoShameless plug: check out [Rosettable https://github.com/francoisp/rosettable https://github.com/francoisp/rosettable]. Brings Postgres triggers and notifies to your MySQL schema, with it you can organize a smooth transition. Using MySQL_fdw you can use your current MySQL schema with the above you can know when a client interacted directly with MySQL; all the while you add some nice jsonb and Plpgsql and transition out of MySQL incrementally. Don’t rewrite, write onward
- chrsig 5y agoThis strikes me as pretty childish. If it had read more like "I left because I was frustrated with code quality", and actually took ownership of the feeling, that'd have made a big difference. It reads more like "i left because the code sucks, everyone around me is dumb, and our users are sheep" I don't think anyone's going to suddenly switch to postgres as a result of this blog post...so really, what's the point? Maybe it'll get more attention on the systems he called out? Probably not. The post just reads as bitter, mostly about the mindset of other contributors, but doesn't outline what they tried to do to change the hearts and mind of those contributors. Or put another way: what did this person do to lift up all of these people? Deriding doesn't actually help anything. If the goal is just to vent that's fine, but there's really not much else to see here.
- jimmyed 5y agoYeah, it's like those new age loud devs on twitter. Slighting a serious software project with generic statements without any nuance is not cool.
- sgift 5y agoHe worked for years on MySQL (writing the executor used in MySQL 8.0 and the upcoming join optimizer) and came to his conclusions based on that time with it. Can you provide comparable knowledge about MySQL to support your accusation that he wrote "generic statements without any nuance"?
- gigatexal 5y agoI dunno. I think the author makes some compelling points. Chief of which is the bad code quality. At least the bit about being unable to do full outer joins without a full rewrite. If the code base got so unwieldy that it made adding features tough all these years after it was started that’s a knock on both Oracle leadership and the project itself which I think is grounds to leave.
- chrsig 5y agoI don't think he's wrong from a technical assessment, but most of the problem that he was experiencing was a social problem. > More jarring were the people who insisted everything was OK (it seems most MySQL users and developers don't really use other databases) i.e., the problem wasn't that the executor was bad, the problem was that everyone thought it was ok And maybe they're not a people person, and trying to enlighten isn't what they signed up for -- all good -- I for one certainly don't want anyone working a job that they're unhappy with. But the post is littered with putdowns -- > Coming to MySQL was like stepping into a parallel universe, where there were lots of people genuinely believing that MySQL was a state-of-the-art product. At the same time, I was attending orientation and told how the optimizer worked internally, and I genuinely needed shock pauses to take in how primitive nearly everything was > Don't believe for a second that MariaDB is any better. Monty and his merry men left because they were unhappy about the new governance, not because they suddenly woke up one day and realized what a royal mess they had created in the code. I guess I've just seen this attitude enough where it's boring. Shock pauses, very primitive, bad code. Got it. Moving on...
- aexl 5y agoSomewhat offtopic, but sesse.net runs a Stockfish instance with the (probably) deepest analysis of live chess games: http://analysis.sesse.net/ http://analysis.sesse.net/ I visit it daily for the Chess World Championship match.
- mmaunder 5y agoAh refactoring and rewrites. Always seem like such a great idea. And can destroy massive companies and projects. Was it Marc Andreessen who wrote that awesome post on how the rewrite of Netscape 6 seemed like a great idea but failed to consider all the little wisdoms built into the current version? It’s impossible to find now with all the noise around him. Production software more than a few versions old is a living breathing thing that has had the hell beaten out of it by QA team and users and survived repeatedly by having passionate devs make it stronger. What it needs is love, not pedants. Some of the most used and popular software in the world is fugly beyond belief but it works, is maintained and loved and continues to defy newcomers.
- 1123581321 5y agoSpolsky wrote about Netscape, or at least that was how I originally encountered the story. https://www.joelonsoftware.com/2000/04/06/things-you-should-never-do-part-i/ https://www.joelonsoftware.com/2000/04/06/things-you-should-...
- deleted 5y ago[deleted]
- lovecg 5y agoThat might be true of some complete rewrites, but refactoring - when done right - improves all the good stuff like composability, readability, and simplicity, without changing any logic (unless you count fixing bugs which it will likely uncover). There’s also a bit of learned helplessness on some teams averse to refactoring, as in “things were always done this way, don’t touch it if it works, the opportunity cost is too large, etc. etc.”. In my experience, this sentiment evaporates when presented with the results of properly done refactoring - when it becomes obvious how much faster the team can move without compromising on quality (and frankly the work just becomes more enjoyable).
- edmundsauto 5y agoWouldn’t any decision - when done right - improve the good things? I tend to look for situations (since I am never an expert, just a person trying to get the job done) felt like I k ow ahead of time what “done right” means.
- dvt 5y agoHonestly, the post feels a bit bitter. > ...let me point out something that I've been saying both internally and externally for the last five years... Life is short, don't get stuck in a job/relationship/situation/etc. you hate for half a decade. IMO the irony is that he's moving to the Chrome team, which has been an absolute shitshow. Woohoo, now you're going to work on fantastic ways of screwing up the web, inserting ads where we don't want them, tracking users against their will, and banning extensions on the Chrome store. Interesting technical challenges, indeed.
- dasil003 5y agoJudging by the focus on internal code quality, I suspect the author is not too concerned about the real-world implications of the overall product.
- weq 5y agoAdmittedly i dont write much C these days. But as someone who has integrated chrome/cef across platform (desktop/mobile) i would say that ive never seen such important piece of software have so many edge cases, regressions and gotchas. The CEF guys are in a constant struggling keeping there implementions stable; it takes a company the size of of MS/Apple todo anything more with it. MS is spending 1-2yrs developing there webview2/Chrome99+ macos integration - even though edge already runs on macos.
- draw_down 5y agoI think it’s ok to say one database is pretty good and another is not very good.
- pkrotich 5y agoHis site would be loading waaay much faster if he was using MySQL even with HN traffic! Joking aside - I see MySQL as an entry level database. There’s time and place for it - just like PHP. Using pq or whatever is better doesn’t automatically make your product a success.
- soheil 5y agoIf Steinar is responsible for removing query_cache in 8.0 I'm glad he's leaving the MySQL team. Sure, MySQL is not the perfect database heck I'm willing to even say it's not good or efficient, but it is extremely well supported across toolchains and programming languages. It just works and it works pretty darn well for most applications. I've built several startups on top of it some even exited.
- radu_floricica 5y agoThere's a huge difference between "you don't need the best infrastructure to build a successful company" and "I left because the place had a culture that refused to acknowledge major product shortcomings". They work in different domains.
- paavohtl 5y ago> extremely well supported across toolchains and programming languages. It just works and it works pretty darn well for most applications The same holds true for PostgreSQL.
- mmaunder 5y agoPlot twist: MySQL runs most of the web.
- bob1029 5y agoStrange. SQLite runs most of the world but I haven't seen it mentioned in this thread once. 90%+ of software products could easily get by with SQLite as a data persistence mechanism if the developers can be brought to a state of humility regarding the scale of their projects. I'm seeing comments in here talking about 200 vs 20 milliseconds. You know what's even faster? Putting the DB engine in-proc and dropping the network from the equation altogether. With NVMe storage, you can reliably complete SQLite transactions in tens to hundreds of microseconds with the appropriate journaling mode enabled. You'll never get this kind of latency bound from something that lives in another process or computer.
- lbruder 5y agoAnother big open source DB I'd like to hear about more often is Firebird (https://firebirdsql.org/ https://firebirdsql.org/) which was forked from the Interbase code released by Borland (don't remember how they were called at the time). Four choices of connection model (process-by-connection, thread-by-connection, some weird mix, and in-process), full ANSI SQL, runs on all major platforms, and uses single file databases. Seems to be the best of SQLite (inmem, single file), MySQL (lots of connections fast) and Postgres (standard, robust) to me, in a single package. But for whatever reason it seems to be completely off the radar on HN...
- zmmmmm 5y agoI am curious how people feel about wisdom and ethics of acting in ways that are actively hostile to the interests of a previous employer immediately after quitting? Regardless of the technical facts, unless there is a very strong ethical argument in favor (for example, say, the employer is outright lying in a highly fraudulent way and customers need to know) .... I find this sort of behavior to be slightly unprofessional. But I am curious if others feel the same ... and does it make a difference that it is Oracle?
- devwastaken 5y agoWhy does "unprofessional" matter here? They're not on company time. If it's accurate, and that's their experience, that should be shared. I'm tired of a culture that thinks professional means advocating against yourself and your peers. Talk about employers, talk about customers, talk about salaries, if a company exec is bad to work with, talk about it. That gives insight for other devs so they're not finding themselves in the same position.
- deleted 5y ago[deleted]
- vanusa 5y agoWhy is it seen as "acting in ways actively hostile" to one's previous employer to... simply speak the truth, as one sees it, about the nuts and bolts the work that one actually did there -- and the quality of the products that emerged as a result? I find this sort of behavior to be slightly unprofessional. I find it highly professional, in the sense that he was being 100 percent honest -- and true to his craft. And not in the least vindictive or spiteful in regard to his previous employer.
- deleted 5y ago[deleted]
- patrickdavey 5y agoI'm super tempted to move from MySQL to postgres, but, we are currently self hosting and MySQL is breeze to run. My only experience with postgres is running a brew update and it completely borking the database and having to track down some cryptic post in order to fix it. Also, as pointed out elsewhere, MySQL seems not to require vacuuming or anything else. It's the unknowns of moving which make me nervous.
- throwawayboise 5y agoModern postgres vacuums automatically. It's as easy to set up and run as MySQL for simple scenarios.
- dirkt 5y ago> running a brew update and it completely borking the database I also ran into this, but to me it more looks like in issue with how Homebrew does Postgres. Also, keeping different versions of Postgres around with Homebrew is a bit of a PITA.
- Bayart 5y agoFor self-hosting one instance, there's little difference. What matters more is how well the tooling you use with with your DB supports it. Recently I've been on a .NET 6.0 project and the Entity Framecore Core MySQL provider was broken for what I was doing. The one for Postgres worked fine so we chose that. >My only experience with postgres is running a brew update and it completely borking the database and having to track down some cryptic post in order to fix it. I find containers work wonders for databases, whether it is for using specific versions, segregating instances, keeping your system clean etc. I keep at least one of MySQL, Mango, MSSQL and Postgres running at all times. I would never install one natively.
- didip 5y agoIt has been so long since I used MySQL (and that’s for the better). Does MySQL still break when running ALTER TABLE on a huge table? One thing I don’t like about MySQL is that it has too many foot gun.
- zinodaur 5y ago> Does MySQL still break when running ALTER TABLE on a huge table? Yes it does. We've been stealing bits from old fields to avoid running alter table IDK if postgres does it any better though, a thing I've noticed from maxing out databases is that many features just don't work any more
- throwawayboise 5y agoCreate the new table. Dump and load from the old table. Drop the old table. Rename the new table.
- jean_rwgps 5y agoWhile you tell your users to come back in 2 hours?
- shlomi-noach 5y agoOnline schema change solutions have been around for over the past decade and are commonly used to ALTER TABLE with no downtime (or with minimal interruption) on the largest deployments of MySQL today. The two most common solutions are pt-online-schema-change and gh-ost, and if you are running MySQL today and still running direct ALTER TABLE suffering outage, then you're in for a pleasant change. On top of that, most MySQL ALTER TABLE operations with InnoDB tables support non-blocking, lockless operation as well. My main concern with these is that they're still replicated sequentially leading to replication lags. MySQL is also slowly adding "Instant DDL", currently still limited to just a few types of changes. Disclosure: I authored gh-ost (at GitHub), oak-online-alter-table (the original schema change tool) and am a maintainer for Vitess and working on online schema changes in Vitess. Links: - https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc... - https://github.com/github/gh-ost https://github.com/github/gh-ost - Past HN discussion: https://news.ycombinator.com/item?id=16982986 https://news.ycombinator.com/item?id=16982986 - https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op... - https://vitess.io/docs/user-guides/schema-changes/ https://vitess.io/docs/user-guides/schema-changes/ Edited for formatting.
- dgdosen 5y agoDon't sugar coat it kid, tell us straight.
- RonaldK9 5y agoI once dig into MySQL source code (mysqlbinlog), but I could not understand how it can get compiled. Later, I found the answer, they added lots #include "xxx.c" at the end of the file, from then, I don't regard the MySQL as a serious project.
- radu_floricica 5y agoI have a moderately large table (hundreds of millions of records) with very simple structure but lots of columns and keys. This post is a huge "aha!" moment for me - I keep struggling with how the optimizer is choosing which index to use. It keeps making stupid mistakes and behaves differently from instance to instance - I still have a staging server where I just have to avoid an admin interface because it takes minutes to load, and I haven't invested more than a few hours to debug because it's not production. I have pieces of half commented code where I do the optimizer's job and try to guess which index is best and chose it manually. Lately I've been considering doing an "explain" first and based on that tweak the query. So yeah - I love MySQL, and I don't think I'll be able to invest the time to switch, but at least now everybody can see the emperor is naked and it wasn't just them. The optimizer sucks, including in MySQL 8.
- plasma 5y agoYou could perhaps fix it by trying to use some index hints in the query to get a bit more consistency, see https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
- radu_floricica 5y agoYeah, that's what I mean by "trying to do the optimizer's job". But this is meant for edge cases - doing this fully would mean starting to rewrite the optimizer in the app. This is where using the "explain" might help - if it's choosing what seems like a sane index and has a limited range of rows, I send the query "as is". If it fucks up and does a table scan, I'll force a compromise index.
- throwdbaaway 5y ago> Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. https://dom.as/2015/07/30/on-order-by-optimization/ https://dom.as/2015/07/30/on-order-by-optimization/ - Wisdom from the guy who managed to get MySQL to work at facebook scale.
- etaioinshrdlu 5y agoWhen should I consider migrating a decent size application from MySQL to postgres, if the product is stable and I don't urgently need to switch? I'd much like to be able to use transactional DDL, and improved general performance (parallel query, etc).
- emodendroket 5y agoOne thing I really appreciate about psql is it has probably the best docs of any product I've used.
- dikaio 5y agoI will now switch to Postgres.
- ttfkam 5y agoWelcome. Be sure to try out the transactional DDL, actual boolean and array types, functional and partial indexes, and other such features that required jumping through hoops in MySQL-land. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html In fact, if you've got the time, I'd highly recommend skimming through the excellent manual. You rarely know what you're missing until it's right in front of you. https://www.postgresql.org/docs/current/index.html https://www.postgresql.org/docs/current/index.html
- pm90 5y ago> In the end, there's just not enough resources that I could see it turn into a competitive product, no matter how internal company communications tried to spin that Oracle is filled with geniuses and WE ARE WINNING IN THE CLOUD. It’s refreshing to know that tier 2 cloud companies (Oracle, IBM etc) all have similar internal perspectives, ie leadership insisting that they’re making amazing progress in the cloud while their market share either stagnates or reduces.
- scrubs 5y agoThis like in the Seinfeld episode when George complains: what another gift? Just because he's moving (into a new apartment) he gets a gift? Birthday, this, that, the other other it never ends. Same: why do I care somebody quit/fired/whatever? Why do we gotta know about and it get involved? My large employer has a certain low, background level of turnover. Work there long enough and this will add up. A dear friend helps run a company where restructuring is going on with mid-high level people get cleaned out. This stuff is all over the place. Look, if you failed to have forethought to arrange for a new position ahead of time, failed to see this event coming, or don't have the contacts for a new position without blabbering on about publicly ... just come out and say: I want a position. I'm shopping. My CSV is <url-here>. Linked-in is nice I hear for things like that. There's a weird signaling of virtuosity or humble-bragging I'm sick of. Absolutely Fabulous had a nice line on this: you get your dry-cleaning back and it's a revolution. This was said sarcastically (because no it's not) by Pats ... because doing/improving things at work while important is not comparable to the rhetoric that currently passes for normal.
- TeeMassive 5y agoI remember a huge Reddit thread about a guy who used to work at Oracle. I can't find it anymore, it probably got deleted as people are leaving the Reddit ship but I digress. I found this on HN making similar points and reflects what OP was saying: https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941
- eyerollamazing 5y agoAuto vacuum doesn't work, vacuum will double your db size, upgrading requires reading the entire changelog and manual intervention, but yeah otherwise I'm sure postgres is just super amazing right?
- abdel_nasser 5y agowait, does mariadb randomly lose data? if youre writing a message service, is it bad practice to store each message individually, one row per message? or clump them together?
- pqyzwbq 5y agoFor us (we build business software distributed to customers), the License is the key factor. MySQL/MariaDB is GPL, and even worse, the JDBC driver is also GPL (which makes any business software built on MySQL jdbc driver violate the GPL license). While postgres's license is more permissive.
- pugz 5y agoJust to clarify for others (you probably already know this), the GPL is only an issue if you distribute the binaries for your code. If it's e.g. a typical SaaS then the GPL isn't nearly as scary. The AGPL is problematic in that case though.
- pqyzwbq 5y agoYes, you're right. I just changed my prev comment to add my usage case to make it clear. Thanks.
- jdthedisciple 5y agoMeans I can use MySQL for my typical SaaS product no problem?
- tetha 5y agoNot a licensing lawyer. But with a system like MySQL, which is installed as a service (opposed to a library you link with, like the MySQL Connector for java), most of the GPL triggers if you distribute a modified version to a third party. Key words there are: Distribute - an employee of your company gives the modified binary to a customer; modified - it's not stock mysql, but you've patched something inside it; and third party - that's some customer outside of your own company or legal entity. If you distribute an unmodified mysql to your customers, that's fine. If you keep a modified mysql running in your infrastructure only without giving anyone outside your company / legal entity access to the binaries, you're legally fine, but a bit of a jerk (this lead to the whole MongoDB licensing fun against SaaS vendors). Only if you patch MySQL and distribute it, you have to make your changes available. This makes GPL licensed systems you don't link any code against fairly safe to use.
- throwaway47292 5y agowhoa, horrible goodbye i hate mysql as well, but it almost runs the world, and onboarded a whole generation of young coders. postgres is not panacea (especially on RDS), i might even say we(my team) have more outages because of it than if we were using mysql (but thats just my gut feeling) the comment about how the team was so oblivious to "genuinely believe in mysql" was in very poor taste. I hope I misread the spirit of the message.
- marcodiego 5y agoComplete outsider view: > internal company communications tried to spin that Oracle is filled with geniuses and WE ARE WINNING IN THE CLOUD. From wikipedia about mariaDB: > A group of investment companies led by Intel has invested $20 million in SkySQL. The European Investment Bank funded MariaDB with €25 million in 2017. Alibaba led a $27M investment into MariaDB in 2017. From wikipedia about MySQL: > MySQL is also used by many popular websites, including Facebook, Flickr, MediaWiki, Twitter, and YouTube. They certainly have their place. I'm sure some of these companies have considered Postgres.
- redleader55 5y agoWhat the Mysql folks never say is that these companies never run the off-the-shelf version, but instead have built engines, tooling and expertise that allow them to prevent developers from being too close to the real Mysql.
- mdasen 5y agoOne thing I'd note is that a lot of these companies made the decision years ago. MySQL's replication story has been good for a lot longer. It wasn't until September 2010 when PostgreSQL landed replication (and you probably didn't want to adopt the first version). If you were looking to create a highly available service like Facebook, Flickr, Twitter, or YouTube before 2010, you were probably looking at MySQL. All those companies were huge before 2010. That was one of MySQL's killer features. No matter how bad any other part of MySQL might be, it did replication. PostgreSQL had some third-party add-ons to do replication, but it was hard, slow, and reasonably easy to do wrong. Some of them were downright bad ideas where they'd just proxy your request to two independent PostgreSQL instances and hope that nothing went wrong, others used triggers on the tables. Literally, the Postgres core team said this about PostgreSQL: "Users who might consider PostgreSQL are choosing other database systems because our existing replication options are too complex to install and use for simple cases." No, those companies wouldn't have considered PostgreSQL and once you get to a certain size, things tend to stick around. That said, many of these companies aren't using MySQL for a lot of new stuff. YouTube developed Vitess to handle some of their MySQL problems, but from what I've heard they've moved off MySQL since then (correct me if I'm wrong). Twitter has its Manhattan database. Facebook has gone through many databases. MediaWiki is a project that people are meant to be able to run on shared hosts and that means PHP/MySQL. Flickr isn't really a company that has done a lot post-2010. That doesn't mean it's a bad site, but it doesn't seem to be making a lot of new stuff. Decisions have context. Without the context, it's easy to come to the wrong conclusion. I don't hate MySQL and PostgreSQL has its problems. I think MySQL's strengths are generally in its current install base and current compatibility. Lots of things work with MySQL. Vitess isn't perfect, but it is a nice project for scaling a relational database. PostgreSQL doesn't have a Vitess. Likewise, many things already work with MySQL like MediaWiki and many things speak the MySQL protocol. However, that's starting to shift. I think we're seeing more things adopt PostgreSQL compatibility. Google's Spanner now has a PostgreSQL layer. RedShift, CockroachDB, and others are going for PostgreSQL compatibility. The thing is that ecosystems take a long time to shift. If it were 2004-2006 when Facebook, YouTube, and Twitter were created, I'd definitely have grabbed MySQL. You need good replication. PostgreSQL wasn't even talking about bringing replication into core back then, never mind having something available. Times change and software changes.
- numlock86 5y ago> MySQL is a pretty poor database, and you should strongly consider using Postgres instead. As someone who has been a long time MySQL and MariaDB "fanboy" I couldn't agree more. A decade ago I switched to Postgres and I was hooked after the first 30 minutes already. There simply is no way back. I check back on MySQL/MariaDB every now and then just out of curiosity and it still gives me the shivers.
- jmnicolas 5y ago> MySQL is a pretty poor database, and you should strongly consider using Postgres instead. It's not like it's an industry secret either: when I started my CS training in 2007, the first course was about relational databases and one of the first things the teacher told us was that MySQL was pretty sucky.
- leptoniscool 5y agoRecently used MongoDB Atlas and it's way better than MySQL in a lot of areas.
- sidcool 5y agoNot sure if that's the right comparison, sure both are DBs, but very different paradigms.
- anyfactor 5y agoFor the amateurs like me, remember this is a personal opinion. If you haven't heard of this guy or familiar with his DB usecase (which I am not), you should be smart enough to know not get on the bandwagon. People who are religious about stack either have been working with it for ages and testing it to its limits or are just writing SEO blogs. Flow my stupid plan if you want to really want to get preachy about DB. I started with CSV, then SQLite3 then I stopped. I am familiar with the syntax and basic workflow of MySQL and PostGreSQL just so I can get a job. I still use SQLite3 because it works for me period.
- sorry_outta_gas 5y agoPostgres is an odd choice now that blockchain services are now mature
- DeathArrow 5y ago>MySQL is a pretty poor database, and you should strongly consider using Postgres instead. Why is MySQL poor and why is SQL better? And for which scenarios? MySQL being poor also means MariaDB being poor.
- ttfkam 5y agoBear in mind that my first experiences with MySQL are 20 years old now, so I freely admit that some of my disdain stems from holding a grudge both toward MySQL AB, the PHP dev team, and hundreds of bad tutorials leading to SQL injection attacks. I still remember vividly when this was 100% relevant. https://sql-info.de/mysql/gotchas.html https://sql-info.de/mysql/gotchas.html Over the years, MySQL devs claimed that foreign keys were mostly superfluous, silently truncating input without warning was normal, implicit lossy data type conversions were acceptable, and quoting numeric values was fine. Don't get me started on weird default character sets or the variously confusing multibyte UTF-8 choices and interactions. MySQL would parse CHECK constraints but not actually enforce them until very recently (version 8 point something). Let me repeat: MySQL would accept the column syntax CHECK (foo BETWEEN 1 AND 10) but would happily accept -306 without so much as a warning, assuming your code was even checking warnings. Most database engines consider saving/retrieving your data to be a strict contract of sorts. MySQL YOLOs it far too often for my taste. Even now, DDL operations are not transaction safe. Got two CREATE TABLE statements, three ALTER TABLE statements, and a CREATE INDEX? Maybe you have an INSERT right after a CREATE TABLE because it's a lookup table. If any part fails due to whatever reason, you are now in an intermediate state. One table and a new column might be there, but not the rest. How do you roll back from that? You don't. You figure out where it failed and try to re-run the commands that didn't go through. Hopefully it didn't bork a running app. But here's a laundry list of features MySQL does(n't) support. Imagine trying to learn English but the teacher uses a regional patois and limits you to a small subset of a normal conversational vocabulary. You wouldn't know any different as a learner until you met someone who spoke one of the major dialects like General American, Received Pronunciation, or Australian English. Even though the latter three vary quite a bit, it is a far cry from the dialect spoken like this: https://youtu.be/0pBOLdZZT6s?t=120 https://youtu.be/0pBOLdZZT6s?t=120 To get a sense of what I'm talking about, compare Oracle, MS SQL, Postgres, and even SQLite to what MySQL and MariaDB have to offer. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html If you've got a particular problem that MySQL happens to solve, good on you. Solving the problem at hand is the most important thing. But I wouldn't count on it as your go-to option. Unless you've got a good and specific reason to use it, I'd steer clear.
- lkrubner 5y agoI got into programming in 2000 and right when I did Tim Perdue wrote a very important article comparing MySQL and Postgres. He concluded that Postgres was the better database. [edit] Thanks to DocTomoe for finding this: https://phpbuilder.com/mysql-and-postgresql-compared/ https://phpbuilder.com/mysql-and-postgresql-compared/ Some of the discussion of the article survives: https://www.postgresql.org/message-id/39635C4A.1A4486D9%40alumni.caltech.edu https://www.postgresql.org/message-id/39635C4A.1A4486D9%40al... The article was a very big deal at the time, because it was so well researched and so devastating for MySQL. I figured that article was the death knell for MySQL, but then MySQL surprised me by just going and going and going. It was helped along back then by having a very close relationship with the PHP language. Nowadays, you can use any database you want with PHP, but in 2000 PHP was very much biased in favor of MySQL, and every article written about PHP was written with the assumption you'd be using MySQL as the database. I suspect that MySQL would have died except for the massive life support it got from PHP during those years. The moral I take from all of this is that sometimes you can have two technologies, and one is clearly better, yet the one that is clearly better can remain under-utilized for 21 straight years. I know a bunch of HN people will now show up and defend MySQL, or defend a lazy style of programming that accepts defaults even when that means using a poor cousin of something good. But we should stop for a moment and really think about the implications of this. Because it really is remarkable that people have known of the superiority of Postgres for 21 years and yet people still use MySQL.
- tomstuart 5y agoYou might find this (from 1989) interesting: https://dreamsongs.com/RiseOfWorseIsBetter.html https://dreamsongs.com/RiseOfWorseIsBetter.html
- lkrubner 5y agoRight, it's important to keep "worse is better" in mind, but also keep in mind that the philosophy fades in importance the longer that a technology has been around. For instance: "C is a programming language designed for writing Unix, and it was designed using the New Jersey approach. C is therefore a language for which it is easy to write a decent compiler" Is that still true? Over time, people have wanted to fix the flaws in C, so they have added features, and nowadays writing a compiler for C is less easy. And there are other ways to measure the declining value of "worse is better." Many people would now argue that memory safety is worth the extra effort. Many would say the world would be a better place if C was banned and everyone switched to something like Rust. C continues to lose market share to those languages that guarantee memory safety, and yet C never fully dies, which is interesting. Over time, the tax you must pay for the "worse" begins to cost more than what you gain from the simplicity. My point, above, was how long this can take. We think of the tech industry as fast moving, and yet many core technologies have had obvious flaws for 30 or 40 years, and yet little action is taken to move to better technologies.
- willvarfar 5y agoFrom having fought it as a savvy user, I completely agree with the article about the poor MySQL query planner. I don't have the under-the-hood insights of the poster, obviously, but everything he says jives with how it felt as a user. Of course the planner is just part of a database, and I have some kind words to say about the other technically-impressive bits of MySQL: A lot of MySQL users are websites and things with pretty CRUD access patterns. That wasn't me. My history is using it for high-throughput real-time batching and fancy buzzword stuff at reasonably massive scale (big distributed teleco systems), which is where a lot of expensive choices were pitched. Doing teleco systems with MySQL was staggeringly cheaper and actually quite cheerful and, for all the times I swore at it, I'm actually still a fan. I went with MySQL for advanced features that, at the time, Postgres was way behind on. MySQL had lots of storage engine choices (including TokuDB, which changed everything for my use-cases) and upserts and compression and things that put it way ahead of Postgres. Of course MySQL had lots of warts too. The query planner was completely poo, but 99% of uses are simple things that it can handle well, and the most critical times it gets things wrong you end up annotating the sql to force indices and do the planner's job for it etc. Of course, nowadays, Postgres is reaching parity on these things too (except, perhaps, compression. My understanding is that Postgres is way behind on decent in-engine page-based compression. It will presumably get something decent eventually.)
- barrkel 5y agoPostgres still needs tweaking for getting the query planner to do the right thing, and you can't easily tell it what to do. I ended up proxying my queries with a preprocessor that uses special comment syntax - e.g. `-- $disable_seqscan` - which wraps query execution in sets of enable_seqscan off and on again, to force PG to use the index. All databases can have performance that falls off a cliff when changing statistics make them choose a less optimal join order (join order is normally the biggest thing that affects performance), but PG is particular in not having much flexibility to lock in or strongly hint the plan. MySQL, on the other hand, is predictably bad and has STRAIGHT_JOIN and other friends which make things much easier to tweak.
- tpetry 5y ago
- Rafuino 5y agoWay to burn bridges on the way out. I see no benefit to doing that when you're just going over to work on Chrome malarkey and get more t-shirts
- scotty79 5y ago> But it doesn't really explain why I did go looking for that somewhere else in the first place. I don't have understand why people think they need to provide a reason. They are just part of economy and should constantly look for better opportunities for themselves because that's what drives the economy towards more efficient state where resources are better utilized. Not to mention they themselves have just one life and have full moral right to live it the best way they can. They don't owe anyone anything they didn't promise and employment is not a promise of dedicating your whole life to a project or an employer.
- jdthedisciple 5y agoWhenever I come across these types of almost-rants, I never know: Is he talking about some specific use cases in which Postgres completely dominates MySQL? Or is it that for literally every CRUD call I do in my app I'm having my users wait a pointless 200 ms when Postgres would be doing the same in 20 ms ... What's the deal? Is the difference that drastic and relevant that I should really switch immediately even for ongoing projects, never to look back on MySQL again ?
- XCabbage 5y agoHaving used both MySQL and PostgreSQL enough to have been annoyed by features of both of them and fall into a bunch of their traps, I feel inclined to point out a few ways in which MySQL is better, most of which relate to the initial learning curve. 1. MySQL has the better CLI tool. psql is full of minor annoyances, like its cryptic and unmemorable backslash commands like \d for schema inspection, or having a pager turned on by default for query results that are more than one screen length. 2. Postgres has a deeply unintuitive concept of "USER"s and "ROLE"s where actually those are the same thing, and commands like CREATE USER and CREATE ROLE are thus basically synonymous (albeit with slight differences in default values). Worse, lots of the docs pages relating to specific commands don't highlight this fact at all. A majority of web devs I've talked to about Postgres have at some point complained about how they don't understand the permissions system or how ROLEs work. 3. While MySQL enforces that a foreign key from column A -> column B requires an index on both A and B, Postgres only requires a foreign key on column B. Maybe this freedom is nice if you know what you're doing, but it's a major footgun otherwise, since it means that deleting or updating rows in B has a time cost that scales linearly with the number of rows in the table A belongs to. 4. MySQL has a handy ON UPDATE current_timestamp shorthand for creating updated_at timestamp columns, which Postgres lacks, requiring you to use a trigger to implement such a column or move the logic to your application layer. 5. MySQL's "online DDL" features allow making lots of kinds of schema changes without ever holding a lock on the table being updated. In Postgres, by contrast, any schema change, even adding an index with the CONCURRENTLY option, needs to at least momentarily hold a totally exclusive lock that blocks all writes and reads on the table. Worse, as soon as an ALTER TABLE statement starts waiting for that lock, it blocks all new reads and writes against the table. This makes all schema changes in Postgres much more dangerous to the naive; even one that's theoretically able to happen concurrently with queries will hang your application if there's already a long-running query going against the table to be modified. It also means that at scale you need to roll a bunch of your own tooling to apply schema migrations safely, where you run them from a connection with a very short lock_timeout and wrap them in some code that retries on failure to acquire the lock. I don't remember any of this crap being necessary in MySQL. Maybe Postgres is still better; in particular, maybe it's more performant in a way that outweighs all these nuisances. I don't really know because I've never done any head-to-head comparison to see how the performance of the two would differ in a given scenario, and so the performance differences aren't something I've had a chance to witness as an ordinary dev using both databases. But I just want to make clear that there absolutely is another side to the story!
- INTPenis 5y agoI only have the untrained DBA perspective coming from building and hosting MySQL, Maria DB and postgres clusters over the last 14 years and I feel that Galera clustering feels a lot better than anything I've seen in postgres. Agreed that postgres has amazing performance. I was actually an early convert to postgres back in 2004 because my boss and coworker at the time saved entire businesses by migrating them to postgres when they had enough of trying to scale MySQL with more hardware vertically. But in the later decade when I myself have used clustering to scale I've found that Galera feels less like a tacked on afterthought than anything I've seen in the postgres world. It's just a lot more integrated than pgoool and watchdog. I know there are new solutions now that run the postgres clusters inside kubernetes that I haven't tried yet. But that isn't more integration, that's just more 3rd party abstraction. Edit: I realize after I wrote this how strange it must seem to a developer. Because galera/wsrep is actually a 3rd party replication product "tacked on" to MariaDB. While postgres replication, afaik, is written into their mainline code. I guess my gripe wasn't about the replication but rather the "clustering" around it, like maxscale/haproxy/pgpool and so forth. And in that sense they seem pretty equal, I just chose to use pgpool for my postgres clusters and that is definitely a hacky, scripty mess compared to HAproxy or Maxscale.
- mst 5y agoI do significantly more pg administration than mysqlish administration. Galera I gaze upon with envy, no question.
- bayesian_horse 5y agoBad code rarely improves, for all sorts of reasons. Refactoring is all well and good, but its hard to get someone to foot the bill for code that (maybe) works just as well and just looks a bit nicer.
- dikei 5y agoIn my opinion, both Postgres and MySQL have their places. While I chose Postgres for most of my projects, some 3rd party software just work better with MySQL because they are written for MySQL first, then ported to Postgres. For example, Hive Metastore still experiences some edge-case bugs when running with Postgres. I would not choose MariaDB for any new projects though, since it's no longer 100% compatible with MySQL, yet I don't see it bringing any game-changing advantage.
- znpy 5y agoWe use mysql in prod at work and frankly the reason I didn't bother bringing up the mysql vs postgresql debate is simply high availability. We have a multi-master mysql database based on percona's pxc. And it works beautifully... sometimes a node crashes and it's no big deal. Meanwhile in pgland it's quite a landmine of solutions, each of which seems to be studied accurately. And there is a constellation of other solutions (extensions or posygresql distributions) that maybe fit your use case, maube don't.
- ttfkam 5y agoPerhaps you should check out YugabyteDB https://www.yugabyte.com/ https://www.yugabyte.com/ or CockroachDB https://www.cockroachlabs.com/ https://www.cockroachlabs.com/
- znpy 5y agoI'll be honest, CockroachDB looks fishy. https://www.cockroachlabs.com/docs/stable/learn-cockroachdb-sql.html CockroachDB SQL? Isn't it supposed to be 100% PostgreSQL compatible? https://www.cockroachlabs.com/pricing/ CockroachDB Core - Open-source with foundational features. Uff... Let's go give a look at the licenses: https://github.com/cockroachdb/cockroach/blob/master/LICENSE https://github.com/cockroachdb/cockroach/blob/master/LICENSE So many licenses, including the Business Source License which is non-free license. Also there's this CockroachDB Community License (CCL), which I guess is a made-up license that's going to include some whatever terms. I might still accept this mix of licenses however I can't find the definition of "foundational features" -- what do I "lose" by using the OSS version instead of one of their managed offerings? From a quick overview, it's soft-no from me (in the sense that it probably wouldn't be my first choice).
- maxpert 5y ago> MySQL is a pretty poor database, and you should strongly consider using Postgres instead. Holy crap! I moved away from to Postgres way back in 2012 just for the features (at cost of connection complexity), but this is a plain blow to MySQL fan boys!
- elktea 5y agoas we are all discussing the pros and cons of various DBs, my favourite MySQL feature has to be the pluggable storage engines. Being able to support both InnoDB and RocksDB backed tables in the same database is wonderful. Just today I made use of the MyRocks storage engine's handy TTL [1] feature instead of a tedious delete & optimise scheduled job [1]https://github.com/facebook/mysql-5.6/wiki/Time-to-Live-%28TTL%29 https://github.com/facebook/mysql-5.6/wiki/Time-to-Live-%28T...
- mst 5y agoThe MySQL storage engine API system is both a massive enabler or really cool stuff and a big restriction on how the optimiser can work. Fascinating trade-off from which many things both good and bad spring.
- fasteo 5y ago> MySQL is a pretty poor database, and you should strongly consider using Postgres instead. This is an insider view, meaning that he is probably addressing things like code quality, compile/run/debug workflow, technical design decisions and so on. As a 20 year MySQL user - starting with version 3.23 if my memory serves me well - and after billions upon billions of inserted/updated/deleted/queried rows, MySQL is not a pretty poor database by any measure. It has served me well: Good performance, near zero maintenance, very few crashes, and a couple of data corruption that resulted in data loss after power outage.
- doctor_eval 5y agoThat's the whole thing with the difference between so-called "ACID" databases and MySQL. Corrupted data after a power outage just shouldn't happen. It's one of the things database engines are supposed to protect us against. In 30 years, it's never happened to me with Postgres or the other two database engines I've used professionally. If it did happen, then most people who know about databases would say "that's a pretty poor database".
- willvarfar 5y agoMySQL is, by default, ACID. There are storage engines that aren't, but they are rare and not default choices.
- EdwardDiego 5y agoMyISAM used to be the default for a long while. I made a bit of extra money when younger tidying up MySQL DBs that were in inconsistent states thanks to MyISAM not enforcing FKs.
- hu3 5y agoInnoDB has been the default for over a decade.
- 5y ago
- hardlianotion 5y agoI used to be pretty scornful of badly put together software, for various definitions of badly put together. There are flavours of usability, but if a thing is around for a long time and manages to attract a community, and that community manages to build useful things around it, that is something to respect and admire as well. MySQL works great for a range of use cases and is easier to use for many simpler use cases. Most folk won't need any extra power offered by Postgres, and won't be encumbered by having to tinker with its implementation.
- justshowpost 5y agoI'd like to grin at the another grimace of corporate world: Oracle also sells MySQL.
- deleted 5y ago[deleted]
- deepstack 5y agoThis is pretty much the same debate in linux vs freebsd back in early 2000s. Better code quality doesn't get the most recognition. Usually the devs just go with what's hip. And MySQL just sounds better on the marketing end (just because of it's name) than postgres (I mean who can pronounce the name).
- tkiolp4 5y agoFor a Saas maintained by one person, would you recommend MySQL or Postgres? I don’t know yet the volume of users or connections I will receive (perhaps zero?), but I do like to keep things simple (this pgbouncer thing one may need with postgres makes me feel uncomfortable).
- funcDropShadow 5y agoI would use always start with Postgres, because of its vast flexibility and extremely solid feature set, not to forget the excellent docomentation. I'v used Postgres in numerous projects and never had the need for pg_bouncer. Although I am sure there are some situation where it might be beneficial to use it. E.g. on the JVM it is best practice since 20 years to always use a connection pool in front of all JDBC drivers. IMHO transactional DDL is the killer feature for a fast moving project with customers.
- mst 5y agoPossibly heretical view: SQLite (with foreign keys) and WAL mode for concurrent writers and filesystem snapshots for backup. Do that until you can't, that way you'll actually have some idea where your database is going before you pick an actual server.
- fraktl 5y ago- Works 5 years on something he hates - No proof (links, anything) that prove what he's stating - Quits and supports the other vendor, knowing there's internet quarrel between <insert-db-name> vs <insert-other-db-name>, nice going there. Real grown up. - Leaves to work for largest spyware company on the planet This sounds like one of those people everyone on HN say to avoid in job environment.
- ttfkam 5y agoPretty sure most folks with relation database experience outside of MySQL already knew. It's just jarring to hear it from someone who was "on the inside". MySQL is a 26 year old database engine. 26 years. MySQL (not MariaDB) started enforcing CHECK constraints only 2.5 years ago in v8.0.16. Before that it would parse them (to make migrations from other DBs easier) but not actually use them. Or the fact that DDL changes weren't transactional, so a goof up halfway through leaves your schema in an indeterminate state. How making a temporary table for intermediate transaction state/materialization? (Since after all MySQL doesn't support materialized views.) Oh! You wanted to use that temp table more than once in the same query through joins? Too bad. So sad. One use per, sadly. Just create a temp_stuff_2 table and re-run the original query. MariaDB finally fixed that 5 years ago, but still an issue in MySQL today. His comments about it not being a state-of-the-art product are well founded and well publicized. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html If MySQL works for you, use it. But I compare it to something like the original Bourne Shell. It works. It's seemingly everywhere. If all you need is something simple, it can work well. And only folks who haven't used anything else ever think it's anything close to the best out there.
- ttfkam 5y agoI can't help but think most folks you use MySQL or MariaDB have never seen feature comparison charts with other database engines. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html There is an awful lot of red in their ledger compared to the others. SQLite is surprising good considering its smaller concurrency scope for use.
- trwhite 5y agoAs someone who isn't remotely interested in the database internals (but open to to being), why should I switch from MariaDB/MySQL (which I've been using for 7 years and never had any major issues with, beyond ONLY_FULL_GROUP_BY being a slight pain) to something else?
- mst 5y agoThere's so many little things that add up that Just Work in terms of SQL usage under pg and are a pain under mysql. A quick example is array types plus rowtypes (plus postgres having auto-group-by but -only- if you GROUP BY a pk, so it's predictable) letting you do something like this: SELECT author.*, json_agg(posts) FROM users author JOIN posts ON author.id = posts.author_id GROUP BY author.id; and you'll get back a json array of the post row objects in your application so you get one-row-per-author-row in your query results but still fetch your 1-to-N relationship efficiently (I used JOIN rather than LEFT JOIN to get only people who'd actually authored a post, note, that was a choice rather than a typo). Note, yes, there's no reason you can't get the same result without the json_agg and with a bit of code to collapse posts onto the right author as you page through the resultset, but the thing I'm trying to gesture at is that lots and lots of little quality of life improvements add up in a way you don't necessarily even notice that much until you've mostly only been working on pg projects for a couple of years and then try and do the now-seems-obvious thing in something else. The people complaining about the steep operations learning curve aren't at all wrong though, lots of postgres tooling was written by people who assume you -want- to read the entire manual first and then configure things exactly how you want and even as that sort of person it still took me a while to get up to speed. So ... maybe you shouldn't want to switch, but the above is why, overall, I usually don't find myself wanting to switch -from- postgres to something else. (except when I realise we're literally just using the database as a key/value store with an SQL interface, because at that point, screw that, back as far as mysql 3.23 it was amazingly good at that and it's still fantastic at it so in that situation "don't be silly" applies)
- dimgl 5y agoWhile I like PG, I think this is such a specific use-case. And in general I've learned to avoid JSON columns in databases. I was doing some large data aggregation using PG and I was pretty disappointed with its performance. I didn't think about trying MySQL...
- AtNightWeCode 5y agoI mostly come across MySQL for simple web applications that need to store some data. For that it works fine and is cheaper than MSSQL for instance. Not really the prime use case for Postgres.
- __float 5y agoWhat makes Postgres unfit for that use? It doesn't have large licensing fees like SQL Server, and Postgres (just like MySQL) can run on a single node and handle small amounts of data just fine.
- mst 5y agoGetting to reasonably solid active/passive failover for relatively simple uses is substantially easier to learn from zero for mysql. And I say this as somebody who defaults to postgres or sqlite over mysql where possible these days just because I expect it to be less of a headache down the road.
- AtNightWeCode 5y agoThe knowledge about running MySQL in production seems to be more widespread. A lot of web-hosting companies offers it. MySQL is the default for some CMS systems and so on. Postgres is powerful and can be used for pretty much everything but it takes more knowledge.
- directionless 5y agoWhat about this does not feel like a postgres use case?
- donatj 5y agoIn my very humble and naive opinion, MySQL has the whole 'worse is better' thing going for it. Does less, does it maybe less than perfect, but it does it very quickly and reliably. We've personally scaled MySQL to many tens of thousands of concurrent users without too much trouble and without a dedicated person watching the database. That said, I've only really worked significantly with MSSQL and MySQL, so my points of comparison are based largely on what I've heard. The majority of my last 15 professional years have been spent with MySQL.
- tossaway9000 5y ago> MySQL has the whole 'worse is better' thing going for it. I've used both MySQL and PostgreSQL and I prefer MySQL, it seems to work and scale better "out of the box" and has a more comprehensible permission model than Postgres. I might be biased somewhat as I've used MySQL a lot longer and have even written plugins to interop with it but its still my number 2 go to (after SQLite)
- tabtab 5y agoIn general it seems MySql targets start-ups while PostgreSql targets more established companies and projects. It's not necessarily about good-vs-bad, but using the right tool for the job.
- vanusa 5y agoThat's a perfectly reasonable assessment. Also, it's certainly a dream product compared to anything on the market 20 years ago. So yeah, it's fair to say the original blog post could have been more nuanced in its holistic assessment of MySQL. But then again, you can tell he was worn down from his experience working there. The industry has a way of doing that to people.
- dijit 5y agoMySQL used to be the "has more features" camp, I'm not sure why you think it does less. There are many things that MySQL does that Postgres still can't do, like memory tables or pluggable storage engines. And for anecdatum: I've been serving a million people concurrently on PG without any handholding except the initial setup: but that tells you nothing about what we were actually doing with it.
- PeterZaitsev 5y agoI was with MySQL AB in its early days, It's codebase and behaviors was much worse than it is now. Yet we had a team of people who loved MySQL and was focused on making it better. I think this is unfortunate culture change in Oracle land - they hire mercenaries rather missionaries to work on the code, so such "I never loved it, but I worked on it for years" is not a surprise. MySQL, MariaDB, MongoDB, PostgreSQL all have skeletons in their closet. Yet if PostgreSQL developer would be leaving with similar attitude community would raise much more to defense
- ksec 5y agoGenerally speaking, HN is pretty much one sided with Postgres Vs MySQL. I can count with both hands amount of comments over the years who dare to post an alternative view. And Oracle ( MySQL ) is evil. So I am surprised this thread had more comments on reason to choose MySQL than everywhere else on HN combined. It is still a minority. But at least not as one sided as usual.
- indymike 5y ago> Generally speaking, HN is pretty much one sided with Postgres Vs MySQL. The community here is very pragmatic, and often is speaking through the lens of personal experience. More so than many other communities. > And Oracle ( MySQL ) is evil. I think they get about the right level of skepticism with respect to motive that every big tech player gets. MySQL has a complicated story, but honestly, it's a great product. > But at least not as one sided as usual. I really enjoy HN because over the years, I've found the community to be pragmatic. There are always responses that tend to favor dogma over practicality, but on the whole, the community really is great about sharing insight and opinion.
- tabtab 5y agoBasically it's saying the MySql code base is a mess. Maybe PostgreSql's codebase is also a mess. Without experience inside it, it's hard to really say. Anybody here worked at both for a good while? Most code bases are messes in the real world, I hate to tell ya.
- deleted 5y ago[deleted]
- bborud 5y agoI've seen a lot of negative reactions to this mike drop. I wouldn't judge him too harshly. He's actually a very good developer worth the benefit of the doubt.
- cryptica 5y agoI think MySQL's success comes down to developer-friendliness. I find Postgres a bit tricky to setup. For example, I don't like how Postgres authentication is intertwined with Linux user accounts by default; it causes more problems than it solves; especially for open source projects. But anyway, it's nice to read an honest piece like this once in a while. Many software projects these days are terrible (to be quite frank) and it's not pleasant to work on such projects as a developer once you understand that it is sub-par and that it will probably remain sub-par because your company doesn't have the talent or right incentives to attract or foster such talent. If you work at Oracle, it's better to do so as a lawyer, not a technologist. Oracle lawyers are world class cronies. Oracle doesn't even need developers; the letters written by their lawyers can negate the shortcomings of any of their code. Why even bother writing complex code to solve a technical problem when the lawyer can solve the same problem with a few paragraphs of English legalese?
- ttfkam 5y agoPostgres auth is intertwined? I mean, yeah, to create the first database and set of users, the local user must be in the postgres group. But after that (or if you edit the pg_hba.conf file)? https://www.postgresql.org/docs/current/auth-pg-hba-conf.html https://www.postgresql.org/docs/current/auth-pg-hba-conf.htm... It uses the current OS user as the default user to connect with. What else would you default to? But you can (and 99% of the time should) specify the role you're connecting with, which is a purely database-controlled role, not an OS username. Honestly I never thought MySQL's method was clearer, but I acknowledge that's subjective. That said, MySQL's solution for a VERY long time in its history was simply to default to superuser/root within the database until you lock it down. "Simple" turned out to be a security nightmare for a lot of folks who didn't realize what was happening, which was almost all new users. But that's mostly moot for most folks now. Managed database instances in Azure, GCloud, AWS, DigitalOcean, et al is rapidly becoming the norm (if it isn't already). In those environments, the connectivity options are really hard to distinguish between Postgres and MySQL.
- ivoras 5y agoIt looks like WordPress is the only reason I've installed MySQL at all in the past decade. It's like WP and MySQL are each other's "killer app" :D
- midom 5y ago¯\_(ツ)_/¯ optimizer guy says he did not do good job, shits on optimizer? There're many reasons to pick MySQL over PG at large scale deployments (economics, replication strategies, etc) - and the fact that some queries will run better on PG may not outweight those benefits. Don't get me wrong, I know many areas where MySQL sucks, and that is mostly in lacking execution strategies and optimizer problems. Indeed, in many of these ways MySQL is stuck years behind, but in other areas (MyRocks, modern hardware use, etc) it is far ahead of PG. The thing is, optimizing has costs, and bypassing those costs is useful, if you're looking at economics of your system, and storage engines that pay attention to data patterns are ahead of just naive heaps of data. It is very simple to diss MySQL when your business does not depend on managing petabytes of data. P.S. We've migrated a major social network from PG onto our MySQL-based platform and our MySQL DBAs didn't even notice. :-)